PostgreSQL Question posted in
The official documentation can be found here.

CASTING integer to integer[] postgresql in UNION ALL

I am using postgresql and have a select statement on mulitple tables

CREATE OR REPLACE VIEW group.all 
AS 
SELECT 'gmail'::text AS main_source,
        gmail.id,
        gmail.from,
        gmail.to,
        gmail.user_id
FROM first_group.gmail
UNION ALL 
SELECT 'yahoo'::text AS main_source,
        yahoo.id,
        yahoo.from,
        yahoo.to,
        yahoo.user_id
FROM second_group.yahoo
UNION ALL
SELECT 'outlook'::text AS main_source,
        outlook.id,
        outlook.from,
        outlook.to,
        NULL::integer AS user_id
FROM third_group.outlook

For the NULL value I now want to change it to equal participant_ids that has the data type integer[] while for all the sources user_id is integer data type.

I tried casting:


SELECT 'outlook'::text AS main_source,
        outlook.id,
        outlook.from
        outlook.to
        outlook.participant_ids::integer AS user_id
FROM third_group.outlook

But the received this error ERROR: UNION types integer and integer[] cannot be matched

I also tried this syntax ARRAY[outlook.participant_ids]::integer but received an error.

I also tried changing the datatype of the column for all sources using this command and altering it for each source.

ALTER TABLE first_group.gmail
    ALTER COLUMN user_id TYPE integer[];
ALTER TABLE second_group.yahoo
    ALTER COLUMN user_id TYPE integer[];
ALTER TABLE third_group.outlook
    ALTER COLUMN user_id TYPE integer[];

But received this error ERROR: column "user_id" cannot be cast automatically to type integer[] HINT: You might need to specify "USING user_id::integer[]".

This is how the table was created.

CREATE TABLE third_group.outlook(
 id integer NOT NULL
 created_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
 participants_ids integer[]
)

The same table was created for first_group and second_group.

Is there a way to cast the datatype integer to integer[]? Or is there a way to change the data type of the column from integer to integer[]?

Tags:

2

Answers


  1. 0

    You cannot cast an integer to an array or back. You can only wrap an integer in an array, or access an array element. You seem to be looking for

    SELECT 'gmail'::text AS main_source,
        gmail.id,
        gmail.from,
        gmail.to,
        ARRAY[gmail.user_id] AS user_ids
FROM first_group.gmail
UNION ALL 
SELECT 'yahoo'::text AS main_source,
        yahoo.id,
        yahoo.from,
        yahoo.to,
        ARRAY[yahoo.user_id] AS user_ids
FROM second_group.yahoo
UNION ALL
SELECT 'outlook'::text AS main_source,
        outlook.id,
        outlook.from,
        outlook.to,
        outlook.participant_ids AS user_ids
FROM third_group.outlook
    Login or Signup to reply.
  2. 0

    Bergi‘s query works with the data types you hinted: integer for both user_id and integer[] for participant_ids. (You didn’t declare properly.)

    Changing the column data type from integer to integer[] works like this:

    ALTER TABLE first_group.gmail
ALTER COLUMN user_id TYPE integer[] USING ARRAY[user_id];

    Either way, you need an Array constructor.

    Note that this produces {null} (an array with one null element) for null input. If you want a plain null instead:

    ALTER TABLE first_group.gmail
ALTER COLUMN user_id TYPE integer[] USING NULLIF(ARRAY[user_id], '{null}');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search