4,332 questions
1
vote
5
answers
84
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
171
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
164
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 ...
- 67
5
votes
4
answers
198
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
174
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
53
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
80
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
219
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
56
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
84
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
190
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
75
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
214
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
135
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,863
1
vote
1
answer
138
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,230