SqlBulkCopy Tutorial - Knowledge Base (KB)
20 results for: in tag: postgresql
postgresql: how to get primary keys of rows inserted with a bulk copy_from?
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
postgresql+python: how to adapt 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...
asked by Claudiu
php postgresql pdo copy from stdin
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...
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...
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...
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=...
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...
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...
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...
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...
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...
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...
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...
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 ...
asked by Fopa Léon Constantin
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...
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...
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...
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 = [] ...
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 ...
asked by user6550024
Bulk data insertion and updating from one db server to another db server
I have some set of tables which has 20 million records in a postgres server. As of now i m migrating some table data from one server to another server using insert and update queries with dependent tables in functions. It takes around 2 hours even after o...
asked by Vignesh Vk
Page 1 of 1