Auto Increment in Postgres from Dropwizard Application [duplicate]

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

Kayaman · Accepted Answer · 2017-10-31 11:57:46Z

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, 2017 at 11:57

Kayaman

73.8k6 gold badges91 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

Actually Postgres 10 does have identity columns (the way they are defined in the SQL standard, not the way SQL Server uses them).

Kayaman Over a year ago

@a_horse_with_no_name ah you're right, and I just read the release notes for 10 too...

Collectives™ on Stack Overflow

Auto Increment in Postgres from Dropwizard Application [duplicate]

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related