How do I update table1 with results of this query – I want to do something like this – Answer This question has been answered many times in SO… anyway, the query should be built this way One option Other option Surely there are other additional ways…
Tag: postgresql
How do I join two tables based on a minimum value in the first table?
I have two tables: one has a list of weather stations near major cities and the distance to the city, and the other has weather averages for each weather station. I want to do a join such as showing weather data from the station closest to San Francisco. Example table distances: Example table weatherdata I…
How to convert generic XML to a table row in PostgreSQL?
PostgresSQL v12.5 There is a table with single column containing strings formatted as XML. For simplicity let’s claim that there are no nesting: all tags inside <something> contain primitive values (strings). Assuming that there are much more elements than <a> and <b> inside, it would …
SQL syntax to achieve the “opposite” of LIKE
I’m very rusty on the little SQL I once learned, and while I totally get SELECT column1 FROM table WHERE column2 LIKE pattern, what I need is: For example, I might have a product family table which has information about the Coca Cola range, and for which I might store coca% in column2 ready to match spe…
Getting row samples from PostgreSQL
I have a data table with timestamp and some data columns. The rows are inserted in arbitrary time intervals, i.e. the timestamp difference of consecutive rows is not stable but ranges from several seconds to several hours. I need to select one row per time interval of constant length, if there is any. Example…
How can I show values with zero in a Group By query
I am trying to get the zero values from my status table associated with the case of one user. So, I have this query that returns me only one value because, I have only one case associated with one status but I want to receive also the another status with value 0. So I have the status ‘Open’ and &#…
PostgreSQL UPSERT (INSERT … ON CONFLICT UPDATE) fails
I have a row in my postgresql database that I want to update. My code generates this SQL statement to insert else update the one field that’s changed: What I don’t understand is that I can select that one row and it is present with a non-NULL num_syncs. But the UPSERT is failing because it doesn&#…
How do I return a row with 0 if the group by has 0 records?
I have this alert_levels table: Then I have this alerts table The alert_level_id in the alerts table is a foreign key of id from the alert_levels table. What I want is to count the number of occurences of each alert_type grouped by the alert_level_id whithin a chosen time period. And if there is no occurency …
Copy table from one database to another in multiple threads in C++
There is a huge SQL table in Postgres Database. I’d like to copy the contents of it to another different database (SQL Server) using C++. I have written a single-thread application and it works fine. I decided to use multiple threads to increase the performance of reading and writing data. Here in the c…
Postgres recursive queries and window functions to produce tree from table
Let’s say I have the following data. I got the request to produce a table with a single column in which categories and subcategories appear arranged like this. I don’t understand how to complete the task with the requirements established by my client (especially the first one). These are: I should…