Postgresql – “INSERT INTO res_partner () VALUES ()” failure (autoincrement in postgres)

auto-incrementinsertpostgresql

This is DDL for the table:

CREATE TABLE public.res_partner (
    id serial NOT NULL,
    CONSTRAINT res_partner_pkey PRIMARY KEY (id)
);

This is SQL:

insert into public.res_partner () values ();

and the error:

SQL Error [42601]: ERROR: syntax error at or near ")"
  Position: 33

$ psql --version
psql (PostgreSQL) 11.1 (Ubuntu 11.1-1.pgdg18.04+1)

I see that this statement is absolutely legal. My code worked 2-3 days ago (PHP with Doctrine). But now I cannot execute this statement from dBeaver client and from my app.

Best Answer

Your linked question is for MySQL which doesn't really have a track record for valid standard SQL - and having an empty column list or empty values list is far from being "legal" syntax in standard SQL or Postgres.

If you want to insert the default values you have two choices

insert into public.res_partner (id) values (default);

Or if you do not want to specify the column name, use:

insert into public.res_partner
default values;

Related Solutions

Postgresql – Postgres: How to insert row with autoincrement id

Apparently you inserted rows into that table without using the sequence and that's why they are out of sync.

You need to set the correct value for the sequence using setval()

select setval('context_context_id_seq', (select max(context_id) from context));

Then the next call to nextval() should return the correct value.

If the column is indeed defined as serial (there is no "auto increment" in Postgres) then you should let Postgres do it's job and never mention it during insers:

insert into context (some_column, some_other_column)
values (42, 'foobar');

will make sure the default value for the context_id column is applied. Alternatively you could use:

insert into context (context_id, some_column, some_other_column)
values (default, 42, 'foobar');

pg_dump Restore – Using psql with –disable-triggers

@dezso had the completely right idea:

All this means that some data has been updated, right? Try to update them back, using a temp table where you copy the original data

The only thing left now was to make it happen.

So here's what I did. I took a leaf out of his book and manually edited the dump-file to use a table named table_backup. Then I created said table using the definition provided in my pgAdmin (but it can be done manually, too).

I left out triggers and constraints, as well as Foreign Keys, and then proceeded to "updating" the original table with the data from the backup table like the following:

BEGIN TRANSACTION;
ALTER TABLE table DISABLE TRIGGER ALL;

UPDATE table SET 
    (column1, column2, ...) = 
    (table_backup.column1, table_backup.colum2, ...)
FROM table_backup WHERE table.pk_column = table_backup.pk_column;

ALTER TABLE table ENABLE TRIGGER ALL;
-- I didn't but you can drop table_backup here
COMMIT;

So I am finally back with my original data, ready for the next testrun ;)

Best Answer

Related Solutions

Postgresql – Postgres: How to insert row with autoincrement id

pg_dump Restore – Using psql with –disable-triggers

Related Question