CODEWITHSUNDEEP
postgresqlauto-increment
ForestPotato
ForestPotato

Reputation: 1

PSQL throwing error: relation "serial" does not exist, when creating a table

It happens when i run this code:

CREATE TABLE distributors (
 did    integer PRIMARY KEY DEFAULT nextval('serial'),
 name   varchar(40) NOT NULL CHECK (name <> '')
);

I have tried remover the nextval('serial') but to no avail

Upvotes: 0

Views: 2040

Answers (2)

Adrian Klaver
Adrian Klaver

Reputation: 19665

You want to do this:

CREATE TABLE distributors (
 did    serial PRIMARY KEY DEFAULT,
 name   varchar(40) NOT NULL CHECK (name <> '')
);

The serial type is actually a macro. That per docs (Serial) does:

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Assuming you are on a recent version(10+) of Postgres generated always as identity(see Create Table) is the preferred alternative these days.

Upvotes: 2

Brandon Lee Detty
Brandon Lee Detty

Reputation: 284

nextval('someseries') relies on having an existing series. You can create that with:

CREATE SEQUENCE someseries;

When you removed the nextval, you probably still had the DEFAULT keyword there, which expects a value afterward to define what the default for the column is.

Upvotes: 1

Related Questions