CODEWITHSUNDEEP
postgresqlpostgresql-9.1
tec
tec

Reputation: 1147

Why SERIAL is not working on this simple table in Postgres?

I'm using Postgres 9.1. and the auto_increment (serial) is not working. I've just found this about 'serial': https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL

CREATE TYPE FAMILY AS(
    id int,
    name VARCHAR(35),
    img_address VARCHAR(150));

CREATE TABLE FAMILIES of FAMILY(
    id SERIAL primary key NOT NULL,
    name NOT NULL
    );

ERROR:  syntax error at or near "SERIAL"
LINE 7:  id SERIAL primary key NOT NULL,
                         ^


********** Error **********

ERROR: syntax error at or near "SERIAL"
SQL state: 42601

Upvotes: 1

Views: 15598

Answers (1)

harmic
harmic

Reputation: 30587

When you create a table using the syntax:

CREATE TABLE xxx OF yyyy

you can add default values and constraints, but not alter or specify the type of the columns.

The type SERIAL is in effect a combination of a data type, NOT NULL constraint and default value specification. It is equivalent to:

integer NOT NULL DEFAULT nextval('tablename_colname_seq')

See: documentation for SERIAL

So, instead you would have to use:

CREATE SEQUENCE families_id_seq;

CREATE TABLE FAMILIES of FAMILY(
    id WITH OPTIONS NOT NULL DEFAULT nextval('families_id_seq'),
    name WITH OPTIONS NOT NULL
);

ALTER SEQUENCE families_id_seq OWNED BY FAMILIES.id;

You would have to create the sequence families_id_seq manually as well, as shown above.

Upvotes: 4

Related Questions