sql - quote_ident() 不会为列名 "first"添加引号

Question

我需要正确地用双引号括起来的列名。 quote_ident()好像不行吗？

select 1 first; -- fails
select quote_ident('first'); -- produces first, not "first"

我可以使用什么命令来成功引用标识符。我正在尝试动态构建一个 SELECT声明:

SELECT 'select ' 
|| string_agg(
        case when udt_name in ('varchar', 'text')
            then 'left(' || quote_ident(column_name) || ', 65535) ' || quote_ident(column_name)
        else quote_ident(column_name)
        end, ',' order by ordinal_position) 
|| ' from "public"."MyTableName"'
FROM information_schema.columns c
join parse_ident('"public"."MyTableName"') t 
on t[1] = table_schema and t[2] = table_name

这是生成:

SELECT id, left(first, 65535) first from "public"."MyTableName";

这是因为首先作为列名需要用双引号括起来。

Answer 1

不要省略 AS列别名的关键字

SELECT id, left(first, 65535) first from "public"."MyTableName";

Which blows up because first as a column name needs to be wrapped in double quotes.

不完全是。它爆炸是因为你省略了关键字 AS 不应该省略的地方。
这有效:

SELECT 'select ' 
|| string_agg(
        case when udt_name in ('varchar', 'text')
            then 'left(' || quote_ident(column_name) || ', 65535) AS '  -- !!
              ||  quote_ident(column_name)
        else quote_ident(column_name)
        end, ', ' order by ordinal_position) 
|| ' from "public"."MyTableName"'
FROM information_schema.columns c
join parse_ident('"public"."MyTableName"') t 
on t[1] = table_schema and t[2] = table_name;

产生:

SELECT id, left(first, 65535) AS first from "public"."MyTableName";

依次按预期工作。
manual about "Omitting the AS Key Word" :

In the SQL standard, the optional key word AS can be omitted beforean output column name whenever the new column name is a valid columnname (that is, not the same as any reserved keyword). PostgreSQL isslightly more restrictive: AS is required if the new column namematches any keyword at all, reserved or not. Recommended practice isto use AS or double-quote output column names, to prevent any possibleconflict against future keyword additions.

可以省略关键字 AS用于表别名，但不适用于列别名。 first不是 reserved word在 Postgres 中。 (它曾经在古老的 SQL 标准 SQL-92 中被“保留”，但在标准 SQL 中也不再是。)准确地说，它是“非保留”*。 The manual :

Non-reserved key words only have a special meaning in particular contexts and can be used as identifiers in other contexts.

省略 AS使它成为这样一个背景。
quote_ident() 工作可靠。 The manual:

Returns the given string suitably quoted to be used as an identifierin an SQL statement string. Quotes are added only if necessary (i.e.,if the string contains non-identifier characters or would becase-folded). Embedded quotes are properly doubled.

format()带有说明符 %I做同样的事情。
保留字未提及，但无论如何都正确引用。准确的说: the SQL Key Words table的“PostgreSQL”一栏内所有标有“保留”或“(不能是函数或类型)”的关键词.
我将提交一个文档错误来添加它。
绝对确定: quote_all_identifiers
如果您想绝对确定并且不介意所有添加的噪音，您可以强制 Postgres 使用配置参数 quote_all_identifiers 引用所有标识符。 . The manual:

When the database generates SQL, force all identifiers to be quoted, even if they are not (currently) keywords.

这包括来自 quote_ident() 的输出和 format() .我会 不是 这样做，害怕所有增加的噪音。
您可以使用 SET LOCAL 在本地设置参数在同一个交易中。喜欢:

BEGIN;
SET LOCAL quote_all_identifiers = true;
SELECT ...
END;

快点
也就是说，我会使用 format()和 concat()并定位目录表 pg_attribute相反:更干净、更简单、更快。但不能移植到其他 RDBMS:

SELECT format('SELECT %s FROM %s;'
            , string_agg(CASE WHEN atttypid = ANY ('{text, bpchar, varchar}'::regtype[])
                              THEN concat('left(', col, ', 65535) AS ', col)
                              ELSE col END, ', ')
            , attrelid)
FROM  (
   SELECT attrelid::regclass, atttypid, quote_ident(attname) AS col
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = 'public."MyTableName"'::regclass  -- provide once, optionally schema-qualified
   AND    attnum > 0
   AND    NOT attisdropped
   ORDER  BY attnum
   ) sub
GROUP  BY attrelid;

产生:

SELECT id, left(first, 65535) AS first FROM "MyTableName";

分贝<> fiddle here
尤其， ...

...您只需要提供一次表名，可选择模式限定。

...如果该表不存在，查询会立即失败并显示有用的错误消息。

...输出表名称仅在必要时进行模式限定和双引号。

... 这也包括 character(N) (内部名称 bpchar)。

进一步阅读:

How to check if a table exists in a given schema

Truncating display by default in postgres psql select statements

PostgreSQL force upper case for all data

Check whether empty strings are present in character-type columns