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.

score 146 · Accepted Answer · answered Jan 28 '14 at 05:41

146

The quoting rules are painful. I think you want:

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

to prevent case-folding of SQ_ID.

answered Jan 28 '14 at 05:41

Craig Ringer

2

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'; – David Chan Sep 19 '14 at 21:06
1

Of all the combinations of single and double quotes I tried, this would have been my last guess. – koks der drache Feb 11 '21 at 15:32

score 13 · Answer 2 · answered Sep 01 '14 at 00:44

13

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 01 '14 at 00:44

2 Answers2