I am trying to simply insert JSON data in to a PostgreSQL database. The first column is a serial ID primary key column. When I try to insert the data and omit the serial id value, the serial id value is populated with my first record instead of automatically populating itself.
Example:
CREATE TABLE test (
id SERIAL PRIMARY KEY,
prem_id text,
name text)
Coming from JSON pipeline:
INSERT INTO test VALUES ('1001','Lucy');
INSERT INTO test VALUES ('1002','Johnny');
Table populates as follows:
id | prem_id | name
1001 | Lucy | null
1002 | Johnny | null
If I directly insert from pgAdmin, then I get the same results as above. The only time it works properly is if I add the DEFAULT keyword to the INSERT statement. Just doesn't make sense... All documentation I have read says if you omit this, then it should automatically increment.
I also tried
CREATE TABLE test (
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
...)
But I am getting a syntax error "at or before GENERATED". I'm really stuck on this one.
Anyone else experience this issue or have any fixes for it?
Much appreciated!