#Citect oid out of sequence serial

To defend against concurrent sequence activity or writes to the table in the above queries, lock the table in SHARE mode. We can't just use the 2-parameter form and start with 0 because the lower bound of sequences is 1 by default (unless customized). If the table can be empty and to actually start from 1 in this case: SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id') Next nextval will advance the sequence before returning a value. Specified value and sets its is_called field to true, meaning that the The two-parameter form sets the sequence's last_value field to the If you don't know the name of the attached sequence (which doesn't have to be in default form), use pg_get_serial_sequence(): SELECT setval(pg_get_serial_sequence('tbl', 'tbl_id'), max(tbl_id)) FROM tbl

#Citect oid out of sequence serial

Tbl_id being the serial column of table tbl, drawing from the sequence tbl_tbl_id_seq (which is the default automatic name). The shortest and fastest way: SELECT setval('tbl_tbl_id_seq', max(tbl_id)) FROM tbl SELECT setval(pg_get_serial_sequence('t2', 'id'), coalesce(max(id),0) + 1, false) FROM t1 SELECT pg_get_serial_sequence('t2', 'id') - returns 't2_custom_id_seq'

SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1 Д«ut if sequences were added manually: CREATE TABLE t2 (ДЄLTER TABLE t2 ALTER COLUMN id SET DEFAULT nextval('t2_custom_id_seq'::regclass) ДЄLTER SEQUENCE t2_custom_id_seq OWNED BY t2.id - required for pg_get_serial_sequence reset the sequence, regardless whether table has rows or not: SELECT pg_get_serial_sequence('t1', 'id') - returns 't1_id_seq' if serial type was used for table creation, this should all work: CREATE TABLE t1 ( This will be the case if the incrementing column was defined as a serial type, however if the sequence was added manually it is necessary to ensure ALTER SEQUENCE. handles tables with existing data, and does not leave aДЇinally, note that pg_get_serial_sequence only works if the sequence is owned by the column.avoids hard-coding the actual sequence name.It seems the best all-purpose solution is to call setval with false as the 3rd parameter, allowing us to specify the 'next value to use': SELECT setval(pg_get_serial_sequence('t1', 'id'), coalesce(max(id),0) + 1, false) FROM t1 ALTER SEQUENCE table_name_id_seq RESTART WITH 1 ДЄLTER SEQUENCE table_name_id_seq RESTART - 8.4 or higherД«ut ALTER SEQUENCE is of limited use because the sequence name and restart value cannot be expressions. One workaround for this is to resort to the ALTER SEQUENCE syntax i.e. However this form can't handle empty tables correctly, since max(id) is null, and neither can you setval 0 because it would be out of range of the sequence. Or more concisely: SELECT pg_tval(pg_get_serial_sequence('table_name', 'id'), MAX(id)) FROM table_name This resets the sequence in one shot: SELECT pg_tval(pg_get_serial_sequence('table_name', 'id'), (SELECT MAX(id) FROM table_name)+1) Pg_get_serial_sequence can be used to avoid any incorrect assumptions about the sequence name.