Escaping Keyword-Like Column Names in Postgres

Escaping keyword-like column names in Postgres

Simply enclose year in double quotes to stop it being interpreted as a keyword:

INSERT INTO table (id, name, "year") VALUES ( ... );

From the documentation:

There is a second kind of identifier: the delimited identifier or
quoted identifier. It is formed by enclosing an arbitrary sequence of
characters in double-quotes ("). A delimited identifier is always an
identifier, never a key word. So "select" could be used to refer to a
column or table named "select", whereas an unquoted select would be
taken as a key word and would therefore provoke a parse error when
used where a table or column name is expected.

PostgreSQL column name as keyword in two table

Use " to escape reserved keywords.

select * from table1 t1 
left join table2 t2 on t1.id=t2.t1_id 
where t1."desc"='something'

But don't forget to add the table name or alias if a column is named equal in 2 tables. The DB engine does not know which one to take if you don't specify the table.

Alternate Postgres syntax for escaping reserved words

There is no alternative to double quotes for quoting identifiers. You will have to pay the price for the bad design choice of choosing identifiers that are not standard compliant.

How to deal with SQL column names that look like SQL keywords?

Wrap the column name in brackets like so, from becomes [from].

select [from] from table;

It is also possible to use the following (useful when querying multiple tables):

select table.[from] from table;

Postgres table column name restrictions?

Here's a nice table of reserved words in PostgreSQL:

http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html

It is probably best to simply avoid using those words as table- or column-names.

An alternative, however, is to enclose the identifier in double-quotes, e.g.:

CREATE TABLE IF NOT EXISTS apiss (
    skey TEXT, 
    time INTEGER, 
    "user" TEXT, 
    ip TEXT);

Additionally, Postgres reserves system column names for internal use in every table:
"Every table has several system columns that are implicitly defined by the system. Therefore, these names cannot be used as names of user-defined columns."

https://www.postgresql.org/docs/current/ddl-system-columns.html

---

Related Topics