Syntax error on PostgreSQL create table without double quotes [duplicate]

Question

Trying to create tables without double quoting the name is resulting in a syntax error, only quoting it works:

# CREATE TABLE user (
email TEXT,
first_name TEXT,
last_name TEXT,
password TEXT,
plan TEXT,
gender gender
);
ERROR:  syntax error at or near "user"
LINE 1: CREATE TABLE user (
                     ^
# CREATE TABLE "user" (
email TEXT,
first_name TEXT,
last_name TEXT,
password TEXT,
plan TEXT,
gender gender
);
CREATE TABLE
# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | user | table | postgres
(1 row)

This is on PosgreSQL 9.5.5 shipping with Ubuntu 16.10:

PostgreSQL 9.5.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.2.0-5ubuntu12) 6.2.0 20161005, 64-bit

I know it's a bad idea to quote names in PostgreSQL but I can't get around this, what's wrong?

suslov.nikita · Accepted Answer · 2016-12-08 18:36:08Z

4

user is a reserved word in many RDBMS including PostgreSQL, so you should either escape it with double quotes " or pick another one.

P.S. As this identifier is a table name, it might be a good idea to use users instead of user.

edited Dec 8, 2016 at 18:36

answered Dec 8, 2016 at 18:28

suslov.nikita

44.7k11 gold badges92 silver badges113 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Juan Carlos Oropeza Over a year ago

Usually you use plural in table names, so the table should be name users

Oxwivi Over a year ago

Ah, how couldn't I've thought of that, dammit all.

Oxwivi Over a year ago

I was trying to use Oracle's DBA naming standards. Looks like it won't work.

Juan Carlos Oropeza Over a year ago

btw isnt good idea have a type gender and call the fieldname gender

Oxwivi Over a year ago

@JuanCarlosOropeza, that's an enum.

|

Collectives™ on Stack Overflow

Syntax error on PostgreSQL create table without double quotes [duplicate]

1 Answer 1

10 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Linked

Related