Last active
June 27, 2025 00:59
-
-
Save mv/08f51e04d0aaa73a9c22e009f77b1c26 to your computer and use it in GitHub Desktop.
Posgresql: move table to PARTITIONED
This file contains hidden or 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
| -- first table | |
| -- <script src="https://gist.github.com/betosaulo-cw/f671d1885bbdecc09d7f074cfda66598.js"></script> | |
| -- | |
| CREATE SEQUENCE customers_id_seq; | |
| CREATE TABLE customers ( | |
| id BIGINT PRIMARY KEY DEFAULT nextval('customers_id_seq'), | |
| name VARCHAR(255), | |
| email VARCHAR(255) | |
| ); | |
| -- second table | |
| CREATE TABLE customers_new ( | |
| id BIGINT PRIMARY KEY -- DEFAULT nextval('customers_id_seq'), | |
| name VARCHAR(255), | |
| email VARCHAR(255) | |
| ) PARTITION BY RANGE(id) | |
| ; | |
| -- migration of that | |
| select into customers_new | |
| from customers; | |
| commit; | |
| -- Move | |
| alter table customers rename to customer_old; | |
| alter table customers_new rename to customers; | |
| -- check for any row behind | |
| select max(id) from customers; | |
| select max(id) from customers_old; | |
| NRO | |
| --- | |
| 10001 | |
| -- bring from behind | |
| insert into customers | |
| select from customers_old | |
| from customers_old | |
| where id > 'nro' | |
| -- CHECCK | |
| select count(id) from customers_old; | |
| select cound(id) from customers; | |
| -- move sequence | |
| alter table customers | |
| alter column id set DEFAULT nextval('customers_id_seq'); | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment