Search code examples
postgresqlauto-increment

Setting an auto increment value

If I have schemas SCHEMA1 with tables tbl_one and tbl_two, SCHEMA2 with table tbl_tree, how to use in PostgreSQL command setval():

SELECT SETVAL('SCHEMA1.tbl_one_id_seq', 234) FROM SCHEMA1.tbl_one;
SELECT SETVAL('SCHEMA1.tbl_two_id_seq', 345) FROM SCHEMA1.tbl_two;
SELECT SETVAL('SCHEMA2.tbl_tree_id_seq', 456) FROM SCHEMA2.tbl_tree;

These don't work.

Solution

  • You don't need the FROM clause. Just:

    SELECT SETVAL('SCHEMA1.tbl_one_id_seq', 234);

    In future, when you say "it doesn't work" you should be specific about what error message you get exactly.

    Possibly your schema is case-perserved? If so, you want:

    SELECT SETVAL('"SCHEMA1"."tbl_one_id_seq"', 234);

    Note the quotes on the identifier to prevent case-folding.