Don't omit AS key word for column aliases
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.
Not exactly. It blows up because you omitted the keyword AS where it should not be omitted.
This works:
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;
Produces:
SELECT id, left(first, 65535) AS first from "public"."MyTableName";
Which works as expected in turn.
The manual about "Omitting the AS Key Word":
In the SQL standard, the optional key word AS can be omitted before
an output column name whenever the new column name is a valid column
name (that is, not the same as any reserved keyword). PostgreSQL is
slightly more restrictive: AS is required if the new column name
matches any keyword at all, reserved or not. Recommended practice is
to use AS or double-quote output column names, to prevent any possible
conflict against future keyword additions.
It's OK to omit the keyword AS for table aliases, but not for column aliases.
first is not a reserved word in Postgres. (It used to be "reserved" in the ancient SQL standard SQL-92, but not any more in standard SQL, either.) It is "non-reserved"* to be precise. The manual:
Non-reserved key words only have a special meaning in particular contexts and can be used as identifiers in other contexts.
Omitting AS makes it just such a context.
quote_ident() works reliably. The manual:
Returns the given string suitably quoted to be used as an identifier
in an SQL statement string. Quotes are added only if necessary (i.e.,
if the string contains non-identifier characters or would be
case-folded). Embedded quotes are properly doubled.
format() with the specifier %I does the same.
Reserved words are not mentioned, but quoted properly regardless. To be precise: all key words marked "reserved" or "(cannot be function or type)" in the column "PostgreSQL" of the SQL Key Words table.
I'll file a documentation bug to add that.
If you want to be absolutely sure and don't mind all the added noise, you can force Postgres to quote all identifiers with the configuration parameter quote_all_identifiers. The manual:
When the database generates SQL, force all identifiers to be quoted, even if they are not (currently) keywords.
That includes output from quote_ident() and format(). I would not do that, dreading all the added noise.
You might set the parameter locally with SET LOCAL in the same transaction. Like:
BEGIN;
SET LOCAL quote_all_identifiers = true;
SELECT ...
END;
Faster
That said, I would use format() and concat() and target the catalog table pg_attribute instead: cleaner, simpler, faster. But not portable to other 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;
Produces:
SELECT id, left(first, 65535) AS first FROM "MyTableName";
db<>fiddle here
Notably, ...
- ... you only need to provide the table name once, optionally schema-qualified.
- ... if the table does not exist, the query fails immediately with a helpful error message.
- ... the output table name is only schema-qualified and double-quoted where necessary.
- ... this also covers
character(N) (internal name bpchar).
Further reading:
