Last active
August 29, 2015 13:57
-
-
Save leonardoandrade/9730698 to your computer and use it in GitHub Desktop.
Migrate ghost from SQLite to PostgreSQL (SQL data type migration)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
--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); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
context: https://andrade.io/ghost-blog-migrate-from-sqlite-to-postgres/