在以數(shù)據(jù)庫指定表方式導(dǎo)出表序列、結(jié)構(gòu)和數(shù)據(jù)時，發(fā)生表結(jié)構(gòu)和數(shù)據(jù)被導(dǎo)出，序列沒有被導(dǎo)出，于是整理了一下說明了序列的正確創(chuàng)建方式
創(chuàng)新互聯(lián)自2013年創(chuàng)立以來，先為廬江等服務(wù)建站，廬江等地企業(yè)，進行企業(yè)商務(wù)咨詢服務(wù)。為廬江企業(yè)網(wǎng)站制作PC+手機+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問題。
一、序列創(chuàng)建方法
1.serial primary key方式建立序列
testdb=#
testdb=# create table t1(id serial primary key, name varchar(10));
CREATE TABLE
testdb=# insert into t1(name) values('David');
INSERT 0 1
testdb=# insert into t1(name) values('Peter');
INSERT 0 1
testdb=#
testdb=#
2.表和序列分別建立，序列附加到表
1).建立表
testdb=# create table t2(id bigint, name varchar(10));
CREATE TABLE
2).建立索引
testdb=# create sequence t2_id_seq increment by 1 minvalue 1 no maxvalue start with 1;
CREATE SEQUENCE
3).設(shè)置序列擁有者
testdb=# ALTER SEQUENCE t2_id_seq OWNED BY t2.id;
ALTER SEQUENCE
4).設(shè)置序列對應(yīng)列默認值
testdb=# ALTER TABLE ONLY t2 ALTER COLUMN id SET DEFAULT nextval('t2_id_seq'::regclass);
ALTER TABLE
testdb=#
5).設(shè)置ID列主鍵約束
testdb=#
testdb=# ALTER TABLE ONLY t2 ADD CONSTRAINT t2_pkey PRIMARY KEY (id);
ALTER TABLE
testdb=#
6).插入數(shù)據(jù)
testdb=# insert into t2(name) values('Jean');
INSERT 0 1
testdb=# insert into t2(name) values('jesmean');
INSERT 0 1
testdb=#
testdb=#
testdb=#
testdb=# \d t1_id_seq
Sequence "public.t1_id_seq"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | t1_id_seq
last_value | bigint | 2
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 31
is_cycled | boolean | f
is_called | boolean | t
Owned by: public.t1.id
testdb=#
testdb=#
testdb=# \d t2_id_seq
Sequence "public.t2_id_seq"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | t2_id_seq
last_value | bigint | 2
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 31
is_cycled | boolean | f
is_called | boolean | t
Owned by: public.t2.id
testdb=#
testdb=#
testdb=#
testdb=# \q
二、驗證序列是否粘連表
1.表導(dǎo)出時附帶序列
1).導(dǎo)出指定表，看是否包含序列
[postgres@localhost ~]$ pg_dump -Fc -d testdb --table=t1 -f testdb_t1_Fc.dump
[postgres@localhost ~]$ pg_dump -Fc -d testdb --table=t2 -f testdb_t2_Fc.dump
[postgres@localhost ~]$
2).建立新數(shù)據(jù)庫
[postgres@localhost ~]$
[postgres@localhost ~]$ createdb tdb
[postgres@localhost ~]$
3).導(dǎo)入數(shù)據(jù)及表結(jié)構(gòu)
[postgres@localhost ~]$
[postgres@localhost ~]$ pg_restore -d tdb testdb_t1_Fc.dump
[postgres@localhost ~]$ pg_restore -d tdb testdb_t2_Fc.dump
[postgres@localhost ~]$
[postgres@localhost ~]$
4).查看表結(jié)構(gòu)
[postgres@localhost ~]$
[postgres@localhost ~]$ psql tdb
psql (9.5.2)
Type "help" for help.
tdb=#
tdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | postgres
public | t2 | table | postgres
(2 rows)
tdb=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+-----------------------+-------------------------------------------------
id | integer | not null default nextval('t1_id_seq'::regclass)
name | character varying(10) |
Indexes:
"t1_pkey" PRIMARY KEY, btree (id)
tdb=# \d t2
Table "public.t2"
Column | Type | Modifiers
--------+-----------------------+-------------------------------------------------
id | bigint | not null default nextval('t2_id_seq'::regclass)
name | character varying(10) |
Indexes:
"t2_pkey" PRIMARY KEY, btree (id)
5).查看序列
tdb=# \d t1_id_seq
Sequence "public.t1_id_seq"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | t1_id_seq
last_value | bigint | 2
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 0
is_cycled | boolean | f
is_called | boolean | t
Owned by: public.t1.id
tdb=# \d t2_id_seq
Sequence "public.t2_id_seq"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | t2_id_seq
last_value | bigint | 2
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 0
is_cycled | boolean | f
is_called | boolean | t
Owned by: public.t2.id
tdb=#
6).插入數(shù)據(jù)
tdb=#
tdb=# insert into t1(name) values('chris');
INSERT 0 1
tdb=# insert into t2(name) values('LCH');
INSERT 0 1
tdb=#
tdb=#
tdb=# select * from t1;
id | name
----+-------
1 | David
2 | Peter
3 | chris
(3 rows)
tdb=# select * from t2;
id | name
----+---------
1 | Jean
2 | jesmean
3 | LCH
(3 rows)
tdb=#
tdb=#
tdb=#
tdb=# \d t1_id_seq
Sequence "public.t1_id_seq"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | t1_id_seq
last_value | bigint | 3
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 32
is_cycled | boolean | f
is_called | boolean | t
Owned by: public.t1.id
tdb=#
tdb=#
tdb=#
tdb=# \d t2_id_seq
Sequence "public.t2_id_seq"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | t2_id_seq
last_value | bigint | 3
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 32
is_cycled | boolean | f
is_called | boolean | t
Owned by: public.t2.id
tdb=#
tdb=#
2.表刪除時序列是否跟隨刪除
1).原數(shù)據(jù)庫
testdb=#
testdb=# drop table t1;
DROP TABLE
testdb=#
testdb=#
testdb=# drop table t2;
DROP TABLE
testdb=#
testdb=#
testdb=# \d t1_id_seq
Did not find any relation named "t1_id_seq".
testdb=#
testdb=#
testdb=# \d t2_id_seq
Did not find any relation named "t2_id_seq".
testdb=#
testdb=#
2).導(dǎo)入數(shù)據(jù)庫
tdb=#
tdb=#
tdb=# drop table t1;
DROP TABLE
tdb=#
tdb=# drop table t2;
DROP TABLE
tdb=#
tdb=#
tdb=#
tdb=# \d t1_id_seq
Did not find any relation named "t1_id_seq".
tdb=#
tdb=#
tdb=# \d t2_id_seq
Did not find any relation named "t2_id_seq".
tdb=#
tdb=#
重慶分公司
重慶分公司
