Move Data from One Table to Another, Postgresql Edition

How do I copy data from one table to another in postgres using copy command

You cannot easily do that, but there's also no need to do so.

CREATE TABLE mycopy AS
SELECT * FROM mytable;

or

CREATE TABLE mycopy (LIKE mytable INCLUDING ALL);

INSERT INTO mycopy
SELECT * FROM mytable;

If you need to select only some columns or reorder them, you can do this:

INSERT INTO mycopy(colA, colB)
SELECT col1, col2 FROM mytable;

You can also do a selective pg_dump and restore of just the target table.

Copy a table from one database to another in Postgres

Extract the table and pipe it directly to the target database:

pg_dump -t table_to_copy source_db | psql target_db

Note: If the other database already has the table set up, you should use the -a flag to import data only, else you may see weird errors like "Out of memory":

pg_dump -a -t table_to_copy source_db | psql target_db

Postgres - move rows to different table

basically I want to redirect an insert from cmdb to cmdb_attic where that condition is met

The trigger function for that should look like this:

BEGIN
    IF NEW.mgmt_ip = '' THEN
        INSERT INTO cmdb_attic VALUES (NEW.*);
        RETURN NULL;
    ELSE
        RETURN NEW;
    END IF;
END;

(online demo)

How to move data from one table to another in Postgres (while working with PK's)

This query returns one column, a tuple, that has multiple fields:

SELECT (symbol, rating_buy, rating_overweight, rating_hold, rating_underweight,  
                    rating_sell, rating_none, rating_scale, consensus_start_date, consensus_end_date,
                    corp_actions_applied, total_recs)
FROM security_recs;

This query -- without the parentheses -- returns multiple columns:

SELECT symbol, rating_buy, rating_overweight, rating_hold, rating_underweight,  
                    rating_sell, rating_none, rating_scale, consensus_start_date, consensus_end_date,
                    corp_actions_applied, total_recs
FROM security_recs;

As a best practice, I would recommend that you list all the columns for the insert as well:

INSERT INTO security_recs_test (symbol, rating_buy, rating_overweight, rating_hold, rating_underweight,  
                    rating_sell, rating_none, rating_scale, consensus_start_date, consensus_end_date,
                    corp_actions_applied, total_recs)
    SELECT symbol, rating_buy, rating_overweight, rating_hold, rating_underweight,  
           rating_sell, rating_none, rating_scale, consensus_start_date, consensus_end_date,
           corp_actions_applied, total_recs
    FROM security_recs;

move part of table postgresql to another database

I'd use the \copy command. The copy is described here https://www.postgresql.org/docs/current/sql-copy.html. The \copy is available inside psql. The difference is that copy assumes the path to a file is on the database machine, while\copy assumes it's on the machine where the psql runs.

You can use \copy like this (this is an example from the above page):

\COPY (SELECT * FROM country WHERE country_name LIKE 'A%') 
TO 'country_data';

This way you have all the rows written to this file. Then you can load it to another database also using copy:

\COPY country FROM 'country_data';

Then you can delete the data with:

DELETE FROM country WHERE country_name LIKE 'A%';

You could also make copy and delete in one query. However, in this case I'd rather carefully check if the data in the new database is fine before deleting it.

---

Related Topics