PostgreSQL：为表列更新唯一非空数据的脚本编写求助

Hey there, let's get this sorted out. Your original PL/pgSQL script had a few syntax and logical issues (like incorrect record array handling and flawed loop tracking), so I'll walk you through a clean, efficient solution to fix your code column before adding the UNIQUE NOT NULL constraint.

Why Your Original Script Failed #

• You tried to declare an array of tablename[], which isn't the right way to hold a set of records in PL/pgSQL. You'd need a cursor or to leverage window functions (far more efficient for bulk operations).
• Your loop logic didn't properly group duplicate codes, leading to incorrect numbering.
• Accessing tablename[0].code isn't valid syntax for referencing records in an array.

Step 1: Fix NULL/Blank code Values #

First, we'll assign unique (1), (2), ... values to any records where code is NULL, empty, or just whitespace. We'll use a window function to generate sequential numbers efficiently:

DO $$
BEGIN
    -- Update NULL/blank codes to (1), (2), ... format
    WITH ranked_nulls AS (
        SELECT 
            id,
            ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM tablename
        WHERE code IS NULL OR TRIM(code) = ''
    )
    UPDATE tablename t
    SET code = '(' || rn || ')'
    FROM ranked_nulls rn
    WHERE t.id = rn.id;
END $$;

Step 2: Fix Duplicate Non-Null/Non-Blank code Values #

Next, we'll handle duplicate codes by appending (1), (2), etc. This ensures every code becomes unique. We have two options depending on whether you want to leave the first duplicate unmodified:

Option A: Modify All Duplicates (Including First Occurrence) #

This will turn duplicates like FOO, FOO, FOO into FOO(1), FOO(2), FOO(3):

DO $$
BEGIN
    WITH ranked_duplicates AS (
        SELECT 
            id,
            code,
            ROW_NUMBER() OVER (PARTITION BY TRIM(code) ORDER BY id) AS rn
        FROM tablename
        WHERE TRIM(code) != '' AND code IS NOT NULL
        QUALIFY COUNT(*) OVER (PARTITION BY TRIM(code)) > 1
    )
    UPDATE tablename t
    SET code = rd.code || '(' || rd.rn || ')'
    FROM ranked_duplicates rd
    WHERE t.id = rd.id;
END $$;

Option B: Leave First Duplicate Unmodified #

If you want to keep the first FOO as-is and turn the rest into FOO(2), FOO(3):

DO $$
BEGIN
    WITH ranked_duplicates AS (
        SELECT 
            id,
            code,
            ROW_NUMBER() OVER (PARTITION BY TRIM(code) ORDER BY id) AS rn
        FROM tablename
        WHERE TRIM(code) != '' AND code IS NOT NULL
        QUALIFY COUNT(*) OVER (PARTITION BY TRIM(code)) > 1
    )
    UPDATE tablename t
    SET code = rd.code || '(' || (rd.rn - 1) || ')'
    FROM ranked_duplicates rd
    WHERE t.id = rd.id AND rd.rn > 1;
END $$;

Step 3: Add the UNIQUE NOT NULL Constraint #

Once all code values are unique and non-null, apply the constraint:

ALTER TABLE tablename
ALTER COLUMN code SET NOT NULL,
ADD CONSTRAINT tablename_code_unique UNIQUE (code);

Key Tips for This Approach #

• Efficiency: Window functions (ROW_NUMBER(), COUNT() OVER()) are way faster than manual loops for bulk updates, especially on large tables.
• Precision: Using the id column (assuming it's your primary key) ensures we update exactly the right records.
• Whitespace Handling: TRIM(code) ensures empty strings and whitespace-only values are treated the same as NULL for duplicate checks.

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