PostgreSQL imports database tables and resets self incrementing attributes

Time:2022-4-26

PostgreSQL uses Navicat software to export database tables, and the operation of importing the database will be.

The auto increment field of PostgreSQL is realized through sequence.

1. Delete the auto increment attribute in the exported database table first

2. After importing the database table, you need to create a sequence.

Note: generally, the sequence name is composed of data table name + primary key field + SEQ (generally, the primary key field is self increasing field), and the following table name is COF_ table_ hot_ Analysis, the primary key field is ID


CREATE SEQUENCE cof_table_hot_analysis_id_seq
  START WITH 1
  INCREMENT BY 1
  NO MINVALUE
  NO MAXVALUE
  CACHE 1;

3. Set table field (ID) self increment SQL


alter table cof_table_hot_analysis alter column id set default nextval('cof_table_hot_analysis _id_seq ');

4. After the above three steps are set

The default value of auto increment field ID is 1. If you need to set the actual value after auto increment, such as 16


select setval('cof_table_hot_analysis_id_seq', 16)

5. When the table is emptied, the sequence will be reset, and the new data will increase automatically from 1


TRUNCATE TABLE cof_table_hot_analysis RESTART IDENTITY

Extension:

Delete sequence:


DROP SEQUENCE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Modify sequence:


ALTER SEQUENCE name [ INCREMENT [ BY ] increment ]
  [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
  [ START [ WITH ] start ]
  [ RESTART [ [ WITH ] restart ] ]
  [ CACHE cache ] [ [ NO ] CYCLE ]
  [ OWNED BY { table.column | NONE } ]
ALTER SEQUENCE name OWNER TO new_owner
ALTER SEQUENCE name RENAME TO new_name
ALTER SEQUENCE name SET SCHEMA new_schema

Supplement: PgSQL adds auto increment sequence and sets auto increment of a field in the table

Add self increasing sequence

Create sequence table name_ id_ seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

Set the self increment of a field in the table

Alter table name alter column ID set default nextval ('table name _id_seq ');

Increment from the current maximum ID

Select SETVAL ('table name _id_seq ', (select max (ID) from the same table name));

The table of uppercase characters needs double quotation marks.

For example:

Select SETVAL ('table name _id_seq ', (select max (ID) from "table name");

The above is my personal experience. I hope I can give you a reference, and I hope you can support developpaer. If there are mistakes or not fully considered, please don’t hesitate to comment.

Recommended Today

Chapter 45 SQL command from (I)

Chapter 45 SQL command from (I) A select clause that specifies one or more tables to query. outline SELECT … FROM [optimize-option] table-ref [[AS] t-alias][,table-ref [[AS] t-alias]][,…] parameter optimize-optioN – optional – specifies a single keyword or a series of keywords separated by spaces for query optimization options (optimizer tips). The following keywords are supported:%ALLINDEX、%FIRSTTABLE […]