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
0
votes
2 answers

How to upload/import NULL values in column from file (csv/text) with data types integer/double precision/bigint/numeric into a PostgreSQL table?

I have some text file. Which I can't import into a PostgreSQL table because some of my columns have NULL values. My table's columns are of type integer, bigint, double precision. If all column's data types are character varying, it imports well.…
Tanzir
  • 1
  • 3
0
votes
0 answers

how to insert postgresql table using C# dataTable & postgresql COPY, without EF core

I want to insert into postgresql(12.9) table using C# datatable object in winform. ◀ DataTable object I want to use a function like this. Is there a way to resolve a C# DataTable object to STDIN ? The reason I don't want to do EF core is because…
qwerjin
  • 11
  • 1
0
votes
0 answers

Import csv file into table in pgAdmin using COPY command

I'm trying to import csv file's table into pgAdmin using COPY command and getting the error: ERROR: could not open file "R:\myfile.csv" for reading: No such file or directory HINT: COPY FROM instructs the PostgreSQL server process to read a file.…
user2381509
  • 5
  • 2
0
votes
2 answers

Skipping first column data in CSV while using copy command in PostgreSQL

I have a pipe delimited data file with no headers. I need to import data into a PostgreSQL table starting with the data from second column in the file i.e skip the data before the first '|' for each line. How do I achieve this using the COPY…
user9057272
  • 367
  • 2
  • 5
  • 17
0
votes
2 answers

Postgresql Copy Function from the Server Computer to the Client Computer

I would like to import a table from the server computer into a Client computer using the copy command. I know this is a recurring issue for users, but I have not been able to get an answer to this particular one and it's also a different scenario,…
olasammy
  • 6,676
  • 4
  • 26
  • 32
0
votes
1 answer

Insert newline in text file using COPY

I want to insert a line break in a text file using copy command I'm asked to port sqlplus scripts that generate reports from the data base in custom csv and text formats. I'm looking for ways to implement functionalities of sqlplus like: set…
Majd
  • 328
  • 4
  • 12
0
votes
1 answer

Is it possible to use variables in a postgresql copy from program URL?

im importing data in postgresql with the following command: COPY test FROM PROGRAM 'curl https://example.com/events/123&start_date=20210331T191500Z' ; i have to manually change the "&startdate=***" to only get the data from the last hour everytime…
Lautaro Aguilera
  • 53
  • 5
0
votes
1 answer

Most efficient method to import bulk JSON data from different sources in postgresql?

I need to import data from thousands of URLs, here is an example of the…
Lautaro Aguilera
  • 53
  • 5
0
votes
0 answers

How to resolve an error in Importing CSV file from PostgreSQL

I want to import a CSV file to PostgreSQL. Shown below is the syntax used to import a CSV file to a created table i.e. salary_supervisor in PostgreSQL, COPY salary_supervisor FROM 'X:\XX\XXX\XXX\XXX\XXX\XXX\supervisor_salaries.csv' /* File path…
WF30
  • 213
  • 1
  • 3
  • 16
0
votes
1 answer

ERROR: missing data for column when using COPY FROM PROGRAM in psql

i'm trying to import the data from a cURL with the next command in psql: COPY testtable FROM PROGRAM 'curl https://.....' This is the data in the…
Lautaro Aguilera
  • 53
  • 5
0
votes
2 answers

Invalid Input Syntax for Type Date

I'm triying to load data to PosgreSQL Data Base remotly by command Line in CMD, copying data from CSV file to a specific Table. It was working well but, somehow when I try to load data now, CMD throws me this error Curiously CSV file has the…
Xkid
  • 338
  • 1
  • 4
  • 17
0
votes
0 answers

Bulk Copying Error In Postgres database, while using Copy syntax to import CSV files into tables

CREATE TABLE parts ( id integer, description character varying, code character varying, manufacturer_id integer ); CREATE TABLE locations ( id integer, part_id integer, location varchar(3), qty integer ); create…
user14225578
0
votes
1 answer

How to load an extracted ORACLE CLOB into only 1 TEXT column in Postgres?

I'm currently looking at migrating CLOB data from ORACLE into Postgres from an external file. I have created my table in Postgres and the data type I'm using is TEXT which will replicate using a CLOB in ORACLE and now I just need to get my data…
sufs2000
  • 23
  • 4
0
votes
2 answers

Copy multiple CSV files into postgres with incrementing id

I have multiple CSV files and I am getting new ones every day. The Data from each file should be added to the postgres DB regularly. The Problem is that every CSV file has a column with id. So when adding the second file COPY public."ERROR" FROM…
Kuempsmichi
  • 69
  • 2
  • 10
0
votes
1 answer

How to import fixed width data in to POSTGRESQL table using copy command

I'm trying to import data from a fixed width text file in to a Postgres table using COPY command. The said file contains approximately million records with a width of 1450 characters most of which are empty spaces with useful data scattered at…
M D S R Kumar
  • 1
  • 1
1 2 3
…
9 10