Stack Overflow
Stack Overflow

Questions tagged [postgresql-copy]

COPY is an SQL command in PostgreSQL to move data between files and tables. There is also the meta-command \copy in the psql interface.

Besides the shell utilities pg_dump and pg_restore for backup and restore, there is also the SQL command COPY in PostgreSQL to move data between files and database tables quickly. Several file formats are supported: text, csv and binary.

The meta-command \copy in the psql interface is a wrapper for the SQL command that reads and writes files local to the client (while COPY is for files on the server).

Examples

Write all rows from a table employees to a CSV file on the DB server with SQL COPY:

COPY employees TO '/path/to/employees.csv' (FORMAT csv);

There are more examples for COPY in the manual .

Read data from a CSV file on the client machine into a table with matching structure with \copy in psql (appending to existing data):

\copy employees FROM '/path/to/employees.csv' (FORMAT csv);
147 questions
2
votes
1 answer

Why does PostgreSQL use so much memory during multiple insert than copy?

I have been practising bulk load using Laurenz Albe' s blog https://www.cybertec-postgresql.com/en/postgresql-bulk-loading-huge-amounts-of-data/. The problem start when I changed it a little bit. The table is the same: CREATE TABLE t_sample ( a…
Umut TEKİN
  • 856
  • 1
  • 9
  • 19
2
votes
2 answers

can't import files csv in pgAdmin 4

i will import data csv to postgresql via pgAdmin 4. But, there are problem ERROR: invalid input syntax for type integer: "" CONTEXT: COPY films, line 1, column gross: "" i understand about the error that is line 1 column gross there is null value…
linggapratama28
  • 23
  • 6
2
votes
1 answer

Issue with importing formatted numbers over 1,000 with COPY

I have a Postgres database and have been trying to import a CSV file into a table with the code below. I keep getting the error ERROR: invalid input syntax for type numeric: " 1,183.26 " I assume the issue is that there is a , in the value but…
Hercislife
  • 159
  • 2
  • 10
2
votes
2 answers

Is it possible to make the psql \copy see a line inside a csv file as a comment?

I'm successfully inserting csv files to postgresql with the following command: \COPY tablename(col1, col2, col3) FROM '/home/user/mycsv.txt' WITH CSV HEADER DELIMITER ';' NULL AS 'null'; However, I'd like to write some metadata inside this csv file…
Rafael Muynarsk
  • 614
  • 2
  • 7
  • 21
2
votes
1 answer

Can reads occur whilst executing a bulk \copy batch of inserts

I plan to be batch inserting a large volume of rows into a Postgres table using the \copy command once per minute. My benchmarks show I should be able to insert about 40k rows per second, and I plan to do this for 3 or 4 seconds each minute. Are…
Fachtna Simi
  • 437
  • 2
  • 13
2
votes
2 answers

Using AWK and PostgreSQL's COPY FROM PROGRAM together

I have a .dat file consisting of thousands of lines, where each line is made of 4 sets of numbers, delimited by ::. It ends up looking something like this: 1234::482::4::1342.5321234 4342::532::1::1532.532431 I'm trying to copy this file into a…
paul go
  • 27
  • 6
2
votes
2 answers

Is it possible to use postgres/psql COPY into a specific partition of a table?

I am currently looking into an efficient way to allocate data into a partitioned table. Is it possible to use postgres/psql to COPY data into a specific table partition (instead of using INSERT)? According to the documentation on COPY here: COPY…
alt-f4
  • 2,112
  • 17
  • 49
2
votes
1 answer

Use COPY to read a csv into database, while adding constant columns

I have a series of CSV files, in the format: "Alice","Bob","A",123.46,"4" "Charlie","Dana","B",987.6543,"9" ... I want to produce a table like this: id file_id mch c1 c2 c3 c4 c5 --- ------- ----- --------- ------ …
Green Cloak Guy
  • 23,793
  • 4
  • 33
  • 53
2
votes
1 answer

Data type for date in PostgreSQL

I've exported a CSV file from Excel which has date in format ddmmyyyy hmm. I'm using the COPY function to import to a table in PostgreSQL. Since I only want to retain the date part I tried the date data type: CREATE TABLE…
ANKIT BISHT
  • 47
  • 8
2
votes
1 answer

How to save PostgreSQL output to text file

I've struggled with other StackOverflow responses. I would like to save the output of a query to a local text file - it doesn't really matter where the text file is located as long as it is on my local machine. Code I am using: \COPY ( select…
ZJAY
  • 2,517
  • 9
  • 32
  • 51
2
votes
1 answer

How to generate table from csv in postgres sql

I am new to database management and we are using psql. All I need to do is to migrate csv (around 200 tables) to our database. Manually creating tables for every csv file is bit tiresome so please help me out, Is there any way to generate table from…
saniojoseph
  • 21
  • 1
  • 2
2
votes
2 answers

postgreSQL COPY command error

Hallo everyone once again, I did various searches but couldn't gind a suitable/applicable answer to the simple problem below: On pgAdminIII (Windows 7 64-bit) I am running the following command using SQL editor: COPY public.Raw20120113 FROM…
Petros Apotsos
  • 615
  • 2
  • 6
  • 13
2
votes
2 answers

COPY command postgres syntax error

I am trying to export from a large postgres 8.1 table a couple of rows using copy (select * from tablename limit 100) to 'absolute path to file'; but I get ERROR: syntax error at or near "(" at character 6. Any ideas what might be going wrong…
user1845360
  • 847
  • 2
  • 12
  • 29
1
vote
2 answers

PostgreSQL COPY command issues

In my Postgresql database I have created a table called NAV_DATA with five columns: id (PKEY) ,name (VARCHAR) ,call_type (VARCHAR) ,date_created (date) For these columns I have the csv file which the delimiter TAB \t and Quotechar ". It also has…
Nava
  • 6,276
  • 6
  • 44
  • 68
1
vote
1 answer

WARNING: nonstandard use of escape in a string literal

I have installed source code of Postgres version 8.4.18, I am trying to create a table from .CSV file. I have created a table successfully when, I am trying to copy the location with command COPY population from 'C:\Users\xyz\Desktop\Data8277.csv'…
Sam
  • 19
  • 3
1 2 3
…
9 10