Migrate non-partioned table to a partitioned table.

create-partitioned-table.sql

-- base table

-- drop table users cascade;

CREATE TABLE users (
    id             serial PRIMARY KEY,
    username       text NOT NULL UNIQUE,
    password       text,
    created_on     timestamptz NOT NULL,
    last_logged_on timestamptz NOT NULL
);

-- populate the base table

insert
	into
	users (username,
	password,
	created_on,
	last_logged_on)
select
	concat(md5(random()::text),' ',md5(random()::text)),
	md5(random()::text),
	now() - '2 years'::interval * random(),
	now() - '2 years'::interval * random()
from
	generate_series(1, 20000);

-- check
--  SELECT COUNT(*), SUM(LENGTH(username)), SUM(LENGTH(password)) FROM users;
					   
-- rename the original table
 alter table users rename to users_old;
					   
-- create partitioned base
 create table users (like users_old) partition by range (id);
					   
-- attach the original
 alter table users attach partition users_old default;
					   
-- loop

-- create partiones for each 1000 records.
do $$ 
declare 
	start_val int;
	end_val int;
	table_name varchar;
begin
	for counter in 1..20 loop
		start_val := (counter-1)* 1000;
		end_val := start_val + 1000;
		table_name := 'users_' || start_val || '_' || end_val;
		
		-- create dummpy table
		execute 'CREATE TABLE ' || table_name || ' ( LIKE users_old including ALL)';
	
		-- populate dummy table with original table's data and delete the original
		execute '
			WITH x AS (
			    DELETE FROM users_old WHERE id < ' || end_val || ' returning *
			)
			INSERT INTO ' || table_name || ' SELECT * FROM x
		';
		
		-- attach the dummy table as a partition
		execute '
			alter table users attach partition '|| table_name ||' 
			for values 
				from ('|| start_val ||') to ('|| end_val ||')
		';
	end loop;
	
	-- analyze
	execute 'analyze users';
end; $$