Questions tagged [cte]
Acronym for Common Table Expression. Temporary, reusable subquery that may be recursive.
409 questions
2
votes
1
answer
71
views
Why delete and insert in a CTE works despite UNIQUE constraint?
Given a table with a unique constraint on a column
CREATE TABLE t
(
id int GENERATED ALWAYS AS IDENTITY,
name text NOT NULL UNIQUE
);
Let's create a single record
INSERT INTO t (name) ...
- 21
3
votes
1
answer
52
views
Are non-used CTEs never materialized in PostgreSQL 16?
I want to return my post rows in one of three sort orders, controlled by a sorting_param function parameter. The sorting is complex, doesn't lend itself to a simple ORDER BY CASE .... So I'm thinking ...
- 133
0
votes
1
answer
62
views
PostgreSQL CTE Recursion - How to Improve Performance for Parent->Child Tree (8 levels deep, 890 roots)
I am working on a query that performs PostgreSQL CTE Recursion in PostgreSQL 13. This query is intended to recurse from the root nodes to the deepest leaf node with certain properties (labeled 'begat'...
- 50
2
votes
1
answer
79
views
Recursively sum child's nominal to parent, on dynamic level parent-child structure
i have data look like this :
i try to sum the Nominal column to each respective parent, so it will looks like this :
i provided the sql script for the data :
SET ANSI_NULLS ON
GO
SET ...
0
votes
1
answer
37
views
PostgreSQL window function: Add value in CASE running sum gets below threshold
For context:
I am playing with a filling level sensor, a water tank and a watering pump and have the following problem:
I have an initial filling value for my tank.
Each time step, I am subtracting ...
1
vote
1
answer
92
views
Trouble with slow stored procedure performance on 35M rows with CTE/Temp table
We recently went through a new feature release to our app that added about 25 new columns to a large table (~35 million rows), and now we're having some major query performance issues. I assume that ...
- 127
3
votes
1
answer
197
views
Locking for update inside CTEs
If I make a query using CTEs that looks like that:
WITH cte_a AS (
SELECT a.id, a.something
FROM a
WHERE a.something IS NOT NULL
),
-- [...] some other CTEs
cte_d AS (
SELECT
...
- 167
0
votes
0
answers
44
views
PostgreSQL CTE behavior influenced by outer `update` statement: Why?
tl;dr
Why does an update from ... statement make a CTE behave differently than an update where... with subquery statement in postgresql?
Full Context
Disclaimer: The information below has been ...
1
vote
1
answer
152
views
Getting sum for each day based on most recent timestamp entry for each entity
I have a table that contains an ID, timestamp, and integer. Each row is a "snapshot" of the entity at a given time:
TableA
ID
timestamp
num
A
2024-02-04
5
B
2024-02-04
3
C
2024-02-07
8
...
- 177
0
votes
1
answer
79
views
When generating combinations using a CTE, is there a way to use an index to group the elements of each combination?
I have a project that involves combining a variety of things into their possible combinations. This includes permutations, combinations, with and without repetition, and some more exotic ways of ...
- 439
2
votes
2
answers
272
views
How to make a Data Modifying CTE abort if an update only partially succeeded?
I am wondering if it is possible to add some kind of check to make a data modifying CTE abort if an update is partially successful. Attached is a minimal query that represents the problem. I would ...
- 23
0
votes
0
answers
173
views
Recursive CTE hiearchy snowflake -- join/ expand outward (columns) instead of rows?
I'm doing a dependency mapping explosion (like parts explosion).
I did a typical recursive CTE with a union all.
It looks like
with CTE as
( select abc from myTable where start_point = X
union all
...
- 1,709
1
vote
0
answers
259
views
Why is PostgreSQL performing a sequential scan, except when my CTE is materialized?
The issue
My application is encountering performance issues on a query that, to the best of my understanding, should be performant. In this post, I have simplified the query and schema while still ...
- 111
1
vote
2
answers
547
views
How can I optimize a recursive CTE inside a IVTF?
I have a recursive CTE inside a inline table-valued function. The ITVF is returning a list of IDs containing a long sequence of ancestors for a person, it usually loops back about 12 to 18 times ...
- 121
0
votes
1
answer
82
views
Unable to use INSERT within CTE
I am trying to use a CTE to insert some random data into a table -
create table foo (id integer)
with x as (select random())
insert into foo (id)
select x from x
This gives an error:
ERROR: ...
- 823
3
votes
1
answer
280
views
Help with CTE syntax in SQL
I am new to SQL, I tried to create a CTE, but I get a red squiggle under the AS and I am getting an error message in the process of executing the syntax:
Msg 102, Level 15, State 1, Line 117 ...
- 41
0
votes
0
answers
35
views
How to match rows from relationship via a recursive CTE
Apologies for the terrible question title, I couldn't think how to describe this question in a single line.
I have a schema that looks roughly like this: https://dbdiagram.io/d/...
- 163
1
vote
1
answer
65
views
Return the longest consecutive sequence of a value in specific column
I am using PostgreSQL 12.0 and trying to get the longest sequence of continuous rows for a specific column and value.
The table is called team2 and contains a team's results which looks like:
match_id ...
- 13
2
votes
1
answer
216
views
Common Table Expression syntax
Considering a query with CTEs:
WITH cte_0 AS (
SELECT SUM(col_0)
FROM table_0
WHERE condition_00
AND condition_01
),
cte_1 AS (
SELECT SUM(...
- 133
1
vote
1
answer
87
views
INSERT result for SELECT FROM join'ed tables just modified
I have the task to insert data into couple tables and return data from their intersection in one query. The tables are linked through specific fields (solditems refers to products and invoices, ...
- 113
2
votes
0
answers
23
views
Find best intersect (most elements) between Order (haystack) and Pricerules (needle)
Full disclosure
I have posted a greater scoped question at SO a couple of days ago with a bounty (no answers yet).
Scope here
How can a CTE be used to find a match with the most elements?
For the ...
- 219
1
vote
1
answer
643
views
Index is not used when doing a query with PostgreSQL CTE
Given I use the following table :
CREATE TABLE operations (
asset_id varchar(255) NOT NULL,
event_id varchar(255) NULL,
updated_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP
);
and ...
- 113
1
vote
1
answer
101
views
How to use WITH in a better way to parametrise a date
I love to use CTEs to create nice and clear queries. However, I'm pretty sure the query I created is really inefficient.
Is there a better way to do this and keep things clear ?
with first_date as (
...
- 347
0
votes
2
answers
3k
views
Performance difference using CTE and subquery
I have run the following queries.
CTE
WITH temp AS (SELECT id, timestamp FROM table group by timestamp, id)
SELECT COUNT(*) FROM temp WHERE timestamp = '2023-01-01 08:28:45'
Subquery
SELECT COUNT(*) ...
- 121
0
votes
1
answer
577
views
PostgreSQL Query Performance: Sorting Array Length vs. CTE
I'm having a significant performance difference between two PostgreSQL queries that I'm trying to understand and optimize. I have a table with around 2TB of data, and both queries use the sequential ...
- 247
1
vote
1
answer
4k
views
Optimal way to get a total count of rows in a paged query in Postgres?
I need to improve the performance of a paged query for customer orders in a Type2 Postgres db (always insert a new record with a new ID, the newest ID is the current version of the record). Changing ...
- 217
-2
votes
1
answer
211
views
I want to make a tree of parents only in sql server
My table in the database
How can I get my result as Where child = 'chl1'?
1
vote
1
answer
37
views
creating a CTE to filter previous CTE populaiton down. How to find new diagnosis of certain ICD 10 code?
The first CTE grabs all patients with the diagnosis codes for substance abuse, the second CTE should filter down to only NEW paitents.
Meaning of the people in the first CTE, which of thses people ...
- 21
0
votes
1
answer
385
views
How do I calculate a streak of data and show the data that corresponds to it on MySQL?
I'm currently working on a project with NBA stats and I want to check a player's longest streak of his highest amount of points on MySQL. I'm open to a 5-10 game streak to simplify it.
For example: ...
0
votes
1
answer
533
views
How can I recursively query a table containing LTREE to get the tree like structure when some of the parent paths are missing?
Lets say I have this table:
CREATE TABLE nodes (node_path ltree);
INSERT INTO nodes VALUES ('Top.Science');
INSERT INTO nodes VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO nodes VALUES ('...
0
votes
2
answers
312
views
oracle working with CTE with condition
DEFINE fromtable1= true;
WITH firstQuery AS (
SELECT columns FROM table
),
secondQuery AS (
SELECT columns FROM second_table
)
IF @fromtable1= true
BEGIN
SELECT *
FROM ...
- 97
1
vote
0
answers
318
views
Is it possible to limit infinte recursion in an Oracle CTE?
In a DBMS like Microsoft SQL, there is a default limit of 100 recursions, in a recursive CTE, which can be adjusted with:
WITH cte AS (
…
)
SELECT * FROM cte OPTION (MAXRECURSION …);
As far as I ...
- 2,919
1
vote
0
answers
126
views
Mysql8 Unable to order the result of WITH RECURSIVE query
I am using the WITH RECURSIVE query to generate a hierarchy of users. The data and the query used is shared in https://www.db-fiddle.com/f/oTbCcMQb5a4B6V96DAYJHG/0 . The query is returning the results ...
- 121
1
vote
0
answers
239
views
Mimicking a filesystem using PostgreSQL and LTree: how to write a query to insert multiple nodes at once
I'm struggling with how to insert a recursive materialised path using one transaction.
This is the situation:
I'm trying to mimic a filesystem of folders and files. I have a table representing nodes ...
- 11
8
votes
2
answers
682
views
How can I efficiently traverse graph data with this pattern?
I have some relations embodying a directed acyclic graph that includes patterns similar to the following:
I'm looking for an efficient way to traverse this graph data. Here is an example of the ...
- 439
1
vote
0
answers
214
views
How to write query to link complex Parent-Child relationships
We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time.
I need to link these fields together by creating a ...
- 11
4
votes
2
answers
1k
views
Is it possible to update an existing table in a single statement, using the output of a MERGE statement?
I know I can achieve this in many ways - this is a purely academic question to see if it's possible to do this in a single statement, in order to further my knowledge of SQL.
We're splitting an old, ...
- 368
1
vote
2
answers
1k
views
Insert values and add values to foreign key
I have two tables:
CREATE TABLE est (
est_id serial PRIMARY KEY
, est_nom varchar(70)
);
CREATE TABLE bm (
id serial PRIMARY KEY
, est_nom int
, nom varchar(70)
, CONSTRAINT FK_bm_est FOREIGN KEY(...
1
vote
1
answer
182
views
Return value from WITH clause in PL/pgSQL
I am attempting to RETURN an integer from a plpgsql MERGE-related action that uses WITH. The value I want to RETURN is within the WITH statement below - the id. The error I receive with the below code ...
- 13
0
votes
0
answers
225
views
Make this recursive CTE more dynamic
I have this script that enables me to extract hierarchies and the description for each level however the number of levels has to be known beforehand to define the number of select queries necessary ...
- 1
1
vote
3
answers
5k
views
CTE with UNION ALL not working as expected
The query below seems simple and straightforward, yet it produces unexpected results.
CREATE TABLE #NUMBERS
(
N BIGINT
);
INSERT INTO #NUMBERS VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9)
;...
- 151
1
vote
0
answers
50
views
Recursive CTE get
I have the following tables:
Table1
id
entity_id
value
Table2 - Same structure as Table1
id
entity_id
value
Table3
source_id
destination_id
entity_id
Table1 and Table2 are both partitions from ...
- 11
0
votes
1
answer
281
views
How does SQL Server know not to execute Anchor element in Recursion CTE for 2nd Iteration / 1st Recursion?
I've been reading up on the inner workings of CTE Recursion and I'm still not quite sure how SQL Server is implementing the Anchor element in their source code and how SQL Server Query Execution ...
- 275
1
vote
1
answer
1k
views
How to find child and grandchildren tables of a main table by order
I have this script to return all child and grandchildren tables with their level from a database . How do I change this if I only want to return the child and grandchildren tables of a particular ...
- 67
5
votes
1
answer
533
views
CTE Error when installing sp_WhoIsActive
I have several identical (near as I can tell) SQL Servers where I've recently added sp_WhoIsActive (showing some folks how much I like this tool) but one of them will not let me create the stored ...
4
votes
1
answer
78
views
best practice for dependency roots
When a database has a dependency table showing the parent-child relationship of items...
I've mostly seen guides that assume the top level parent has a "parent id" field which is either zero ...
- 41
1
vote
1
answer
1k
views
Why does a Postgres recursive CTE need the list of columns as argument?
This is kind of a subjective question.
If I have a recursive CTE something like:
WITH RECURSIVE r(x, y) AS ( ...
SELECT x, y -- select1
FROM t WHERE ..
UNION
SELECT t.x, t.y -- ...
- 823
0
votes
0
answers
81
views
How to show all the columns data in full outer join
I have 2 tables.
The Result is
Issue is EnteredBy=23 is not displaying because I am using CT1.EnteredBy.
How to display CT11.EnteredBy value instead of NULL
The Actual Query I am using is given ...
- 23
2
votes
0
answers
180
views
Problem with sysdepends while checking dependencies
I have this script to list all dependencies of all views in a database . I get the dependencies in the right order when I use sysdepends ,but I am missing a few dependencies while using sysdepends. ...
- 99
2
votes
2
answers
1k
views
CTE vs. temp table for batch deletes
Two-part question here.
We have a large table (between 1-2 million rows) with very frequent DML operations on it. During low volume periods, we have an agent job to remove older rows to keep the ...
- 255