Can't use "GENERATED ALWAYS AS IDENTITY" when creating Postgres tables on Dbeaver?

Question

I am using Dbeaver to create a Postgres database table but am getting a syntax error when using "GENERATED ALWAYS AS IDENTITY" for my incremented id value. It is strange because I used the exact same syntax when creating the table on my localhost and had no problem with any syntax errors or creating the table.

This is the SQL preview I have when attempting to save the table:

CREATE TABLE public.conversation (
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
    startdatetime timestamptz NOT NULL,
    enddatetime timestamptz NOT NULL,
    CONSTRAINT conversation_pk PRIMARY KEY (id)
);

When I try to save the table, I get "ERROR: syntax error at or near 'GENERATED'". I thought this was correct syntax considering the SQL is built by Dbeaver itself and it worked fine when creating a local database to test on?

@a_horse_with_no_name PostgreSQL 9.5.17 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.10) 5.4.0 20160609, 64-bit — ronhoward, Jun 25 '19 at 19:05
Identity columns were introduced in Postgres 10, you can't use them in (the somewhat outdated) Postgres 9.5 — , Jun 25 '19 at 19:07
@a_horse_with_no_name so what should I do as an alternative? — ronhoward, Jun 25 '19 at 19:08
So will the type be serial in other tables that reference the id as well? — ronhoward, Jun 25 '19 at 19:09
no, that would be regular integer - just like with the identity column — , Jun 25 '19 at 19:11

score 2 · Answer 1 · answered Jun 25 '19 at 18:11

2

Just use bigserial:

CREATE TABLE public.conversation (
    id bigserial primary key,
    startdatetime timestamptz NOT NULL,
    enddatetime timestamptz NOT NULL
);

answered Jun 25 '19 at 18:11

Gordon Linoff

1,242,037
58
646
786

a `bigserial` behaves a bit different to a `generated always as identity` – Jun 25 '19 at 18:48
@a_horse_with_no_name . . . They are similar enough if for some reason the OP cannot use `generated always as identity`. – Gordon Linoff Jun 04 '20 at 12:50
2

absolutely, that's why I suggested to use it in my comments to the question itself. I just wanted to point out, that there are slight differences. – Jun 04 '20 at 12:50

Can't use "GENERATED ALWAYS AS IDENTITY" when creating Postgres tables on Dbeaver?

1 Answers1