Advertisements I have a table CREATE TABLE IF NOT EXISTS club.climbers ( climber_id SERIAL PRIMARY KEY, climber_first_name VARCHAR(20) NOT NULL, climber_last_name VARCHAR(30) NOT NULL, climber_full_name TEXT GENERATED ALWAYS AS (climber_first_name || ‘ ‘ || climber_last_name) STORED NOT NULL, sex_id INTEGER NOT NULL REFERENCES club.sex, climber_date_birth DATE NOT NULL, climber_phone VARCHAR(20) NOT NULL, postal_code_id INTEGER REFERENCES… Read More NULL-value in generated column Postgres

Advertisements I have the below SQL that takes only data from the Name column up to the point where a space or hyphen appears. I would like to order the results by the output of the case statement but can’t find a way to do it. select case when strpos("Name",’ ‘) > 0 then substr("Name",… Read More Order by case statement that extracts substring from column

Advertisements I have a table with such structure: id product company 1 table A 2 table B 3 table C 4 chair A 5 chair B 6 sofa A 7 sofa C I need to find all products whose companies include both A and B. In the example above these would be table and chair.… Read More SQL: How to use grouping and condition at the same time in a table?

Advertisements I am trying to write an postgresql query and one of the where statements is a check to see if an array of filters that a user passes contains a value the array of types on a database column has in a json blob. for example, if the user sets their filters to look… Read More Is there a way to check to see if an array contains another array in postgres?

Advertisements I am trying to count the total number of rows returned, additionally I want to count the number of occurrences of a boolean condition. For example: SELECT COUNT(1), COUNT(field > 42) FROM some_table WHERE some_other_conditions However the query above doesn’t work because the boolean condition field > 42 evaluates to false, which is still… Read More Postgresql – Simpler way to count occurrences of a boolean condition

Advertisements I would like to clarify answer for this question because marked statements are contradictory. Can SELECT get different results within one transaction using Read Committed Isolation Level? Based on official documentation: When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) 1. sees only data committed before the query… Read More Contradiction in Read Committed Isolation Level?

Advertisements In PostgreSQL I have: SELECT * FROM weather_data WHERE parameter_type = ‘TEMPERATURE’; I want to write this as: WITH ParameterType AS (VALUES (‘TEMPERATURE’)) SELECT * FROM weather_data WHERE parameter_type = ParameterType; I get error: "column "parametertype" does not exist" >Solution : A CTE works just like a table. The query below is an equivalent… Read More Hot to use SQL WITH VALUES clause to define constant value

Advertisements I have a text[] column which consists of elements of stringified json, and I want to convert it to jsonb[]. Here is my table shrunked to only relevant text[] column: CREATE TABLE certificate ( … criterias text[] … ) An example criterias column looks like this: ‘{"{\"url\":\"https://criteria.com\", \"description\":\"My Criteria\"}","{\"url\":\"https://criteria2.com\", \"description\":\"Other Criteria\"}"}’ Each criteria is… Read More Alter column type from text[] to jsonb[]

Advertisements I have three tables Owner ———————– id | long name | string Animal ———————– id | long status | string external_d | string region | long (fk to region table) owner_id | long (fk to owner table) Region ———————– id | long name | string I want to get multiple rows from the Animal… Read More How do i convert each row from resultset to a json and then add them to a single array in postgresql

Advertisements based on the following data in the database: i would like to query for all the records where status = null, but the status of the previous step_no is "done" group by the order. appreciate some guidance on what sql can do so. >Solution : You can use lag() function like below: with cte… Read More Postgresql querying records where the previous record already in completed status