Обсуждение: More on my partitioning problem

Hi guys, again,

Laurenz’s suggestion to use create table like including all did not work, due to the fact that the partitioning key is
notpart of the original base table’s primary key. I had also forgotten about using the LIKE construct. So, thanks
Laurenz,but this also means I cannot use INCLUDING INDEXES, so I have to recreate all the indexes again on the new
partitionedbase table,, but I’m having an issue with the new table’s primary key. 

I created a UNIQUE INDEX on the new base table using the original primary key and the partitioning key. So far, so
good.But doing an ALTER TABLE ADD PRIMARY KEY USING INDEX, is not supported. So. being that  I already have a unique
indexhere, does it really matter if I do not declare a primary key here? Otherwise, how would I go about creating a
primarykey on this partitioned table? 
—
Jay
Sent from my iPad

On Mon, 2020-11-02 at 07:40 -0500, John Scalia wrote:
> I created a UNIQUE INDEX on the new base table using the original primary key
>  and the partitioning key. So far, so good. But doing an ALTER TABLE ADD PRIMARY KEY USING INDEX,
>  is not supported. So. being that  I already have a unique index here, does it
>  really matter if I do not declare a primary key here? Otherwise, how would I go
>  about creating a primary key on this partitioned table?

Don't create a unique index and then use it to define a primary key constraint,
create the primary key directly:

ALTER TABLE part ADD PRIMARY KEY (id, part_key);

Yours,
Laurenz
-- 
Cybertec | https://www.cybertec-postgresql.com

Thank, Laurenz ( again ) don’t know why I didn’t think of that. 😎

Sent from my iPad

> On Nov 2, 2020, at 9:34 AM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Mon, 2020-11-02 at 07:40 -0500, John Scalia wrote:
>> I created a UNIQUE INDEX on the new base table using the original primary key
>> and the partitioning key. So far, so good. But doing an ALTER TABLE ADD PRIMARY KEY USING INDEX,
>> is not supported. So. being that  I already have a unique index here, does it
>> really matter if I do not declare a primary key here? Otherwise, how would I go
>> about creating a primary key on this partitioned table?
>
> Don't create a unique index and then use it to define a primary key constraint,
> create the primary key directly:
>
> ALTER TABLE part ADD PRIMARY KEY (id, part_key);
>
> Yours,
> Laurenz
> --
> Cybertec | https://www.cybertec-postgresql.com
>