How to copy table data in postgres Database

You can use INSERT  INTO statement by selected specific table column or use * for selecting all column

dblink also will server purpose even if it is remote database.. with condition X server is able to reach Y.

# you can use dblink to connect remote database and fetch result. For example:

psql dbtest
CREATE TABLE tblB (id serial, time integer);
INSERT INTO tblB (time) VALUES (5000), (2000);

psql postgres
CREATE TABLE 2ndquadrant.in (id serial, time integer);

INSERT INTO 2ndquadrant.in SELECT id, time 
    FROM dblink('dbname=dbtest', 'SELECT id, time FROM tblB')
    AS t(id integer, time integer)
    WHERE time > 1000;

TABLE in;
 id | time 
----+------
  1 | 5000
  2 | 2000
(2 rows
# If you want insert into specify column:

INSERT INTO 2ndquadrant.in (time)
(SELECT time FROM 
    dblink('dbname=dbtest', 'SELECT time FROM tblB') AS t(time integer) 
    WHERE time > 1000
);


Copy

Note : See here 2ndquadrant is a schema name  and in is the table name

Or use copy statement from table to text file and back to another table.

2ndquadrant.in=# COPY ehis.citytemp FROM 'D:\2ndq/nijamutheen.csv' DELIMITER '|' CSV HEADER;
ERROR: character with byte sequence 0x9d in encoding "WIN1252" has no equivalent in encoding "UTF8"
CONTEXT: COPY citytemp, line 358646
### solution
2ndquadrant.in=# SET CLIENT_ENCODING TO 'utf8';
SET
2ndquadrant.in=# COPY ehis.citytemp FROM 'D:\2ndq/nijamutheen.csv' DELIMITER '|' CSV HEADER;
COPY 679249


Copy

For more details about COPY command go through this Tutorial

https://2ndquadrant.in/postgres-copy-command-tutorial/

Or use CTAS  create table statement if you want full copy

create table tutorialba.t
as
select * from 2ndquadrant.t2
# this script will copy the table
from t2 and newly created table
name is t ,
Newly created table t will
be stored in tutorialdba schema an
source table is in 2ndquadrant schema.