Syntax error AUTO_INCREMENT

Question

I need to create a table with an auto increment primary key so I do:

String sql = "CREATE TABLE Home" +
                       "(id INTEGER not NULL AUTO_INCREMENT PRIMARY KEY, " +
                       " name VARCHAR(255), " + 
                       " value DOUBLE PRECISION, " + 
                       " data DATE " + 
                       " )"; 

The problem is that when I executee this query I obtain:

Syntax error AUTO_INCREMENT

Anyone can help me?

Answer 1

There is no auto_increment in PostgreSQL. Use serial instead.

Your create table statement should be:

create table Home (
   id serial not null primary key,
   name varchar(255),
   value double precision,
   data date
);

Here's updated documentation on full set of PostgreSQL data types.

Take a look at this DB-FIDDLE with the exact same working example.

Answer 2

If you are using PostgreSQL v10 or later, you might want to use the standard conforming identity column syntax:

CREATE TABLE home (
   id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
   name varchar(255),
   value double precision,
   data date
);

Apart from standard compliance, it has the advantage that you get an error message if you try to explicitly add a value for the id column, which is usually a mistake that will lead to collisions with future auto-generated values.