Re: [GENERAL] [PG9.1] CTE usage

2013-09-16 Thread David Johnston
Ladislav Lenart wrote > Hello all. > > I am curious about the following usage of CTEs: > > Imagine three tables: > * item (id, item_type1_id, item_type2_id, ...) > * item_type1 (id, ...) > * item_type2 (id, ...) > where > * item_type1_id is FK to item_type1 (id) > * item_type2_id is FK to it

Re: [GENERAL] [PG9.1] CTE usage

2013-09-16 Thread Vincent Veyron
Le lundi 16 septembre 2013 à 08:30 -0700, David Johnston a écrit : > Ladislav Lenart wrote > > Hello all. > > > > I am curious about the following usage of CTEs: > > > > Imagine three tables: > > * item (id, item_type1_id, item_type2_id, ...) > > * item_type1 (id, ...) > > * item_type2 (id, ..

Re: [GENERAL] [PG9.1] CTE usage

2013-09-16 Thread Ladislav Lenart
On 16.9.2013 17:30, David Johnston wrote: > Ladislav Lenart wrote >> Hello all. >> >> I am curious about the following usage of CTEs: >> >> Imagine three tables: >> * item (id, item_type1_id, item_type2_id, ...) >> * item_type1 (id, ...) >> * item_type2 (id, ...) >> where >> * item_type1_id is

Re: [GENERAL] [PG9.1] CTE usage

2013-09-16 Thread Ladislav Lenart
On 16.9.2013 17:12, Adrian Klaver wrote: > On 09/16/2013 07:38 AM, Ladislav Lenart wrote: >> On 16.9.2013 15:50, Adrian Klaver wrote: >>> On 09/16/2013 04:57 AM, Ladislav Lenart wrote: On 16.9.2013 13:26, Alban Hertroys wrote: >>> > >>> .." >> >> >> Hello. >> >> Thank you but I have read this

Re: [GENERAL] [PG9.1] CTE usage

2013-09-16 Thread Adrian Klaver
On 09/16/2013 07:38 AM, Ladislav Lenart wrote: On 16.9.2013 15:50, Adrian Klaver wrote: On 09/16/2013 04:57 AM, Ladislav Lenart wrote: On 16.9.2013 13:26, Alban Hertroys wrote: .." Hello. Thank you but I have read this in the official documentation before posting my (previous) reply. S

Re: [GENERAL] [PG9.1] CTE usage

2013-09-16 Thread Ladislav Lenart
On 16.9.2013 15:50, Adrian Klaver wrote: > On 09/16/2013 04:57 AM, Ladislav Lenart wrote: >> On 16.9.2013 13:26, Alban Hertroys wrote: > >>> >>> Wouldn't it be much easier to define an FK constraint with ON DELETE >>> CASCADE? >>> With that, you only need to worry about which rows you delete from

Re: [GENERAL] [PG9.1] CTE usage

2013-09-16 Thread Adrian Klaver
On 09/16/2013 04:57 AM, Ladislav Lenart wrote: On 16.9.2013 13:26, Alban Hertroys wrote: Wouldn't it be much easier to define an FK constraint with ON DELETE CASCADE? With that, you only need to worry about which rows you delete from the parent table and dependant children will be removed aut

Re: [GENERAL] [PG9.1] CTE usage

2013-09-16 Thread Ladislav Lenart
Nevermind, I already found the root cause of my problem: boolean logic of NULL in conjunction with the NOT IN operator. My real usecase was a bit more involved: WITH items_to_delete AS ( SELECT item.id AS item_id, item.item_type1_id AS item_type1_id, item.item_type2_id

Re: [GENERAL] [PG9.1] CTE usage

2013-09-16 Thread Ladislav Lenart
On 16.9.2013 13:26, Alban Hertroys wrote: > On 16 September 2013 11:58, Ladislav Lenart wrote: >> Hello all. >> >> I am curious about the following usage of CTEs: >> >> Imagine three tables: >> * item (id, item_type1_id, item_type2_id, ...) >> * item_type1 (id, ...) >> * item_type2 (id, ...) >>

Re: [GENERAL] [PG9.1] CTE usage

2013-09-16 Thread Alban Hertroys
On 16 September 2013 11:58, Ladislav Lenart wrote: > Hello all. > > I am curious about the following usage of CTEs: > > Imagine three tables: > * item (id, item_type1_id, item_type2_id, ...) > * item_type1 (id, ...) > * item_type2 (id, ...) > where > * item_type1_id is FK to item_type1 (id) >

[GENERAL] [PG9.1] CTE usage

2013-09-16 Thread Ladislav Lenart
Hello all. I am curious about the following usage of CTEs: Imagine three tables: * item (id, item_type1_id, item_type2_id, ...) * item_type1 (id, ...) * item_type2 (id, ...) where * item_type1_id is FK to item_type1 (id) * item_type2_id is FK to item_type2 (id) Items are of two types (type1