Error with auto_increment while conneted to Postgres via psql and puTTY

Question

I'm getting this error in puTTY. Not sure why, looks right to me ...

psql:pierre.sql:10: ERROR:  syntax error at or near "AUTO_INCREMENT"
LINE 2:  c_id  INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
                                ^
psql:pierre.sql:18: ERROR:  syntax error at or near "AUTO_INCREMENT"
LINE 2:  r_id  INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,

--DROP TABLE customer, reservation;
CREATE TABLE customer(
    c_id        INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c_ref       VARCHAR(30) NOT NULL,
    f_name      VARCHAR(30) NOT NULL,
    l_name      VARCHAR(30) NOT NULL,
    address     VARCHAR(100) NOT NULL,
    email       VARCHAR(100) NOT NULL,
    phone       VARCHAR(11) NOT NULL
);
CREATE TABLE reservation(
    r_id        INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c_id        VARCHAR(30) NOT NULL REFERENCES customer(c_id),
    book_date   DATE NOT NULL CHECK (book_date <= now()),
    s_time      DOUBLE NOT NULL,
    e_time      DOUBLE NOT NULL,
    amount      INTEGER NOT NULL
);

Any ideas why?

Answer 1

auto_increment looks like something you'd use with MySQL.

But, here, it seems you are using PostgreSQL.

According to the datatype serial section of the manual, postgresql's equivalent of auto_increment is serial or bigserial.

Quoting that page :

The data types serial and bigserial are not true types, but merely a notational convenience for setting up unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases).

Answer 2

In Postgres 10 or later consider an IDENTITY column:

CREATE TABLE customer(
    c_id        INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    ...

(PRIMARY KEY also makes it NOT NULL automatically.)

Details:

• Auto increment table column

In Postgres 9.6 or older consider a serial like Pascal already suggested.
Works in pg 10 or later, too, but IDENTITY is generally superior.