Error during creation of sequence on specific schema in Postgres

Question

I have a strange error in Postgres during creation of sequence and linking on existing table.

I tried to execute the following script on existing DB which have a schema named 'testschema':

BEGIN TRANSACTION;

CREATE TABLE testschema.mytable (
    id INTEGER,
    value VARCHAR(30),
    CONSTRAINT pk_mytable PRIMARY KEY (id));

CREATE SEQUENCE testschema.mytable_id_seq;
ALTER TABLE testschema.mytable ALTER COLUMN  id SET NOT NULL;
ALTER TABLE testschema.mytable ALTER COLUMN  id SET DEFAULT nextval('mytable_id_seq');

COMMIT;

I got the following error for the second ALTER TABLE query:

********** Erreur **********

ERREUR: la relation « mytable_id_seq » n'existe pas État SQL :42P01

Error message can be translated by "Relation 'mytable_id_seq' does not exist".

If I try to execute this script without schema (i.e. in the public schema), it works.

I don't understand what it is failing. Does anyone see where the problem is?

Thanks for your help.

Houari · Accepted Answer · 2014-11-07 11:27:07Z

2

Replace:

ALTER TABLE testschema.mytable ALTER COLUMN  id SET DEFAULT nextval('mytable_id_seq');

by

ALTER TABLE testschema.mytable ALTER COLUMN  id SET DEFAULT nextval('testschema.mytable_id_seq');

By default, objects without using schema quallifier are searched in the schema that are in the search_path variable. by default default, only public schema exists in this variable.

So the simplest way is to use full quallified qualifier when referencing your objects. Or, you can put your new schema in the path, like this:

SET search_path TO testschema,public;

Source

edited Nov 7, 2014 at 11:27

answered Nov 7, 2014 at 11:16

Houari

5,6513 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error during creation of sequence on specific schema in Postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related