0

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

Shubham Bhewanewala
  • 587
  • 5
  • 18

1 Answers1

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.

Kayaman
  • 72,141
  • 5
  • 83
  • 121
  • 1
    Actually Postgres 10 does have identity columns (the way they are defined in the SQL standard, not the way SQL Server uses them). –  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