From: Andreas <maps.on@gmx.net>
To: pgsql-sql@postgresql.org
Cc: Kretschmer Andreas <akretschmer@spamfence.net>
Cc: M.Mamin@intershop.de
Subject: Re: Prevent double entries ... no simple unique index
Date: Thu, 12 Jul 2012 10:44:32 +0200
Message-ID: <4FFE8E70.3060109@gmx.net> (raw)
In-Reply-To: <20120712051445.GA5421@tux>
References: <4FFD3050.3010509@gmx.net>
<20120711081607.GA17798@tux>
<20120711082458.GA19013@tux>
<C4DAC901169B624F933534A26ED7DF310861B61A@JENMAIL01.ad.intershop.net>
<20120712051445.GA5421@tux>
Am 12.07.2012 07:14, schrieb Andreas Kretschmer:
> Marc Mamin <M.Mamin@intershop.de> wrote:
>
>> A partial index would do the same, but requires less space:
>>
>> create unique index on log(state) WHERE state IN (0,1);
>
OK, nice :)
What if I have those states in a 3rd table?
So I can see a state-history of when a state got set by whom.
objects ( id serial PK, ... )
events ( id serial PK, object_id integer FK on objects.id, ... )
event_states ( id serial PK, event_id integer FK on events.id, state
integer )
There still should only be one event per object that has state 0 or 1.
Though here I don't have the object-id within the event_states-table.
Is it still possible to have a unique index that needs to span over a
join of events and event_states?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: maps.on@gmx.net, akretschmer@spamfence.net, M.Mamin@intershop.de
Subject: Re: Prevent double entries ... no simple unique index
In-Reply-To: <4FFE8E70.3060109@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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