Migrate ghost from SQLite to PostgreSQL (SQL data type migration)

ghost_sqlite2pgsql.sql

--Migrate ghost from SQLite to PostgreSQL (SQL data type migration)
-- Could have done this inside a more generalistic script, 
-- but every table an idiossyncracy, so here it goes as a flat script.
-- Is ugly but easier to mantain
 
 --POSTS
ALTER TABLE posts ADD page_tmp BOOLEAN NOT NULL DEFAULT false;
UPDATE posts SET page_tmp = true WHERE page = 1;
ALTER TABLE posts DROP COLUMN page;
ALTER TABLE posts RENAME COLUMN page_tmp TO page;
 
ALTER TABLE posts ADD created_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  posts SET created_at_tmp = to_timestamp(created_at/1000);
ALTER TABLE posts DROP COLUMN created_at;
ALTER TABLE posts RENAME COLUMN created_at_tmp TO created_at;
 
ALTER TABLE posts ADD updated_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  posts SET updated_at_tmp = to_timestamp(updated_at/1000);
ALTER TABLE posts DROP COLUMN updated_at;
ALTER TABLE posts RENAME COLUMN updated_at_tmp TO updated_at;
 
ALTER TABLE posts ADD published_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  posts SET published_at_tmp = to_timestamp(published_at/1000);
ALTER TABLE posts DROP COLUMN published_at;
ALTER TABLE posts RENAME COLUMN published_at_tmp TO published_at;
 
-- TAGS
ALTER TABLE tags ADD created_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  tags SET created_at_tmp = to_timestamp(created_at/1000);
ALTER TABLE tags DROP COLUMN created_at;
ALTER TABLE tags RENAME COLUMN created_at_tmp TO created_at;
 
ALTER TABLE tags ADD updated_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  tags SET updated_at_tmp = to_timestamp(updated_at/1000);
ALTER TABLE tags DROP COLUMN updated_at;
ALTER TABLE tags RENAME COLUMN updated_at_tmp TO updated_at;
 
 
--USERS
ALTER TABLE users ADD created_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  users SET created_at_tmp = to_timestamp(created_at/1000);
ALTER TABLE users DROP COLUMN created_at;
ALTER TABLE users RENAME COLUMN created_at_tmp TO created_at;
 
ALTER TABLE users ADD updated_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  users SET updated_at_tmp = to_timestamp(updated_at/1000);
ALTER TABLE users DROP COLUMN updated_at;
ALTER TABLE users RENAME COLUMN updated_at_tmp TO updated_at;
 
ALTER TABLE users ADD last_login_tmp TIMESTAMP;
UPDATE  users SET last_login_tmp = to_timestamp(last_login/1000);
ALTER TABLE users DROP COLUMN last_login;
ALTER TABLE users RENAME COLUMN last_login_tmp TO last_login;
 
--SETTINGS
ALTER TABLE settings ADD created_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  settings SET created_at_tmp = to_timestamp(created_at/1000);
ALTER TABLE settings DROP COLUMN created_at;
ALTER TABLE settings RENAME COLUMN created_at_tmp TO created_at;
 
ALTER TABLE settings ADD updated_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  settings SET updated_at_tmp = to_timestamp(updated_at/1000);
ALTER TABLE settings DROP COLUMN updated_at;
ALTER TABLE settings RENAME COLUMN updated_at_tmp TO updated_at;
 
 --SESSIONS (timestamp value stays as bigint)
ALTER TABLE sessions DROP column expires;
ALTER TABLE sessions ADD expires bigint;

--ROLES
ALTER TABLE roles ADD created_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  roles SET created_at_tmp = to_timestamp(created_at/1000);
ALTER TABLE roles DROP COLUMN created_at;
ALTER TABLE roles RENAME COLUMN created_at_tmp TO created_at;
 
ALTER TABLE roles ADD updated_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  roles SET updated_at_tmp = to_timestamp(updated_at/1000);
ALTER TABLE roles DROP COLUMN updated_at;
ALTER TABLE roles RENAME COLUMN updated_at_tmp TO updated_at;

--PERMISSIONS
ALTER TABLE permissions ADD created_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  permissions SET created_at_tmp = to_timestamp(created_at/1000);
ALTER TABLE permissions DROP COLUMN created_at;
ALTER TABLE permissions RENAME COLUMN created_at_tmp TO created_at;
 
ALTER TABLE permissions ADD updated_at_tmp TIMESTAMP NOT NULL default '01-01-1970';
UPDATE  permissions SET updated_at_tmp = to_timestamp(updated_at/1000);
ALTER TABLE permissions DROP COLUMN updated_at;
ALTER TABLE permissions RENAME COLUMN updated_at_tmp TO updated_at;

--FIX SEUQUENCES

select setval('permissions_id_seq',(select max(id)+1 from permissions), true);
select setval('permissions_roles_id_seq',(select max(id)+1 from permissions_roles), true);
select setval('permissions_users_id_seq',(select max(id)+1 from permissions_users), true);
select setval('posts_id_seq',(select max(id)+1 from posts), true);
select setval('posts_tags_id_seq',(select max(id)+1 from posts_tags), true);
select setval('roles_id_seq',(select max(id)+1 from roles), true);
select setval('posts_tags_id_seq',(select max(id)+1 from posts_tags), true);
select setval('roles_users_id_seq',(select max(id)+1 from roles_users), true);
select setval('settings_id_seq',(select max(id)+1 from settings), true);
select setval('tags_id_seq',(select max(id)+1 from tags), true);
select setval('users_id_seq',(select max(id)+1 from users), true);

context: https://andrade.io/ghost-blog-migrate-from-sqlite-to-postgres/