Написал(а) PostgreSQL: Convert int4 Column to Auto-Increment (Serial)
PostgreSQL: Convert int4 Column to Auto-Increment (Serial)
I understand you have a table with a rowid column of type int4 that contains NULL values, and you want to:
- Fill existing NULL values with auto-generated sequential numbers
- Make the column auto-increment for future inserts
Solution
Here are the SQL commands to achieve this:
Step 1: Create a Sequence
sql
— Create a sequence for auto-increment
CREATE SEQUENCE your_table_rowid_seq;
sql
— Set the default value to use the sequence
ALTER TABLE your_table
ALTER COLUMN rowid SET DEFAULT nextval(‘your_table_rowid_seq’);
sql
— Update existing NULL values with auto-generated sequential numbers
UPDATE your_table
SET rowid = nextval(‘your_table_rowid_seq’)
WHERE rowid IS NULL;
sql
— Set sequence to continue from the highest existing value
SELECT setval(‘your_table_rowid_seq’, COALESCE((SELECT MAX(rowid) FROM your_table), 1));
sql
— If you want the column to not accept NULL anymore
ALTER TABLE your_table
ALTER COLUMN rowid SET NOT NULL;
All-in-One Complete Solution
sql
— Complete solution in one block
DO $$ BEGIN
— 1. Create sequence
IF NOT EXISTS (SELECT 1 FROM pg_sequences WHERE sequencename = ‘your_table_rowid_seq’) THEN
CREATE SEQUENCE your_table_rowid_seq;
END IF;
— 2. Set default
EXECUTE ‘ALTER TABLE your_table ALTER COLUMN rowid SET DEFAULT nextval(‘‘your_table_rowid_seq’‘)’;
— 3. Fill NULL values
EXECUTE ‘UPDATE your_table SET rowid = nextval(‘‘your_table_rowid_seq’‘) WHERE rowid IS NULL’;
— 4. Sync sequence to max value
EXECUTE ‘SELECT setval(‘‘your_table_rowid_seq’‘, COALESCE((SELECT MAX(rowid) FROM your_table), 1))’;
END $$;
Alternative: Convert to serial Type (PostgreSQL 9.x — 14)
If you want to truly convert it to a serial type:
sql
— Step 1: Create sequence owned by the column
CREATE SEQUENCE your_table_rowid_seq OWNED BY your_table.rowid;
— Step 2: Set default
ALTER TABLE your_table
ALTER COLUMN rowid SET DEFAULT nextval(‘your_table_rowid_seq’);
— Step 3: Fill NULLs
UPDATE your_table
SET rowid = nextval(‘your_table_rowid_seq’)
WHERE rowid IS NULL;
— Step 4: Make NOT NULL
ALTER TABLE your_table
ALTER COLUMN rowid SET NOT NULL;
For PostgreSQL 10+: Use GENERATED ALWAYS AS IDENTITY
sql
— Modern approach (PostgreSQL 10+)
— First, fill NULLs with temporary values
UPDATE your_table
SET rowid = (SELECT COALESCE(MAX(rowid), 0) + row_number() OVER (ORDER BY ctid)
FROM your_table t2
WHERE t2.rowid IS NULL)
WHERE rowid IS NULL;
— Then convert to identity column
ALTER TABLE your_table
ALTER COLUMN rowid ADD GENERATED ALWAYS AS IDENTITY;
Summary Table
Replace your_table with your actual table name!
Would you like me to help you with any specific part of this implementation?
PostgreSQL: Fix Error 2100 When Converting to IDENTITY
The error occurs because the column already exists with values and possibly a default. Here’s the correct approach:
Step-by-Step Solution
Step 1: Remove Existing Default (if any)
sql
— First, remove any existing default constraint
ALTER TABLE your_table
ALTER COLUMN rowid DROP DEFAULT;
Step 2: Fill NULL Values First
sql
— Option A: Simple sequential fill
WITH null_rows AS (
SELECT ctid,
ROW_NUMBER() OVER (ORDER BY ctid) + COALESCE((SELECT MAX(rowid) FROM your_table), 0) AS new_id
FROM your_table
WHERE rowid IS NULL
)
UPDATE your_table t
SET rowid = n.new_id
FROM null_rows n
WHERE t.ctid = n.ctid;
Step 3: Make Column NOT NULL
sql
ALTER TABLE your_table
ALTER COLUMN rowid SET NOT NULL;
Step 4: Add IDENTITY (The Correct Way)
sql
— Drop default first (if exists), then add identity
ALTER TABLE your_table
ALTER COLUMN rowid
DROP DEFAULT,
ADD GENERATED ALWAYS AS IDENTITY (START WITH 1);
Step 5: Sync Sequence to Current Max Value
sql
— Set the sequence to continue from the highest existing value
SELECT setval(
pg_get_serial_sequence(‘your_table’, ‘rowid’),
(SELECT COALESCE(MAX(rowid), 1) FROM your_table)
);
All-in-One Complete Script
sql
DO $$ DECLARE
max_id integer;
seq_name text;
BEGIN
— 1. Drop existing default if exists
BEGIN
EXECUTE ‘ALTER TABLE your_table ALTER COLUMN rowid DROP DEFAULT’;
EXCEPTION WHEN OTHERS THEN
NULL; — Ignore if no default exists
END;
— 2. Fill NULL values with sequential numbers
EXECUTE ‘
WITH null_rows AS (
SELECT ctid,
ROW_NUMBER() OVER (ORDER BY ctid) +
COALESCE((SELECT MAX(rowid) FROM your_table), 0) AS new_id
FROM your_table
WHERE rowid IS NULL
)
UPDATE your_table t
SET rowid = n.new_id
FROM null_rows n
WHERE t.ctid = n.ctid
‘;
— 3. Set NOT NULL
EXECUTE ‘ALTER TABLE your_table ALTER COLUMN rowid SET NOT NULL’;
— 4. Get max value
EXECUTE ‘SELECT COALESCE(MAX(rowid), 0) FROM your_table’ INTO max_id;
— 5. Add identity with proper start value
EXECUTE format(‘
ALTER TABLE your_table
ALTER COLUMN rowid
ADD GENERATED ALWAYS AS IDENTITY (START WITH %s)
‘, max_id + 1);
RAISE NOTICE ‘Successfully converted rowid to IDENTITY starting at %’, max_id + 1;
END $$;
Common Errors and Solutions
Simpler Alternative: Use SERIAL Instead
If IDENTITY still causes issues, use the classic serial approach:
sql
— 1. Fill NULL values
UPDATE your_table
SET rowid = nextval(‘your_table_rowid_seq’)
WHERE rowid IS NULL;
— 2. Create sequence
CREATE SEQUENCE your_table_rowid_seq;
— 3. Set default
ALTER TABLE your_table
ALTER COLUMN rowid SET DEFAULT nextval(‘your_table_rowid_seq’);
— 4. Sync sequence
SELECT setval(‘your_table_rowid_seq’, (SELECT MAX(rowid) FROM your_table));
Quick Fix for Your Specific Case
sql
— Replace ‘your_table’ with your actual table name
— Run these commands in order:
— 1
ALTER TABLE your_table ALTER COLUMN rowid DROP DEFAULT;
— 2
UPDATE your_table SET rowid = (SELECT COALESCE(MAX(rowid), 0) + 1 FROM your_table) WHERE rowid IS NULL LIMIT 1;
— Run above line multiple times or use loop for each NULL
— OR use this for all NULLs at once:
WITH upd AS (
SELECT ctid,
(SELECT COALESCE(MAX(rowid), 0) FROM your_table) +
ROW_NUMBER() OVER (ORDER BY ctid) AS new_val
FROM your_table WHERE rowid IS NULL
)
UPDATE your_table t SET rowid = u.new_val FROM upd u WHERE t.ctid = u.ctid;
— 3
ALTER TABLE your_table ALTER COLUMN rowid SET NOT NULL;
— 4
ALTER TABLE your_table ALTER COLUMN rowid ADD GENERATED ALWAYS AS IDENTITY;
— 5
SELECT setval(pg_get_serial_sequence(‘your_table’, ‘rowid’), (SELECT MAX(rowid) FROM your_table));
Replace your_table with your actual table name and let me know if you get any specific error messages!
What's your reaction?
Excited
0Happy
0In Love
0Not Sure
0Silly
0
