Why is the “multiple default values” error when creating a postgresql table?

I am trying to create a table with this query:

CREATE TABLE "real_estate_static" (
    "uid" SERIAL NOT NULL DEFAULT NEXTVAL("real_estate_uid_seq") PRIMARY KEY,
    "source_id" JSON NOT NULL,
    "source_url" TEXT NOT NULL,
    "created_at" TIMESTAMP NULL DEFAULT NULL,
    "address" TEXT NOT NULL,
    "city" TEXT NOT NULL,
    "state" TEXT NOT NULL,
    "zip" INTEGER NULL DEFAULT NULL,
    "latitude" NUMERIC NULL DEFAULT NULL,
    "longitude" NUMERIC NULL DEFAULT NULL,
    "type" TEXT NOT NULL,
    "category" TEXT NOT NULL,
    "square_ft" NUMERIC NULL DEFAULT NULL,
    "acres" NUMERIC NULL DEFAULT NULL,
    "images" JSON NULL DEFAULT NULL
);

Error:

multiple default values specified for column "uid" of table "real_estate_static"

I thought that maybe a mistake due to the parameter NOT NULL, but removing it did not change anything. Most of the problems that I found on the Internet with such an error are due to Django, or other ORMs, but this is not my case, so I could not get around this.

Answer

serial is not a real type, but an alias which includes a default.

From the docs….

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

is equivalent to specifying:

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;

So all that nextval and not null is unnecessary. serial does it for you.

"uid" SERIAL PRIMARY KEY,