I have a large PostgreSQL DB table. From this table I need to take rows grouped by Car_id and position columns. The problem is that I have a lot of duplicates and need to take one row with the best…
I am trying to identify sum of quantity for each group if date ranges are overlapping. I am using postgresql to solve for it. For example id group start_date end_date quantity 1 a 2020-09-11 2020-10-09 50 1 a 2020-09-11 2020-10-31…
CREATE or replace PROCEDURE mytransactions (n_transactions_id VARCHAR, n_transaction_amount SMALLINT, n_transaction_date TIMESTAMP, n_Delivery_date Date, n_customer_id VARCHAR, n_product_id VARCHAR, n_store_id VARCHAR) LANGUAGE plpgsql AS $BODY$ BEGIN INSERT INTO transactions (transactions_id, transaction_amount, transaction_date, Delivery_date, customer_id, product_id, store_id) VALUES (n_transactions_id, n_transaction_amount, n_transaction_date, n_Delivery_date, n_customer_id,…
I have a hard time understanding why I can refer to the output columns in returns table(col type). There is a subtle bug in the below code, the order by var refers to res in returns, not to data1 which…
with res as(select us.column,mg.column as status,count(mg.column) FROM Table_name1 it LEFT JOIN Table_name2 us on it.column=us.column LEFT JOIN Table_name3 mg on it.column=mg.column where it.column is not null and it.column in(5,6) and (it.column + '05:30:00'::INTERVAL)::date between '2022-08-28' and '2022-10-03' group by us.column,mg.column)…
If I have a table like this: CREATE TABLE mytable ( id SERIAL, content TEXT, copyofid INTEGER ); Is there a way to copy id into copyofid in a single insert statement? I tried: INSERT INTO mytable(content, copyofid) VALUES("test", id);…
How could I extract only alphabets of this column not considering characters after number? Also, there are situations where there are no numbers. In this case, is it possible to create a condition using CASE and WHEN to apply extraction…
I have the function below in Postgres which was working fine. but then I had to add other types, So I tried to add cases to it. But this isn't working as expected. Basically what I am trying to do…
In PostgreSQL I have an orders table that represents orders made by customers of a store: SELECT * FROM orders order_id customer_id value created_at 1 1 188.01 2020-11-24 2 2 25.74 2022-10-13 3 1 159.64 2022-09-23 4 1 201.41 2022-04-01…
My problem is that when I try to delete records from a table in postgreSQL with python I can't do it, the method I created deletes 1 by 1, but when I want to delete 2 or more at once…