Postgres table column name restrictions?

Question

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)

Cundiff

Answer 1

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

Prostatectomy

To paraphrase Yoda, "If quoted identifiers you use, forever will they dominate your destiny." Quoted identifiers are right pain to use, especially mixed with non-quoted. As the OP said, best to avoid them completely. – Timoshenko

Answer 2

In my company, I had to scan an entire database for reserved words. I solved the task with the help of

select * from pg_get_keywords()

Farahfarand

Answer 3

select * from pg_get_keywords() where catdesc = 'reserved'

This solution is culture dependent. It only works if your database is setup in english. If you use german locale the catdesc would be translated as well and give back no results. In order to get the correct information be prepared to change the word depending on your locale. For example: German users would have to use this query

select * from pg_get_keywords() where catdesc = 'reserviert'

Twyla

Better to use catcode = 'R' that works for all languages – Clarkia