Questions tagged [cte]

Acronym for Common Table Expression. Temporary, reusable subquery that may be recursive.

Filter by
Sorted by
Tagged with
3 votes
3 answers
73 views

Do I need an explicit FOR UPDATE lock in a CTE in UPDATE?

In Postgres 13, I have a table which gets updated frequently. However, the update query is rather complicated and uses the same values multiple times. So, using a CTE seems quite a logical thing to do....
user avatar
  • 289
2 votes
1 answer
51 views

postgresql Recursive Update resulting in Sequential Scan

I have a table with a self-referential foreign key, and I would like to update a given parent, and all of the descendants of the parent, with the same recursive update. When using an UPDATE with a ...
user avatar
4 votes
2 answers
122 views

Index converted from Seek to Scan in CTE

SQL Server version: Microsoft SQL Server 2019 (RTM-CU8-GDR) (KB4583459) - 15.0.4083.2 (X64) Nov 2 2020 18:35:09 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-...
user avatar
  • 279
1 vote
1 answer
39 views

CTE clause not able to be used in final ORDER BY statement

Trying to order by distance using an input geometry t(x), while comparing to geometries from a JOINed table. WITH "nearest_groups" as ( SELECT groups.id, ST_Distance(t.x, locations....
user avatar
0 votes
1 answer
27 views

Can EXPLAIN be used to get some insights about Common Table Expression (CTE)?

I need to find the ancestors in a table having an ID value and a Google research point me to recursive Common Table Expression (CTE) I wonder if EXPLAIN can be used to get some insights on how MySQL ...
user avatar
  • 1,690
0 votes
0 answers
68 views

How to retrieve min/max values and corresponding dates in a table

I'm trying to figure how to retrieve minimum/maximum values and minimum/maximum dates from a data set, but also the date value that corresponds to each minimum/maximum value. Example Data: CREATE ...
user avatar
2 votes
1 answer
86 views

Recursive CTE in PostgreSQL to generate data with different frequencies

I am trying to write a data generation query which uses recursive CTE in Postgresql 14. Consider schema "sc" which includes a function getfreq. getfreq takes an int as param (which ...
user avatar
0 votes
1 answer
1k views

ON CONFLICT DO UPDATE command cannot affect row a second time when trying to pass additional columns from a CTE in postgresql

Based on the SO: https://dba.stackexchange.com/a/46477 answer provided, I have been extending the Function with UPSERT in Postgres 9.5 or later for my own purposes to insert data into multiple ...
user avatar
0 votes
1 answer
52 views

Difference between a query based on CTE and a simple query

i'm doing a select starting from a table named events that is joined with device measurements using a date between function (this time between is a sort of "mobile windowing") in order to ...
user avatar
  • 171
0 votes
2 answers
35 views

Significance of $$ in MongoDB

I was recently given this snippet of code that generates an arbitrary document in MongoDB. I've never encountered $$ in Mongo before and I'm curious about its significance. All I know is that if I run ...
user avatar
1 vote
3 answers
175 views

What are use cases for Recursive CTEs?

I am looking for examples of why you would use a recursive CTE, but the dozens of examples I have found in my web searching basically reduce to two: Generating a sequence Iterating through an ...
user avatar
  • 2,337
0 votes
1 answer
34 views

How can I group / display same date data in one row?

I have a table that gets inserted into every night that is a snapshot of the data. At any point in time, the data might change in the columns (AccountNo stays the same, RunKey is incremented by 1, and ...
user avatar
  • 15
0 votes
1 answer
37 views

How is the postgres actually getting some of the columns? Execution plan question

I've got a table with a bunch of columns. Among them facility_id and po_date. I'm writing a complex query and I have an index on those two columns. You can see from the planner output below that ...
user avatar
  • 307
0 votes
1 answer
174 views

How to create multiple temp tables using records from a CTE that I need to call multiple times in Postgres plpgsql Procedure?

I am already using a CTE expression within a plpgsql Procedure to grab some Foreign Keys from (1) specific table, we can call it master_table. I created a brand new table, we can call this table ...
user avatar
  • 111
0 votes
2 answers
55 views

Improving query (Doing a nasty self join on a range with ctes)

-- The CTE1 is just to create columns that dictate the bound of what is considered the same entry -- Also I do a dense rank by ACT_TIME, and a PARITION BY on ID1, ID2 -- so all ID1/ID2 combos are ...
user avatar
0 votes
0 answers
49 views

JOIN vs WITH differences

apologize if there is the same question. When I started my career, my manager told me that you don't need to use "WITH", you can do anything with "JOIN". Now I am wondering is it ...
user avatar
  • 109
0 votes
1 answer
205 views

Optimizing a recursive CTE or replacing it with a temporary table

I have a recursive query like this: with recursive PCte(id) as ( select p1.id from Product p1 where p1.parent_id is null and p1.serial_number in ('123', '124','125', 'n') union all ...
user avatar
0 votes
1 answer
120 views

MYSQL Error 1064 on INSERT INTO with CTE [closed]

I am trying to write a stored procedure to return a calculated value from a dataset by writing the result to a table. When I run this code on MySQL 8.0.32, I get an error 1064 Syntax error on the ...
user avatar
  • 1
0 votes
2 answers
83 views

Update rows based on a recursive CTE query result

This query shows all lines recursively based on a uuid / parentUUID relationship: WITH RECURSIVE files_paths (id, parent) AS ( SELECT uuid, parentuuid FROM core_data WHERE uuid = '2c828bbb-...
user avatar
0 votes
4 answers
227 views

Slow CTE update query

I have a query that is running for more than three hours without completing. I realized that the bad part is the Filter operator which has a cost of 89%. WITH FASES_NUMERADAS_CTE AS ( ...
user avatar
1 vote
2 answers
79 views

SELECT... with LIMIIT, but iterate forward getting other records?

I am doing a SELECT * FROM... with LIMIT query inside of a LOOP in my pgplsql function/procedure - something like this, e.g. $$ DECLARE BEGIN LOOP exit when get diagnostics n_rec_count = 0; ...
user avatar
  • 111
2 votes
1 answer
91 views

Why my not-null columns become nullable in my CTE recursive query?

I have a simple hierarchical table. Let's say it has these columns: Id, [Guid], ParentId, Title Only ParentId is nullable. Other columns are not nullable. I have created a recursive query to show me ...
user avatar
1 vote
2 answers
526 views

How can a cheap function in a SELECT make the whole query slow?

I'm using Postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts). I can't figure out why Query2 below is so much slower than Query1. They ...
user avatar
  • 203
8 votes
2 answers
1k views

Nested CTEs Returning Incorrect Results in Certain Situations

OK, first let me say I got this issue from: Seeking Tsql wrong join explantion in CTE [closed] I was trying to assist to figure out the issue, but got stumped when trying to debug the code one step ...
user avatar
0 votes
1 answer
151 views

How to eliminate duplicates made by recursive CTE [closed]

I have following schema in PostgreSQL 12 (objects and their relations are versioned by date ranges): CREATE TABLE tmp_deps ( id bigint, code text, name text, start_date date, ...
user avatar
  • 123
-1 votes
1 answer
84 views

How to execute join statement dynamically without using static group id? [closed]

I work on SQL server 2014 I need to run this statement below dynamically without using group id static because I don't know maximum group id using may be 10,15,20 so How to run query below dynamically ...
user avatar
4 votes
1 answer
313 views

How to turn a set of flat trees into a single tree with multiple leaves?

We have this beautiful Postgres tree generator. Yet it kind of produces cuts of a tree not a whole tree all at once: item_id jsonb_pretty 1 { "title": "PARENT", "...
user avatar
  • 65
3 votes
1 answer
36 views

I would want, through recursive CTEs, a query to return all cars at tree-shaped, but sorted in each category

Here is both table and data for testing: CREATE TABLE if not exists cars ( id serial PRIMARY KEY, parent_code INT, descr CHARACTER VARYING(50) NOT NULL ); -- Level 1 INSERT INTO cars(...
user avatar
  • 77
2 votes
1 answer
186 views

How can I change string output in a hierarchy tree with PostgreSQL?

I have a table like this: ------------------------------------------------- | id | description | parent_id | cost -------------------------------------------------- | 1 | Radiology ...
user avatar
  • 47
1 vote
1 answer
131 views

How to merge multiple rows in one column with hierarchy Postgresql 12?

I have a table like this to save the catalog of 90 services: ------------------------------------------------------------------------------------- | id | service | subservice | description ...
user avatar
  • 47
4 votes
1 answer
191 views

INSERT duplicate rows for multiple, cascading 1:M child tables

Imagine multiple parent-child tables with 1:M relationships. I would like to "cascade"-insert duplicate rows based on root parent table row selection. Each table has IDENTITY primary key and ...
user avatar
  • 451
1 vote
1 answer
76 views

Order a nested table with path index according to list_order in SQLite

I have a SQL table with this nested structure: CREATE TABLE items (`id` INTEGER, `item_name` VARCHAR(6), `parent_id` INTEGER, `list_order` INTEGER); My SQLite version does not support window function,...
user avatar
1 vote
1 answer
75 views

Can CTE simplify repeated and possibly recursive joins?

I have three tables organized as depicted below, showing foreign keys and unique fields (green): That represents email messages received by a server. (Sorry I couldn't simplify more. Full version ...
user avatar
  • 115
2 votes
1 answer
79 views

Why can't unused columns and tables be ignored in a recursive CTE

Usually, SQL Server can optimize away any unused columns in the execution plan, and unused joined tables are not queried. But as soon as a recursive CTE comes into play, apparently all columns and ...
user avatar
  • 121
1 vote
1 answer
177 views

How do I return all column values for the recently inserted row/rows?

I have these two tables in a PostgreSQL 12 database: CREATE TABLE public.test ( entity_id uuid PRIMARY KEY DEFAULT uuid_generate_v4() ); CREATE TABLE public.test_registration ( entity_id uuid ...
user avatar
  • 111
0 votes
1 answer
33 views

Getting Indirect parents

I have following different tables LeafChilds ChildId, ChildName 1, Child1 2, Child2 Group GroupId, GroupName 1, Group1 2, Group2 3, All GroupChildMapping GroupId, ChildId 1, 1 2, 1 2, 2 ...
user avatar
  • 9
0 votes
0 answers
55 views

Improving query performance with two inner joins on tables that have > 40M rows

Context I have three tables - nodes, edges and sections. This is a graph network of paths from open street maps where edges are created from two neighbouring 3D nodes (that give distance, elevation ...
user avatar
0 votes
0 answers
65 views

Oracle 17410(connection reset) when using count(*) on cross join cte with json_table

Met strange behavior using query with json_table in cte at oracle - connection reset from server, without details. Illustrating artificial query is: with data as ( select 5 as some_value from ...
user avatar
0 votes
1 answer
1k views

Recursive CTE to get Grandparent, parent, children [duplicate]

I have a data structure that relies on a parent/child model. The top level parent can have multiple children where they can also be parents. Here's an example of how things can look in the tree: ...
user avatar
  • 309
6 votes
1 answer
639 views

String manipulation in Recursive CTE

I am trying to make something like the following work: WITH results(n, string ) AS ( SELECT 1,'lol' UNION ALL SELECT n+1, string + ' lol' FROM results ...
user avatar
  • 249
2 votes
1 answer
72 views

Org chart CTE with exact index but plan shows expensive sort node

I've got a moderately large table (148 million rows) that's got pretty common org chart attributes to it. Names are changed, but it's the same idea. I was thinking of adding a hierarchyid column to ...
user avatar
0 votes
0 answers
36 views

How To Allocate 2+ Balances Across 2+ Timeslots, Without A Cursor

Please refer to the following schema at SQL Fiddle when reading the use case below. In a timekeeping application I'm working with, there is a business process where individuals are entitled to a ...
user avatar
  • 1,913
1 vote
2 answers
739 views

Update Column from Recursive CTE (MySQL/MariaDB) [closed]

I need to update the same table I'm using to create a recursive cte. The cte works as a select statement, but I can't get it to actually insert those values back into the table. My code is below- ...
user avatar
0 votes
1 answer
873 views

Insert multiple rows into a table with id from other table if not exists insert to other table

I have done similar task where I can insert a row into a table if data doesn't exists: WITH user_exists AS ( Select id from users where username='%s' ), user_new ...
user avatar
  • 121
0 votes
1 answer
34 views

Pivoting 4 by 44 result set

I have a SQL Server query that gets the sales data for a 44 store market chain, it includes 4 values for each store (Sales, COGS, GP and GP Margin), I declared a CTE and joined it 44 times for each ...
user avatar
1 vote
1 answer
45 views

SQL: Query to insert records into the table if there is not even one record with certain condition

I have a table train_statuses. With the schema as below: CREATE TABLE public.train_statuses ( id uuid NOT NULL, status text NOT NULL, updated_at timestamp without time zone NOT NULL ); ...
user avatar
  • 131
0 votes
1 answer
57 views

How to improve poor performance with two joined CTE expressions on hierarchical data?

I have a table of hierarchical data where I want to select entries that, 1) only include matches that are descendants of a given parent, and 2) returns the full hierarchical path of the matched ...
user avatar
  • 103
0 votes
1 answer
34 views

PostgreSQL skips WITH query

Consider this CTE: WITH division_by_zero AS ( SELECT 1/0 ) SELECT 42 It returns 42 instead of raising an error. How can I force PostgreSQL to evaluate the SELECT 1/0?
user avatar
  • 237
1 vote
1 answer
96 views

Index on a Computed Column(using udf)

I am trying to create a computed column using a udf. I keep hitting the cannot be persisted because the column does user or system data access, when i try creating a index for that column or even for ...
user avatar
  • 11
0 votes
1 answer
449 views

How to rewrite slow CTE construction to match speed of temp tables

I think the general advice of this community is to avoid temp tables in favor of CTEs. However, I sometimes encounter situations in which CTE constructions are very slow, while their temp table ...
user avatar
  • 123

1
2 3 4 5
7