让id自增的办法

Introduction to the PostgreSQL SERIAL pseudo-type

In PostgreSQL,
a [sequence]{.ul} is
a special kind of database object that generates a sequence of integers.
A sequence is often used as the [primary
key]{.ul} column
in a table.

When [creating a new
table]{.ul},
the sequence can be created through the SERIAL pseudo-type as follows:

CREATE TABLE table_name(

    id SERIAL

);

Code language: SQL (Structured Query Language) (sql)

By assigning the SERIAL pseudo-type to the id column, PostgreSQL
performs the following:

• First, create a sequence object and set the next value generated by
  the sequence as the default value for the column.

• Second, add a [NOT
  NULL]{.ul} constraint
  to the id column because a sequence always generates an integer,
  which is a non-null value.

• Third, assign the owner of the sequence to the id column; as a
  result, the sequence object is deleted when the id column or table
  is dropped

Behind the scenes, the following statement:

CREATE TABLE table_name(

    id SERIAL

);

Code language: SQL (Structured Query Language) (sql)

is equivalent to the following statements:

CREATE SEQUENCE table_name_id_seq;

CREATE TABLE table_name (

    id integer NOT NULL DEFAULT nextval(‘table_name_id_seq’)

);

ALTER SEQUENCE table_name_id_seq

OWNED BY table_name.id;

Code language: SQL (Structured Query Language) (sql)

PostgreSQL provides three serial pseudo-types SMALLSERIAL, SERIAL,
and BIGSERIAL with the following characteristics:

---

Name Storage Size Range

---

SMALLSERIAL 2 bytes 1 to 32,767

SERIAL 4 bytes 1 to 2,147,483,647

BIGSERIAL 8 bytes 1 to 9,223,372,036,854,775,807

PostgreSQL SERIAL example

It is important to note that the SERIAL does not implicitly [create an
index]{.ul} on
the column or make the column as the [primary
key]{.ul} column.
However, this can be done easily by specifying the PRIMARY
KEY constraint for the SERIAL column.

The following statement creates the fruits table with the id column as
the SERIAL column:

CREATE TABLE fruits(

id SERIAL PRIMARY KEY,

name VARCHAR NOT NULL

);

Code language: SQL (Structured Query Language) (sql)

To assign the default value for a serial column when you [insert row
into the
table]{.ul}, you
ignore the column name or use the DEFAULT keyword in
the [INSERT]{.ul} statement.

See the following example:

INSERT INTO fruits(name)

VALUES(‘Orange’);

Code language: SQL (Structured Query Language) (sql)

Or

INSERT INTO fruits(id,name)

VALUES(DEFAULT,’Apple’);

Code language: SQL (Structured Query Language) (sql)

PostgreSQL inserted two rows into the fruits table with the values for
the id column are 1 and 2.

SELECT * FROM fruits;

Code language: SQL (Structured Query Language) (sql)

id | name

—-+——–

1 | Apple

2 | Orange

(2 rows)

Code language: SQL (Structured Query Language) (sql)

To get the sequence name of a SERIAL column in a table, you use
the pg_get_serial_sequence() function as follows:

pg_get_serial_sequence(‘table_name’,’column_name’)

Code language: SQL (Structured Query Language) (sql)

You can pass a sequence name to the  currval() function to get the
recent value generated by the sequence. For example, the following
statement returns the recent value generated by
the fruits_id_seq object:

SELECT currval(pg_get_serial_sequence(‘fruits’, ‘id’));

Code language: SQL (Structured Query Language) (sql)

currval

---

2

(1 row)

Code language: SQL (Structured Query Language) (sql)

If you want to get the value generated by the sequence when you insert a
new row into the table, you use the RETURNING id clause in
the INSERT statement.

The following statement inserts a new row into the fruits table and
returns the value generated for the id column.

INSERT INTO fruits(name)

VALUES(‘Banana’)

RETURNING id;

Code language: SQL (Structured Query Language) (sql)

id

---

3

(1 row)

Code language: SQL (Structured Query Language) (sql)

The sequence generator operation is not transaction-safe. It means that
if two concurrent database connections attempt to get the next value
from a sequence, each client will get a different value. If one client
rolls back the transaction, the sequence number of that client will be
unused, creating a gap in the sequence.

In this tutorial, you have learned how to use the PostgreSQL
pseudo-type SERIAL to create an auto-increment column for a table.

创建自增ID后的数据库

修改Sequence的方法：

ALTER SEQUENCE gamersky_id_seq RESTART WITH 1;