Can't create tables with sequences in a non-default schema

Question

I'm trying to create a system, where I'm wanna sort the data for years (using schemas), for example now I have a schema named datos_2016 then I create a sequence named seq_datos.

When I try to create the table datos and set the sequence for cdata it says

"An error has ocurred: ERROR The relation "seq_data" doesn't exits"

But if I create the schema "public" and then create the sequence there, no error happens (the table is successfully created).

Why can't I create sequences in another schema?

The SQL CODE:

CREATE TABLE datos_2016.data
(
   cdata integer NOT NULL DEFAULT nextval('seq_data'), 
   CONSTRAINT fk_cdata PRIMARY KEY (cdata) USING INDEX TABLESPACE sistema_index
) 
WITH (
  OIDS = FALSE
)

TABLESPACE sistema_data;

The Sequence Code:

CREATE SEQUENCE datos_2016.seq_data
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
ALTER TABLE datos_2016.seq_data
  OWNER TO postgres;
GRANT ALL ON SEQUENCE datos_2016.seq_data TO public;
GRANT ALL ON SEQUENCE datos_2016.seq_data TO postgres;

user330315 · Accepted Answer · 2016-11-25 15:12:34Z

2

You need to fully qualify the sequence name:

CREATE TABLE datos_2016.data
(
   cdata integer NOT NULL DEFAULT nextval('datos_2016.seq_data'), 
   ....
);

answered Nov 25, 2016 at 15:12

user330315

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

1 Comment

JuJoGuAl Over a year ago

Great, that was it! I assumed that not having the "public" schema created did not have to specify the schema

Amy Doxy · Accepted Answer · 2019-09-15 06:50:14Z

0

Try this nextval('datos_2016."seq_data"')

answered Sep 15, 2019 at 6:50

Amy Doxy

7697 silver badges12 bronze badges

Collectives™ on Stack Overflow

Can't create tables with sequences in a non-default schema

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related