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?

I'm not familiar with row in postgres but can you just move the where criteria to the subquery instead and filter on the needed field? — sgeddes
– sgeddes, Commented Aug 9, 2016 at 23:04
Yes, I could but that wouldn't fit in the query I am trying to work with. The above is a simplification of it and it were that simple, then yes of course I could simply have done that. — Walker Farrow
– Walker Farrow, Commented Aug 10, 2016 at 1:29

Clodoaldo Neto · Accepted Answer · 2016-08-10 16:37:02Z

6

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

edited Aug 10, 2016 at 16:37

answered Aug 10, 2016 at 0:57

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

2 Comments

Kamil Gosciminski Over a year ago

Note: If you have a column in a table with a name that equals alias on your table, you will get that column, not the entire row in the output.

Clodoaldo Neto Over a year ago

@KamilG. Updated accordingly.

Paarth · Accepted Answer · 2016-08-10 09:45:03Z

1

Alternately You can try this

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

answered Aug 10, 2016 at 9:45

Paarth

5903 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to query a Postgres `RECORD` datatype

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related