pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMore on my partitioning problem
3+ messages / 2 participants
[nested] [flat]
* More on my partitioning problem
@ 2020-11-02 12:40 John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: John Scalia @ 2020-11-02 12:40 UTC (permalink / raw)
To: pgsql-admin
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 not part 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 partitioned base 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 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?
—
Jay
Sent from my iPad
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: More on my partitioning problem
@ 2020-11-02 14:34 Laurenz Albe <laurenz.albe@cybertec.at>
parent: John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2020-11-02 14:34 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: More on my partitioning problem
@ 2020-11-02 15:38 John Scalia <jayknowsunix@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: John Scalia @ 2020-11-02 15:38 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-11-02 15:38 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-02 12:40 More on my partitioning problem John Scalia <jayknowsunix@gmail.com>
2020-11-02 14:34 ` Laurenz Albe <laurenz.albe@cybertec.at>
2020-11-02 15:38 ` John Scalia <jayknowsunix@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox