I'm trying to use INSERT as a subquery but I'm not sure if that's possible. it's a little difficult to google because of all of the questions asking about subqueries inside INSERT
in a nutshell, I have some rows e.g. ```(1, 3), (4, 2), (null, null), (3, 7), (null, 3)``` and I want to ```INSERT``` the sum of the two values somewhere and have the query return results like this: ```(4), (6), (null), (10), (null)```
oh dear I missed the edit time, sorry. Better ask a question
I'm not sure how exactly to title this question. Something like: "Include non-inserted rows as result of INSERT INTO ... RETURNING" ? any better ideas?
or potentially "INSERT INTO inside CASE" (though this is a little more XY)
@Colin'tHart I'm doing it because I have a bunch of data that I'm importing column-wise where there are some queries (like this) which depend on the results of previous "row-wise" queries
if, for a particular row, a query fails, a query inspect a later column needs to know
I'm using PostgreSQL 11.
I want to conditionally insert values into a table while having the result of the insertion include a null for each row of the input which did not result in an insertion.
For example
CREATE TABLE all_sums (sum INTEGER);
SELECT
CASE WHEN a_sum IS NULL THEN null
...
From the post:
I heard feedback from a significant number of Stack Overflow employees that they avoided [posting] on Meta because of the reaction it garnered. Though this was not felt by all staff who interacted on Meta, and it affected different people to varying degrees, many felt discourag...
Does anyone know how to select the only integer attribute value from a function into a variable declared in a trigger? For some reason I'm getting null instead of an integer. paste.ofcode.org/EyvhSkSHkTerL6VE8H4XWD
You could use dbfiddle.uk to setup a reproducible example. create the tables, the functions, the triggers and run them so we can see the error and make changes. Just copy your code there and give us the link.
Second, is this trig_assign_rider() a trigger for updates on table Orders? If yes, why does it try to the whole table again?
If the aim is to change the value of the rider column in the same row that is updated, you need something much simpler
elsif NEW.order_timestamp is not null then
NEW.rider :=
select T.rider
from find_highest_priority_rider(NEW.order_timestamp) T;
return NEW;
end if;
or just, since the function returns an integer
elsif NEW.order_timestamp is not null then
NEW.rider := select find_highest_priority_rider(NEW.order_timestamp) ;
return NEW;
end if;
You have that where Orders.id = NEW.id; in the update which I thought it means you want to change the value of rider in the very same row you are updating.
Both OLD and NEW are virtual rows, the same row you are updating. OLD has the values before the update, NEW has the values that will replace the old ones.
You could use dbfiddle.uk to setup a reproducible example. create the tables, the functions, the triggers and run them so we can see the error and make changes. Just copy your code there and give us the link.
I asked a question, a user flagged it as an appropriate duplicate of another question, and I accepted the question as a duplicate. My question now has the "This question already exists" banner, but the link to the older question is missing from the banner. Is this a bug?
For reference this is ...
Here's what I see when I open this question
Notice there's no link to the dupe after "This question already exists". The comment with the link is also gone. This looks like a bug, as the question as it is now is entirely useless.
@PrashinJeevaganth I remember a nice book about database design patterns that might suit for such questions but I can't remember the name. And it is about 3000 miles away ...
some proactive developer went ahead and made everything non-nullable in the model and then handled unknown values in app code by inserting the empty string
I'm using PostgreSQL 11.
I want to conditionally insert values into a table while having the result of the insertion include a null for each row of the input which did not result in an insertion.
For example
CREATE TABLE all_sums (sum INTEGER);
SELECT
CASE WHEN a_sum IS NULL THEN null
...
From the post:
I heard feedback from a significant number of Stack Overflow employees that they avoided [posting] on Meta because of the reaction it garnered. Though this was not felt by all staff who interacted on Meta, and it affected different people to varying degrees, many felt discourag...
Here's what I see when I open this question
Notice there's no link to the dupe after "This question already exists". The comment with the link is also gone. This looks like a bug, as the question as it is now is entirely useless.
