Python wechat ordering applet course video
https://edu.csdn.net/course/detail/36074
Python actual combat quantitative transaction financial management system
https://edu.csdn.net/course/detail/35475
preface
This article is the second in the sequence series after the Huashan argumentative sword of the three major databases sequence (Oracle PostgreSQL MySQL sequence ten-year experience summary). It mainly shares some experience about sequence in PostgreSQL.
Test environment preparation
The following tests were performed in PostgreSQL 11.
Created with the following SQL:
Test user: alvin, ordinary user, non superuser
Test database: alvindb, owner is alvin
Test schema: alvin, and the owner is also alvin
Here, the user and schema have the same name, combined with the default search_path("$user", public), so there is no need to add schema prefix when operating objects (table, sequence, etc.).
| | postgres=# CREATE USER alvin WITH PASSWORD 'alvin'; | | | CREATE ROLE | | | postgres=# CREATE DATABASE alvindb OWNER alvin; | | | CREATE DATABASE | | | postgres=# \c alvindb | | | You are now connected to database "alvindb" as user "postgres". | | | alvindb=# CREATE SCHEMA alvin AUTHORIZATION alvin; | | | CREATE SCHEMA | | | alvindb=# \c alvindb alvin | | | You are now connected to database "alvindb" as user "alvin". | | | alvindb=> SHOW search\_path; | | | search\_path | | | ----------------- | | | "$user", public | | | (1 row) |
There are two ways to create a sequence
The general purpose of sequence is to be used as the generation of primary key sequence. Next, we will discuss how to create a sequence by creating a sequence and a table.
Create sequence directly
The following is a simple way to directly create sequence and table.
| | alvindb=> CREATE SEQUENCE tb\_test\_sequence\_test\_id\_seq; |
| | CREATE SEQUENCE |
| | alvindb=> |
| | CREATE TABLE tb\_test\_sequence ( |
| | test\_id INTEGER DEFAULT nextval('alvin.tb\_test\_sequence\_test\_id\_seq') PRIMARY KEY, |
| | create\_time TIMESTAMP DEFAULT clock\_timestamp() |
| | ); |
| | CREATE TABLE |
View created objects
| | alvindb=> \d | | | List of relations | | | Schema | Name | Type | Owner | | | --------+------------------------------+----------+------- | | | alvin | tb\_test\_sequence\_test\_id\_seq | sequence | alvin | | | alvin | tb\_test\_sequence | table | alvin | | | (2 rows) |
View the structure of the created object
| | alvindb=> \d tb\_test\_sequence |
| | Table "alvin.tb\_test\_sequence" |
| | Column | Type | Collation | Nullable | Default |
| | -------------+-----------------------------+-----------+----------+--------------------------------------------------- |
| | test\_id | integer | | not null | nextval('tb\_test\_sequence\_test\_id\_seq'::regclass) |
| | create\_time | timestamp without time zone | | | clock\_timestamp() |
| | Indexes: |
| | "tb\_test\_sequence\_pkey" PRIMARY KEY, btree (test\_id) |
| | |
| | alvindb=> \d tb\_test\_sequence\_test\_id\_seq |
| | Sequence "alvin.tb\_test\_sequence\_test\_id\_seq" |
| | Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| | --------+-------+---------+---------------------+-----------+---------+------- |
| | bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 |
| | |
| | alvindb=> |
At this point, we will notice that problem 1, column TB_ test_ sequence. test_ The type of ID is integer, while the default type of sequence created is bigint.
This is no problem, but it will be better if the types are consistent.
Next, if we drop sequence, we will find that because the table depends on sequence, we cannot drop sequence alone.
| | alvindb=> DROP SEQUENCE tb\_test\_sequence\_test\_id\_seq; | | | ERROR: cannot drop sequence tb\_test\_sequence\_test\_id\_seq because other objects depend on it | | | DETAIL: default value for column test\_id of table tb\_test\_sequence depends on sequence tb\_test\_sequence\_test\_id\_seq | | | HINT: Use DROP ... CASCADE to drop the dependent objects too. | | | alvindb=> |
Let's drop the table tb_test_sequence.
| | alvindb=> DROP TABLE tb\_test\_sequence; | | | DROP TABLE | | | alvindb=> \d | | | List of relations | | | Schema | Name | Type | Owner | | | --------+------------------------------+----------+------- | | | alvin | tb\_test\_sequence\_test\_id\_seq | sequence | alvin | | | (1 row) |
You can see that in question 2, although the table is drop ped, the sequence is still there.
What's the problem?
In a large database system, we may find many isolated sequences, because we may forget to drop the corresponding sequence when we drop the table.
Now drop the sequence manually.
| | alvindb=> DROP SEQUENCE tb\_test\_sequence\_test\_id\_seq; | | | DROP SEQUENCE | | | alvindb=> \d | | | Did not find any relations. | | | alvindb=> |
Let's optimize SQL to solve the above two problems:
| | alvindb=> CREATE SEQUENCE tb\_test\_sequence\_test\_id\_seq AS INTEGER; |
| | CREATE SEQUENCE |
| | alvindb=> |
| | CREATE TABLE tb\_test\_sequence ( |
| | test\_id INTEGER DEFAULT nextval('alvin.tb\_test\_sequence\_test\_id\_seq') PRIMARY KEY, |
| | create\_time TIMESTAMP DEFAULT clock\_timestamp() |
| | ); |
| | CREATE TABLE |
| | alvindb=> ALTER SEQUENCE tb\_test\_sequence\_test\_id\_seq OWNED BY tb\_test\_sequence.test\_id; |
| | ALTER SEQUENCE |
The above SQL functions are:
- When creating a sequence, specify the type to make the column consistent with the type of sequence
- Associate the column and sequence of the table, so that the sequence associated with it will be automatically dropped when dropping the table or column
View the table structure,
| | alvindb=> \d tb\_test\_sequence |
| | Table "alvin.tb\_test\_sequence" |
| | Column | Type | Collation | Nullable | Default |
| | -------------+-----------------------------+-----------+----------+--------------------------------------------------- |
| | test\_id | integer | | not null | nextval('tb\_test\_sequence\_test\_id\_seq'::regclass) |
| | create\_time | timestamp without time zone | | | clock\_timestamp() |
| | Indexes: |
| | "tb\_test\_sequence\_pkey" PRIMARY KEY, btree (test\_id) |
| | |
| | alvindb=> \d tb\_test\_sequence\_test\_id\_seq |
| | Sequence "alvin.tb\_test\_sequence\_test\_id\_seq" |
| | Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| | ---------+-------+---------+------------+-----------+---------+------- |
| | integer | 1 | 1 | 2147483647 | 1 | no | 1 |
| | Owned by: alvin.tb\_test\_sequence.test\_id |
As you can see,
- Column TB_ test_ sequence. test_ The type of ID and sequence is integer
- 'Owned by' is added below the sequence, indicating that the column is associated with the sequence.
After the drop table below, you can see that the sequence has also been dropped.
| | alvindb=> DROP TABLE tb\_test\_sequence; | | | DROP TABLE | | | alvindb=> \d | | | Did not find any relations. |
In fact, if drop drops the test column_ ID, and its associated sequence will also be dropped.
| | alvindb=> ALTER TABLE tb\_test\_sequence DROP COLUMN test\_id; | | | ALTER TABLE | | | alvindb=> \d tb\_test\_sequence | | | Table "alvin.tb\_test\_sequence" | | | Column | Type | Collation | Nullable | Default | | | -------------+-----------------------------+-----------+----------+------------------- | | | create\_time | timestamp without time zone | | | clock\_timestamp() | | | alvindb=> \d | | | List of relations | | | Schema | Name | Type | Owner | | | --------+------------------+-------+------- | | | alvin | tb\_test\_sequence | table | alvin | | | (1 row) |
The second way to create a sequence is to create it through serial
The following is an SQL to achieve exactly the same effect as above.
| | alvindb=> | | | CREATE TABLE tb\_test\_sequence ( | | | test\_id SERIAL PRIMARY KEY, | | | create\_time TIMESTAMP DEFAULT clock\_timestamp() | | | ); | | | CREATE TABLE |
View the table structure, which is exactly the same as that in mode 1.
| | alvindb=> \d |
| | List of relations |
| | Schema | Name | Type | Owner |
| | --------+------------------------------+----------+------- |
| | alvin | tb\_test\_sequence | table | alvin |
| | alvin | tb\_test\_sequence\_test\_id\_seq | sequence | alvin |
| | (2 rows) |
| | alvindb=> \d tb\_test\_sequence |
| | Table "alvin.tb\_test\_sequence" |
| | Column | Type | Collation | Nullable | Default |
| | -------------+-----------------------------+-----------+----------+--------------------------------------------------- |
| | test\_id | integer | | not null | nextval('tb\_test\_sequence\_test\_id\_seq'::regclass) |
| | create\_time | timestamp without time zone | | | clock\_timestamp() |
| | Indexes: |
| | "tb\_test\_sequence\_pkey" PRIMARY KEY, btree (test\_id) |
| | |
| | alvindb=> \d tb\_test\_sequence\_test\_id\_seq |
| | Sequence "alvin.tb\_test\_sequence\_test\_id\_seq" |
| | Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| | ---------+-------+---------+------------+-----------+---------+------- |
| | integer | 1 | 1 | 2147483647 | 1 | no | 1 |
| | Owned by: alvin.tb\_test\_sequence.test\_id |
Here is a summary of what a word SERIAL does:
- According to the rule tablename_colname_seq creates a sequence and sets DEFAULT
- Add NOT NULL constraint
- Associate columns with sequences. When a table or associated column is dropped, the associated sequence will also be dropped
Note: NOT NULL constraint will be added to one of the SERIAL and primary keys by default
Using SERIAL does save a lot of things, but is there any problem with it? Will using it introduce new problems?
- The data type corresponding to SERIAL is integer. As the data type of primary key, is integer enough?
- After associating the column and sequence, it is convenient to drop, but will it bring new problems to the operation and maintenance at the same time? For example, rename table, column or sequence?
- When copying or migrating tables, what should be done to sequence?
Let's further explore these issues.
serial and bigserial
serial corresponds to integer, which is 4 bytes. The maximum value is 2147483647, or about 2.1 billion.
Is 2.1 billion really enough for a sequence of large table primary keys? According to the global population of 7 billion, not one person is enough.
To solve this problem, you can use bigserial, or bigint, which is 8 bytes. The maximum value is 9223 372 036 854 775807, or about 92.2 billion. This is sufficient for most scenarios, and it is also the maximum value of sequence in PostgreSQL.
To create a table using bigseries:
| | alvindb=> | | | CREATE TABLE tb\_test\_bigserial ( | | | test\_id BIGSERIAL PRIMARY KEY, | | | create\_time TIMESTAMP DEFAULT clock\_timestamp() | | | ); | | | CREATE TABLE |
View the table structure,
| | alvindb=> \d tb\_test\_bigserial |
| | Table "alvin.tb\_test\_bigserial" |
| | Column | Type | Collation | Nullable | Default |
| | -------------+-----------------------------+-----------+----------+---------------------------------------------------- |
| | test\_id | bigint | | not null | nextval('tb\_test\_bigserial\_test\_id\_seq'::regclass) |
| | create\_time | timestamp without time zone | | | clock\_timestamp() |
| | Indexes: |
| | "tb\_test\_bigserial\_pkey" PRIMARY KEY, btree (test\_id) |
| | |
| | alvindb=> \d tb\_test\_bigserial\_test\_id\_seq |
| | Sequence "alvin.tb\_test\_bigserial\_test\_id\_seq" |
| | Type | Start | Minimum | Maximum | Increment | Cycles? | Cache |
| | --------+-------+---------+---------------------+-----------+---------+------- |
| | bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1 |
| | Owned by: alvin.tb\_test\_bigserial.test\_id |
As you can see, the column test_ The Type of ID and sequence is bigint. In this way, the problem of the Type of sequence is solved.
official account
DBA Daily official account was first noticed.
Through the daily work of front-line DBA, learn practical database technology dry goods!
The official account is recommended by quality articles.
PostgreSQL VACUUM in simple terms
PostgreSQL sequence of Huashan sword
[PG Upgrade Series] Extract Epoch Trap
[PG Upgrade Series] Toast Dump Error
GitLab supports only PostgreSQL now
ReIndex failure cause investigation
Investigation on Hive garbled code of PG data import
Investigation on the causes of PostGIS extension creation failure