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 Agatha Christie Hercule Poirot Agatha Christie Miss Marple instead of firstName surName
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-00-0001 Joe 3 May 16 100-00-0001 Nike
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 table services alter column office_ids type uuid[] USING office_ids::uuid[]; but gives an error – invalid syntax for type uuid:
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) is always in the first row. Therefore,
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 as update
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 your query is redundant.
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 client_id that performed action type_id = 34
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 from the LHS of the SET clause:
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: