pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
idiom 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