PostgreSQL increase a table's sequence with one query

Question

I want to unite the following two queries into one:

SELECT pg_get_serial_sequence('purchase_orders', 'id');
SELECT setval('purchase_orders_id_seq', 30000);

But if I place the upper SELECT into the setval's first parameter I get:

SELECT setval(SELECT pg_get_serial_sequence('purchase_orders', 'id'), 30000);

ERROR: syntax error at or near "SELECT"
SQL state: 42601
Character: 15

How can I pass on the select's result ("purchase_orders_id_seq") for setval?

EDIT: The reason for this is that; I want to use it like a function where a user only have to enter the table's name and a number to where sequence will be set.

FUNCTION set_id_sequence(TEXT table_name, INTEGER sequence);

What is pg_serial_get_sequence? I've never heard of it, and my 9.4 install professes ignorance when asked with \df pg_get_serial_sequence. What're you trying to accomplish/fix here, what problem are you trying to solve? — Craig Ringer
– Craig Ringer, Commented Jul 15, 2014 at 11:30
No, not really. Why do you need to do this in the first place? You shouldn't need to go around setting sequences. — Craig Ringer
– Craig Ringer, Commented Jul 15, 2014 at 16:24
It's not me who needs this. :) Our client has a sys-admin and he requested that he could separate default data by having their ids starting from 1 and user-created entries with an offset of let's say 10000. — Adam Horvath
– Adam Horvath, Commented Jul 17, 2014 at 9:34

Nick Barnes · Accepted Answer · 2014-07-15 11:53:07Z

5

If you want to pass a subquery result as a function argument, you need parentheses around it:

SELECT setval((SELECT pg_get_serial_sequence('purchase_orders', 'id')), 30000);

But in this case, the SELECT is redundant; you can invoke the function directly:

SELECT setval(pg_get_serial_sequence('purchase_orders', 'id'), 30000);

edited Jul 15, 2014 at 11:53

answered Jul 15, 2014 at 11:44

Nick Barnes

21.8k5 gold badges63 silver badges70 bronze badges

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

1 Comment

Adam Horvath Over a year ago

YES! That's it! Thank you so much, it was a redundant SELECT. :-)

2 revs · Accepted Answer · 2018-04-15 12:26:26Z

General function and automation

All "migration" or "starting database" SQL-script files have some controlled INSERT sequence before to use serial automation, so it need a simple command to say "ok, back to standard operation".

This generic operation is SELECT MAX(id)+1 FROM schemaName.tableName... and, as @NickBarnes showed above, the basic setval() operation is setval(pg_get_serial_sequence('schemaName.tableName', 'idName'), NEWVAL), so putting all together we automate the task.

2018's standardization proposal

CREATE FUNCTION std_setmaxval(
    p_tname text,
    p_id_name text DEFAULT 'id'
) RETURNS SETOF bigint AS $f$
BEGIN 
RETURN QUERY EXECUTE  format(
    'SELECT setval(pg_get_serial_sequence(%L,%L), COALESCE((SELECT MAX(%s)+1 FROM %s), 1) , false)'
     ,p_tname, p_id_name, p_id_name, p_tname
    );
END
$f$ LANGUAGE PLpgSQL;

See quotation problem/solution to optimize. Please review this answer, it is a Wiki (!). And update the standard snippet proposal.

PS: I not understand why postgreSQL not offer a native function for this task... Well, I not see at info's Guide or sequence's Guide.

Vin.AI · Accepted Answer · 2014-07-15 10:48:24Z

0

Change Sequence owner:

ALTER SEQUENCE purchase_orders_id_seq OWNED BY purchase_orders.id;

Set sequence value:

SELECT pg_catalog.setval('purchase_orders_id_seq', 30000, true);
ALTER TABLE ONLY purchase_orders ALTER COLUMN id SET DEFAULT
    nextval('purchase_orders_id_seq'::regclass);

answered Jul 15, 2014 at 10:48

Vin.AI

2,4373 gold badges22 silver badges42 bronze badges

1 Comment

Adam Horvath Over a year ago

It's almost there! But the SET DEFAULT will not accept pg_get_serial_sequence('purchase_orders', 'id') for the ::regclass. So who wants to modify the purchase_orders table still have to know the 'purchase_orders_id_seq' identifier. What I had in my mind was a function like: set_id_sequence(TEXT table_name, INTEGER sequence). Thus a person calling this function only have to know the table's name.

Collectives™ on Stack Overflow

PostgreSQL increase a table's sequence with one query

3 Answers 3

1 Comment

General function and automation

2018's standardization proposal

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

General function and automation

2018's standardization proposal

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related