CODEWITHSUNDEEP
postgresql
herme 0
herme 0

Reputation: 972

postgresql 10 altering serial column error 42p01

I am facing an issue when correcting an existing table to use serial column on the primary key. In order to simulate the issue I created a new table:

CREATE TABLE markets."TestSequence" (
    "Id" integer NOT NULL,
    "Name" text COLLATE pg_catalog."default",
    CONSTRAINT "PK_TestSequence" PRIMARY KEY ("Id")
);

Then I ran the query that is causing problem:

ALTER TABLE markets."TestSequence" ALTER COLUMN "Id" TYPE integer;
ALTER TABLE markets."TestSequence" ALTER COLUMN "Id" SET NOT NULL;
CREATE SEQUENCE "TestSequence_Id_seq" AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE;
ALTER TABLE markets."TestSequence" ALTER COLUMN "Id" SET DEFAULT (nextval('"TestSequence_Id_seq"'));
ALTER SEQUENCE "TestSequence_Id_seq" OWNED BY "TestSequence"."Id";

I get the following error:

ERROR:  relation "TestSequence" does not exist
SQL state: 42P01

According to the doc OWNED BY does not take any schema prefix. So I tried to create the table without schema and it works fine.

CREATE TABLE "TestSequence" (
    "Id" integer NOT NULL,
    "Name" text COLLATE pg_catalog."default",
    CONSTRAINT "PK_TestSequence" PRIMARY KEY ("Id")
);

and run the corresponding alter queries:

ALTER TABLE "TestSequence" ALTER COLUMN "Id" TYPE integer;
ALTER TABLE "TestSequence" ALTER COLUMN "Id" SET NOT NULL;
CREATE SEQUENCE "TestSequence_Id_seq" AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE NO CYCLE;
ALTER TABLE "TestSequence" ALTER COLUMN "Id" SET DEFAULT (nextval('"TestSequence_Id_seq"'));
ALTER SEQUENCE "TestSequence_Id_seq" OWNED BY "TestSequence"."Id";

How can I make this work for relations with schema?

Upvotes: 1

Views: 246

Answers (1)

JGH
JGH

Reputation: 17846

The doc you have linked says, for owned by

The specified table must have the same owner and be in the same schema as the sequence.

You haven't specified a schema for the sequence, so it is created in public by default, which is not the same as the table schema.

Try creating the sequence as

CREATE SEQUENCE markets."TestSequence_Id_seq" AS integer  ...

That being said, nothing prevents you from specifying the schema of both the sequence and the table

ALTER SEQUENCE markets."TestSequence_Id_seq" OWNED BY markets."TestSequence"."Id";

Upvotes: 2

Related Questions