ALTER the definition of SQL objects

ALTER DATABASE

ALTER TABLE

ADD a new column to a table

ALTER TABLE organizations
  ADD COLUMN slug VARCHAR(255) GENERATED ALWAYS AS (slugify(name)) STORED;
-- assuming slugify() function is defined

Set / update or remove the default value of a column

ALTER TABLE <table>
  ALTER COLUMN timestamp SET DEFAULT NOW();

ALTER TABLE <table>
  ALTER COLUMN <column> DROP DEFAULT;

Rename a column

ALTER TABLE <table>
  RENAME COLUMN <old_column> TO <new_column>;

Change the data type of a column

ALTER TABLE <table>
  ALTER COLUMN <column> TYPE <new_type>;

Add a constraint to a column

ALTER TABLE <table>
  ADD CONSTRAINT <constraint_name> CHECK (<condition>);
-- add not null constraint
ALTER TABLE <table>
  ALTER COLUMN <column> SET NOT NULL;

ALTER TYPE: Adding / Removing / Renaming TYPE Attributes

ALTER TYPE cloud_provider_region ADD ATTRIBUTE display_name TEXT;

Renaming attributes

step 1: update the existing data

WITH updated_regions AS (
  SELECT
    cp.id,
    ARRAY_AGG(
      ROW(
        unnested.region,
        unnested.region_display_name,
        unnested.is_active,
        unnested.info,
        unnested.region_display_name -- Assuming you want to copy region_display_name to display_name for now
      )::cloud_provider_region
    ) AS regions
  FROM
    cloud_providers cp,
    LATERAL unnest(cp.regions) AS unnested(region, region_display_name, is_active, info)
  GROUP BY
    cp.id
)
UPDATE cloud_providers cp
SET
  regions = ur.regions
FROM
  updated_regions ur
WHERE
  cp.id = ur.id;

Step 2: Create a New Type (Temporary)

PostgreSQL does not allow altering a type to drop an attribute if it's in use. The workaround involves creating a new type with the desired structure and then updating the database to use this new type.

CREATE TYPE cloud_provider_region_new AS (
    region TEXT,
    display_name TEXT,
    is_active BOOLEAN,
    info JSONB
);

-- Example for Step 3 onward, highly conceptual
ALTER TABLE cloud_providers
  ADD COLUMN regions_new cloud_provider_region_new[];

-- You would then need a complex SQL statement or a PL/pgSQL function to migrate and transform each element in the regions array to the new type structure, populating regions_new.

-- After confirming data integrity:
ALTER TABLE cloud_providers DROP COLUMN regions;

-- Rename the new column and type
ALTER TABLE cloud_providers RENAME COLUMN regions_new TO regions;
DROP TYPE cloud_provider_region;
ALTER TYPE cloud_provider_region_new RENAME TO cloud_provider_region;