'How to get the first field from an anonymous row type in PostgreSQL 9.4?

=# select row(0, 1) ;
  row
-------
 (0,1)
(1 row)

How to get 0 within the same query? I figured the below sort of working but is there any simple way?

=# select json_agg(row(0, 1))->0->'f1' ;
 ?column?
----------
 0
(1 row)

No luck with array-like syntax [0].

Thanks!

sqlpostgresql


Solution 1:[1]

Your row type is anonymous and therefore you cannot access its elements easily. What you can do is create a TYPE and then cast your anonymous row to that type and access the elements defined in the type:

CREATE TYPE my_row AS (
  x integer,
  y integer
);

SELECT (row(0,1)::my_row).x;

Like Craig Ringer commented in your question, you should avoid producing anonymous rows to begin with, if you can help it, and type whatever data you use in your data model and queries.

Solution 2:[2]

If you just want the first element from any row, convert the row to JSON and select f1...

SELECT row_to_json(row(0,1))->'f1'

Or, if you are always going to have two integers or a strict structure, you can create a temporary table (or type) and a function that selects the first column.

CREATE TABLE tmptable(f1 int, f2 int);
CREATE FUNCTION gettmpf1(tmptable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;

SELECT gettmpf1(ROW(0,1));

Resources:

https://www.postgresql.org/docs/9.2/static/functions-json.html https://www.postgresql.org/docs/9.2/static/sql-expressions.html

Solution 3:[3]

The json solution is very elegant. Just for fun, this is a solution using regexp (much uglier):

WITH r AS (SELECT row('quotes, "commas",
and a line break".',null,null,'"fourth,field"')::text AS r)
--WITH r AS (SELECT row('',null,null,'')::text AS r)
--WITH r AS (SELECT row(0,1)::text AS r)
SELECT CASE WHEN r.r ~ '^\("",' THEN ''
            WHEN r.r ~ '^\("' THEN regexp_replace(regexp_replace(regexp_replace(right(r.r, -2), '""', '\"', 'g'), '([^\\])",.*', '\1'), '\\"', '"', 'g')
            ELSE (regexp_matches(right(r.r, -1), '^[^,]*'))[1] END
FROM r

When converting a row to text, PostgreSQL uses quoted CSV formatting. I couldn't find any tools for importing quoted CSV into an array, so the above is a crude text manipulation via mostly regular expressions. Maybe someone will find this useful!

Solution 4:[4]

With Postgresql 13+, you can just reference individual elements in the row with .fN notation. For your example:

select (row(0, 1)).f1; --> returns 0.

See https://www.postgresql.org/docs/13/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Patrick
Solution 2 Tyler
Solution 3 Ezequiel Tolnay
Solution 4 Dan Syrstad

Related Questions