Re: PostgreSQL - Ordering Table based of Foreign Key

[FOUTE K . Jaurès]

Hello,

Thx for all the feedback.
After googling that, I saw this link that can be a way I can go.
https://stackoverflow.com/questions/51279588/sort-tables-in-order-of-dependency-postgres


Le dim. 3 oct. 2021 à 22:33, David G. Johnston 
a écrit :

>
>
> On Sun, Oct 3, 2021, 00:48 FOUTE K. Jaurès  wrote:
>
>> Hello,
>>
>> I want to order tables based on the foreign key so that I can delete
>> tables one by one without facing "ERROR: update or delete on table
>> "table" violates foreign key constraint. DETAIL: Key is still referenced
>> from table"
>>
>
> As others have said this is why on delete cascade exists.  Unfortunately
> this does require some advanced planning as changing it on the fly doesn't
> really work.
>
> I do not believe there is a built-in way to return an ordered listing of
> dependent relations given a base relation as an input.  But the catalog
> entries do exist should you wish to build such yourself.
>
> That said maybe deferred constraint evaluation will work so that at least
> the order doesn't matter.  But you still.would.need to know which tables to
> write delete commands for.
>
> There is some recent discussion on making this work in a more
> user-friendly away but that would be only available in v15 at best.
>
> David J.
>
>
>>

-- 
Jaurès FOUTE

Re: PostgreSQL - Ordering Table based of Foreign Key

[David G. Johnston]

On Sun, Oct 3, 2021, 00:48 FOUTE K. Jaurès  wrote:

> Hello,
>
> I want to order tables based on the foreign key so that I can delete
> tables one by one without facing "ERROR: update or delete on table
> "table" violates foreign key constraint. DETAIL: Key is still referenced
> from table"
>

As others have said this is why on delete cascade exists.  Unfortunately
this does require some advanced planning as changing it on the fly doesn't
really work.

I do not believe there is a built-in way to return an ordered listing of
dependent relations given a base relation as an input.  But the catalog
entries do exist should you wish to build such yourself.

That said maybe deferred constraint evaluation will work so that at least
the order doesn't matter.  But you still.would.need to know which tables to
write delete commands for.

There is some recent discussion on making this work in a more user-friendly
away but that would be only available in v15 at best.

David J.


>

Re: PostgreSQL - Ordering Table based of Foreign Key

[Adrian Klaver]

On 10/3/21 9:24 AM, FOUTE K. Jaurès wrote:

Please don't top post, inline/bottom posting is the preferred style on 
this list.


I need to delete data from about 100 table in the production system for 
a specific client without stopping application.


Honestly that is not that much more informative.

So:

1) Do the FK's have "ON DELETE CASCADE" already?

2) Does a single FK cascade through all 100 tables or are there groups 
of parent/child tables and associated FK's


3) Show an example DELETE query and tables affected.



On Sun, 3 Oct 2021, 17:19 Adrian Klaver, > wrote:


On 10/3/21 7:40 AM, FOUTE K. Jaurès wrote:
 > Thanks for your input but I want to make a delete with where clause

Then Thomas Kellerer's suggestion of having "ON DELETE CASCADE" on the
FK would seem to be what you need. If not then you will need to provide
more information about what you are trying to achieve?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PostgreSQL - Ordering Table based of Foreign Key

[FOUTE K . Jaurès]

I need to delete data from about 100 table in the production system for a
specific client without stopping application.

On Sun, 3 Oct 2021, 17:19 Adrian Klaver,  wrote:

> On 10/3/21 7:40 AM, FOUTE K. Jaurès wrote:
> > Thanks for your input but I want to make a delete with where clause
>
> Then Thomas Kellerer's suggestion of having "ON DELETE CASCADE" on the
> FK would seem to be what you need. If not then you will need to provide
> more information about what you are trying to achieve?
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: PostgreSQL - Ordering Table based of Foreign Key

[Adrian Klaver]

On 10/3/21 7:40 AM, FOUTE K. Jaurès wrote:

Thanks for your input but I want to make a delete with where clause


Then Thomas Kellerer's suggestion of having "ON DELETE CASCADE" on the 
FK would seem to be what you need. If not then you will need to provide 
more information about what you are trying to achieve?



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: PostgreSQL - Ordering Table based of Foreign Key

[FOUTE K . Jaurès]

Thanks for your input but I want to make a delete with where clause

On Sun, 3 Oct 2021, 10:26 Andreas Joseph Krogh,  wrote:

> På søndag 03. oktober 2021 kl. 10:49:49, skrev Thomas Kellerer <
> sham...@gmx.net>:
>
> FOUTE K. Jaurès schrieb am 03.10.2021 um 09:48:
> > I want to order tables based on the foreign key so that I can delete
> > tables one by one without facing "ERROR: update or delete on table
> > "table" violates foreign key constraint. DETAIL: Key is still
> > referenced from table"
>
> You can create the foreign key constraints with the "ON DELETE CASCADE"
> option.
> Then Postgres will handle dependencies automatically for you.
>
> But that means that *all* DELETEs will be cascaded.
>
> Unfortunately, there is no DELETE FROM ... CASCADE option (similar to DROP)
>
>
> There is TRUNCATE ... CASCADE
> https://www.postgresql.org/docs/14/sql-truncate.html
>
> --
> Andreas Joseph Krogh
>

Re: PostgreSQL - Ordering Table based of Foreign Key

[Andreas Joseph Krogh]

På søndag 03. oktober 2021 kl. 10:49:49, skrev Thomas Kellerer mailto:sham...@gmx.net>>: 
FOUTE K. Jaurès schrieb am 03.10.2021 um 09:48:
 > I want to order tables based on the foreign key so that I can delete
 > tables one by one without facing "ERROR: update or delete on table
 > "table" violates foreign key constraint. DETAIL: Key is still
 > referenced from table"

 You can create the foreign key constraints with the "ON DELETE CASCADE" 
option.
 Then Postgres will handle dependencies automatically for you.

 But that means that *all* DELETEs will be cascaded.

 Unfortunately, there is no DELETE FROM ... CASCADE option (similar to DROP) 

There is TRUNCATE ... CASCADE 
https://www.postgresql.org/docs/14/sql-truncate.html 



-- 
Andreas Joseph Krogh 

Re: PostgreSQL - Ordering Table based of Foreign Key

[Thomas Kellerer]

FOUTE K. Jaurès schrieb am 03.10.2021 um 09:48:

I want to order tables based on the foreign key so that I can delete
tables one by one without facing "ERROR: update or delete on table
"table" violates foreign key constraint. DETAIL: Key is still
referenced from table"


You can create the foreign key constraints with the "ON DELETE CASCADE" option.
Then Postgres will handle dependencies automatically for you.

But that means that *all* DELETEs will be cascaded.

Unfortunately, there is no DELETE FROM ... CASCADE option (similar to DROP)

Thomas

Re: PostgreSQL - Ordering Table based of Foreign Key

[Karsten Hilbert]

Am Sun, Oct 03, 2021 at 08:48:13AM +0100 schrieb FOUTE K. Jaurès:

> I want to order tables based on the foreign key so that I can delete tables
> one by one without facing "ERROR: update or delete on table "table"
> violates foreign key constraint. DETAIL: Key is still referenced from table"

drop table if exists ... cascade;

Karsten
--
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

PostgreSQL - Ordering Table based of Foreign Key

[FOUTE K . Jaurès]

Hello,

I want to order tables based on the foreign key so that I can delete tables
one by one without facing "ERROR: update or delete on table "table"
violates foreign key constraint. DETAIL: Key is still referenced from table"

Any help is appreciated.
Thank you in advance.

Best regards
Jaurès FOUTE