Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

postgresql - Postgres table column name restrictions?

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)

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...