Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [copy]

COPY is a PostgreSQL SQL statement for bulk loading tables and fetching table data or query results

135 questions
23
votes
2 answers

Export remote Postgres table to CSV file on local machine

I have read-only access to a database on a remote server. So, I can execute: COPY products TO '/tmp/products.csv' DELIMITER ','; But on that server I don't have permissions to create/save a file, so I need to do this on my local machine. When I…
tasmaniski
  • 1,195
  • 4
  • 13
  • 16
11
votes
1 answer

Postgres COPY with on conflict ignore - possible?

I want to add on conflict ignore to the Postgres copy command. I know I can copy the data to a table without unique index /primary key and then use insert with the on conflict syntax. But I wanted to know if this is possible directly from COPY?
Nir
  • 529
  • 2
  • 11
  • 27
10
votes
5 answers

Postgres inherit indexes for partitioned tables

I have a table with approximately 60 million rows that I have partitioned by state into 53 sub-tables. These tables "inherit" the big table like so: CREATE TABLE b2b_ak (LIKE b2b including indexes, CHECK ( state = 'AK') ) INHERITS (b2b8) TABLESPACE…
ABCD EFGHIJK
  • 109
  • 1
  • 1
  • 7
9
votes
2 answers

Does the PostgreSQL COPY command have the option of choosing which fields to map the CSV columns to?

Does the PostgreSQL COPY command have the option of choosing which fields to map the CSV columns to? The PostgreSQL COPY command appears to expect that the target table matches its columns exactly. Am I missing something or is that how it actually…
vfclists
  • 1,093
  • 4
  • 14
  • 21
7
votes
1 answer

How do I use the `COPY` command with JDBC

So I've been working with postgre databases and I noticed that it throws an error sometimes when I try to read a csv file and copy to a table remotely. It works perfectly when I use a buffered reader but i'm trying to see if there's a way i can get…
JCole
  • 73
  • 1
  • 1
  • 5
6
votes
1 answer

Fastest way to extract full table in Postgres

I'm trying to dump a table with 50M records to a file, and my goal is to reduce the time in which this action is performed. I usually use the COPY metrics TO 'metrics.csv' DELIMITER ',' CSV; This could take like an hour in the best cases. I'm also…
Imanol Y.
  • 785
  • 2
  • 10
  • 28
6
votes
2 answers

Scripting the copy of SQL Server production database to test environment

We are introducing continuous integration on our project and we decided to automate the refresh of our test database instance with fresh production data. I'm currently looking for the best way to go in order to avoid any production disruptions or…
Koresh
  • 291
  • 1
  • 5
  • 9
5
votes
2 answers

Escaping delimiter in postgresql

I am trying to import data into postgres. Data fields are comma separated, strings may contain commas, in which case those are escaped: Data structure: create table v (a varchar); Data file: bus 'Gat\,\\e\'way_MQB' Import command (run under Linux…
arthur
  • 888
  • 4
  • 13
  • 29
5
votes
2 answers

Change permissions of postgres copy to exported file

I have asked this question on SO but perhaps that was the wrong location. Apologies for double posting but the question may have a better audience here. The original question is:…
Mitch Kent
  • 205
  • 2
  • 3
  • 9
5
votes
1 answer

PostgreSQL PSQL client-side commands and new lines (\COPY)

The error message is the same with super user using COPY. The files are located on the same server as the postgres server. Saw many similar posting but not a single one answer my question. \copy table_name from '/path/to/csv/file.csv' with format…
Kemin Zhou
  • 219
  • 2
  • 3
  • 10
5
votes
3 answers

copy command in postgresql to append data

I am executing copy command from a function execute 'copy (select * from tableName) to ''/tmp/result.txt''' It works fine. But, tableName is dynamic and will be having more tables (iterating in a loop). result.txt is having only the last iteration…
RBB
  • 815
  • 2
  • 15
  • 37
5
votes
1 answer

Copy-only as the routine backup

We are in planning to upgrade from SQL Server 2008 R2 to SQL Server 2014, with the intention of using Availability Groups. One of the secondaries will be used to do backups. I have tested and found that I can do point in time restores as long as I…
JohnH
  • 433
  • 4
  • 10
5
votes
1 answer

CREATE DATABASE ... WITH TEMPLATE seems to lose relations in PostgreSQL

I'm trying to Create a copy of a database in postgresql per the SO answer from 2009, but running into problems. In Postgres 9.3.9, this creates a database with no relations (the gcis db exists and has tables and data): postgres=# CREATE DATABASE…
Randall
  • 385
  • 5
  • 18
5
votes
0 answers

postgres \copy file to RDS timeout

Postgres version 9.3, hosted on Amazon RDS. I have a messages table with 10G of data. I connected the database using psql on the EC2. Then used \COPY (select * from messages) TO '/tmp/messages.csv' WITH (FORMAT CSV, FORCE_QUOTE *) to copy all…
Zhaohan Weng
  • 343
  • 1
  • 3
  • 10
4
votes
1 answer

Parsing COPY's binary format to access a tsrange

How is tsrange stored in binary? For example create table CREATE TABLE public.test (t tsrange); INSERT INTO test VALUES ('[2010-01-01 14:30, 2010-01-01 15:30)'); INSERT INTO test VALUES ('[2011-01-01 14:31, 2015-11-01 15:30)'); INSERT INTO test…
eri
  • 143
  • 1
  • 6
1
2 3
…
8 9