Shulou(Shulou.com)06/01 Report--

When the table sequence, structure and data are exported in the way of database specified table, the table structure and data are exported, but the sequence is not exported, so it is sorted out to explain the correct way to create the sequence.

I. the method of sequence creation

Establishing sequence in 1.serial primary key mode

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

two。 The table and the sequence are established respectively, and the sequence is attached to the table.

1)。 Create a table

Testdb=# create table T2 (id bigint, name varchar (10))

CREATE TABLE

2)。 Build an index

Testdb=# create sequence t2_id_seq increment by 1 minvalue 1 no maxvalue start with 1

CREATE SEQUENCE

3)。 Set sequence owner

Testdb=# ALTER SEQUENCE t2_id_seq OWNED BY t2.id

ALTER SEQUENCE

4)。 Set the default value of the column corresponding to the sequence

Testdb=# ALTER TABLE ONLY T2 ALTER COLUMN id SET DEFAULT nextval ('t2_id_seq'::regclass)

ALTER TABLE

Testdb=#

5)。 Set ID column primary key constraint

Testdb=#

Testdb=# ALTER TABLE ONLY T2 ADD CONSTRAINT t2_pkey PRIMARY KEY (id)

ALTER TABLE

Testdb=#

6)。 Insert data

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

Second, verify whether the sequence adheres to the table

1. Table is exported with a sequence

1)。 Export the specified table to see if it contains a sequence

[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)。 Set up a new database

[postgres@localhost ~] $

[postgres@localhost ~] $createdb tdb

[postgres@localhost ~] $

3)。 Import data and table structure

[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)。 View table structure

[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)。 View sequenc

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)。 Insert data

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

two。 Does the sequence follow the deletion when the table is deleted

1)。 Original database

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)。 Import database

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.