pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPartition table with a foreign key self-reference
3+ messages / 3 participants
[nested] [flat]
* Partition table with a foreign key self-reference
@ 2025-02-04 15:57 Oliver Urones <ourones.joinup@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Oliver Urones @ 2025-02-04 15:57 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello.
I need help with the process of partitioning tables.
How can I partition a table that has a foreign key that references itself?
Something like this example:
CREATE TABLE employees (
employee_id SERIAL PRIMARY KEY,
name VARCHAR(50),
boss_id INTEGER,
CONSTRAINT fk_boss FOREIGN KEY (boss_id) REFERENCES
employees(employee_id)
);
I'm using Postgresql 14.
Thanks!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partition table with a foreign key self-reference
@ 2025-02-05 07:18 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Oliver Urones <ourones.joinup@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2025-02-05 07:18 UTC (permalink / raw)
To: Oliver Urones <ourones.joinup@gmail.com>; pgsql-admin@lists.postgresql.org
On Tue, 2025-02-04 at 16:57 +0100, Oliver Urones wrote:
> I need help with the process of partitioning tables.
>
> How can I partition a table that has a foreign key that references itself?
>
> Something like this example:
>
> CREATE TABLE employees (
> employee_id SERIAL PRIMARY KEY,
> name VARCHAR(50),
> boss_id INTEGER,
> CONSTRAINT fk_boss FOREIGN KEY (boss_id) REFERENCES employees(employee_id)
> );
>
> I'm using Postgresql 14.
The question makes no sense as such. You'd have to tell us the purpose
you want to achieve with partitioning. Depending on that, you'd choose
the partitioning strategy and the partitioning key.
At any rate, you'll probably have to drop the foreign key constraint
unless your partitioning key is "id".
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partition table with a foreign key self-reference
@ 2025-02-05 10:25 Juan Rincon Gonzalez <juanrincongonzalez347@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: Juan Rincon Gonzalez @ 2025-02-05 10:25 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Oliver Urones <ourones.joinup@gmail.com>; pgsql-admin@lists.postgresql.org
I can't help if am not present I need to follow rules
Juan Rincon
On Tue, Feb 4, 2025, 11:18 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> On Tue, 2025-02-04 at 16:57 +0100, Oliver Urones wrote:
> > I need help with the process of partitioning tables.
> >
> > How can I partition a table that has a foreign key that references
> itself?
> >
> > Something like this example:
> >
> > CREATE TABLE employees (
> > employee_id SERIAL PRIMARY KEY,
> > name VARCHAR(50),
> > boss_id INTEGER,
> > CONSTRAINT fk_boss FOREIGN KEY (boss_id) REFERENCES
> employees(employee_id)
> > );
> >
> > I'm using Postgresql 14.
>
> The question makes no sense as such. You'd have to tell us the purpose
> you want to achieve with partitioning. Depending on that, you'd choose
> the partitioning strategy and the partitioning key.
>
> At any rate, you'll probably have to drop the foreign key constraint
> unless your partitioning key is "id".
>
> Yours,
> Laurenz Albe
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-02-05 10:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-04 15:57 Partition table with a foreign key self-reference Oliver Urones <ourones.joinup@gmail.com>
2025-02-05 07:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
2025-02-05 10:25 ` Juan Rincon Gonzalez <juanrincongonzalez347@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