How can I use a parameter within a JSONPath expression in a PostgreSQL SQL query? I want to query something like this: SELECT * FROM table WHERE jsondata @@ '$.pathto.array[*].** ? (@.someproperty == ":VALUE").anotherproperty != null'; I tried: SELECT *…
I am using postgresql and I am storing recipe data. Each recipe could have up to 200 nutrients associated with it, but it must have these nutrients not null - kcal, carbs, fats, protein other than that it might or…
I'm taking beginner postgres online course and below is the question and sample answer. But I'm wondering if I can use MAX instead of ORDER BY DESC? Question: What customer has the highest customer ID number whose name starts with…
I have a table customer. The table has columns cust_id, member, date. The cust_id is a primary key (auto-increment), member can have either 1 or 0 and date contains the timestamp. I want to run a query on MySQL which…
I want to optimize the following DB SELECT query: SELECT ls.caller_id, ls.caller_path_id, ls.caller_path_name, ls.caller_data->>'worker' AS worker, ls.caller_stage, ls.current_status, ls.date_inserted AS date_closed, ls.caller_data->>'name' AS firstname, ls.caller_data->>'surname' AS surname, ls.caller_data->>'cell' AS cell, ls.caller_data->>'home' AS home, ls.caller_data->>'work' AS work, ls.caller_data->>'other' AS other FROM…
I have a list of n rows (n < 100) with '#' separated values in postgres (version 13+) row value 1 'ABC#DEF#123' 2 'ABC#DEF#156' 3 'ABC#DEF#178' ... These values can be of any length (e.g. 'ABC#DEFDEF#GHIG#123123#456#678') There are no NULL…
I have a table which looks like below: CREATE TABLE time_records ( id uuid NOT NULL, employee_id uuid NOT NULL, starttime timestampt NOT NULL, endtime timestampt NOT NULL ) There will be overlap in times between the records for the…
I need to count quantity of some condition and avoid 'NaN' in result. I tried ways with CASE and COALESCE constructions but have no needed result: -- 1st: it works with 'NaN' in part of results: SELECT response_date, COUNT(*) AS…
I have a table vehicle with columns id, model, colour, is_default. in this is_default is a newly added column and we have 4000 old recored before adding is_primary. the default value for is_primary is 0. I want to fill this…
I have the following SQL code: SELECT dip || ' ' || ( SELECT lib1 || ' ' || lib2 FROM bds WHERE dip_s = dip ) AS label, COUNT(dip) AS freq FROM current_table WHERE v_deg = '3' GROUP BY…