Auto increment table column

Question

Using Postgres, I'm trying to use AUTO_INCREMENT to number my primary key automatically in SQL. However, it gives me an error.

CREATE TABLE Staff   (
  ID        INTEGER NOT NULL AUTO_INCREMENT,
  Name      VARCHAR(40) NOT NULL,
  PRIMARY KEY (ID)
);

The error:

********** Error **********
ERROR: syntax error at or near "AUTO_INCREMENT"
SQL state: 42601
Character: 63

Any idea why?

Shannanshannen

Which database do you use? "AUTO INCREMENT" exists only in a handful. In Oracle for example you need to create a SEQUENCE. – Desiccator

Use Serial: error-with-auto-increment-psql-putty – Hysterotomy

Answer 1

Postgres 10 or later

Consider a standard-SQL IDENTITY column. serial columns remain unchanged. (See below.) But the former is preferable in modern Postgres. Can be GENERATED BY DEFAULT or (stricter) GENERATED ALWAYS.
Basics in the manual for CREATE TABLE.
Details in this blog entry by its principal author Peter Eisentraut.

Create table with IDENTITY column

CREATE TABLE staff (
  staff_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, staff    text NOT NULL
);

Add IDENTITY column to existing table

Table may or may not be populated with rows.

ALTER TABLE staff ADD COLUMN staff_id int GENERATED ALWAYS AS IDENTITY;

To also make it the PK at the same time (table can't have a PK yet):

ALTER TABLE staff ADD COLUMN staff_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY;

See:

• How to add a PostgreSQL 10 identity column to an existing table with rows?

Replace serial with IDENTITY column

See:

• How to change a table ID from serial to identity?

You can override system values or user input in INSERT commands with OVERRIDING {SYSTEM|USER} VALUE.

Postgres 9.6 or older

(Still supported in newer versions, too.)
Use the serial pseudo data type:

CREATE TABLE staff (
  staff_id serial PRIMARY KEY,
, staff    text NOT NULL
);

It creates and attaches the sequence object automatically and sets the DEFAULT to nextval() from the sequence. It does all you need.

I use legal, lower-case, unquoted identifiers in my examples. Makes your life with Postgres easier.

Industrials

Answer 2

You do not specify which RDBMS you are using, however, in SQL Server you can use this syntax:

CREATE TABLE [dbo].[Staff]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] VARCHAR(40) NOT NULL,
CONSTRAINT [ID] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Vibraculum

Answer 3

In the SQL server database you can use Identity(1,1) like this:

CREATE TABLE Staff
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name VARCHAR(40) NOT NULL,
    PRIMARY KEY (ID)
);

Undirected

Answer 4

PostgreSQL: If you absolutely must have your own auto increment value:

Then use a sequence:

ericlesc_schools=> drop table yar;
DROP TABLE
ericlesc_schools=> drop sequence user_id_seq;
DROP SEQUENCE
ericlesc_schools=> create sequence user_id_seq;
CREATE SEQUENCE
ericlesc_schools=> create table yar(
                   id int default nextval('user_id_seq'), 
                   foobar varchar);
CREATE TABLE
ericlesc_schools=> insert into yar (foobar) values('hey alex');
INSERT 0 1
ericlesc_schools=> insert into yar (foobar) values('hey what derick');
INSERT 0 1
ericlesc_schools=> insert into yar (foobar) values('I look like a hushpuppy');
INSERT 0 1

ericlesc_schools=> select * from yar;
 id |     foobar      
----+-----------------
  1 | hey alex
  2 | hey what derick
  3 | I look like a hushpuppy
(3 rows)

Gabbey