Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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;

 id | time 
  1 | 5000
  2 | 2000
(2 rows
# If you want insert into specify column:

INSERT INTO 2ndquadrant.in (time)
    dblink('dbname=dbtest', 'SELECT time FROM tblB') AS t(time integer) 
    WHERE time > 1000
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';
2ndquadrant.in=# COPY ehis.citytemp FROM 'D:\2ndq/nijamutheen.csv' DELIMITER '|' CSV HEADER;
COPY 679249
For more details about COPY command go through this Tutorial
Or use CTAS  create table statement if you want full copy
create table tutorialba.t
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.


Popular posts from this blog

PostgreSQL pgBadger

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Database startup / shutdown /restart