I migrated a old mysql database to postgresql. After finshed all the process, and inserting the data I found out that the way that I've created the Pkey is not the correct. I used integer not Null instead of Serial.
How can I change all the pkeys from my tables to the Serial type without changing all the database?
Thanks !
format() to construct the dynamic SQLDROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;
-- Create sequence for column and attacht it as a default
-- sch := schemaname
-- tab := tablename
-- zname := columnname
-- NOTE:
-- if 'sch' is NULL, 'public' is used
-- if 'tab' is NULL, *ALL* tables in the schema are affected.
-- 'zname' must always be supplied
-- -----------------------------------------------------------
CREATE FUNCTION inttoseq(sch text, tab text, zname text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
zresult text;
tabname text;
colname text;
seqname text;
schtabname text;
schseqname text;
tabcolname text;
schtabcolname text;
zrec RECORD;
zcount INTEGER;
BEGIN
zcount = 0;
FOR zrec IN
SELECT ns.nspname AS sname
, cl.relname AS tname
, cl.relowner AS onumm
, at.attname AS cname
FROM pg_class cl
JOIN pg_namespace ns ON ns.oid = cl.relnamespace
JOIN pg_attribute at ON at.attrelid = cl.oid
WHERE ns.nspname = COALESCE( sch, 'public' )
AND (cl.relname = tab OR tab IS NULL)
AND (at.attname = zname)
LOOP
tabname := quote_ident(zrec.tname)
;
schtabname := quote_ident(zrec.sname) || '.' || tabname
;
colname := quote_ident(zrec.cname)
;
tabcolname := tabname || '.' || colname
;
schtabcolname := schtabname || '.' || colname
;
seqname := trim(zrec.tname) || '_' || trim(zrec.cname) || '_seq'
;
schseqname := quote_ident(zrec.sname) || '.' || seqname
;
zresult := 'CREATE SEQUENCE ' || schseqname
|| ' MINVALUE 1'
|| ';'
|| E'\n'
|| 'ALTER SEQUENCE ' || schseqname
|| ' OWNER TO ' || pg_get_userbyid(zrec.onumm)
|| ';'
|| E'\n'
|| 'ALTER TABLE ' || schtabname
|| ' ALTER COLUMN ' || colname
|| ' SET DEFAULT nextval(' || quote_literal(schseqname) || ');'
|| E'\n'
|| 'ALTER SEQUENCE ' || schseqname
|| ' OWNED BY ' || schtabcolname || ';'
|| E'\n'
;
-- RAISE NOTICE 'Result:=%', zresult;
IF (zresult IS NOT NULL) THEN
EXECUTE zresult;
zcount := zcount +1;
END IF;
END LOOP;
RETURN zcount;
END;
$$;
ALTER FUNCTION inttoseq(sch text, tab text, zname text) OWNER TO postgres;
-- Set the value for a sequence to the max(value)
-- in the corresponding column.
CREATE FUNCTION syncseq(sch text, tab text, zname text) RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
zresult text;
tabname text;
colname text;
seqname text;
schtabname text;
schseqname text;
tabcolname text;
schtabcolname text;
zrec RECORD;
zcount INTEGER;
BEGIN
zcount = 0;
FOR zrec IN
SELECT ns.nspname AS sname
, cl.relname AS tname
, cl.relowner AS onumm
, at.attname AS cname
FROM pg_class cl
JOIN pg_namespace ns ON ns.oid = cl.relnamespace
JOIN pg_attribute at ON at.attrelid = cl.oid
WHERE ns.nspname = COALESCE( sch, 'public' )
AND (cl.relname = tab OR tab IS NULL)
AND (at.attname = zname)
LOOP
tabname := quote_ident(zrec.tname)
;
schtabname := quote_ident(zrec.sname) || '.' || tabname
;
colname := quote_ident(zrec.cname)
;
tabcolname := tabname || '.' || colname
;
schtabcolname := schtabname || '.' || colname
;
seqname := trim(zrec.tname) || '_' || trim(zrec.cname) || '_seq'
;
schseqname := quote_ident(zrec.sname) || '.' || seqname
;
zresult := 'ALTER SEQUENCE ' || schseqname
|| ' MINVALUE 1'
|| ';'
|| E'\n'
|| ' SELECT setval( '
|| quote_literal(schseqname)
|| ', COALESCE(GREATEST( zt.' || colname || ' ,1), 1)'
|| ', True)'
|| ' FROM ' || schtabname || ' zt;'
|| E'\n'
;
-- RAISE NOTICE 'Result:=%', zresult;
IF (zresult IS NOT NULL) THEN
EXECUTE zresult;
zcount := zcount +1;
END IF;
END LOOP;
RETURN zcount;
END;
$$;
ALTER FUNCTION syncseq(sch text, tab text, zname text) OWNER TO postgres;
-- do some testing
CREATE TABLE app_user (
id bigint NOT NULL
, username character varying NOT NULL
);
INSERT INTO app_user(id, username) VALUES
( 1, 'Joan' )
,( 2, 'John' )
,( 3, 'Bill' )
,( 9, 'Hillary' )
;
-- invoke the functions to alter the PK type to SERIAL
SELECT inttoseq( 'tmp', 'app_user', 'id' );
-- and set the serial to the max value in the column
SELECT syncseq( 'tmp', 'app_user', 'id' );
-- show it to the world
\d app_user
SELECT * FROM app_user;
SELECT nextval('app_user_id_seq');
\ds app_user_id_seq
The names could be found by consulting the catalogs, looking for the PK, but that is difficult, and possibly dangerous. With the given functions, you'll have to supply the {schemaname,tablename,columnname} (but you can omit schema and table, which is intended to be handy if all your PKs are named 'id' ;-)
