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.
149
questions
1122
votes
21
answers
941k
views
Save PL/pgSQL output from PostgreSQL to a CSV file
What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file?
I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.
- 39.3k
788
votes
21
answers
1.2m
views
How to import CSV file data into a PostgreSQL table
How can I write a stored procedure that imports data from a CSV file and populates the table?
- 7,889
494
votes
14
answers
528k
views
How to export table as CSV with headings on Postgresql?
I'm trying to export a PostgreSQL table with headings to a CSV file via command line, however I get it to export to CSV file, but without headings.
My code looks as follows:
COPY products_273 to '/...
- 36k
299
votes
13
answers
329k
views
Export specific rows from a PostgreSQL table as INSERT SQL script
I have a database schema named: nyummy and a table named cimory:
create table nyummy.cimory (
id numeric(10,0) not null,
name character varying(60) not null,
city character varying(50) not null,...
- 8,926
118
votes
7
answers
217k
views
How to copy from CSV file to PostgreSQL table with headers in CSV file?
I want to copy a CSV file to a Postgres table. There are about 100 columns in this table, so I do not want to rewrite them if I don't have to.
I am using the \copy table from 'table.csv' delimiter ','...
- 10.5k
59
votes
3
answers
113k
views
In PostgreSQL, how to insert data with COPY command?
I have problem when run 1 project NodeJs with PostgreSQL database.
I have error when trying to insert data in pgAdmin using the COPY command.
COPY beer (name, tags, alcohol, brewery, id, brewery_id, ...
user4646310
57
votes
3
answers
46k
views
How does COPY work and why is it so much faster than INSERT?
Today I spent my day improving the performance of my Python script which pushes data into my Postgres database. I was previously inserting records as such:
query = "INSERT INTO my_table (a,b,c ... ) ...
- 2,276
41
votes
6
answers
93k
views
How to export table data from PostgreSQL (pgAdmin) to CSV file?
I am using pgAdmin version 4.3 and i want to export one table data to CSV file. I used this query
COPY (select * from product_template) TO 'D:\Product_template_Output.csv' DELIMITER ',' CSV HEADER;
...
- 711
32
votes
8
answers
87k
views
copy data from csv to postgresql using python
I am on windows 7 64 bit.
I have a csv file 'data.csv'.
I want to import data to a postgresql table 'temp_unicommerce_status' via a python script.
My Script is:
import psycopg2
conn = psycopg2....
- 4,528
32
votes
4
answers
53k
views
ERROR: missing data for column when using \copy in psql
I'm trying to import a .txt file into PostgreSQL. The txt file has 6 columns:
Laboratory_Name Laboratory_ID Facility ZIP_Code City State
And 213 rows.
I'm trying to use \copy to put the ...
- 427
29
votes
2
answers
19k
views
How to generate a schema from a CSV for a PostgreSQL Copy
Given a CSV with several dozen or more columns, how can a 'schema' be created that can be used in a CREATE TABLE SQL expression in PostgreSQL for use with the COPY tool?
I see plenty of examples for ...
- 777
28
votes
6
answers
130k
views
PostgreSQL: export resulting data from SQL query to Excel/CSV
I need to export the resulting data from a query in PostgreSQL to Excel/CSV.
I use PostgreSQL 8.2.11.
SQL error:
ERROR: relative path not allowed for COPY to file
In statement:
COPY (select ...
- 6,064
26
votes
8
answers
17k
views
ERROR: could not stat file "XX.csv": Unknown error
I run this command:
COPY XXX FROM 'D:/XXX.csv' WITH (FORMAT CSV, HEADER TRUE, NULL 'NULL')
In Windows 7, it successfully imports CSV files of less than 1GB.
If the file is more then 1GB big, I get ...
- 391
20
votes
3
answers
15k
views
Importing zipped CSV file into PostgreSQL
I have a big compressed csv file (25gb) and I want to import it into PostgreSQL 9.5 version. Is there any fast way to import zip or qzip file into postgres without extracting the file?
- 462
14
votes
5
answers
32k
views
ERROR: COPY delimiter must be a single one-byte character
I want to load the data from a flat file with delimiter "~,~" into a PostgreSQL table. I have tried it as below but looks like there is a restriction for the delimiter. If COPY statement doesn't allow ...
- 2,083
14
votes
1
answer
11k
views
COPY with dynamic file name
I am trying to write a function to load csv data into a table. I want the input argument to be the path to the file.
CREATE OR REPLACE FUNCTION public.loaddata(filepathname varchar)
RETURNS void AS
$...
- 243
13
votes
3
answers
46k
views
How to convert date strings to timestamp without knowing the date format
I am trying to write a query to insert a value into a timestamp with no timezone data type field. The value is coming from CSV file.
The version I am working with is PostgreSQL 8.1.21.
The CSV file ...
- 225
13
votes
3
answers
19k
views
postgresql csv copy unquoted newline found in data
I have some csv data in excel, and I'm importing it into postgresql. I'm opening the excel csv file with a notepad editor (have tried notepad, wordpad and notepad++) and am then copying/pasting into ...
- 131
12
votes
2
answers
26k
views
Correct way to use copy Postgres jdbc
Unable to use copy command with jdbc Postgres. Whats wrong with the below code snippet sample.
public boolean loadReportToDB(String date) {
// TODO Auto-generated method stub
...
- 1,346
11
votes
2
answers
51k
views
COPY function in PostgreSQL
I would like to use the COPY function in PostgreSQL to import a CSV file into a PostgreSQL database.
Where it says the filename in the documentation, does the CSV file have to be stored in a specific ...
- 1,151
10
votes
2
answers
14k
views
COPY command: copy only specific columns from csv
I had a question surrounding the COPY command in PostgreSQL. I have a CSV file that I only want to copy some of the columns values into my PostgreSQL table.
Is it possible to do this? I am familiar ...
- 1,141
8
votes
1
answer
10k
views
Saving psql output to csv file
I have a query written in a file located at /path/to/query. How can I save the output result to a csv file, without using COPY in the query? I tried the following command, but the output file's ...
- 3,127
7
votes
2
answers
4k
views
Delete rows of a table specified in a text file in Postgres
I have a text file containing the row numbers of the rows that should be deleted in my table like this:
3
32
40
55
[...]
How can I get a PostgreSQL compatible SQL statement which deletes each of ...
- 2,690
7
votes
1
answer
14k
views
Importing CSV with commas in string values
I am trying to import a trivial CSV to Postgres 8.4 database:
Here is a table:
CREATE TABLE public.sample (
a VARCHAR,
b VARCHAR
) WITHOUT OIDS;
Here is a CSV file sample:
"foo","...
- 253k
6
votes
1
answer
13k
views
Execute COPY command in remote database using local file
I'm trying to execute the Postgres COPY command.
COPY warehouse_terminal.test_table FROM 'C:\file.csv' DELIMITERS E'\t'
CSV HEADER QUOTE E'\"' ESCAPE E'\\' NULL AS '';
The problem is, the db ...
- 2,064
6
votes
1
answer
7k
views
How to tell if record has changed in Postgres
I have a bit of an "upsert" type of question... but, I want to throw it out there because it's a little bit different than any that I've read on stackoverflow.
Basic problem.
I'm working on ...
- 13.4k
6
votes
4
answers
3k
views
insert additional column values(not available in file) in postgresql table using COPY
Table columns:
col1, col2, col3, fname, lname
CSV file contains values like:
col1,col2,col3
val1,val2,val3
val1,val2,val3
I want to add data from csv along with additional data i.e. col1,col2,col3,...
- 1,648
5
votes
2
answers
21k
views
How to use \copy in postgresql with pgadmin4
I want to use the \copy command to make csv file with relative path.
I used a query to make from test table to csv file named test.csv
\copy (SELECT * FROM test) to './test.csv' with csv
But in ...
- 85
4
votes
2
answers
14k
views
Using Postgres's COPY FROM file query in Python without writing to a temporary file
I need to load data from some source data sources to a Postgres database.
To do this task, I first write the data to a temporary CSV file and then load data from the CSV file to Postgres database ...
- 4,109
4
votes
2
answers
253
views
COPY csv file with additionnal datas
I have the following table :
persons(id,id_tech,name,nationality,id_list)
And a CSV file containing the datas for the column id_tech, name, and nationality.
Importing the data like so is working :
\...
- 14.8k
4
votes
2
answers
30k
views
Postgres SQL - error: must be superuser to copy to or from a file
I have copied function(from one of the webportal and modified accordingly) to copy data from csv file to table.
create or replace function public.load_csv_file
(
target_table text,
csv_path text,
...
- 415
4
votes
1
answer
3k
views
importing data with commas in numeric fields into redshift
I am importing data into redshift using the SQL COPY statement. The data has comma thousands separators in the numeric fields which the COPY statement rejects.
The COPY statement has a number of ...
- 7,883
4
votes
2
answers
2k
views
Postgres \copy throws a syntax error at STDOUT when exporting as CSV (v8.0.2)
I'm trying to locally download the results of a query as csv on a postgres instance that I have read-only access to (v8.0.2). I've truly read 10 different ways of going about this and have tried ...
- 398
4
votes
1
answer
2k
views
How to convert hex characters when using Postgres COPY FROM?
I am importing data from a file to PostgreSQL database table using COPY FROM.
Some of the strings in my file contain hex characters (mostly \x0d and \x0a) and I'd like them to be converted into ...
- 181
3
votes
2
answers
6k
views
Problems while importing a txt file into postgres using php
I am trying to import a txt/csv file into my postgres database from php using "\copy" command. I cannot use COPY instead of \copy as I need it to execute as a psql client. My code is:
$query = '\\'.'...
- 447
3
votes
2
answers
5k
views
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)?
...
- 2,240
3
votes
1
answer
2k
views
Is there a way to use Postgresql copy (loading CSV in table) from Hibernate?
In my current application I am using Hibernate + PostgreSQL. For a particular case I need to use the COPY functionality available in postgres to load data from CSV file. Is there any way to use COPY ...
- 1,648
3
votes
2
answers
894
views
Alternative to "write to file" for transfering CSV data to PostgreSQL using COPY for better performance?
I have a dataset in a CSV file consisting of 2500 lines. The file is structured that (simplified) way:
id_run; run_name; receptor1; receptor2; receptor3_value; [...]; receptor50_value
Each receptor ...
- 911
3
votes
2
answers
6k
views
PostgreSQL COPY command with csv file
I have a csv file with two columns: productId, productName.
My table has the following columns: productId, productName, productprice.
I want to be able to copy this file into a Postgres table using ...
- 135
3
votes
1
answer
1k
views
Split a big postgres table into multiple csv
I am using following psql query to connect to a remote host and split a big table into multiple csv files.
psql -h xx -p xx -U xx -d xx -c "\COPY (select * from table) TO program 'split --lines 1000' ...
- 1,401
3
votes
1
answer
3k
views
Copy output of WITH subquery to CSV in postgres
I am trying to save the output of below 'WITH' sub-query to a csv file.
WITH mer9 AS (
SELECT *,
substring(seq_window_mut_9mers, split9.start, 9)
...
- 1,401
3
votes
3
answers
6k
views
Cloud SQL - PostgreSQL - how to import a .csv file?
I have a Cloud SQL instance running PostgreSQL, and a .csv file on my local computer.
I created a user and a database. I logged in as that user, with that database and created a table that matches ...
- 1,226
3
votes
2
answers
4k
views
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 ...
3
votes
1
answer
918
views
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 ...
- 24.3k
3
votes
1
answer
5k
views
How PostgreSQL Copy command work on Windows?
I need to copy table data into text file on Windows in csv format. How to do that?
What I have tried:
COPY test TO '"E:\\test.csv"' DELIMITER ',' CSV HEADER;
I am receiving an error while executing ...
- 105
3
votes
2
answers
2k
views
How to force postgres to use columns order from csv when dumping/restore data?
I dump my table data:
COPY( SELECT * FROM tariff_details ) TO STDOUT WITH( FORMAT CSV, HEADER )
The data:
id,tariff_id,name,price,option,periodic,value,sorder
17,1,Setup fee,5.000000000000000000,,f,,...
- 23.6k
3
votes
1
answer
4k
views
How to ignore duplicate keys while using copy in postgresql
I am using COPY table_name FROM STDIN to import data. It is
very efficient, but if there's any violation of duplicate keys, the whole
procedure will be stopped. Is there anyway to around this?
why ...
- 31
3
votes
1
answer
3k
views
Python PostgreSQL using copy_from to COPY list of objects to table
I'm using Python 2.7 and psycopg2 to connect to my DB server ( PostgreSQL 9.3 ) and I a list of objects of ( Product Class ) holds the items which i want to insert
products_list = []
products_list....
- 1,040
2
votes
2
answers
2k
views
PostgreSQL benchmarking over a RAMdisk?
I have been considering the idea of moving to a RAMdisk for a while. I know its risks, but just wanted to do a little benchmark. I just had two questions: (a) when reading the query plan, will it ...
- 1,541
2
votes
1
answer
242
views
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 "
...
- 158