Postgres table column name restrictions?

create-table, postgresql

Solution

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

Problem

I did this in psql: ``` CREATE TABLE IF NOT EXISTS apiss (skey TEXT, time INTEGER, user TEXT, ip TEXT); ``` I get ``` ERROR: syntax error at or near "user" LINE 1: ...BLE IF NOT EXISTS apiss (skey TEXT, time INTEGER, user TEXT,... ``` I do: ``` CREATE TABLE IF NOT EXISTS apiss (skey TEXT, time INTEGER, userd TEXT, ip TEXT); ``` It works. Note the userd instead of user. Are there some restrictions on the column names that a table can have? (postgresql v9.1.3)

Original source