I have two tables, lets call them t1 and t2. Fiddle select setseed(.42); create table t1(a,b,c,d)as select (random()*9)::int , (random()*9)::int , (random()*9)::int , (random()*9)::int from generate_series(1,100); create table t2(a,b,c,d)as select (random()*9)::int , (random()*9)::int , (random()*9)::int , (random()*9)::int from generate_series(1,200); I'm…
I have 2 tables in Postgres, one is of about 200M records, and the other a few hundreds. I want to get 10 values from the small table and find the corresponding values in the long table. both are indexed.…
I have the following data in a PostgreSQL table: id amount opening_amount closing_amount 1 200 1000 1200 2 -500 NULL NULL 3 -200 NULL NULL 4 1000 NULL NULL I want to convert this data into a ledger format, calculating…
I try deleting all the goods, whose type_name starts with D. Let's say I wanna delete goods from the types of dentistry, delicacies, darts_game. So I check whether the type is in the list d_goods_ids (type is a FK of…
This might be a bit elaborate so please bear with me. I'm looking to create a new column free_duration in a table that currently looks like this, when grouped by country and product in ASCENDING order of month- month country…
I am trying to make a simple SQL query in PostgreSQL to use in our team: with findData as ( select '(''XXX'',''YYYY'')' as dataList ) select * from findData That gives the result: ('XXX','YYYY') Exactly what I wanted ! But…
From DBeaver Community, I access my database and create a new SQL query. I write the corresponding code for the CTE I want to create, but it constantly shows a syntax error. I've tried everything, but I can't find the…
I have an input table like this: Load_date projected_date current stock consumed demand 2020-01-01 2020-01-01 100 15 2020-01-01 2020-01-02 150 80 2020-01-01 2020-01-03 200 25 2020-01-01 2020-01-04 300 70 2020-01-01 2020-01-05 400 65 My intermediate output will be like this:…
I have created a CTE using 'with' clause and I am using case when expression to populate a column in CTE and I wanted to use the result of case when expression in where condition as a criteria value to…
I use the id directly in the query, it has a low cost: -- Query 1. -- Nested Loop Semi Join (cost=2.27..280.78 rows=1 width=129) EXPLAIN SELECT * FROM view_companies_extra WHERE company_id iN ( SELECT id FROM companies WHERE id =…