Given this table, which is just an example. I need to do an upsert on multiple records. payment_status returns as ambiguous. The problem I’m trying to solve here is that I have a process that imports data but if it encounters a conflict, it needs to update the current data with the new data. (it’s…
Tag: postgresql
pgsql merge two json arrays
I have to select two type of result set from a table and merge them in single json array like Its saying syntax error I believe due to order by. I need to return this in json array so I am trying like this Its retiring two json arrays i.e. obvious, How can I make this in single array Thanks,
PostgreSql Right Join From 3 Tables [closed]
Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers. This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers. Closed las…
Google BigQuery Federated Query connection Error
I am trying to use the Federated Query functionality in BigQuery to query some data from my Cloud SQL Postgres DB. Unfortunately I get the following error: It is quite similar to the error discussed here, but it was not solved (the word that appears in the quotes is different though): Connection Error while r…
Percentage calculation in SQL
I have some cars in a table along with the year they came out, and I’m trying to calculate the percentages of the cars that belong in the 4 following age groups: 0-4, 5-9, 10-19, 20+. I tried to do this in 3 different ways: 1. I got this error: I got this error: I got this error: Any ideas
Name of customers who are of legal age (18 years) Postgresql
I have a question about how to return only customers over 18 years old. I’m just getting back from the customers Answer If you want to filter the rows, use a where clause. If you want to filter by age, you can use date comparisons: Note that phrasing the condition this way makes it friendlier to indexes…
Postgres on Conflict Update With Default Column Expression
In case a column has a default value expression Is it possible for on conflict statement reset the column with the default expression without copy-paste the expression it self using default as a value while composing excluded record it self I.e. just to refer the default exactly from the update statement. Som…
How to group by fields which have specific value in SQL?
I have the following columns in my table (I’m using postgresql): parent, child, status. Each parent has 1 or 2 children. I want to select all parents whose first child has status x and second child has status y or in case there’s only 1 child than parent with child with status x. For example, a ta…
Updating a table using values in previous rows and then using the outputted value in the next calculation
Month Value a b June 400 50 20 July NULL 25 10 August NULL 50 20 I want to forecast future months using existing values. I need to update the NULL values for July using the calculation 400 + 50 – 20 and obtain the value 430. I then need to use July’s value of 430 in a calculation (430
How to achieve conditional array aggregate?
For simplicity I’ll use the following patient and appointment tables. What I want is one report that contains the following data. So what I need to do is: Get the patient names from patient table aggregate all appointment statuses of the patient aggregate all appointment times of appointments that have …