mySQL to PostgreSQL conversion (particular query)

Question

Query below is working fine both in mySQL and Oracle:

select * from (
(select distinct msg_key from lct_messages where msg_step = 2) x,
(select distinct msg_step from lct_messages where msg_next_step = 3) y
)

Its purpose is to get two values in one row in separate columns. Can you help me in transforming this query to the one that will be working in Postgres?

When I run the one below I receive error:

SELECT (
(SELECT DISTINCT "MSG_KEY" FROM LCT_MESSAGES WHERE "MSG_STEP" = 2) x,
(SELECT DISTINCT "MSG_STEP" FROM LCT_MESSAGES WHERE "MSG_NEXT_STEP" = 3) y
)

Regards Michal

And what is the error you get? Let me guess: "subquery in FROM must have an alias"? — user330315
– user330315, Commented Aug 12, 2014 at 22:30
in what way do the values of X relate to the values of Y? If you want them on the same row -- in separate columns -- then they have to be related in some way — Brian DeMilia
– Brian DeMilia, Commented Aug 12, 2014 at 22:33
These two values not necesarly are taken from the same row in table lct_messages. What they share in common is parameter that they receive: — Michal
– Michal, Commented Aug 12, 2014 at 22:41
$sql = pg_query_params ($db, $query, array( $_POST['STEP'] ,$_POST['STEP']) — Michal
– Michal, Commented Aug 12, 2014 at 22:41

cdhowie · Accepted Answer · 2014-08-12 22:46:04Z

1

You have an extra pair of parens in there. This combines the two results into one single result column of type record, and the syntax to create a record object does not allow defining aliases of the record columns like that¹. This is why it chokes on the x with a syntax error.

Try this:

SELECT
    (SELECT DISTINCT "MSG_KEY" FROM LCT_MESSAGES WHERE "MSG_STEP" = 2) x,
    (SELECT DISTINCT "MSG_STEP" FROM LCT_MESSAGES WHERE "MSG_NEXT_STEP" = 3) y;

This works correctly for me given a simple dummy table:

$ WITH LCT_MESSAGES ("MSG_KEY", "MSG_STEP", "MSG_NEXT_STEP") AS (VALUES
    ('a', 1, 2),
    ('b', 2, 3),
    ('c', 3, 4)
)
SELECT
    (SELECT DISTINCT "MSG_KEY" FROM LCT_MESSAGES WHERE "MSG_STEP" = 2) x,
    (SELECT DISTINCT "MSG_STEP" FROM LCT_MESSAGES WHERE "MSG_NEXT_STEP" = 3) y;

 x | y
---+---
 b | 2
(1 row)

¹ See section 8.15.2 "Composite Value Input" of the PostgreSQL 9.1 documentation for a description of the syntax you were inadvertently using: "The ROW expression syntax can also be used to construct composite values. ... The ROW keyword is actually optional as long as you have more than one field in the expression ..." So what it was seeing was equivalent to this:

SELECT ROW(
    (SELECT DISTINCT ...) x,
    (SELECT DISTINCT ...) y
);

And the x is indeed invalid syntax in this kind of expression.

edited Aug 12, 2014 at 22:46

answered Aug 12, 2014 at 22:38

cdhowie

172k25 gold badges303 silver badges324 bronze badges

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

1 Comment

Michal Over a year ago

Works! Thank you very much. My mistakes: to many '(' and no need for '* from'

Collectives™ on Stack Overflow

mySQL to PostgreSQL conversion (particular query)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related