PGAdmin CREATE script sequence relation does not exist-CodePudding

I've been creating tables with PGAdmin and I've got a field that I want to auto-inc, and PGAdmin will create a sequence. Then I'll want to edit the table in some fashion so I pull up the CREATE script which will have this line:

CREATE TABLE IF NOT EXISTS public.mytable
(
    mykey bigint NOT NULL DEFAULT nextval('mytable_mykey_seq'::regclass),

Invariably, when I try to execute this, I'll see:

relation "mytable_mykey_seq" does not exist

Which is really irritating because I can see it right there in the object browser. I can even drag the sequence in from the object window:

mykey bigint NOT NULL DEFAULT nextval('public.mytable_mykey_seq'::regclass),

And get:

relation "public.mytable_mykey_seq" does not exist

Which is also what I get if I try:

mykey bigint NOT NULL DEFAULT nextval('"public".mytable_mykey_seq'::regclass),

or:

mykey bigint NOT NULL DEFAULT nextval('public."mytable_mykey_seq"'::regclass),

as suggested in other similar SO questions.

This happens invariably whenever I try to run a create script for a table with a sequence. Here's the smallest use case script:

-- Table: public.mytable

-- DROP TABLE IF EXISTS public.mytable;

CREATE TABLE IF NOT EXISTS public.mytable
(
    mykey integer NOT NULL DEFAULT nextval('mytable_mykey_seq'::regclass),
    CONSTRAINT mytable_pkey PRIMARY KEY (mykey)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.mytable
    OWNER to oikos;

"oikos" is my login; I am the only user. Latest version of PGAdmin 4 (6.3). Postgres version 12.3, I believe.

My questions are: a) What am I doing wrong in PGAdmin, if anything?; b) What is the correct namespace/convention/magic word to have PG be able to locate this sequence?

CodePudding user response：

do you have SEQUENCE mytable_mykey_seq

CREATE SEQUENCE public.mytable_mykey_seq;

ALTER SEQUENCE public.mytable_mykey_seq OWNER TO oikos;

CodePudding user response：

First off PGAdmin does not create anything. It is an graphical interface to the Postgres as psql is a commend line interface to Postgres. It id Postgres doing the work. You need to always keep in mind what software you are talking.
Secondly, your syntax is incorrect. There are 2 approaches you can take: Let Postgres create the sequence (this seems to be what you want). The other way is to manually create the sequence and then manually specify the sequence as the default (the second part is what you actually did). Postgres generates and names and makes the default when you specify serial/bigserial instead of an actual data type. So (for versins prior to 10)

CREATE TABLE IF NOT EXISTS public.mytable
(
    mykey serial primary key, ...
);

For versions 10 and after use a Generated Identity

CREATE TABLE IF NOT EXISTS public.mytable
    (
        mykey bigint generated always as identity primary key, ... 
    );

With manual method you need to you need to first create the sequence

create sequence mytable_mykey_seq;  --parameters and needed