How do I copy data from one table to another in postgres using copy command
We use copy command to copy data of one table to a file outside database.
Is it possible to copy data of one table to another table using command.
If yes can anyone please share the query.
Or is there any better approach like we can use pg_dump or something like that.
insert into other (col1, col2) select col1, col2 from one
INSERT statement?
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.
If the columns are the same (names and datatypes) in both tables then you can use the following
INSERT INTO receivingtable (SELECT * FROM sourcetable WHERE column1='parameter' AND column2='anotherparameter');
Suppose there is already a table and you want to copy all records from this table to another table which is not currently present in the database then following query will do this task for you:
SELECT * into public."NewTable" FROM public."ExistingTable";
select ... into syntax is deprecated in favor of the standard compliant create table ... as select ...
Follow WeChat
Success story sharing
Want to stay one step ahead of the latest teleworks?
Subscribe Now相似问题
- Copy the entire contents of a directory in C#
- SQL Update from One Table to Another Based on a ID Match
- When should I use CROSS APPLY over INNER JOIN?
- How to copy data from one table to another new table in MySQL?
- How to exit from PostgreSQL command line utility: psql
- How do I copy a folder from remote to local using scp?
- Insert text with single quotes in PostgreSQL
- Copy data into another table
- How do I import an SQL file using the command line in MySQL?
INSERT INTO mycopy SELECT * FROM mytableapproach I getERROR: column "order" is of type integer but expression is of type text LINE 2: SELECT * FROM django_apps.about_postTo be clear,orderis an integer in both tables.\doutput frompsqlin both tablesINSERT INTO mycopy (a, b) SELECT * FROM mytable, but I was coming here to try to find an alternative to that. My tables have all the same columns, just in a different order. Turns out it's not possible. stackoverflow.com/questions/1267427/…