pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedidiom to move records?
3+ messages / 3 participants
[nested] [flat]
* idiom to move records?
@ 1998-10-26 05:31 Leslie Mikesell <les@Mcs.Net>
0 siblings, 2 replies; 3+ messages in thread
From: Leslie Mikesell @ 1998-10-26 05:31 UTC (permalink / raw)
To: pgsql-sql
Is there a handy way to move a set of records to a different
table in sql? I'd like to do this based on a WHERE clause
and have it work atomically such that it will not lose new
records added between the step that copies to the other table
and deleting the copied records.
Les Mikesell
les@mcs.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] idiom to move records?
@ 1998-10-26 09:34 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Leslie Mikesell <les@Mcs.Net>
1 sibling, 0 replies; 3+ messages in thread
From: Herouth Maoz @ 1998-10-26 09:34 UTC (permalink / raw)
To: Leslie Mikesell <les@Mcs.Net>; +Cc: pgsql-sql
At 7:31 +0200 on 26/10/98, Leslie Mikesell wrote:
> Is there a handy way to move a set of records to a different
> table in sql? I'd like to do this based on a WHERE clause
> and have it work atomically such that it will not lose new
> records added between the step that copies to the other table
> and deleting the copied records.
Do it in a transaction, then. Assuming that the target table is defined
(not a temporary table you create ad-hoc), you do:
BEGIN TRANSACTION;
LOCK TABLE source_table;
INSERT INTO target_table (col1, col2, col3)
SELECT (expr1, expr2, expr3)
FROM source_table
WHERE <where condition here>;
DELETE FROM source_table
WHERE <where condition here>;
COMMIT;
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] idiom to move records?
@ 1998-10-26 10:02 Jan Wieck <jwieck@debis.com>
parent: Leslie Mikesell <les@Mcs.Net>
1 sibling, 0 replies; 3+ messages in thread
From: jwieck@debis.com @ 1998-10-26 10:02 UTC (permalink / raw)
To: les@Mcs.Net; +Cc: pgsql-sql
>
> Is there a handy way to move a set of records to a different
> table in sql? I'd like to do this based on a WHERE clause
> and have it work atomically such that it will not lose new
> records added between the step that copies to the other table
> and deleting the copied records.
Use a transaction and lock the source table first.
BEGIN TRANSACTION;
LOCK TABLE src_table;
INSERT INTO dest_table SELECT * FROM src_table
WHERE ...;
DELETE FROM src_table WHERE ...;
COMMIT TRANSACTION;
No other backend can add, modify or remove rows to/in/from
src_table while you have a lock on it.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1998-10-26 10:02 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-10-26 05:31 idiom to move records? Leslie Mikesell <les@Mcs.Net>
1998-10-26 09:34 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1998-10-26 10:02 ` Jan Wieck <jwieck@debis.com>
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