postgresql sequence nextval in schema

Question

I have a sequence on postgresql 9.3 inside a schema.

I can do this:

SELECT last_value, increment_by from foo."SQ_ID";`

last_value | increment_by
------------+--------------
          1 |            1 (1 fila)

But this doesn't work:

SELECT nextval('foo.SQ_ID');

ERROR:  no existe la relación «foo.sq_id»
LÍNEA 1: SELECT nextval('foo.SQ_ID');

What is wrong ?

It says that not exist the relation foo.sq_id, but it exists.

Craig Ringer · Accepted Answer · 2014-01-28 05:41:34Z

165

The quoting rules are painful. I think you want:

SELECT nextval('foo."SQ_ID"');

to prevent case-folding of SQ_ID.

answered Jan 28, 2014 at 5:41

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

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

2 Comments

David Chan Over a year ago

SQL does not "case fold" in SQL the double quote is for denoting db objects seq and table names the single quote is for denoting static string values . They are not interchangeable. select "field" from "table" where "field" = 'value';

koks der drache Over a year ago

Of all the combinations of single and double quotes I tried, this would have been my last guess.

Raghulan Gowthaman · Accepted Answer · 2014-09-01 00:44:53Z

12

SELECT last_value, increment_by from "other_schema".id_seq;

for adding a seq to a column where the schema is not public try this.

nextval('"other_schema".id_seq'::regclass)

answered Sep 1, 2014 at 0:44

Raghulan Gowthaman

4685 silver badges11 bronze badges

Comments

NSysX · Accepted Answer · 2024-06-14 21:45:10Z

3

SELECT nextval('"your_schema_name"."your_sequence_name"')

works for me !!

answered Jun 14, 2024 at 21:45

NSysX

312 bronze badges

1 Comment

Pwnstar Jun 24 at 14:27

+100, this is the correct answer!

Collectives™ on Stack Overflow

postgresql sequence nextval in schema

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related