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
You can’t perform that action at this time.