Imagine that we have the following table using jsonb on PostgreSQL: And the following data is available: Is it possible to expand the JSON array to 2 columns similarly to what PowerBI expand does and get the following result? firstName surName bookName William Shakespeare Hamlet William Shakespeare Juliet Aga…
Tag: postgresql
Select last nontrivial value from each column, by group
How might I write in SQL a query that accumulates a table of rows representing selective updates into a single “latest and greatest” row per BY group? For instance from the following table: index date ssn first last title car shoe 1 Apr 1 100-00-0001 Joseph Schmoe Analyst Honda Adidas 2 May 1 100-…
convert varchar[ ] column type to uuid[ ]
there is a column of type character varying[] , which contains data of type uuid like {0f071799-e37e-4e1c-9620-e580447416fe,913a7134-6092-45fa-ae18-163302db8112}, but there are also some old values of another type like {5edfd4edfa1bb21a142442a0}. How can the column type be converted? I used the script: alter …
First row of results should never be displayed
DB-Fiddle Expected Result In the results I want to display the quantity for all dates except for the first row. I know in the example above I can do this with a simple WHERE-condition like this: However, it is neither guaranteed that the first time_stamp will be always 2020-01-14 nor that the MIN(2020-01-14) …
Postgres – Add a column to a table with a default value, but set existing rows to a different default value
I want to add a boolean column to a table and have the default value be false for newly created rows, but all existing rows should be set to true. How can I do that? Answer First alter table and add column as alter table table_name add column column_name boolean default false; Then update value of that column…
Adding constraint in PostgreSQL that requires information from another table
I have the following schema in PostgreSQL How can I enforce the constraint that studentmarks.marks_scored <= exam.total_marks such that the behaviour is just like the CHECK constraint? Answer Use trigger. You need to create trigger function first. Then create trigger. NOTE I have tested in postgres
Calculate total difference of values between two timestamps
DB-Fiddle Expected Result: I want to calculate the total difference of the quantity between two timestamps. Therefore, I tried to use the query from this question: However, I am not able to get the expected result. What do I need to change to make it work? Answer The partition by clause in the lag call of you…
postgresql – condition met in a time interval
I have the following table and I want to create the condition_met column. The condition_met column is my expected output. Condition_met is TRUE when type_id = 34 and within 5 seconds after this type_id, the client_id of type_id = 34 becomes also prospect_id. To say it differently: for each type_id = 34 the cl…
update rows using inner join posgresql
I have 2 tables users and countries, and i’m trying to set the country.name with users.country_name for all countries with null name. Schema : Ref: U.country_code > countries.code QUERY Error : DBSQLException: SQL Error [42P01] Answer Follow the correct update join syntax and also remove the alias fr…
Add join and search in columns for any string
I have these Postgres tables: I use this SQL query: How I extend this SQL query to search also in table employees by employee id and filter the result by any character found in table columns first_name and last_name? Answer You can join the new table using employee_id: