PostgreSQL 10.2+ IDENTITY support (SERIAL replacement)

[gencer]

What version of SQLBoiler are you using (sqlboiler --version)?

v3.0.0-rc10

PostgreSQL introduced GENERATE IDENTITY feature in 10.2 to drop-in replacement for SERIAL.

When a table primary key id generated using an IDENTITY it does not have a default value like SERIAL has which is nextval('test_id_seq'::regclass).

Due to this, ID inserted as 0 all the time and causes duplicate pkey.

So this is an actually a request to support IDENTITY for auto-increment. It will be very handy if we at least add this to 3.0.0-stable.

Workaround

If we do Blacklist for ID then everything goes smooth.

p1.Insert(db, boil.Blacklist("id"))

But this has a downside too. We do not get p1.ID back as it is blacklisted.

Or, manually edit models as below:

Solution

If ID is an IDENTITY remove "id" from messageColumnsWithoutDefault and add it to the messageColumnsWithDefault as follows:

    messageColumnsWithoutDefault = []string{"id", "user_id", "target_user_id", "message", "created_at", "updated_at"}
    messageColumnsWithDefault    = []string{}

should be:

    messageColumnsWithoutDefault = []string{"user_id", "target_user_id", "message", "created_at", "updated_at"}
    messageColumnsWithDefault    = []string{"id"}

[aarondl]

Do we understand how this new feature appears in the information_schema or the "System Catalogs" so we can detect it in the driver?

[gencer]

    select tc.column_name, tc.is_identity
    from information_schema.columns as tc
    where tc.table_name = 'messages' and tc.table_schema = 'public';

result as follows:

column_name is_identity
id              YES
user_id         NO
target_user_id  NO
message         NO
created_at      NO
updated_at      NO

there is an is_identity property (column) in information_schema.columns. This provides which columns are identity.

I've sent a PR. This PR will add identity support along with check if server has is_identity column. If we do not check this field, it may lead to error on older postgresql versions.

[aarondl]

Fixed in #357