4,334 questions
1
vote
1
answer
115
views
Avoid invalid cast inside CASE expression, in a query plan not respecting JOIN
I'm trying to get partitioned tables from a postgres DB. Our partitions have the date in them in various formats which I'm extracting like this
SELECT tab.relname AS table_name,
(CASE
...
- 1,016
1
vote
5
answers
102
views
Grouping rows, and then deleting only a sub range (based on their dates) from each of those groups
I use Postgres on my web server in order to record incoming queries into a table calls2, basically writing a single row each time with lots of repeating information, such as a date field ("when&...
- 12.4k
2
votes
2
answers
223
views
How to avoid "ON CONFLICT DO UPDATE command cannot affect row a second time" error in WITH statement
I have two tables: demo at db<>fiddle
Table keywords has two columns id and v (which hold the keyword's value)
create table keywords(
id int generated always as identity primary key
,v text ...
- 12.4k
1
vote
2
answers
181
views
How to efficiently calculate an exponential moving average in postgres?
I'm trying to calculate the average true range on some time series dataset stored in postgres. Its calculation requires a 14 period exponential moving average of true range which based on the answer ...
- 61
5
votes
4
answers
226
views
UPDATE with LEFT JOIN and condition IS NULL
I have the following table definitions:
Table public.messages:
Column
Type
Collation
Nullable
Default
ip
text
msg
text
ignore
boolean
Table public.host:
Column
Type
Collation
Nullable
Default
ip
text
...
-4
votes
1
answer
182
views
Recursive CTE looking for all combinations of values that equal one target amount
I found examples online using a recursive CTE to find all combinations of values that equal one target amount. The database column ledger_amount is DECIMAL(26,6).
DECLARE @TARGET_AMOUNT DECIMAL(26, 6) ...
- 499
2
votes
1
answer
69
views
Do explicit locks in postgresql CTEs lock every row even with a cursor?
Let's say I have a table
CREATE TABLE mytable (
user_id bigint,
someval text
)
In my application, I want to lock and fetch every row for some given user_id e.g. 123 to do some additional ...
- 119
3
votes
2
answers
81
views
Cumulative Stock Query without the participation of an Initial Stock Table
I'm designing a stock management program, here I have an Incoming Table like below:
id (integer)
date (date)
type (varchar)
warehouse_id (tinyint)
goods_id (integer)
quantity (decimal)
price (decimal)...
- 145
3
votes
1
answer
234
views
How to implement this SQL query with CTE in Exposed DSL?
I have a SQL query that uses a Common Table Expression (CTE) with window functions, and I need to implement it using Kotlin's Exposed DSL. Since Exposed doesn't have built-in CTE support, I'm looking ...
user31219547
2
votes
0
answers
72
views
Attempting to understand CTE behaviour in clickhouse with ReplacingMergeTrees and FINAL
We Have the following table in clickhouse:
CREATE TABLE db.user
(
`id` String,
`name` String,
`version` UInt64 DEFAULT 0,
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{uuid}/{shard}',...
0
votes
1
answer
97
views
Why OPENQUERY fails with multiple CTEs with a where clause in some cases but not others
I have a query that contains two CTEs, one based on a linked Oracle server (from a SQL Server) that fails. I can fix it by doing any of the following: add a select limit (e.g. TOP 1000) to the SELECT ...
4
votes
5
answers
200
views
Query to find the product name which has a continuous increase in sales every year
Product table with columns - PRODUCT_ID PRODUCT_NAME
PRODUCT_ID
PRODUCT_NAME
100
NOKIA
200
IPHONE
300
SAMSUNG
400
OPPO
Sales table with columns - SALE_ID PRODUCT_ID YEAR QUANTITY Price
SALE_ID
...
2
votes
1
answer
76
views
Recursive SQL query to hierarchyId
I currently have the problem that I get performance problems when migrating a large amount of data, the more data is migrated. As I cannot query the assignments of children to the TeamIds directly via ...
- 67
0
votes
0
answers
253
views
How do I programmatically build a WITH 'ALIAS' AS (SELECT ...) CTE in sqlglot?
I'm trying to build a Common Table Expression (CTE) like this using the sqlglot Python library:
WITH A AS (
SELECT * FROM my_table
)
SELECT * FROM A
When I attempt to build this with the ...
0
votes
4
answers
137
views
Recursive CTE to insert an entry for every day
Consider the following:
IF OBJECT_ID('tempdb..#TetstData') IS NOT NULL
DROP TABLE #TetstData
CREATE TABLE #TetstData
(
Id INT NOT NULL,
PurchaseDate DATE NOT NULL,
DepreciationRate ...
- 7,883
1
vote
1
answer
166
views
How to use a recursive CTE in a JOIN statement
I'm working on converting an old database and queries to recursive CTEs. In the current code, I'm storing hierarchical data as a dash separated list of 0 padded strings. So as an example, I may have ...
- 3,240
4
votes
5
answers
156
views
Cannot update postgres table from cte, value goes missing
I have a Postgres Timescaledb table (mysolar) in which I collect energyvalues (time,energy) from my solarpanels. Sometimes, for whatever reason, I get a ‘zero-value’ reading. (see example table). I ...
- 61
0
votes
2
answers
144
views
SQL query returns `ERROR: syntax error at or near "union"`
I am a beginner in SQL. What is the syntax issue with the query shown here?
I just needed the random rows to be present n number of times.
with recursive cte1 as
(
select 1 as idx, company_id, ...
- 21
3
votes
2
answers
153
views
Recursive query for nomenclature like table with parent contains sum of all cost from children
I have a table like this:
COMPOSANT
COMPOSE
QTE
PA
COST
LEVEL
PARENT1
CHILD1
24
0
PARENT1
CHILD2
2
0
CHILD1
CHILD11
10
1
CHILD1
CHILD12
4
3
12
1
CHILD11
CHILD111
100
1
100
2
CHILD2
CHILD21
5
10
50
1
...
- 33
1
vote
3
answers
105
views
SQL query to aggregate and count non-null values across multiple columns
I have the following database schema. It looks at shoppers and how many orders they have made from three websites in a network.
ID
Name
Country
website1_Orders
website2_Orders
website3_Orders
123
...
- 11
1
vote
3
answers
147
views
Postgres CTE with insert and update of same row
I have a table with auto generated id like below :
CREATE TABLE wf_policy
(
id BIGSERIAL PRIMARY KEY,
name TEXT NOT NULL,
time BIGINT,
ver BIGINT ...
- 3,577
1
vote
1
answer
52
views
Traversing graph using recursive CTE - using "array" to store visited vertices
I'm studying graph traversal using recursive CTEs (learnSQL course). The question asks about visiting every city from a starting city. They use a "path" string built up from appended city ...
- 1,507
1
vote
2
answers
118
views
Detect 1st row of an ordinal chain using recursive CTE or alternative
I have the following data:
UserId
SubId
EduId
SemOrd
1
701
500
10
1
702
255
11
1
703
500
12
1
704
500
14
1
705
500
15
1
706
500
16
1
707
500
17
2
...
...
..
Given a UserId and SubId, I want to find ...
- 2,187
0
votes
2
answers
58
views
how can I solve the error in terms of "Column 'product_aggregation.product_key' is invalid in the select list."
This code is written to report consolidates key product metrics and behavior, however, I get this error, that I don't know how to handel it:
Msg 8120, Level 16, State 1, Procedure report_products, ...
- 15
0
votes
1
answer
48
views
Issue with Subquery/Outer Query with BOM LEVEL - Results Messed Up
I would greatly appreciate any suggestions or help regarding the use of outer queries or subqueries. I want to use multiple subqueries and join them into one query, but I’m encountering an issue. Here ...
1
vote
1
answer
55
views
How to UPDATE a JSON column with a nested aggregation?
How do I write an UPDATE statement for a JSON column using a nested aggregation?
I have two tables, jobs and job_statuses. Jobs has a rows with a group and status column and job_statuses has a ...
- 2,025
0
votes
1
answer
180
views
Passing values from a CTE (Common Table Expression) to user-defined functions (UDF) in Spark SQL / Databricks
I'm trying to pass a value from a CTE to my function (UDF). Unfortunately, it's not working.
Here is the first variant:
WITH fx_date_new AS (
SELECT CASE
WHEN '2025-01-01' > ...
- 1
1
vote
0
answers
149
views
Converting SQL Server CTE with multiple recursive members to Postgres throws error as multiple recursive members are not allowed
The SQL below gets ancestors in SQL Server using multiple recursive members, copied from an article by Itzik Ben-Gan in ITProToday back in 2013;
DROP TABLE IF EXISTS tmp_FamilyTree;
CREATE temporary ...
- 43
0
votes
1
answer
77
views
SQL CTE Joins are producing wrong table output for Count statement
I've been trying to create a feature analysis for our app by counting specific attributes of users from given database tables and store it in an analytical view via SQL CTE's
As soon as I add more ...
- 17
-1
votes
1
answer
53
views
For each day between and upper and lower id, add a row with the same info but only that day in the start/end columns
I had a look at the following questions (it allowed me to get to where I am stuck):
38986774
64371506
27489564
Updated:
Table Definition:
DECLARE @DowntimeFact TABLE
(
[ID] INT IDENTITY(1, 1),
...
- 192
0
votes
1
answer
83
views
Why does using `LIMIT` in this CTE give different results compared to using `LIMIT` in a subquery that refers to that CTE?
We have this query:
WITH
childObject AS (
SELECT
JSONB_BUILD_OBJECT(
'childObjectId',
(
SELECT
values_table.value::text
FROM
...
1
vote
1
answer
107
views
Nested JSON object from composite results - PostgreSQL
Postgresql 17.
Having recipes management system with the following tables:
Chefs -> Recipes -> Ingredients
-> Sausages
Each table contains a field with the id of its ...
- 303
1
vote
1
answer
100
views
Recursive CTE with cycle detection using path array
I have a recursive CTE definition (graph) in Postgres that involves cycle detection: fiddle
CREATE TABLE relationships (
subject_type,
subject_id,
subject_relation,
resource_type,
...
-1
votes
2
answers
111
views
Sqlite3, how to make an INSERT statement inside a SELECT query?
I want to do, in exactly one sqlite3 query, an operation that :
Checks for the existence of a value in my table (call it V), written in a row indexed by A
If V exists and is equal to V_Param (...
- 9
0
votes
1
answer
110
views
CTE query for hierarchical data excluding some records despite having full hierarchy [closed]
I have a table that stores hierarchical data, where each record may have a parent_item_code referencing another record in the same table. I'm using a Common Table Expression (CTE) to build the full ...
- 5
0
votes
2
answers
113
views
SQL recursive CTEs to perm array
I'm having some trouble formulating the correct recursive behaviour to get what I want in SQL.
I'm limited to the BigQuery environment and I want to avoid using any JavaScript if I can, so I wanted to ...
- 1,693
0
votes
3
answers
102
views
CTE to match days after discharge and days supply from prescription
I'm assuming CTE is the most efficient way to do this, but any suggestions would be great. I think I can do this in multiple steps a different way if I have to, but would love to make this as ...
- 25
0
votes
1
answer
37
views
CTE CONCAT not joining strings
I'm new to SQL. I'm using MySQL Ver 15.1 Distrib 10.3.27-MariaDB. For the following. I'm trying to compute paths in a graph like this: a->b.
create table edges
(
start varchar(4),
end ...
- 113
0
votes
3
answers
182
views
Table based on values in CTE
I have this SQL using a table-value constructor:
select * from (values ('a', 'b'), ('c', 'd')) v (e, f)
This works to select data from the table with alias v.
However, I want to build a with ...
- 4,356
-1
votes
1
answer
84
views
How do I use a PostgreSQL recursive CTE when the iteration number would be part of the "where ? between ?" clause
I have put together a query, using a CTE, to track the status of a set of pg_cron batch jobs running on our RDS server. I would like to make it more dynamic by changing it to be recursive.
Any help ...
- 21
1
vote
1
answer
220
views
Use CTE with JOIN to Update a Table in PostgreSQL
I attempted to use a Common Table Expression (CTE) to update a table in PostgreSQL with the following query:
WITH TMP AS (
INSERT INTO TABLE1 (...)
SELECT (...) FROM TABLE2
RETURNING *)
...
- 29
0
votes
0
answers
19
views
Creating a VIEW with PostgreSQL in PgAdmin
Having to query several steps to get a "totals" result, I used a cte and I have also used a subquery with OVER (PARTITION BY) command.
Which query, the cte or the subquery with OVER (...
0
votes
2
answers
112
views
Will the CTE reflect changes in the data?
Lets say you create a CTE in Oracle, then manipulate some of the data in the tables its pulling from. Once you select the values from the CTE will the values changed be reflected in the result set? I ...
- 17
1
vote
2
answers
146
views
Select counts from one table based in the results from another table
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
...
- 15
0
votes
0
answers
59
views
MySQL 8.0 Query to MariaDB [duplicate]
I created the following query in MySQL 8.0, but when I try to add using phpMyAdmin I get the following error:
MySQL said: #1064 - You have an error in your SQL syntax; check the manual that ...
- 11
1
vote
2
answers
151
views
Efficiently mark and retrieve rows
I'm working on a SQL Server query where I need to perform the following tasks efficiently:
Execute a complex and resource-intensive SELECT statement to retrieve a subset of rows.
Update same table ...
- 31
0
votes
0
answers
27
views
What is the difference between PL SQL CTE and cache Lazy table in spark
When a spark sql "CACHE TABLE AS SELECT * FROM TABLE" is cached and can be used in reduced scanning of the original files in future queries.
And
A CTE in sql does the same when a same ...
- 1
0
votes
1
answer
130
views
Recursive CTE returns duplicate rows
In SQL Server, I have a Transactions and a TransactionDetails table.
Transactions table has the following columns:
TransactionId
Amount
ProcessedDate
TransactionDetailsId
TransactionDetails table has ...
0
votes
2
answers
184
views
SQL Oracle adding new rows to CTE
I'm trying to add addition rows to a CTE table using Oracle SQL and I can't quite get it to work. I've read up and it appears using INSERT INTO () is my best option, but I feel I am missing something ...
1
vote
1
answer
29
views
MySQL Recursive CTE to retrieve all Attached Nodes of a Table
I have a recursive structure with the following two tables:
tree represents a hierarchy of nodes where the top node has parent = NULL.
the items can be attached to any of the tree nodes, anywhere in ...
- 3,319