How Can I Escape Keyword-Like Column Names (e.g., 'year') in PostgreSQL INSERT Statements?

Escape keyword-like column names in PostgreSQL

When creating queries in PostgreSQL, it may cause errors if you encounter column names like reserved keywords. For example, the "year" column.

Question

Trying to run an INSERT query using the "year" column results in an error like this:

<code class="language-sql">INSERT INTO table (id, name, year) VALUES ( ... );</code>

Solution

To resolve this issue, enclose the "year" column name in double quotes:

<code class="language-sql">INSERT INTO table (id, name, "year") VALUES ( ... );</code>

Explanation

Double quotes are used to define delimited identifiers, which prevent PostgreSQL from interpreting the enclosed text as a keyword. This allows you to use column names containing reserved words without triggering an error.

As stated in the PostgreSQL documentation:

"The second type of identifier is a delimited or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double quotes ("). Delimited identifiers are always identifiers, not keywords. Therefore, "select" can be used to refer to a column or table named "select", and unquoted select will be treated as a keyword..."

The above is the detailed content of How Can I Escape Keyword-Like Column Names (e.g., 'year') in PostgreSQL INSERT Statements?. For more information, please follow other related articles on the PHP Chinese website!