Updating psql Sequence IDs

I found that I had to update the sequence IDs in all of my Wikidot tables that used them when trying to migrate my Wikidot database from PostgreSQL 8.2 to version 8.3. It took a while, but I finally figured it out and ran the code below using a SQL command in pgAdmin3. After this was done, it fixed all of the "duplicate key" errors I was getting every time I tried to save a page or upload a file.

This should cover all Wikidot tables that have sequence IDs as of rev 393.

SELECT setval('admin_admin_id_seq', (SELECT max(admin_id) + 1 FROM admin));
SELECT setval('admin_notification_notification_id_seq',(SELECT max(notification_id)+1 FROM admin_notification));
SELECT setval('anonymous_abuse_flag_flag_id_seq',(SELECT max(flag_id)+1 FROM anonymous_abuse_flag));
SELECT setval('category_category_id_seq',(SELECT max(category_id)+1 FROM category));
SELECT setval('category_template_category_template_id_seq',(SELECT max(category_template_id)+1 FROM category_template));
SELECT setval('comment_comment_id_seq',(SELECT max(comment_id)+1 FROM comment));
SELECT setval('comment_revision_revision_id_seq',(SELECT max(revision_id)+1 FROM comment_revision));
SELECT setval('contact_contact_id_seq',(SELECT max(contact_id)+1 FROM contact));
SELECT setval('domain_redirect_redirect_id_seq',(SELECT max(redirect_id)+1 FROM domain_redirect));
SELECT setval('email_invitation_invitation_id_seq',(SELECT max(invitation_id)+1 FROM email_invitation));
SELECT setval('file_file_id_seq',(SELECT max(file_id)+1 FROM file));
SELECT setval('files_event_file_event_id_seq',(SELECT max(file_event_id)+1 FROM files_event));
SELECT setval('forum_category_category_id_seq',(SELECT max(category_id)+1 FROM forum_category));
SELECT setval('forum_group_group_id_seq',(SELECT max(group_id)+1 FROM forum_group));
SELECT setval('forum_post_post_id_seq',(SELECT max(post_id)+1 FROM forum_post));
SELECT setval('forum_post_revision_revision_id_seq',(SELECT max(revision_id)+1 FROM forum_post));
SELECT setval('forum_thread_thread_id_seq',(SELECT max(thread_id)+1 FROM forum_thread));
SELECT setval('front_forum_feed_feed_id_seq',(SELECT max(feed_id)+1 FROM front_forum_feed));
SELECT setval('fts_entry_fts_id_seq',(SELECT max(fts_id)+1 FROM fts_entry));
SELECT setval('global_ip_block_block_id_seq',(SELECT max(block_id)+1 FROM global_ip_block));
SELECT setval('global_user_block_block_id_seq',(SELECT max(block_id)+1 FROM global_user_block));
SELECT setval('ip_block_block_id_seq',(SELECT max(block_id)+1 FROM ip_block));
SELECT setval('license_license_id_seq',(SELECT max(license_id)+1 FROM license));
SELECT setval('log_event_event_id_seq',(SELECT max(event_id)+1 FROM log_event));
SELECT setval('member_application_application_id_seq',(SELECT max(application_id)+1 FROM member_application));
SELECT setval('member_invitation_invitation_id_seq',(SELECT max(invitation_id)+1 FROM member_invitation));
SELECT setval('member_member_id_seq',(SELECT max(member_id)+1 FROM member));
SELECT setval('membership_link_link_id_seq',(SELECT max(link_id)+1 FROM membership_link));
SELECT setval('moderator_moderator_id_seq',(SELECT max(moderator_id)+1 FROM moderator));
SELECT setval('notification_notification_id_seq',(SELECT max(notification_id)+1 FROM notification));
SELECT setval('openid_entry_openid_id_seq',(SELECT max(openid_id)+1 FROM openid_entry));
SELECT setval('ozone_group_group_id_seq',(SELECT max(group_id)+1 FROM ozone_group));
SELECT setval('ozone_group_permission_modifier_group_permission_id_seq',(SELECT max(group_permission_id)+1 FROM ozone_group_permission_modifier));
SELECT setval('ozone_permission_permission_id_seq',(SELECT max(permission_id)+1 FROM ozone_permission));
SELECT setval('ozone_user_group_relation_user_group_id_seq',(SELECT max(user_group_id)+1 FROM ozone_user_group_relation));
SELECT setval('ozone_user_permission_modifier_user_permission_id_seq',(SELECT max(user_permission_id)+1 FROM ozone_user_permission_modifier));
SELECT setval('ozone_user_user_id_seq',(SELECT max(user_id)+1 FROM ozone_user));
SELECT setval('page_abuse_flag_flag_id_seq',(SELECT max(flag_id)+1 FROM page_abuse_flag));
SELECT setval('page_edit_lock_lock_id_seq',(SELECT max(lock_id)+1 FROM page_edit_lock));
SELECT setval('page_external_link_link_id_seq',(SELECT max(link_id)+1 FROM page_external_link));
SELECT setval('page_inclusion_inclusion_id_seq',(SELECT max(inclusion_id)+1 FROM page_inclusion));
SELECT setval('page_link_link_id_seq',(SELECT max(link_id)+1 FROM page_link));
SELECT setval('page_metadata_metadata_id_seq',(SELECT max(metadata_id)+1 FROM page_metadata));
SELECT setval('page_page_id_seq',(SELECT max(page_id)+1 FROM page));
SELECT setval('page_rate_vote_rate_id_seq',(SELECT max(rate_id)+1 FROM page_rate_vote));
SELECT setval('page_revision_revision_id_seq',(SELECT max(revision_id)+1 FROM page_revision));
SELECT setval('page_source_source_id_seq',(SELECT max(source_id)+1 FROM page_source));
SELECT setval('page_tag_tag_id_seq',(SELECT max(tag_id)+1 FROM page_tag));
SELECT setval('petition_campaign_campaign_id_seq',(SELECT max(campaign_id)+1 FROM petition_campaign));
SELECT setval('petition_signature_signature_id_seq',(SELECT max(signature_id)+1 FROM petition_signature));
SELECT setval('private_message_message_id_seq',(SELECT max(message_id)+1 FROM private_message));
SELECT setval('private_user_block_block_id_seq',(SELECT max(block_id)+1 FROM private_user_block));
SELECT setval('simpletodo_list_list_id_seq',(SELECT max(list_id)+1 FROM simpletodo_list));
SELECT setval('site_backup_backup_id_seq',(SELECT max(backup_id)+1 FROM site_backup));
SELECT setval('site_site_id_seq',(SELECT max(site_id)+1 FROM site));
SELECT setval('site_tag_tag_id_seq',(SELECT max(tag_id)+1 FROM site_tag));
SELECT setval('site_viewer_viewer_id_seq',(SELECT max(viewer_id)+1 FROM site_viewer));
SELECT setval('theme_theme_id_seq',(SELECT max(theme_id)+1 FROM theme));
SELECT setval('user_abuse_flag_flag_id_seq',(SELECT max(flag_id)+1 FROM user_abuse_flag));
SELECT setval('user_block_block_id_seq',(SELECT max(block_id)+1 FROM user_block));
SELECT setval('watched_forum_thread_watched_id_seq',(SELECT max(watched_id)+1 FROM watched_forum_thread));
SELECT setval('watched_page_watched_id_seq',(SELECT max(watched_id)+1 FROM watched_page));

Discuss This Article

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License