I am dealing with a PostgreSQL (v14) query of this kind SELECT ..., EXISTS ( SELECT 1 FROM table2 t2 WHERE t2.fk = t1.id AND LOWER(t2.table_name) = 't1' ) AS t2_record_exists FROM table1 t1; and was hoping to extract the…
I have rewritten the below query from using a correlated subquery to using a LATERAL JOIN. It is now faster, and I would like to understand why? This SO answer says that there is no golden rule and that it…
How does ON predicate of Postgres LATERAL JOIN work? Let me clarify question a bit. I've read the official documentation and a bunch of articles about this kind of JOIN. As far as I understood it is a foreach loop…
so I have two tables, A and B. first I do a lateral join of A with one column (items) select A.id, item->>'id' as item_id, b.id from a, jsonb_array_elements(items) rx(item) then I do a right join with table b (has…
Here are the exemple tables: Product Prices (prd) start_date product price 2023-04-01 prod_A 10.0 2023-04-15 prod_A 20.0 2023-04-01 prod_B 20.0 Order Products (ord) order_date id product 2023-04-01 10001 prod_A 2023-04-01 10001 prod_B 2023-04-02 10002 prod_A 2023-04-02 10002 prod_B 2023-04-16 10003…
I have a table which has only one row like below, Here I want to convert the column names to one column and the row into another column, with the column names converted to some predefined numbers as below (A->1E9,…
Simplifying a bit. I have the following data structure: | id | name | traversal_ids[] | | -------- | -------------- |-----------------| | 1 | container1 | {1} | | 2 | container2 | {2} | | 3 | subcontainer1 |…
I have a table tab1 in timescale db which has 3 columns tag, time, value. time and tag make up the pk for the table: (time, tag). There are more than 500 lakhs (50 000 000) of rows. I need…
I need some help here, my request is : List the bookID and number of copy of this in Library (Number of copy in library = number of copy – number of copy student borrow but not return yet) and…