Hi folks,
I'm back with my lnumbers table again.
nymr=# \d lnumbers Table "lnumbers" Column | Type | Modifiers -----------+-----------------------+----------- lnid | integer | not null lnumber | character varying(10) | not null lncurrent | boolean | Primary key: lnumbers_pkey Triggers: RI_ConstraintTrigger_7575462
While each loco can have a number of different numbers, only one can be current at any one time.
I want to make it so that if I set lncurrent to true for one row, any existing true rows are set to false.
I'm guessing that I need to create a trigger to be actioned after an insert or update which would update set lncurrent=false where lnid not = <current lnid>
Why "not"? I thought, you wanted just the opposite - update the ones that *do* have the same lnid?
I'd also recommend you to add ' and lncurrent' to the query - otherwise every insert would be updating *every* row with the same lnid (it doesn't check if the new row is actually the same as the old one) before updating, and that may be expensive.
You may also want to create a pratial index on lnumbers (lnid) where lncurrent to speed up your trigger
but I can't seem to sus it put.What is the problem?
Dima
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
