CODEWITHSUNDEEP
mysqlsqlpostgresql
Michal
Michal

Reputation: 139

mySQL to PostgreSQL conversion (particular query)

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

Upvotes: 0

Views: 36

Answers (1)

cdhowie
cdhowie

Reputation: 169353

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 that1. 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)

1 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.

Upvotes: 1

Related Questions