I have a table Item: id | price —+——– 1 | 20000 2 | 30000 3 | 40000 4 | 43000 How to update the price column id(1) = 1000, id(2) = 2000 in a single SQL query?
Tag: postgresql
Can PostgreSQL JOIN on jsonb array objects?
I am considering switching to PostgreSQL, because of the JSON support. However, I am wondering, if the following would be possible with a single query: Let’s say there are two tables: Table 1) organisations: Table 2) users: Now I want to get a result like this (all i have is the ID of the organisation […
showing columns in a particular pattern in postgresql
i have written a query which returns a table showing monthly total working hours of each person in the company. so the result is: name*****jan************feb**********march ……… dec Tom 170:…
How to order the query result based on following condition?
Let’s suppose I have a table as below: employee | period | commission A | 1Y | 100 A | 2Y | 150 B | 1Y | 80 C | 1Y | 200 C | 2Y | 270 C …
Postgres convert date time difference into HH:MM:SS
Ex: Diff is 0 years 0 months 5 days 20 hours 6 minutes 30 seconds. I want to convert this into HH:MM:SS format : 120:06:30. Answer tl;dr java.time Doing it on the Java side is simple with a column of a data type akin to the SQL-standard TIMESTAMP WITH TIME ZONE and driver compliant with JDBC 4.2 or later (for
SQL – Fetch associated data from multiple table
I have 4 tables. The table does not contain any foreign key reference t1: | id | name | +—–+—————-+ | a1 | cheese | | a2 | butter | | a3 | milk | …
Postgresql update specific fields based on a value (trigger)
I am writing a Trigger in Postgresql to update dynamically the content of specific columns of a revenues table based on inserts on a consultations table. [edit] Here is how my tables look like …
Very slow (12+ hours) large table joins in postgres
I am struggling to optimize a simple LEFT JOIN against two very large tables that so far has been taking > 12 hours to complete and ongoing. Here is the execution plan: Gather (cost=1001.26…..
Why does adding ORDER BY drastically speed up query?
I’ve discovered some very strange and counter-intuitive behaviour in PostgreSQL. I have a query structure as follows. I am selecting both the IDs and the count from a subquery. The subquery does the …
MS-Access Query to PostgreSQL View
I am converting a microsoft access query into a postgresql view. The query has obvious components that I have found reasonable answers to. However, I am still stuck on getting the final result: …