0

Is it possible to cast a value to an Array or a Struct with the normal cast syntax? For example, similar to how I can do:

SELECT 
    '1'::INT, CAST('1' AS INT)
    CAST('1' AS INT)

Am I able to do something like:

SELECT '[1,2,3]'::ARAY

Or:

SELECT '{"x":1}'::RECORD

To cast something to a nested - and not pre-specified with a previously-created custom TYPE?

As an example for comparison, here is using BigQuery:

select 
  CAST(([1,2,3],[4,5,6]) AS STRUCT<ARRAY<INT64>, ARRAY<INT64>>) AS a, 
  CAST([1,2,3] AS ARRAY<INT64>) AS b

enter image description here

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
carl.hiass
  • 1,526
  • 1
  • 6
  • 26

2 Answers2

1

There is no "STRUCT" in Postgres.

To cast to an array, pass typed element values to an ARRAY constructor (or live with the default element type):

SELECT ARRAY[1,2,3];            --> type int[]
SELECT ARRAY[[1,2,3],[4,5,6]];  --> type int[]

Array types can hold any number of nested dimensions, it's still the same type on the outside to Postgres.

Or cast an array literal to a specific array type:

SELECT '{1,2,3}'::int[];            --> type int[]
SELECT '{{1,2,3},{4,5,6}}'::int[];  --> type int[]

To form an arbitrary "record", i.e. an anonymous ROW type, a.k.a. "composite type", use the ROW constructor:

SELECT ROW('x', 1);  --> anonymous row type

(The keyword ROW is optional in most contexts.)

But named (registered) composite types (ROW types) are more useful in most contexts, as Postgres knows how to properly de-compose it:

SELECT ('x', 1)::my_registered_row_type;  --> well-known row type

Every table, view, or materialized view in the same database registers a row type implicitly. Or create a composite type explicitly.

What you show as "RECORD" looks like a JSON value. There are two data types for that in Postgres, json and jsonb ...

Related:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

To cast as an ARRAY:

SELECT '{1,2,3}'::int[];
 int4   
---------
 {1,2,3}

To create an on the fly record:

select * from  json_each('{"x": 1}'::json);
 key | value 
-----+-------
 x   | 1

To name it:

select * from  json_to_record('{"x": 1}'::json) as t(x int);
 x 
---
 1
Adrian Klaver
  • 15,886
  • 2
  • 17
  • 28