How to query a Postgres `RECORD` datatype

Question

I have a query that will return a row as a RECORD data type from a subquery - see below for example:

select *
from (
    select row(st.*) table_rows
    from some_table st
) x
where table_rows[0] = 339787

I am trying to further qualify it in the WHERE clause and I need to do so by extracting one of the nodes in the returned RECORD data type.

When I do the above, I get an error saying:

ERROR:  cannot subscript type record because it is not an array

Does anybody know of a way of implementing this?

Answer 1

Use (row).column_name. You can just refer to the table itself to create the record:

select *
from (
    select r
    from some_table r
) x
where (r).column_name = 339787

There is a small chance that later a column is created with the same name as the alias you chose and the above query will fail as select r will return the later created column in instead of the record. The first solution is to use the row constructor as you did in your question:

select row(r.*) as r

The second solution is to use the schema qualified name of the table:

select my_squema.some_table as r

Answer 2

Alternately You can try this

select *
from (
    select *
    from tbl
) x
where x.col_name = 339787