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

Ora2PG - Oracle/MySQL to Postgres DB migration Version 20.0

spfile and pfile errors

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Return a Result Set from a PostgreSQL Stored Procedure

PostgreSQL New version upgrade