Kysely•15mo ago

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

Hello, I get an error when dealing with an update object factory for doUpdateSet expression during an on conflict statement for an insert. From my experiments, if the select type of the column is different (not assignable to) the insert/update types than something breaks at the type level and gives me an error Repro: https://kyse.link/?p=s&i=Wt7lre2AUUH7EdAInOja

6 Replies

Kristian NotariOP•15mo ago

This works fine instead, if the select type matches the insert/update type: https://kyse.link/?p=s&i=6fFLOsEIQ2yBNiKmGbt3

Kristian NotariOP•15mo ago

Kristian NotariOP•15mo ago

koskimas•15mo ago

eb.ref('excluded.updated_at') takes the select type, but updated_at expects the update type. That's an issue but I don't know how to fix it. You can cast the reference using $castTo https://kyse.link/?p=s&i=PYUhQEBHqUtsfTmer4pR I'd configure the driver to always rerturn strings for dates. The Date type causes so many issues. You can also fix this by making update and insert accept Dates https://kyse.link/?p=s&i=blW4EyWoOoa4tSYc1aA3

Kristian NotariOP•15mo ago

My specific usecase doesn’t have Date and string. I have a jsonb column and pg underneath automatically transform that on selections to a json-like data structure which comes handy. But when updating/inserting, if it comes from json-like primitive data structure like a string or a number it doesn’t get encoded back to json on the pg level, so the database is getting a normal varchar/text parameter which if not properly json encoded will throw an error trying to work with jsonb columns So I ended up asking for non primitive values in the insert and update types of the column That caused the error I’m assuming since eb.ref reference a column for example that you grab the select type of the given in memory table where the referenced column is, the excluded table in this case I agree that sounds difficult to fix without reworking a ton of stuff or adding some special nuances to the on conflict bit From my understanding whenever you’re working with in database operations like the update set clause within an on conflict the type of the value you expect needs to be the update one. But eb.ref get the select one coming from the excluded.col right? Wait a second, the excluded table get added in the db type for the onConflictBuilder based on the schema of the to be modified table, but it should really be typed based on the actual values table I'm putting in the values expression, or not? Also, giving the ability to reference excluded columns which are not part of the values expression result in NULL being set for those columns during the do update set, which is not what you'd expect it to do I guess. As an example:

-- create
CREATE TABLE EMPLOYEE (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT
);

-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');

-- fetch 
SELECT * FROM EMPLOYEE;

-- THIS OVERWRITE THE dept COLUMN WITH null !!!!
insert into EMPLOYEE(id, name)
VALUES (3, 'Matt')
on CONFLICT (id) DO UPDATE SET name = excluded.name, dept = excluded.dept;

select * from EMPLOYEE;

-- create
CREATE TABLE EMPLOYEE (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT
);

-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');

-- fetch 
SELECT * FROM EMPLOYEE;

-- THIS OVERWRITE THE dept COLUMN WITH null !!!!
insert into EMPLOYEE(id, name)
VALUES (3, 'Matt')
on CONFLICT (id) DO UPDATE SET name = excluded.name, dept = excluded.dept;

select * from EMPLOYEE;

https://onecompiler.com/postgresql/3zna9hgs6

Igal•15mo ago

what if we introduce $castAsUpdateType and $castAsInsertType ?

Gaming

Programming

Error "isSelectQueryBuilder" while using doUpdateSet on conflict