Assign to NEW by key in a Postgres trigger; Assuming (since it's not defined in the question) that people.id is a serial column with an attached sequence, you'll want the next value from the sequence. We can determine the sequence name with pggetserialsequence. Details: PostgreSQL SELECT primary key as 'serial' or 'bigserial'. PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns.These are similar to AUTOINCREMENT property supported by some other databases. If you wish a serial column to have a unique constraint or be a primary key, it must now be specified, just like any other data type.

• Auto-generated Definition
• Postgresql Auto Generated Primary Key Examples
• Postgresql Auto Generated Primary Key
• Postgresql Add Auto Increment Primary Key
• Postgres Auto Generated Uuid Primary Key
• Postgresql Add Primary Key Constraint

• PostgreSQL Tutorial

• PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns.These are similar to AUTOINCREMENT property supported by some other databases.
• How to get primary key value (auto-generated keys) from inserted queries using JDBC? Description: When we are inserting a record into the database table and the primary key is an auto-increment or auto-generated key, then the insert query will generate it dynamically.
• Dec 08, 2017 Database Research & Development: PostgreSQL 10 introduced the concept of IDENTITY COLUMN which used for creating an auto-increment column, and it is also good then SEQUENCE Object.

• Advanced PostgreSQL

• PostgreSQL Interfaces

• PostgreSQL Useful Resources

• Selected Reading

PostgreSQL has the data types smallserial, serial and bigserial; these are not true types, but merely a notational convenience for creating unique identifier columns. These are similar to AUTO_INCREMENT property supported by some other databases.

Auto-generated Definition

If you wish a serial column to have a unique constraint or be a primary key, it must now be specified, just like any other data type.

The type name serial creates an integer columns. The type name bigserial creates a bigint column. bigserial should be used if you anticipate the use of more than 2³¹ identifiers over the lifetime of the table. The type name smallserial creates a smallint column.

Syntax

The basic usage of SERIAL dataype is as follows −

Example

Postgresql Auto Generated Primary Key Examples

Consider the COMPANY table to be created as follows −

Hide my ip serial key generator. Now, insert the following records into table COMPANY −

This will insert seven tuples into the table COMPANY and COMPANY will have the following records −

Tom Lane wrote:
> Russell Shaw <rjshaw(at)iprimus(dot)com(dot)au> writes:
>
>>Is a long field ok for a primary key, or should i use
>>a unique integer?:
>
>
>>create table parts (
>> manufacturers_code char(40) primary key,
>> description char(40),
>> man_id int references manufacturers (man_id)
>>);
>
>
> There's nothing wrong with using a character field as primary key,
> but I'd advise you to think twice about defining it as char(40).
> Almost certainly you want varchar(40), so as not to be wasting huge
> amounts of space on padding blanks.

I'd assumed databases would save the number of blanks needed in any
position, and restore/add the blanks when the field was retrieved.

> For that matter, where did
> the '40' come from in the first place? Is there a really good
> application-driven reason to limit the codes or descriptions to 40
> characters? If your answer reveals that the number was picked out
> of the air, I'd suggest dropping the constraint entirely. Text or
> unconstrained varchar is a better choice, though it's not completely
> SQL-standard.

I assumed that for some reason it would be faster or more efficient
than something of unfixed length or else why does it exist? Maybe it
is only kept for compatability purposes?

Postgresql Auto Generated Primary Key

Postgresql Add Auto Increment Primary Key

> One thing you should think carefully about before using externally
> supplied data as a primary key is 'what happens if the manufacturer's
> code changes'? You'll have to update not only this table, but all
> references to it from other tables. It's usually considered good
> practice to choose primary keys that will *never* change, and that
> essentially means that they can't have any externally-imposed meaning.
> The popularity of auto-generated serial numbers as primary keys comes
> from this consideration.

Postgres Auto Generated Uuid Primary Key

That is something hard to decide on. If a part becomes obsolete, i
thought maybe i could fill in an 'obsolete' field so that any report
that is generated using it will alert me to change to a new part in
anything that uses it.

Postgresql Add Primary Key Constraint

I'm using postgresql from php script embedded in html (apache on debian, etc).