All Questions
Tagged with subquery postgresql
142 questions
0
votes
3
answers
57
views
Dynamic column selection in a subquery
I have two tables, one containing polls and the other poll votes, like this (simplified):
CREATE TABLE IF NOT EXISTS polls (
id SERIAL PRIMARY KEY,
token VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS ...
0
votes
1
answer
128
views
Debug RPC Function Creation in Supabase
I'm pretty new to database stuff.
I'm using Supabase to create an application where I keep track of the number of likes on certain items ('clicks'). I want to filter items either by the date the likes ...
- 3
1
vote
1
answer
156
views
Limiting the number of rows that can be inserted for a given WHERE clause
In Postgres, I want to be able to confirm users' RSVPs for an event that has limited capacity. How do I do this in a way that protects against race conditions?
I have a table event_attendance with ...
- 141
0
votes
1
answer
57
views
Efficient PostgreSQL query syntax for duplicating the result
Assuming I have the following PostgreSQL query:
SELECT (
SELECT SUM (t.customers)
FILTER (
WHERE id = 1
AND date
BETWEEN %(start)s AND %(stop)s
...
- 133
1
vote
0
answers
19
views
postgresql sub query
I have three tables
kit - contains kit_id and kit_type (kit type is with android or IOS)
appointment - contains appointment_id, created_datetime, kid_id, created_by_id
user - contains user_id, user ...
- 11
1
vote
1
answer
1k
views
Return Subquery with Multiple Columns as JSON Array of JSON Objects
In my setup, the relevant tables for this problem are:
Items
Users
Collections (of Items) — each user can have multiple
Collection Items — where I track what items are in each collection.
The items ...
- 121
2
votes
2
answers
558
views
How to update a foreign key with random values from the referenced table?
In order to generate test data for a Postgres database, I want to update a foreign key in a table, let's say 'users' with random values from the referenced table, let's say 'shift'.
How to do it?
I ...
0
votes
0
answers
18
views
Why SQL subquery returns no error on trying to select not existing column? [duplicate]
I use PostgreSQL 15 and have two tables:
table 1 has only one column id1 (pkey) and has 6 rows (values 1-6)
table 2 has only one column id2 (pkey) and has 6 rows with some another values
Then I try ...
- 1
0
votes
1
answer
74
views
Compose a complex query in Postgresql
I have a huge table like following:
CREATE TABLE public.huge_table (
sampl_day date NOT NULL,
tick_time timestamp(6) with time zone NOT NULL,
crit_feat integer NOT NULL,
---
--- ...
- 359
4
votes
1
answer
14k
views
postgres - copy values from one column to another column (same table)
I want to copy the entire content from one column to another (same datatype) and thought of a subselect to be part of the equasion.
UPDATE tab01
SET column2=(SELECT column1 FROM tab01);
ERROR: ...
- 215
1
vote
0
answers
25
views
query customer retention over range
I am trying to find the best way to accomplish the following.
Get the beginning customer count, which carries from the previous day
Get New Customer count
Get the number of Customers who have not ...
- 111
0
votes
1
answer
436
views
Join result of crosstab to main query
I'm trying to get this column structure:
| id | name | main_image | rating | shop_names | prices | urls | 2 | 3 |
My table structure looks like this
CREATE TABLE products (
id SERIAL PRIMARY KEY,
...
3
votes
3
answers
11k
views
"ERROR: column "a" does not exist" when referencing column alias
I am attempting to retrieve either a null or boolean value from a query. Here is my query:
SELECT EXISTS (SELECT 1 FROM employee
where add_uuid = '7e53fa47-ade3-4e94-8efd-b25f998b09c6') ...
- 133
1
vote
1
answer
499
views
Parallel index-only scan with two sub-queries is slow
I'm trying to analyze a slow query that uses parallel index-only scan of two filters (using sub-queries). Specifically I'm the number of 'open_questions' that returned by either subplan1 OR subplan2.
...
- 135
1
vote
1
answer
162
views
DIV and GROUP by issues
I have one query, made it in MySQL.
And now try to run on PostgreSQL, but get syntax error twice.
Once on the DIV command, and then on the GROUP BY.
Here is my code:
UPDATE status_table
SET Status_CG ...
0
votes
1
answer
515
views
How do I filter a set of child records to get a single child record for each parent? [duplicate]
Sorry if it's a simple question but I can't wrap my head around it. I have two tables, users and carts. For each user record I need to get it's most recent cart (ordered by created_at desc).
I've ...
- 103
0
votes
2
answers
773
views
Postgres sort results by id's from subquery
In postgres 11 im implementing something in type of suggesting new tag for user. To achieve that im first counting amount of activities of specific user in specific category. Query looks as follows
...
- 15
4
votes
1
answer
461
views
Concurrent update on two different tables fail
I'm using PostgreSQL with the following database schema:
CREATE TABLE plans (
slug VARCHAR(500) PRIMARY KEY
);
CREATE TABLE users (
id VARCHAR(16) PRIMARY KEY,
org_id VARCHAR(16) NOT NULL
...
- 143
0
votes
0
answers
135
views
Is the use of a dependent subquery justified in this case?
SELECT a.id,
(SELECT ARRAY(
SELECT c.id FROM table_c c
JOIN table_d d ON d.id = c.id
JOIN table_e e ON e.id = d.id
WHERE a_id = a.id AND ...
- 209
2
votes
4
answers
2k
views
Why is my big Postgres query faster if I separate it into 3 smaller queries?
I have a big/complex query (with an OR clause and 2 subqueries that have multiple joins and an IN condition). Executing the big query is slow.
However, if I break my big query down into 3 smaller ...
- 121
1
vote
0
answers
458
views
Postgres: one-to-one join with duplicates and inequality matching
** Update: Fiddles now included https://dbfiddle.uk/?rdbms=postgres_14&fiddle=0c618e64c7fcd54c8781ed7d1ac540f0 and https://sqlfiddle.com/#!17/12053 **
I have two tables taken from two data sources,...
- 11
3
votes
2
answers
2k
views
How to query through foreign keys while only having the value in the the same row as the value used as foreign key in another column
db admins, I require your help!
My experience working with databases directly using SQL is rather limited, since I've mostly been using ORMs to cover all my needs as a frontend developer, but this ...
- 133
0
votes
1
answer
290
views
How to speed up subquery
Let's suppose we have:
select
o_orderpriority,
count(*) as order_count
from
orders
where
o_orderdate>='01/07/1993'and o_orderdate<'01/10/1993'
and
exists
(select
*
from
...
- 235
6
votes
1
answer
780
views
Unused columns in WHERE clause
I found this query in our codebase:
DELETE FROM "Foo"
WHERE ("Foo"."Id", "Foo"."CreatedAt")
IN (SELECT "f"."Id", "f".&...
- 205
0
votes
1
answer
687
views
how to use distinct on to avoid aggregating column in this query?
what I am trying to do is I want to calculate the quantity for the inventory the formula for that is receive_order_entries.quantity+ the sum of all inventory_transactions.quantity for that inventory
...
- 13
2
votes
2
answers
2k
views
Postgres slow exists subquery
I have a queue system that has two tables, jobs and jobs_queues.
jobs is where we put jobs to run and job_queues we populate with queue names so that when we pick jobs we can discard those that are ...
1
vote
1
answer
220
views
Postgres v10 is not using the index in a subquery
I have a subquery that will return 3 records, to later add the result of some calculations (using some functions).
When I am running the full query, Postgres is calculating a high number of expected ...
- 21
3
votes
2
answers
13k
views
Update with subquery referencing the main table
I'm trying to understand how to filter my subquery in context to the main query. Ultimately I'm trying to get the MAX value from the latest record BEFORE the date of the current record. Here is where ...
- 31
0
votes
2
answers
3k
views
Query with CASE in subquery join clause is very slow on PostgreSQL but fast on Oracle and MariaDB
We have an attribute-oriented data management system with bitemporality and flexible tenant priorization logic. Deletion of records is done by inserting the same row in DELETED status. Tenants may ...
- 11
1
vote
1
answer
508
views
Why does the function TO_CHAR get invoked for each row when not wrapped in a subquery?
I am working in an Oracle to PostgreSQL migration project.
Today I found what I think is a PostgreSQL bug. I wonder whether I missed something in my experimentation.
The following is my test code.
...
- 51
0
votes
2
answers
425
views
Store value of subquery in update for later use
Trying to calculate % change of price. Query below works, but was hoping there was a way that I didn't have to call the same subquery first? I don't think WITH works here unless I'm mistaken.
update ...
- 121
0
votes
2
answers
159
views
Query slow when a sub select is used
I've created a query which brings back all revenue for specific groups with different columns for YTD, Last year, the year before and the year before that.
It works but extremely slowly and from what ...
1
vote
1
answer
404
views
basic subquery evaluation
I am new at subqueries. Is there a general strategy on how basic subqueries are evaluated and executed (besides from sql engines automatically optimizing them). For example, consider this select query:...
0
votes
1
answer
404
views
Using subquery in WHERE makes query extremely slow
I have this rather basic query that is very slow for reasons I can't figure out:
SELECT s.id
FROM segments s
WHERE
ST_DWithin(
s.geom::GEOGRAPHY,
ST_Envelope((SELECT ST_COLLECT(s2....
- 193
0
votes
0
answers
135
views
Postgres locking issue
I have the following Postgres table:
CREATE TABLE my_db.url_data (
url varchar(2500) NOT NULL,
status int,
priority int,
...
last_update_ts timestamp,
CONSTRAINT url_data_pk ...
1
vote
2
answers
128
views
Index isn't used when value is a result of a subquery or from a wrapper query
I have a simple one-to-many relationship: accounts and events where an account may have many events.
accounts
id: uuid
...more irrelevant fields
events
id: serial
type: enum ['activated', '...
- 111
0
votes
2
answers
1k
views
Limit rows in Correlated Subquery
I have one big and two small issues with my query I need help with.
First, I want a SQL (postgres if it matters) query that gets SUM of last two ratings of fruits and makes a new column for each fruit ...
1
vote
1
answer
2k
views
subquery must return onl one column in postgresql
I want to get the XML response after the stored procedure call in PostgreSQL. In the below query I was trying to get the output, but it throws an Error: subquery must return only one column. Can ...
4
votes
2
answers
2k
views
Select rows with the highest value for one ID, after grouping by two IDs
It is given in the problem that occurrences has to happen more than once, but the person (PID) should only be selected with the (agent) AID having the highest BustCount.
SELECT I.AgentID, I.PersonID, ...
0
votes
1
answer
383
views
SQL (Postgres): Subquery in WHERE clause with IN operator is slow
I have the following SQL Statement:
select count(*)
from person p
cross join task t
where t.id in (
select tp.task_id
from task_participant tp
where tp.person_id = p.id )
and p.id = 1234
...
- 111
4
votes
1
answer
6k
views
Unexpected unique constraint violation during update
In table foo with two columns (id and seq), I'd like to add +1 to seq for all records with an arbitrary seq > 4738. The plan is to insert a new record at seq=4739 once all the seq > 4738 records ...
- 153
0
votes
3
answers
3k
views
I am trying to find the average of a COUNT request [closed]
I have completed the query below from an invoices table which gives me the total invoices attached to each house ID. So house_id 114 has 14 invoices attached to it. Is there a way to feed this result ...
- 13
-1
votes
1
answer
2k
views
Write a dynamic query of nested subqueries [duplicate]
Let's take an everyday query with a list of nested queries so I can show you the problem, see how we'd have to add a subquery when a new Certification comes out:
select DISTINCT e.display_name as &...
- 201
2
votes
2
answers
36
views
How to filters data with two windows conditions?
I want to get the result of two sub-queries. In my case I have users, user can have multiple access for doors, but sometimes doors are disabled so the access exist but do not point to any doors. I ...
- 651
0
votes
2
answers
73
views
Independent aggregation of related entities, while also sorting by related (in a single statement)
I have a model with books and authors, with a many-to-many relationship between them (because a book can have multiple authors, and an author can write multiple books) through a table which I've named ...
- 101
3
votes
1
answer
81
views
Query optimisation
I want to optimize 2 queries from 2 tables(orders, actions) with relation one to many
Now I make one select from orders:
SELECT "id"
FROM "orders"
WHERE "orders"."...
- 142
1
vote
1
answer
1k
views
how to replace with default value when querying a JSONB column with a key that is not present in a particular row giving empty result
I have a table that has a jsonb column as 'result' which has an array of objects.
Looks like this for every row:
[{"grade": "A", "subject": "MATH"}, {"...
- 11
0
votes
1
answer
27
views
Return average using same table data
I've got this query I'm testing with:
select sum(units) / (select count(distinct week) from record_sales_data where customer_country_id=2 group by customer_country_id) as average
from ...
- 141
7
votes
2
answers
10k
views
Does Postgres optimize this JOIN with subquery?
In Postgres 12, I have a table purchase_orders and one for its items. I'm running a query that returns PO's for a given shop and a sum of items ordered on each PO:
SELECT po.id,
SUM(...
- 73
3
votes
1
answer
20k
views
ERROR: subquery must return only one column
I have two tables one is Category and other one is Product.
Table Description are
Category
name
description
parent_id (self referencing key) (can be upto 3 levels)
Product
name
description
type
...
- 133