Sample contents are: id created_dt data 1 2023-01-14 11:52:41 {"customers": 1, "payments: 2} 2 2023-01-15 11:53:43 {"customers": 1, "payments: 2} 3 2023-01-18 11:51:45 {"customers": 1, "payments: 2} 4 2023-01-15 11:50:48 {"customers": 1, "payments: 2} ID 4 or 2 should be…
I create an API that updates a record associated with a foreign key if I just put a value to items so I want it to return remove other values that I don't put if I edit some value in…
I have daily change in a table like below. Table: performance date percent_change 2022/12/01 2 2022/12/02 -1 2022/12/03 3 I want to assume initial value as 100. and shows cumulative value till date, like below. expected output: date percent_change cumulative…
I have a table datetime value 2022-10-21 11:23:00 1 2022-10-22 12:12:00 2 2022-10-23 13:43:00 0 2022-10-24 14:01:00 5 2022-10-25 10:23:00 2 and I would like to get a result like this (aggregate each 3 days), gaps are possible datetime value…
How to add space between two name columns concatenated in SQL ? SELECT CONCAT(a.first_name, a.last_name), f.title, f.description, f.length FROM actor a JOIN film_actor fa ON a.actor_id = fa.actor_id JOIN film f ON f.film_id = fa.film_id snapshot of result of query…
I have accidentally created hundreds of indexes that have double quotes in the name. Is it possible to drop them altogether or change their name? I have tried to delete them individually with drop index '"thing"somethingelse';, with double quotes "drop…
I'm trying to create a system in Postgres where each client can create its own subscriptions via listen + notify + triggers. I.e. the client will listen to a channel, and then create a trigger which runs notify on that…
I'm trying to write a procedure in PostgreSQL to update number of products sold from Order_items table to Stock table. Here's my query CREATE OR REPLACE PROCEDURE smallerp.sp_calculate_stock () LANGUAGE plpgsql AS $$ DECLARE f record; v_pid integer; v_sid integer;…
set session myconstants.test = '10'; set session myconstants.testb = '10 min'; SELECT now()::time - interval concat (current_setting('myconstants.selfName')::varchar,' min'); SELECT now()::time - INTERVAL '10 min'; set session myconstants.test = '10'; SELECT now()::time - interval current_setting('myconstants.testb')::varchar; SELECT now()::time - INTERVAL '10 min';…
Here's the problem. create table customer ( customer_id int generated by default as identity (start with 100) primary key ); create table cart ( cart_id int generated by default as identity (start with 100) primary key ); I want to…