"select abc from (select 1) as abc" produces "(1)" instead of "1"

Question

In Postgre, why does

select abc from (select 1) as abc

produces:

(1)

and

select * from (select 1) as abc

produces:

1

That's really strange to me. Is that the case with MySQL, Oracle, etc? I spent hours figuring out why my conditions were failing...

Answer 1

What does

select foo from ( select 1 foo ) as abc

produce?

Answer 2

The rows returned by your queries have different type: the first one is ROW(INT), while the second one is INT.

MySQL and others lack this feature.

In your first query, you are selecting a whole ROW as a single column. This query

SELECT abc FROM (SELECT 1, 2) abc

will produce (1, 2), which is a single column too and has type ROW.

To select the INT value, use:

SELECT  abc.col
FROM    (
        SELECT  1 AS col
        ) abc