Escaping keyword-like column names in Postgres

Question

If the column in Postgres' table has the name year, how should look INSERT query to set the value for that column?

E.g.: INSERT INTO table (id, name, year) VALUES ( ... ); gives an error near the year word.

Wyatan

Answer 1

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.

Selfassertion

Some warning: Without the quotes PostgreSQL folds all identifiers to lowercase. MyTable, myTable and mytable are just the same. With the quotes this folding is not done. So "MyTable" is no more the same as mytable. – Moller

Better yet, obstain from using reserved words or mixed case as identifiers and you will never have to use the double quotes or get strange error messages. – Valentinavalentine

I thought SELECT "year" would have returned the string year, but I guess that was a MySQL thing. – Flier

@djjeck: #1992814 – Selfassertion

Better not use reserved words at all. The trick here doesn't work with UPDATE table SET .. – Oneself

@ErwinBrandstetter Problem is when you work on an established project. – Selfsealing

@HoàngLong yes it does. update "user" set "password" = 'value...'; works perfectly fine... – Parnell

Mostly I got bumped with column name as timestamp, this helped a lot. – Electrolier

Answer 2

If you are not providing quotes in any Fields/Columns, It will be lowercased by Postgres by default. And Postgres will skip checking keyword when it comes to the column name.

In your case, I don't think it's mandatory to add quotes when it comes to the columns. But if you are using keywords (registered by Postgres) as the name of Table, Schema, Function or Trigger etc, you must have to use either double quotes, or you can specify schema name with dot concatenation.

Let's Suppose, order is the keyword registered by Postgres. And in some scenarios, you must have to use this keyword as a table name.

At that time, Postgres will allow you to create a table with keywords. That is the beauty of Postgres.

To access the order table, Either you have to use a Double quote or you can you schema name before table name.

E.G.

1.

select * from schema_name.order;

2.

select * from "order";

Likewise, you can use this type of combination. Hope this will help you.

Convertiplane

Answer 3

To be on the safe side: Always quote identifiers! For this you have to build the insert statement with delimited identifiers.

SQL 2003 specifies that a delimited identifier should be quoted with double quotes " and if a double quote occurs in the identifier, the double quote must be duplicated. See the BNF:

https://ronsavage.github.io/SQL/sql-2003-2.bnf.html#delimited%20identifier

This is Java code to quote the identifier:

static String delimited_identifier (String identifier)
{
  return "\"" + identifier.replaceAll ("\"", "\"\"") + "\"";
}

And this is the code to build the insert:

static String build_insert (String table, String[] columns)
{
  StringBuilder sql = new StringBuilder ();
  StringBuilder values = new StringBuilder ();

  sql.append ("INSERT INTO ");
  sql.append (delimited_identifier (table));
  sql.append (" (");
  int c = 0;
  if (columns.length > 0) {
    sql.append (delimited_identifier (columns[c]));
    values.append ("?");
  }
  for (++c; c < columns.length; c++) {
    sql.append (", ");
    sql.append (delimited_identifier (columns[c]));
    values.append (", ?");
  }
  sql.append (") VALUES (");
  sql.append (values.toString ());
  sql.append (")");

  return sql.toString ();
}

Example:

String sql = build_insert ("Person", new String[]{"First name", "Last name"});

Enscroll

@questionto42 There is no need to distinguish between two cases. Just quote every identifier. It is almost impossible to know all reserved words of all databases. And I am not sure what you mean by "inverted commas". The right characters to quote are "double quotes" ("). MySQL violates the SQL standard by the use of backquotes (`). My answer covers the correct way to quote. – Enscroll

@questionto42 I am not sure if I understand. You want, that I move the comment into the answer? – Enscroll

I would understand your answer much better when there was an introduction like "There is no need to distinguish between two cases. Just quote every identifier. It is almost impossible to know all reserved words of all databases." – Goodden

Throwing Java code at this question is not only not useful, it's terrifying... – Mccrory

I think adding some utility code (in whatever language) to aid in avoiding issues with the OPs question in hand is actually very useful. – Dramamine

However, I am not sure about quoting all identifiers in postgres schemas (e.g. a create table/create view script)... seems to me that is entering a world of pain... what I see is that if you use double quotes for this, then you can only access those schema objects using double quotes (and the exact same casing). This can be really painful e.g. you have code from 3rd parties that generates SQL and does not handle this correctly. I'd say in postgres 1. avoid keyword clashes for your custom identifiers 2. and don't use double quotes at all for your own data access code. – Dramamine

Even better - avoid PostGres – Dramamine

@Dramamine You can not avoid keyword clashes, because you are not in the position to reject new keywords. Whenever Postgesql decides to add a new keyword to the language, your application may crash, if you do not quote your own identifiers. Not quoting identifiers is Harakiri. It is just a stupid idea, but a kind of well received stupidity. – Enscroll