postgresql的自增长id(获取最后插入id)

FAQ: Using Sequences in PostgreSQL

Many of the questions asked in #postgresql revolve around using sequences in PostgreSQL. To avoid answering the same questions again and again, I thought it would be worthwhile to summarize the basic steps involving in using sequences in PostgreSQL.

What Is A Sequence?

A sequence is a special kind of database object designed for generating unique numeric identifiers. It is typically used to generate artificial primary keys. Sequences are similar, but not identical, to the AUTO_INCREMENT concept in MySQL.

How Do I Use A Sequence In A Table?

Sequences are most commonly used via the serial pseudotype. A serial is a special data type that encodes the following information:

• it indicates that the values for the column will be generated by consulting the sequence
• therefore, it creates a new sequence object, and sets the default value for the column to be the next value produced by the sequence
• since a sequence always produces non-NULL values, it adds a NOT NULL constraint to the column
• since the sequence that is produced is created "behind the scenes", PostgreSQL assumes that the sequence is only used to generate values for the table containing the serial column. Therefore, if this column is dropped, the sequence will be automatically removed.

For example, this command creates both a new table and a new sequence generator, and associates the sequence with the id column of the table:

test=# CREATE TABLE users (
test(#     id    SERIAL, -- assign each user a numeric ID
test(#     name  TEXT,
test(#     age