Create and Alter Sequence in
PostgreSQL
To activate autoincrement property in
PRIMARY KEY field for a table having huge records present in
postgresql. We need to create sequence and assign it to the
corresponding table.
Creating a sequence :
Assume that we have a table called [test_table] with an unique column called [id]
Generate sequence
run command > create <sequence name> , i.e. create test_table_id_sequence;
After the sequence’s already created, we can call NEXTVAL(‘<Sequence name>’) to generate a new value automatically.
Link the sequence to the unique column
ALTER TABLE <table name> ALTER COLUMN <column name> SET DEFAULT NEXTVAL(<created sequence name>);
=> ALTER TABLE test_table ALTER COLUMN id SET DEFAULT NEXTVAL('test_table_id_sequence');
Now
the sequence is created and linked to the desired table .
If
the table is empty or max id is 0 then we dont have to set value for
the sequence .
If
the table contains records then we have to set intial value . Neither
it will start the value from 1.
Alter
the sequence now :
Selecting
maximum id from the table to run
SELECT
MAX(id)
FROM
your_table;
=> SELECT
MAX(id)
FROM
test_table;
The
value should be higher than the record.
SELECT
nextval(
'your_table_id_seq'
);
=> SELECT
nextval(
'test_table_id_seq'
);
If
it's not higher... run this set the sequence last to your highest pid
it.
SELECT
setval(
'your_table_id_seq'
, (
SELECT
MAX(id)
FROM
your_table));
=> SELECT
setval(
'test_table_id_seq'
, (
SELECT
MAX(id)
FROM
test_table));
If your
tables might have no rows
-- false
means the set value will be returned by the next nextval() call
SELECT
setval(
'your_table_id_seq'
,
COALESCE
((
SELECT
MAX(id)
+1
FROM
your_table),
1)
);
=>SELECT
setval(
'test_table_id_seq'
,
COALESCE
((
SELECT
MAX(id)
+1
FROM
test_table),
1)
);
No comments:
Post a Comment