Auto Increment in Postgres from Dropwizard Application

Question

I am connecting my dropwizard application with the Postgres db. I am creating tables dynamically at the time of starting the application server.

private static void createTableUser(Connection connection) throws SQLException {
    Statement statement= connection.createStatement();
    statement.executeUpdate("CREATE TABLE USERR"
            +"("+
            "id INTEGER,"+
            "first_name VARCHAR(20),"+
            "middle_name VARCHAR(20),"+
            "last_name VARCHAR(20),"+
            "age INTEGER,"+
            "image_url VARCHAR(250),"+
            "joining_date DATE ,"+
            "country_code INTEGER,"+
            "mobile_no VARCHAR(20),"+
            "email_id VARCHAR(20),"+
            "CONSTRAINT pk_user PRIMARY KEY (id)"+
            ")"
    );
    statement.close();
}

Above is the function creating my table. This way it is working very well. But I want to make id as auto increment. I went through articles which gave suggestion of keyword "SERIAL", "BIGSERIAL", "SMALLSERIAL".

When I was using sql server I was doing it the way

id INTEGER IDENTITY (1)

and here also IDENTITY is available as option but when I tried it I was getting error

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at or near "IDENTITY"

Can anyone help me? Thanks in advance

score 1 · Accepted Answer · answered Oct 31 '17 at 11:57

1

Yeah, use id SERIAL like was suggested.

There is no IDENTITY in Postgres, so using a SERIAL column will automatically create a sequence that is used for filling the value, giving the same exact behaviour.

answered Oct 31 '17 at 11:57

Kayaman

70,361
5
75
119

1

Actually Postgres 10 does have identity columns (the way they are defined in the SQL standard, not the way SQL Server uses them). – a_horse_with_no_name Oct 31 '17 at 12:03
@a_horse_with_no_name ah you're right, and I just read the release notes for 10 too... – Kayaman Oct 31 '17 at 12:07

Auto Increment in Postgres from Dropwizard Application

1 Answers1