Postgres单表拆分多表：如何将地址数据迁移至三张关联表？

Got it, let's break this down step by step—using PostgreSQL's data-modifying CTEs is perfect here because it lets us insert into multiple related tables in a single query chain, keeping everything consistent and avoiding duplicates. First, let's lock in the new table structures (I'll add critical constraints to enforce uniqueness and relationships):

-- Create states table (enforce unique state names)
CREATE TABLE states (
    id SERIAL PRIMARY KEY,
    state VARCHAR(100) UNIQUE NOT NULL
);

-- Create cities table (enforce unique city-state pairs)
CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    city VARCHAR(100) NOT NULL,
    state_id INT NOT NULL REFERENCES states(id),
    UNIQUE(city, state_id)
);

-- Create addresses table
CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
    address TEXT NOT NULL,
    city_id INT NOT NULL REFERENCES cities(id)
);

Now, let's dive into the migration. We'll use CTEs to chain inserts and pass generated IDs between tables, plus handle deduplication to avoid redundant entries.

Option 1: Single Atomic Query (Recommended) #

This runs all operations in one transaction—if any part fails, nothing gets committed, keeping your data consistent. It handles both new and pre-existing records in the target tables:

WITH inserted_states AS (
    -- Insert unique states from the old table; skip duplicates
    INSERT INTO states (state)
    SELECT DISTINCT state FROM old_table
    ON CONFLICT (state) DO NOTHING
    RETURNING id, state
),
existing_states AS (
    -- Grab IDs for states that were already in the table
    SELECT id, state FROM states
),
all_states AS (
    -- Combine new and existing states to cover every case
    SELECT * FROM inserted_states
    UNION ALL
    SELECT * FROM existing_states
),
inserted_cities AS (
    -- Insert unique city-state pairs, linking to state IDs
    INSERT INTO cities (city, state_id)
    SELECT DISTINCT ot.city, s.id
    FROM old_table ot
    JOIN all_states s ON ot.state = s.state
    ON CONFLICT (city, state_id) DO NOTHING
    RETURNING id, city, state_id
),
existing_cities AS (
    -- Grab IDs for pre-existing city-state pairs
    SELECT id, city, state_id FROM cities
),
all_cities AS (
    -- Combine new and existing cities
    SELECT * FROM inserted_cities
    UNION ALL
    SELECT * FROM existing_cities
)
-- Finally, map old addresses to their corresponding city IDs
INSERT INTO addresses (address, city_id)
SELECT ot.address, c.id
FROM old_table ot
JOIN all_states s ON ot.state = s.state
JOIN all_cities c ON ot.city = c.city AND s.id = c.state_id;

Option 2: Step-by-Step Migration (Easier for Debugging) #

If you prefer to run each stage separately to troubleshoot issues:

1. Populate the states table (deduplicate first):

INSERT INTO states (state)
SELECT DISTINCT state FROM old_table
ON CONFLICT (state) DO NOTHING;

2. Populate the cities table (link to states, avoid duplicate city-state pairs):

INSERT INTO cities (city, state_id)
SELECT DISTINCT ot.city, s.id
FROM old_table ot
JOIN states s ON ot.state = s.state
ON CONFLICT (city, state_id) DO NOTHING;

3. Populate the addresses table (map to the correct city IDs):

INSERT INTO addresses (address, city_id)
SELECT ot.address, c.id
FROM old_table ot
JOIN states s ON ot.state = s.state
JOIN cities c ON ot.city = c.city AND s.id = c.state_id;

Key Tips: #

• The ON CONFLICT clauses are non-negotiable here—they prevent duplicate states/cities, but only work if you've set up the unique constraints we defined earlier.
• Using DISTINCT ensures we don't waste time inserting identical location entries multiple times, even if your old table has hundreds of rows for the same city/state.
• If you want to preserve the primary key from your old table in the new addresses table, just adjust the addresses schema to use that ID instead of a SERIAL, then include it in the final INSERT statement.

内容的提问来源于stack exchange，提问作者lawdybucket