SQL Upsert does not allow for default values

Queries and Resources
1

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!

2

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).

3

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

4

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

Thanks again for this @cameronmoreau

5

Any update on this? I'm having an issue with this as well

6

Hey @derrickmar! There are no updates here right now, but I added your +1 to this bug report and attached it to this thread! :grinning_face_with_smiling_eyes: I am going to close this thread for now, but I'll reopen it if I have any updates. Please don't hesitate to reach out if you hit any roadblocks, in the meantime. Chat soon :wave:

7

Update here, a team member informed me that the bulk upsert action should handle this case:

1464×786 58 KB

8

Hi everyone,

I tried the "Bulk upsert via a primary key", however it is not working for me.

I am trying to read a csv file and to update a postgre table with the read content of it. The uid is set to auto increment.
I tried

  • input csv without the uid,
  • input csv with an empty uid

image
image1940×1155 106 KB

Is it me doing something wrong? Or does the Bulk upsert via a primary key not work as expected?

1 Like
9

Hey @Korbinian123, the error message is saying that the uid that you set as the primary key is empty. Is that field supposed to just be id?

1842×366 50.1 KB

Also, could you share a screenshot of your left panel expanded out to show the table.data as much as possible?

772×128 6.46 KB

666×1558 65.7 KB

10

@Jay How can I set multiple??? I can only set one column as PK

11

Hi @Jay ,
thanks for the quick reply.
Here is my screenshot:

image
image1600×890 124 KB

In my understanding, the error message is saying that the uid is not unique due to that both are undefined ...

My goal is, that I insert the upper table (my csv upload) in my postgre table. The uid is a primary key which is set in the postgre table to auto increment. So in my understanding I do not have to provide a uid as it auto increments itself.