Please advise how to achieve below ask in PostgreSQL query Input Column_x null null 1257-1;1258-3;1235-4;111-1 1260-3 1263-6;1457-1 null null Basically, need to count of id's Expected output Column_x 0 0 4 (Count of Id's) 1 2 0 0 Since I'm…
I'm working with PostgreSQL and I want to enforce a particular column to be unique. But this table has some legacy data in which duplicate of the column are there and they have to be retained unmodified. So when I…
I have a SQL function that I am unable to query with Postgraphile. I am getting Cannot query field "get_eligible_shifts" on type "Query"." I noticed that in the PostGraphiQL the function does not appear. Why would I not be able…
Given a table with a column containing double precision[] with a fixed/same size. I am able to fetch an element wise aggregation for min and max: select min(array_col) from table; select max(array_col) from table; The result should be again a…
I'd like to assign type timestamp without timezone to all columns name created_at at my database scheme. I'm trying to run this query: DO $$ DECLARE table_name text; BEGIN FOR table_name IN SELECT table_name FROM information_schema.columns WHERE column_name = 'created_at'…
I have a postgreSQL function which takes a text array as one of the input parameters: CREATE OR REPLACE FUNCTION report.fn_get_metrics( from_date timestamp without time zone, to_date timestamp without time zone, location_ids text[]) RETURNS TABLE(demand_new_count bigint) LANGUAGE 'plpgsql' COST 100…
I'm building a language learning platform, here are some tables: create table users ( id bigserial primary key, ... ) create table users_languages ( id bigserial primary key, user_id bigint not null constraint users_languages_user_id_foreign references users, level varchar(255) not null,…
I've a Postgres table like this where a order count for each tenant is inserted when it changes. datetime tenant_id orders 2023-09-15 22:00 tenant3 2 2023-09-16 01:00 tenant1 2 2023-09-16 02:00 tenant1 3 2023-09-16 02:00 tenant2 5 2023-09-16 03:00 tenant1…
I have an entity with specific fields, each of which is mapped to columns in a postgres database. I am using a JpaRepository to persist my entity. I have one column, called info, that can be arbitrary json, and is…
I'm using PostgreSQL with the following (simplified) DB design: I have a table Auction, each auction can be handled from a Tribunal or a Court of Appeal. So that I have the table Court of Appeal (primary key = [id]…