i have 2 tables ,A and A_backup,now i want to drop the backup table if the original exists ?

luvpurohit

i am extracting tables weekly from a different Db ,before extraction i rename original tables to backup,and after extraction delete the backup table ,How could this be achived

Jim_Knicely

Maybe something lilke this?

dbadmin=> CREATE TABLE a (c INT); -- Original table
CREATE TABLE

dbadmin=> INSERT INTO a SELECT 1; -- Original table
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT COPY_TABLE('a', 'a_backup'); -- Make backup pre-extraction
                     COPY_TABLE
-----------------------------------------------------
 Created table a_backup.
Copied table a to a_backup

(1 row)

dbadmin=> INSERT INTO a SELECT 2; -- Extracted data added to original table
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT COUNT(*) FROM a;
 COUNT
-------
     2
(1 row)

dbadmin=> SELECT COUNT(*) FROM a_backup;
 COUNT
-------
     1
(1 row)

dbadmin=> \! vsql -Atc "SELECT 'SELECT ''DROP TABLE ' || table_schema || '.' || table_name || ' CASCADE;'' sql FROM dual WHERE (SELECT COUNT(*) FROM ' ||  table_schema || '.' || REPLACE(table_name, '_backup', '') || ') > (SELECT COUNT(*) FROM ' || table_schema || '.' || table_name || ');' FROM tables WHERE table_name ILIKE '%backup';" | vsql -At | vsql # Run the DDL
DROP TABLE

dbadmin=> \dt a*
              List of tables
 Schema | Name | Kind  |  Owner  | Comment
--------+------+-------+---------+---------
 public | a    | table | dbadmin |
(1 row)

But why not extract into a new table first then swap the tables?

dbadmin=> \dt a*
              List of tables
 Schema | Name | Kind  |  Owner  | Comment
--------+------+-------+---------+---------
 public | a    | table | dbadmin |
(1 row)

dbadmin=> SELECT * FROM a;
 c
---
 1
 2
(2 rows)

dbadmin=> CREATE TABLE a_temp LIKE a INCLUDING PROJECTIONS; -- Load new data here
CREATE TABLE

dbadmin=> INSERT INTO a_temp SELECT 1;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO a_temp SELECT 2;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO a_temp SELECT 3;
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT

dbadmin=> ALTER TABLE a, a_temp, temp RENAME TO temp, a, a_temp; -- Swap the tables!
ALTER TABLE

dbadmin=> SELECT * FROM a;
 c
---
 1
 2
 3
(3 rows)

dbadmin=> SELECT * FROM a_temp;
 c
---
 1
 2
(2 rows)

dbadmin=> DROP TABLE a_temp;
DROP TABLE