Kristian Notari Comments - Answer Overflow

Kristian Notari

Explore posts from servers

•Created by Kristian Notari on 3/6/2024 in #help

Static/reusable custom window functions

Also, I noticed there's no way to accomplish the following if I'm right:

array_agg("c1" order by "c2" asc)

array_agg("c1" order by "c2" asc)

cause the orderBy bit is only available after applying a over partitioning. Is that correct?

3 replies

KKysely

•Created by Kristian Notari on 2/28/2024 in #help

On Conflict do update set ALL to be inserted columns

Maybe by adding a doUpdateSetAllFromExcluded

5 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

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

16 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

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;

16 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

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?

16 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

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?

16 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

I agree that sounds difficult to fix without reworking a ton of stuff or adding some special nuances to the on conflict bit

16 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

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

16 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

That caused the error

16 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

So I ended up asking for non primitive values in the insert and update types of the column

16 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

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

16 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

16 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

16 replies

KKysely

•Created by Kristian Notari on 9/21/2023 in #help

Error "isSelectQueryBuilder" while using doUpdateSet on conflict

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

16 replies

Gaming

Programming