Does postgres support explicitly typed arrays?-CodePudding

Does postgres support the following?

SELECT ARRAY<INT64>[1,2];

Instead of just:

SELECT ARRAY[1,2]

As an example from BigQuery of what I mean:

Or is the only way to cast it after declaring a literal, such as:

select ARRAY[1,2]::int[];

CodePudding user response：

specify the array type in Postgres

Manual 8.15.1. Declaration of Array Types
when you create an table, you can declare the array type. The following is manual example.

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

When you do a select then you need cast. Because it will be resolved to a certain type. Then you can cast to the type you want.

SELECT ARRAY[1,2,3], pg_typeof(ARRAY[1,2,3])
union
SELECT ARRAY[1,2,3]::smallint[], pg_typeof(ARRAY[1,2,3])
union
SELECT ARRAY[1,2,3]::bigint[], pg_typeof(ARRAY[1,2,3])
union all
SELECT ARRAY[1,2,3]::[numeric], pg_typeof(ARRAY[1,2,3]);

Quote from manual:

By default, the array element type is the common type of the member expressions, determined using the same rules as for UNION or CASE constructs.