From: | Eagna <eagna(at)protonmail(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Problem with LATERAL |
Date: | 2022-10-13 07:05:48 |
Message-ID: | Ibm2WcUuz9rJSUtznoh50x1Kn7YekKSFyMijP6xIVnrEl9WfWmEbVYboiEdEp5Alpin6LlkQGnDXcsVs_R7vSR0DxhtHShOtVj8B8NhAcHo=@protonmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Good Morning all,
I am having a problem understanding a simple LATERAL join - I'm working on grasping them.
All tables and data are at the bottom of this question and on the fiddles, SQL Server (working) and Postgres (not working).
SQL Server fiddle - https://dbfiddle.uk/hjBBd87B
Postgres fiddle - https://dbfiddle.uk/PihnqTwG
I have the following - create table scripts and sample data are at end of this question and on the fiddles.
The query in question which works on SQL Server is the following:
SELECT o.order_id,
o.total_price - COALESCE(p.paid, 0) AS remaining
FROM _order o
CROSS APPLY (
SELECT SUM(p.amount) AS paid
FROM payment p
WHERE p.order_id = o.order_id
) AS p
WHERE o.total_price > ISNULL(p.paid, 0);
relatively simple one would have thought! I tried to convert this into a Postgres query as follows:
SELECT o.order_id,
o.total_price - COALESCE(sub.paid, 0)
FROM _order o
LEFT JOIN LATERAL (
SELECT SUM(p.amount) AS paid
FROM payment p
WHERE p.order_id = o.order_id
) AS sub
WHERE o.total_price > ISNULL(sub.paid, 0); -- << line 10 - Error occurs!
but I receive the error:
ERROR: syntax error at or near "WHERE"
LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0);
I would be grateful if I could get a working query and also for an explanation as to what it is I'm doing incorrectly?
The payment table has no primary key. Any input on the suitability of creating a surrogate one would also be appreciated.
Please let me know if there's any important information missing.
Rgs and TIA,
E.
CREATE TABLE _order
(
order_id INT NOT NULL PRIMARY KEY,
total_price INT NOT NULL
);
INSERT INTO _order VALUES
(1, 1000), (2, 2000), (3, 3000), (4, 4000);
CREATE TABLE payment
(
order_id INT NOT NULL,
amount INT NOT NULL,
CONSTRAINT payment_order_id_fk FOREIGN KEY (order_id) REFERENCES _order (order_id)
);
CREATE INDEX pt_order_id_ix ON payment (order_id); -- normal indexing of foreign key field
INSERT INTO payment VALUES
(1, 500), (2, 2000), (3, 1000), (3, 500), (3, 750); -- note - no payment for order_id = 4
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Rouhaud | 2022-10-13 07:30:11 | Re: Problem with LATERAL |
Previous Message | Jeffrey Walton | 2022-10-13 05:27:16 | Re: Re: Does psqlodbc_11_01_0000-x64 support special characters? |