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[];
-
1What happend when you tried? dbfiddle.uk/Ho4tsJ0sFrank Heikens– Frank Heikens2022-09-06 19:15:59 +00:00Commented Sep 6, 2022 at 19:15
-
@FrankHeikens added to question.David542– David5422022-09-06 19:17:01 +00:00Commented Sep 6, 2022 at 19:17
-
Well, that's pretty clear, it's not valid syntax for PostgreSQLFrank Heikens– Frank Heikens2022-09-06 19:18:33 +00:00Commented Sep 6, 2022 at 19:18
-
@FrankHeikens sure, my question is if there is any way to specify the array type in Postgres? Is the answer that it's not and only via a cast?David542– David5422022-09-06 19:20:16 +00:00Commented Sep 6, 2022 at 19:20
Add a comment
|
1 Answer
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.
