PostgreSQL sequence Use

The generation of primary keys in the database is usually generated by sequence, and the sequence knowledge of PG is mainly listed as follows:

How to find the name of the sequence: Open the current database with Pgadmin, find the relevant sequence under Schemas->sequences, and then Selectnextval (' im_indicator_results_seq '); View the current serial number and view the existing maximum sequence number in the related table, such as: Selectmax (result_id) from Im_indicator_results, and finally set the current serial number to the maximum sequence number selectsetval (' im_ Indicator_results_seq ', 5288);

Method A: Specifying the field type directly in the table is the SERIAL type.

CREATE TABLE person (   ID   SERIAL,   name TEXT  );

CREATE SEQUENCE Person_id_seq; CREATE TABLE person (id INT4 not NULL DEFAULT nextval (' Person_id_seq '), name TEXT); SELECT currval (' person_id_seq ');select nextval (' person_id_seq '); select last_value from Person_id_seq; select setval (seq_name,new_seq_value);

  SELECT setval (' foo ', 42);   --The next nextval will return to the setval SELECT (' foo ', A, false); --The next nextval will return to 

DROP TABLE table_name;
Drop sequence seq_name;

A.currval gets the sequence value of the current session, which is not changed in the current session because the other session took Nextval. Will change the global Last_value value, and the current session if not read the Nextval value of the direct read Currval will be an error.
B. When a sequence is created by specifying serial when the table is built, the corresponding sequence is also deleted when the table is deleted.
C. Table primary key data can be used with table-related sequences, or other sequences can be used, but not recommended, just PG default it is correct.
D. Nextval is not rollback to allow multiple users to repeat the same sequence values, but you can use Setval to reset

PostgreSQL sequence Use