序列的使用

[tbase@VM_0_29_centos tbase_mgr]$  psql -p 15432 -U dbadmin -d postgres
psql (PostgreSQL 10 (tdsql_pg 2.01))
Type "help" for help.
postgres=# create sequence tdsql_pg_seq;
CREATE SEQUENCE

postgres=# create sequence IF NOT EXISTS tdsql_pg_seq; 
NOTICE:  relation "tdsql_pg_seq" already exists, skipping
CREATE SEQUENCE

 postgres=# \\x  
 Expanded display is on.
 postgres=# select * from tdsql_pg_seq ;
 -[ RECORD 1 ]-
 last_value | 1
 log_cnt   | 0
 is_called  | f

postgres=# select nextval('tdsql_pg_seq');
 nextval 
---------
    1


 postgres=# select currval('tdsql_pg_seq');
  currval 
 ---------
     1

postgres=# select last_value from tdsql_pg_seq ;
  last_value 
 ------------
      3

 postgres=# select setval('tdsql_pg_seq',1);
  setval 
 --------
    1

postgres=# INSERT INTO t (id,nickname) VALUES(nextval('tdsql_pg_seq'),'tdsql_pg好');
INSERT 0 1
postgres=# select * from t;
 id | nickname  
----+-----------
 1 | 腾讯tdsql_pg
 2 | tdsql_pg好
(2 rows)

postgres=# alter table t alter column id set default nextval('tdsql_pg_seq');
postgres=# INSERT INTO t (nickname) VALUES('hello tdsql_pg');                      
INSERT 0 1
postgres=# select * from t;                        
 id |  nickname  
----+-------------
 3 | hello tdsql_pg
 1 | 腾讯tdsql_pg
 2 | tdsql_pg好
(3 rows)

postgres=# drop table t;
DROP TABLE
postgres=# create table t (id serial not null,nickname text);
CREATE TABLE
postgres=# INSERT INTO t (nickname) VALUES('hello tdsql_pg');   
INSERT 0 1
postgres=# select * from t;
 id |  nickname  
----+-------------
 1 | hello tdsql_pg
(1 row)

postgres=# drop sequence tdsql_pg_seq;
DROP SEQUENCE

postgres=# drop sequence  IF EXISTS  tdsql_pg_seq;   
NOTICE:  sequence "tdsql_pg_seq" does not exist, skipping
DROP SEQUENCE
postgres=#