80

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)

S-Man
  • 22,521
  • 7
  • 40
  • 63
resting
  • 16,287
  • 16
  • 59
  • 90

3 Answers3

134

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

Ed C
  • 3
  • 1
  • 2
mechanical_meat
  • 163,903
  • 24
  • 228
  • 223
  • 65
    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. – Matthew Wood Jun 05 '12 at 14:19
73

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()
R13e
  • 1,026
  • 7
  • 12
3
select * from pg_get_keywords() where catdesc = 'reserved'
Minimul
  • 4,060
  • 2
  • 21
  • 18