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;

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
);
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
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
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.

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

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

7 Steps to configure BDR replication in postgresql

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

How to Enable/Disable autovacuum on PostgreSQL