By Jimmy


2012-03-26 15:42:40 8 Comments

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?

4 comments

@Erwin Brandstetter 2012-03-26 15:59:37

Postgres 10 or later

serial columns (see below) remain unchanged. But consider an IDENTITY column. Postgres 10 implements this standard-SQL feature.

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

Basic syntax and info in the manual for CREATE TABLE.
Detailed explanation in this blog entry of its primary author Peter Eisentraut.


To add an IDENTITY column to a pre-existing table (populated with rows or not):

ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT 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 BY DEFAULT AS IDENTITY PRIMARY KEY;

There was a bug in early versions, that could lead to an error message like:

ERROR: column "staff_id" contains null values

This was fixed with Postgres 10.2. Details:

Postgres 9.6 or older

Use the serial pseudo data type instead:

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 also use just lower case identifiers in my example. Makes your life with Postgres easier.

And better use descriptive column names. "id" as name is an anti-pattern, used by some middle-ware, but hardly descriptive. Similar with "name".

@Jimmy 2012-03-26 18:01:45

Thanks very much for the help!

@victor n. 2018-05-22 00:50:12

Erwin, you are a life saver!

@Eric Leschinski 2015-04-07 03:13:48

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)

@Royi Namir 2012-03-26 15:44:11

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)
);

@Barry Kaye 2012-03-26 15:47:54

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

Related Questions

Sponsored Content

22 Answered Questions

[SOLVED] Show tables in PostgreSQL

  • 2009-04-20 19:07:39
  • flybywire
  • 1761041 View
  • 1695 Score
  • 22 Answer
  • Tags:   postgresql

18 Answered Questions

[SOLVED] psql: FATAL: database "<user>" does not exist

  • 2013-07-13 19:18:24
  • Ryan Rich
  • 298694 View
  • 639 Score
  • 18 Answer
  • Tags:   postgresql psql

37 Answered Questions

29 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2637636 View
  • 1780 Score
  • 29 Answer
  • Tags:   sql duplicates

8 Answered Questions

[SOLVED] Best practices for SQL varchar column length

19 Answered Questions

[SOLVED] PostgreSQL "DESCRIBE TABLE"

21 Answered Questions

[SOLVED] What's the best practice for primary keys in tables?

0 Answered Questions

Dynamically execute the create table in PostgreSQL

  • 2016-06-02 05:44:07
  • Ajay Jain
  • 288 View
  • 0 Score
  • 0 Answer
  • Tags:   postgresql

1 Answered Questions

[SOLVED] plpgsql function returns table(..)

  • 2010-11-25 18:18:55
  • David Dias
  • 40254 View
  • 15 Score
  • 1 Answer
  • Tags:   postgresql plpgsql

6 Answered Questions

[SOLVED] Auto increment column

Sponsored Content