I am using PostgreSQL 15 and have the following two statements in PL/pgSQL: -- Statement 1 SELECT to_json(T) FROM (SELECT * FROM category WHERE category.id = 43) T) -- Statement 2 SELECT json_build_object('products', JSON_AGG(T)) FROM (SELECT * FROM product WHERE…
I have a PostgreSQL (version 12.15) database with a table like this: id created_at number chat_id ... 2023-08-10 21:44:19.272299 0 27936 ... 2023-08-09 10:32:28.850167 0 27936 ... 2023-08-08 17:32:11.207523 0 27936 ... 2023-08-07 17:16:52.068309 0 27936 ... 2023-08-06 17:15:47.014364 0…
I have the following query which I'm unable to solve: SELECT DISTINCT m.id, TO_CHAR(m.date, 'YYYY-MM-DD')::timestamp AS formatted_date, FROM table.match m In the formatted_date field I get the following output: 2012-12-02T00:00:00.000Z Does anyone know how to format it as to get…
I have a table called Configuration: CREATE TABLE "Configuration" ( "id" CHAR(36) NOT NULL, "namespace" VARCHAR(255) NOT NULL, "key" VARCHAR(255) NOT NULL, "value" JSONB NOT NULL, "level" "ConfigLevel" NOT NULL, "userId" CHAR(36), "orgId" CHAR(36), "teamId" CHAR(36), "moduleId" CHAR(36), "moduleSubscriptionId" CHAR(36),…
how to order by '0057','0058', '1', '2', '3', 'A1', 'A2', 'A3' in sql Can anyone plese give idea to diplay the section data (alpha numeric column) in this below order? Actual 0057 0058 10 11 5 6 A5 A6 Expected…
The picture shows example oF JSON data I am trying to query "id" value under images field in aws redshift mode. I tried SELECT json_extract_path_text(images, 'id') FROM table_name; This query give me an error ERROR: function json_extract_path_text(super, "unknown") does not…
This is my sample dataset. Seq_no and value columns are given, Cum.sum and Bucket columns we need to find using postgresql. To Find cumulative sum , i can write sum(value) over (order by seq_no rows between unbounded preceding and current…
In one of my Postgres Transaction (executing a procedure), I'm using more than 50 temp tables and for each temp table it is creating pg_toast_temptableOID, pg_toast_temptableOID_index with AccessExclusiveLock and fastpath = false for these pg_toast tables. It is creating slowness…
I have two tables, table1 and table2. Both use the same primary key of ID and I've made sure there's no overlap between the IDs so they can be merged together. Both the tables are almost identical in structure, apart…
I have database like this (in real there are over 30 different sKey): +----+------+------+---------------------+ |ID | sKey | sVal | timestamp | +----+------+------+---------------------+ | 1 | temp | 19 | 2023-07-14 20:32:06 | | 2 | humi | 60 |…