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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.