From: | Mark Mielke <mark(at)mark(dot)mielke(dot)cc> |
---|---|
To: | Postgresql-Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | insert ... delete ... returning ... ? |
Date: | 2008-02-24 21:21:27 |
Message-ID: | 47C1DFD7.7060603@mark.mielke.cc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi all:
I'm at PostgreSQL 8.3 for my production database and everything is
working great. I had no problems converting free text search from 8.2 to
8.3, and I really like the improvements.
I tried using insert ... delete ... return ... and get a syntax error:
> pccyber=# insert into product_manufacturer_archived (itemno,
> manufacturer_id)
> pccyber-# delete from product_manufacturer
> pccyber-# where not exists (select * from icitem
> pccyber(# where icitem.itemno =
> product_manufacturer.itemno and
> pccyber(# not inactive)
> pccyber-# returning itemno, manufacturer_id;
> ERROR: syntax error at or near "delete"
> LINE 2: delete from product_manufacturer
> ^
The goal here is to move inactive records to an archived table. This is
to be performed as part of a daily batch job instead of as a trigger.
Assume my model is correct - my question isn't how can I do this. I
would like to know if insert .. delete .. returning is intended to work
or not.
In the past I've executed insert ... select and then the delete.
However, I believe there is race condition here as the delete may see
more or less rows than the insert ... select. I thought the above would
be a clever PostgreSQL-8.3 alternative, but I'm either stupid or it
doesn't work... :-)
Any ideas?
Thanks,
mark
--
Mark Mielke <mark(at)mielke(dot)cc>
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Smet | 2008-02-24 21:39:03 | Re: insert ... delete ... returning ... ? |
Previous Message | Florian G. Pflug | 2008-02-24 19:25:34 | Re: Behaviour of rows containg not-null domains in plpgsql |