Title
#users-market-data
s

Shriram Sunder

09/05/2022, 4:07 PM
I've also encountered another weird issue, I don't know if this is standard in SQL (I've been working with Postgres for the past 8 years and I've not encountered this). I'm open to all criticism. I've got a table full of aggregates called 'aggs', and I've got a table full of urls called 'urls' that are used to pull down the aggregates from a data provider. I use a small program to check which urls from 'urls' have not been used to populate 'aggs' yet, and then use them to fill up 'aggs'. But I need a query to determine which urls are "done", by checking 'aggs', as a sanity check. So I just update the table 'urls', based on what's in aggregates using the following query, or so I thought. 1. The below query does not work, it throws a
Invalid column: q.timestamp
error ONLY when I include both the conditions.
WITH q AS (
  SELECT ticker, timestamp
  FROM aggs 
  LATEST ON timestamp 
  PARTITION BY ticker
)
UPDATE urls u
SET done = true
FROM q
WHERE q.timestamp <= u.end
AND q.ticker = u.ticker;
2. This query below works however.
WITH q AS (
  SELECT ticker, timestamp
  FROM aggs 
  LATEST ON timestamp 
  PARTITION BY ticker
)
UPDATE urls u
SET done = true
FROM q
WHERE q.timestamp <= u.end;
--AND q.ticker = u.ticker;
3. And this query works.
WITH q AS (
  SELECT ticker, timestamp
  FROM aggs 
  LATEST ON timestamp 
  PARTITION BY ticker
)
UPDATE urls u
SET done = true
FROM q
WHERE q.ticker = u.ticker;
What's the issue with the first statement ? Obviously, I don't need just one condition, I need both of them. The schema for 'aggs' is as follows:
CREATE TABLE 'aggs' (
  ticker SYMBOL capacity 256 CACHE,
  timespan STRING,
  multiplier LONG,
  open DOUBLE,
  high DOUBLE,
  low DOUBLE,
  close DOUBLE,
  volume DOUBLE,
  vw DOUBLE,
  n DOUBLE,
  timestamp TIMESTAMP
) timestamp (timestamp) PARTITION BY DAY;
The schema for 'urls' is as follows:
CREATE TABLE 'urls' (ticker SYMBOL capacity 256 CACHE, start TIMESTAMP,end TIMESTAMP,
url STRING,
done BOOLEAN
) timestamp (end) PARTITION BY DAY;
I think it's related to this issue: https://github.com/questdb/questdb/issues/2329