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

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

CodePudding user response：

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.

CodePudding user response：

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.