PostgreSQL SQL Error [42703]: ERROR: column 'minute' does not exist

Question

I have the below query and I am getting the following error:

SQL Error [42703]: ERROR: column 'minute' does not exist

However I am not referencing any field called minute.

SELECT customer_id,
MAX(created_at) last_order_date,
MAX(created_at) + ((SELECT EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at)) 
FROM (SELECT customer_id, created_at
FROM (SELECT customer_id, created_at, rank() over (partition by customer_id order by created_at desc) lasttwo
FROM orders) sub
WHERE sub.lasttwo <= 2
AND SUM(DATEDIFF(MINUTE,MIN(created_at),MAX(created_at))) > 2) s2) ::text||' minute')::INTERVAL AS nextdate,
(SELECT AVG(total_price - total_tax) 
FROM (SELECT customer_id, created_at, total_price, total_tax
FROM (SELECT customer_id, created_at, total_price, total_tax, rank() over (partition by customer_id order by created_at desc) lasttwo
FROM orders) sub
WHERE sub.lasttwo <= 2
AND SUM(DATEDIFF(MINUTE,MIN(created_at),MAX(created_at))) > 2) s2) nextval 
FROM orders 
GROUP BY customer_id

This is not Postgres syntax: DATEDIFF(MINUTE,MIN(created_at),MAX(created_at)). Why are you using SQL Server syntax in a Postgres query? — Gordon Linoff
– Gordon Linoff, Commented Mar 6, 2019 at 20:28
First figure out which dbms you are using. Then find out what date functions are available for the dbms you are using. Using other dbms function on another dbms, then asking why it doesn't work is probably not a good question. — Eric
– Eric, Commented Mar 6, 2019 at 23:27

Matt · Accepted Answer · 2019-03-06 21:48:26Z

1

Needed to use EXTRACT rather than DATEDIFF

SELECT customer_id,
MAX(created_at) last_order_date,
MAX(created_at) + ((SELECT EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at)) 
FROM (SELECT customer_id, created_at
FROM (SELECT customer_id, created_at, rank() over (partition by customer_id order by created_at desc) lasttwo
FROM orders) sub
WHERE sub.lasttwo <= 2
AND SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2) s2) ::text||' minute')::INTERVAL AS nextdate,
(SELECT AVG(total_price - total_tax) 
FROM (SELECT customer_id, created_at, total_price, total_tax
FROM (SELECT customer_id, created_at, total_price, total_tax, rank() over (partition by customer_id order by created_at desc) lasttwo
FROM orders) sub
WHERE sub.lasttwo <= 2
AND SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2) s2) nextval  
FROM orders 
GROUP BY customer_id

edited Mar 6, 2019 at 21:48

answered Mar 6, 2019 at 20:36

Matt

15.1k28 gold badges109 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL SQL Error [42703]: ERROR: column 'minute' does not exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related