From: | Kevin Wilkinson <w(dot)kevin(dot)wilkinson(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | how best to specify table constraints with "create table as <query>" |
Date: | 2019-03-02 00:39:53 |
Message-ID: | 39455b88-04a5-ea0c-1016-16132b15d838@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
i want to recluster an immutable table without locking the table and
then add the table as a partition of a parent table. my plan was:
create table tbl_cpy as select * from tbl order by c1 asc;
alter table tbl_cpy add constraint c1 not null, c1>=lo c1<hi;
alter table tbl_parent attach partition tbl_cpy for values from (lo)
to (hi);
i want this to run quickly. so, i populate the table with a parallel
query (using create table as ...). i do NOT want attach partition to
rescan the table (which it does to validate the value range) so i have
to explicitly add constraints to tbl_cpy. but adding these constraints
itself causes a scan of the table.
so, i want to specify the constraints when the table is created. but
that seems not possible. if i first create an empty table with
constraints, then i cannot populate it with a parallel query (since
insert into is not parallel).
am i missing something? any ideas?
thanks,
kevin
From | Date | Subject | |
---|---|---|---|
Next Message | Karsten Hilbert | 2019-03-02 13:16:53 | Re: Methods to quickly spin up copies of an existing databases |
Previous Message | Arjun Ranade | 2019-03-01 21:51:32 | Re: Methods to quickly spin up copies of an existing databases |