Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

postgresql

=# 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!

like image 576
Nobu Avatar asked May 19 '15 22:05

Nobu


People also ask

What is $1 SQL?

Arguments to the SQL function are referenced in the function body using the syntax $n: $1 refers to the first argument, $2 to the second, and so on. If an argument is of a composite type, then the dot notation, e.g., $1.name, can be used to access attributes of the argument.

How do I SELECT a row in PostgreSQL?

If you want to select data from all the columns of the table, you can use an asterisk ( * ) shorthand instead of specifying all the column names. The select list may also contain expressions or literal values. Second, specify the name of the table from which you want to query data after the FROM keyword.

What does :: means in PostgreSQL?

The type 'string' syntax is a generalization of the standard: SQL specifies this syntax only for a few data types, but PostgreSQL allows it for all types. The syntax with :: is historical PostgreSQL usage, as is the function-call syntax.

What is Unnest in PostgreSQL?

The purpose of unnest function in PostgreSQL is to expand the array into rows. Unnest function generates a table structure of an array in PostgreSQL. Unnest array function is beneficial in PostgreSQL for expanding the array into the set of values or converting the array into the structure of the rows.


1 Answers

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.

like image 188
Patrick Avatar answered Sep 20 '22 14:09

Patrick