0

I have a user_profiles table.

I want to add a new column and alter and existing type.

How to set default values for the new column and new attribute so that the existing rows have default values.

I don't want to run an update since the table has billions of row.

I tried using domain but it doesn't put default values in existing rows.

Below is the snippet. I want ATTRIBUTE message_status in type user_contact_info and in communication_preferences to have deafult value ACTIVE. How to do this?

CREATE TYPE message_status AS ENUM (
    'ACTIVE',
    'UNSUBSCRIBED',
    'FLAGGED_AS_SPAM',
    'PERMANENTLY_BOUNCED',
    'TEMPORARILY_BOUNCED'
);


CREATE TYPE campaign_category AS ENUM (
    'UPDATE_NOTIFICATION',
    'DISCOUNT_OFFER',
    'NEW_PRODUCT_ALERT',
    'LOYALTY_PROGRAM',
    'GENERAL'
);

ALTER TYPE user_contact_info ADD ATTRIBUTE message_status message_status;

CREATE TYPE communication_preferences AS (
    message_status message_status,
    allowed_categories campaign_category[]
);


ALTER TABLE user_profiles
ADD COLUMN communication_preferences communication_preferences;

1 Answer 1

0

Adding or altering a column default value doesn't work "backwards"; you'll have to update existing rows manually.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.