Column Operations

SELECT COUNT(*) as total_rows FROM target_table;
SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'target_table' ORDER BY ordinal_position;

CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  tenant_id VARCHAR(255) NOT NULL,
  kept_column1 VARCHAR(255),
  kept_column2 INTEGER
  -- dropped_column is NOT included
);

INSERT INTO target_table_new (id, tenant_id, kept_column1, kept_column2)
SELECT id, tenant_id, kept_column1, kept_column2
FROM target_table;

-- Example: VARCHAR to INTEGER - check for non-numeric values
SELECT COUNT(*) as invalid_count FROM target_table
WHERE column_to_change !~ '^-?[0-9]+$';
-- MUST abort if invalid_count > 0

-- Show problematic rows
SELECT id, column_to_change FROM target_table
WHERE column_to_change !~ '^-?[0-9]+$' LIMIT 100;

CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  converted_column INTEGER,  -- Changed from VARCHAR
  other_column TEXT
);

INSERT INTO target_table_new (id, converted_column, other_column)
SELECT id, CAST(converted_column AS INTEGER), other_column
FROM target_table;

SELECT COUNT(*) as null_count FROM target_table
WHERE target_column IS NULL;
-- MUST ABORT if null_count > 0, or plan to provide default values

CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  target_column VARCHAR(255) NOT NULL,  -- Changed from nullable
  other_column TEXT
);

INSERT INTO target_table_new (id, target_column, other_column)
SELECT id, COALESCE(target_column, 'default_value'), other_column
FROM target_table;

SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'target_table' ORDER BY ordinal_position;
-- Identify current column definition and any existing defaults

CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  status VARCHAR(50) DEFAULT 'pending',  -- Added default
  other_column TEXT
);

INSERT INTO target_table_new (id, status, other_column)
SELECT id, status, other_column
FROM target_table;

CREATE TABLE target_table_new (
  id UUID PRIMARY KEY,
  status VARCHAR(50),  -- Removed DEFAULT
  other_column TEXT
);

INSERT INTO target_table_new (id, status, other_column)
SELECT id, status, other_column
FROM target_table;