表中序列正确的定义方式(表与序列的粘连配置)

发布时间：2020-06-21 07:36:51 来源：网络阅读：758 作者：pgmia 栏目：数据库

在以数据库指定表方式导出表序列、结构和数据时，发生表结构和数据被导出，序列没有被导出，于是整理了一下说明了序列的正确创建方式

一、序列创建方法

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=#

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).设置序列拥有者

testdb=# ALTER SEQUENCE t2_id_seq OWNED BY t2.id;

ALTER SEQUENCE

4).设置序列对应列默认值

testdb=# ALTER TABLE ONLY t2 ALTER COLUMN id SET DEFAULT nextval('t2_id_seq'::regclass);

ALTER TABLE

testdb=#

5).设置ID列主键约束

testdb=#

testdb=# ALTER TABLE ONLY t2 ADD CONSTRAINT t2_pkey PRIMARY KEY (id);

ALTER TABLE

testdb=#

6).插入数据

testdb=# insert into t2(name) values('Jean');

INSERT 0 1

testdb=# insert into t2(name) values('jesmean');

INSERT 0 1

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=# \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=# \q

二、验证序列是否粘连表

1.表导出时附带序列

1).导出指定表，看是否包含序列

[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).建立新数据库

[postgres@localhost ~]$

[postgres@localhost ~]$ createdb tdb

[postgres@localhost ~]$

3).导入数据及表结构

[postgres@localhost ~]$

[postgres@localhost ~]$ pg_restore -d tdb testdb_t1_Fc.dump

[postgres@localhost ~]$ pg_restore -d tdb testdb_t2_Fc.dump

[postgres@localhost ~]$

4).查看表结构

[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).插入数据

tdb=#

tdb=# insert into t1(name) values('chris');

INSERT 0 1

tdb=# insert into t2(name) values('LCH');

INSERT 0 1

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=# \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=# \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=#

2.表删除时序列是否跟随删除

1).原数据库

testdb=#

testdb=# drop table t1;

DROP TABLE

testdb=#

testdb=# drop table t2;

DROP TABLE

testdb=#

testdb=# \d t1_id_seq

Did not find any relation named "t1_id_seq".

testdb=#

testdb=# \d t2_id_seq

Did not find any relation named "t2_id_seq".

testdb=#

2).导入数据库

tdb=#

tdb=# drop table t1;

DROP TABLE

tdb=#

tdb=# drop table t2;

DROP TABLE

tdb=#

tdb=# \d t1_id_seq

Did not find any relation named "t1_id_seq".

tdb=#

tdb=# \d t2_id_seq

Did not find any relation named "t2_id_seq".

tdb=#

向AI问一下细节

表中序列正确的定义方式(表与序列的粘连配置)

猜你喜欢

最新资讯

相关推荐

相关标签