SQL Upsert does not allow for default values

I'm running into an issue that I believe is a bug. I'm using the "Update a record, or create a new record if it doesn't exist" (Upsert) feature on a Posgres table that has an auto incremented primary key.

The primary key (id, int) is what i'm using to filter the table. When the id is not found and the record should be created, retool passes in "NULL" as the id.

As of right now the query running is

INSERT INTO "foo" ("id", "name") values (NULL, "TEST")

Please correct me if I'm wrong, but I believe the expected be behavior should be one of the following:

  1. Do not pass in "filter by" fields when inserting a record
-- Example
INSERT INTO "foo" ("name") values ("TEST")
  1. Allow for the "DEFAULT" keyword on insert/update (see example below)
-- Example
INSERT INTO "foo" ("id", "name") values (DEFAULT, "TEST")
  1. (complex) recognize that the type is "serial" and look at the nex_val

Any help would be appreciated! For now, we'll just create two queries (update and create) and avoid upsert. Thanks!

Hey Cameron,

What SQL database are you using? If I recall correctly inserting a NULL value to an auto increment field for a mysql database should auto increment though it won't work on psql. That is not to say that it isn't a problem so I will do a bit more digging to see if I can repro this for a psql database and file a bug report if it is indeed the case. In the mean time, you can also write your own sql insert statement by choosing SQL mode in the drop down for the query editor (I assume you are currently using GUI mode for your query editor).

Using PSQL 11. I'm not sure how MySQL behaves with NULL on auto incremented fields, but I know postgres expects DEFAULT or nothing at all.

As a work around, I replicated an upsert with a few queries. So not blocked, but the fix would be great for the future.

Were you able to reproduce the issue and file the bug report?

Thanks @chenbill

Thank you and yes I was able to reproduce this and I will file a report today.

Thanks again for this @cameronmoreau