Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Migrate ghost from SQLite to PostgreSQL (SQL data type migration)
--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);
@leonardoandrade

This comment has been minimized.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment