Postgres table column name restrictions?

postgresql create-table
resting picture resting · Jun 5, 2012 · Viewed 69.9k times · Source

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)

Answer

mechanical_meat picture mechanical_meat · Jun 5, 2012

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

Related questions

PostgreSQL create table if not exists

In a MySQL script you can write: CREATE TABLE IF NOT EXISTS foo ...; ... other stuff ... and then you can run the script many times without re-creating the table. How do you do this in PostgreSQL?

Read More
create table in postgreSQL

I do not understand what is wrong with this query? Query tool does not want to create a table in PostgreSQL. CREATE TABLE article ( article_id bigint(20) NOT NULL auto_increment, article_name varchar(20) NOT NULL, article_desc text NOT …

Read More
PostgreSQL Error: Relation already exists

I am trying to create a table that was dropped previously. But when I do the CREATE TABLE A ... I am getting below error: Relation 'A' already exists. I verified doing SELECT * FROM A, but then I got another error: …

Read More