knowledge Base in SqlBulkCopy Tutorial

How do I retrieve the main keys of rows entered using a bulk copy from in PostgreSQL?

The goal is this: I have a set of values to go into table ...A..., and a set of values to go into table ...B.... The values going into ...B... reference values in ...A... (via a foreign key), so after inserting the ...A... values I need to know how to ref...

asked by Claudiu

How to adjust postgresql+python for copy from?

What function can I use to adapt values for suitable use in a ...COPY FROM... statement? I tried this ...adapt... function:...from psycopg2.extensions import adapt ...However, it gives the wrong thing for datetimes (appends ...::timestsamp... to the strin...

database postgresql python sql sqlbulkcopy

asked by Claudiu

php postgresql pdo stdin copy

COPY table_name ( field1, field2, field3) FROM STDIN CSV; 1,2,"q w" 3,4,"a s" 5,6,d \. ...How to execute this query by PDO ?...Update:...Problem is PDO driver executes this query as statement.... For example, if you paste it into pgAdmin, it throws an err...

pdo php postgresql sqlbulkcopy

asked by cetver

How to debug Postgres copy command failure

I have around 75k records which I am loading to a Postgres table using copy command which is failing. I get an exception ...ERROR: invalid byte sequence for encoding "UTF8": 0xbd...Now i need to find which line is having this entry. Is there any way to do...

postgresql postgresql-8.4 sqlbulkcopy

asked by rajesh

How to copy a .out file to postgresql?

I have ...this file... (warning: potentially unsafe file download site) that I need to insert into the database using Postgres and Java. Please download it and let me know how I can extract data from this file. I tried the copy command. For one, it did no...

copy java out postgresql sqlbulkcopy

asked by CodingInCircles

cursor.copy_from failed in postgres

I am facing problems in coping data from one db to another db here is python code....import psycopg2 db = psycopg2.connect("dbname='db' user='xx' password='xx' host='127.0.0.2'") st = db.cursor(); db_l = psycopg2.connect("dbname='xx' user='xx' password=...

cursor postgresql psycopg2 python sqlbulkcopy

asked by sharafjaffri

How use GNU parallel and GNU SQL with \copy in PostgreSQL

I want to do a bulk load to a PostgreSQL database, there are several files and are pretty big. I just read in ...Using GNU Parallel With split... about the ...GNU Parallel... and ...GNU SQL..., and It looks fantastic, Could some one help me with an exampl...

parallel-processing postgresql sqlbulkcopy

asked by nanounanue

postgresql how to have COPY interpret formatted numeric fields automatically?

I have an input CSV file containing something like:...SD-32MM-1001,"100.00",4/11/2012 SD-32MM-1001,"1,000.00",4/12/2012 ...I was trying to COPY import that into a postgresql table(varchar,float8,date) and ran into an error:...# copy foo from '/tmp/foo.csv...

postgresql sqlbulkcopy

asked by Dinesh

"ERROR: extra data after last expected column" when using PostgreSQL COPY

Please bear with me as this is my first post. ...I'm trying to run the ...COPY... command in PostgreSQL-9.2 to add a tab delimited table from a .txt file to a PostgreSQL database such as:...COPY raw_data FROM '/home/Projects/TestData/raw_data.txt' WITH (D...

database-design dynamic-sql postgresql sqlbulkcopy

asked by dnak

New to SQL - Trouble getting quarterly date output

I'm really new to sql and databases in general. Trying to teach myself the language but I'm stuck and would like to know what's wrong. So here's what I have:...1976-01-01,6.1 1976-04-01,5.7 1976-07-01,5.7 1976-10-01,5.8 .........And I'm inserting it into...

date postgresql sql sqlbulkcopy sql-insert

asked by Jefftopia

Get the count of rows from a COPY command

When copying data from a file, you get the count of rows in psql with the "command tag":...db=# COPY t FROM '/var/lib/postgres/test.sql'; COPY ...10...I need the number of rows and would like to avoid a redundant ...count()... on the table....Is there a w...

count plpgsql postgresql sqlbulkcopy

asked by Erwin Brandstetter

Bulk insert between tables in same database

Fastest way to do insert in a table A with same structure of a table B:...INSERT INTO A SELECT * FROM B ...I can't rename table because in table A there are indexes that when query is executed it's impossible to know without read catalog and create a dyna...

bulkinsert postgresql sql sqlbulkcopy

asked by Luigi Saggese

How to do PostgreSQL Bulk INSERT without Primary Key Violation

I'm trying to achieve database abstraction in my project, but now I got stuck with doing a bulk INSERT in PostgreSQL. My project is in C# and I'm using PostgreSQL 9.3 with npgsql.dll 2.0.14....For Microsoft SQL Server I'm doing the bulk INSERT simply by c...

npgsql postgresql sqlbulkcopy

asked by Loonis

Impact of existing indexes on performance of COPY FROM

After reading the ...PostgreSQL reference doc on ...COPY..., I did not find how ...COPY... deals with indexes. What exactly happens when COPYing data from a file into a table when the table already has indexes. I wonder if these indexes will be destroyed ...

indexing postgresql sqlbulkcopy

Select specific Fields to output to CSV - Postgresql

I have a database from which i have to export to a csv/text file the ...longtitude... and ...lantitude... in this form:...25.767368,-80.18930 34.088808,-118.40612 40.727093,-73.97864 # Lat,Long ...The attributes in my database are in a correct format and...

csv postgresql sqlbulkcopy

asked by Mini John

COPY to remote databse from a local file

There are similar questions but they didn't address my problem: I have a stored procedure defined in my remote database. As part of it operation, the SP has to bulk insert from a local file (through my application running on my local machine): the procedu...

postgresql soci sqlbulkcopy

asked by rahman

Bulk C# datatable to postgresql table

I have got a datatable with thousands of records. I have got a postgres table with the same fields of the datatable. I want everyday to truncate this table and fill again with the data of the datatable. I have seen sql bulk copy, but it is not avalaible o...

bulkinsert c# datatable postgresql sqlbulkcopy

asked by Za7pi

Python Postgres Best way to insert data from table on one DB to another table on another DB

I have the following python code that copies content of a table on postgres DB1 and INSERTS into a similar table on postgres DB2....I want to speed it up by using BULK INSERTS. How do I achieve this...import psycopg2 import sys import os all_data = [] ...

bulkinsert plpgsql postgresql python-3.x sqlbulkcopy

asked by George Appiah Sarfo

Postgres: Copying large subsets of data(10k+rows) from one schema to another for immediate use

I'm needing to push rows from one Postgres schema to another on demand. One schema stores the content and the other is a copy of a subset of data that the end user will add, modify, delete for their custom use. There are 10+million records and growing in ...