pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedBulk Load and Extract from PostgreSQL
8+ messages / 3 participants
[nested] [flat]
* Bulk Load and Extract from PostgreSQL
@ 2006-10-16 07:07 Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
2006-10-16 10:34 ` Re: Bulk Load and Extract from PostgreSQL Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Sandeep Khandelwal @ 2006-10-16 07:07 UTC (permalink / raw)
To: pgsql-interfaces
Hi All.
I want to extract and Load data from PostgreSQL using Libpq C API. Please let me know which approach will be good to load large number of rows into PostgreSQL(Insert or COPY FROM) and, which approach will be good to extract large number of rows from PostgreSQL (COPY TO or SELECT).
I want to handle all the data types supported in the PostgreSQL.
Thanks,
Sandeep
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Bulk Load and Extract from PostgreSQL
2006-10-16 07:07 Bulk Load and Extract from PostgreSQL Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
@ 2006-10-16 10:34 ` Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 0 replies; 8+ messages in thread
From: Sean Davis @ 2006-10-16 10:34 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
On Monday 16 October 2006 03:07, Sandeep Khandelwal wrote:
> Hi All.
>
> I want to extract and Load data from PostgreSQL using Libpq C API. Please
> let me know which approach will be good to load large number of rows into
> PostgreSQL(Insert or COPY FROM) and, which approach will be good to extract
> large number of rows from PostgreSQL (COPY TO or SELECT). I want to handle
> all the data types supported in the PostgreSQL.
copy is the faster way to go for a single table.
Sean
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Bulk Load and Extract from PostgreSQL
@ 2006-10-17 05:36 Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
2006-10-17 10:43 ` Re: Bulk Load and Extract from PostgreSQL Davis, Sean (NIH/NCI) [E] <sdavis2@mail.nih.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Sandeep Khandelwal @ 2006-10-17 05:36 UTC (permalink / raw)
To: sdavis2@mail.nih.gov; pgsql-interfaces
Hi Sean.
Thanks for the reply.
I have one more question. While extracting data using COPT TO command (in TEXT mode) we have an API, PQunescapeBytea, to convert string representation of binary data(bytea) into binary. Similary we need to convert binary data into it's string representation while loading data into PostgreSQL ,using COPY FROM command in TEXT mode, with PQputCopydata. But there is no API to convert binary data into it's string representation. Could you tell me API or I am misinterpreting something.
Thanks in advance,
Sandeep
----- Original Message ----
From: Sean Davis <sdavis2@mail.nih.gov>
To: pgsql-interfaces@postgresql.org
Cc: Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
Sent: Monday, October 16, 2006 4:04:45 PM
Subject: Re: [INTERFACES] Bulk Load and Extract from PostgreSQL
On Monday 16 October 2006 03:07, Sandeep Khandelwal wrote:
> Hi All.
>
> I want to extract and Load data from PostgreSQL using Libpq C API. Please
> let me know which approach will be good to load large number of rows into
> PostgreSQL(Insert or COPY FROM) and, which approach will be good to extract
> large number of rows from PostgreSQL (COPY TO or SELECT). I want to handle
> all the data types supported in the PostgreSQL.
copy is the faster way to go for a single table.
Sean
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Bulk Load and Extract from PostgreSQL
2006-10-17 05:36 Re: Bulk Load and Extract from PostgreSQL Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
@ 2006-10-17 10:43 ` Davis, Sean (NIH/NCI) [E] <sdavis2@mail.nih.gov>
0 siblings, 0 replies; 8+ messages in thread
From: Davis, Sean \(NIH/NCI\) [E] @ 2006-10-17 10:43 UTC (permalink / raw)
To: Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>; pgsql-interfaces
Sandeep,
Try looking here:
http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html
Under "Escaping Binary Strings for Inclusion in SQL Commands"? Will that satisfy your needs, or did I miss what you are trying to do?
Sean
-----Original Message-----
From: Sandeep Khandelwal [mailto:sandeep_khandelwal27@yahoo.com]
Sent: Tue 10/17/2006 1:36 AM
To: Davis, Sean (NIH/NCI) [E]; pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] Bulk Load and Extract from PostgreSQL
Hi Sean.
Thanks for the reply.
I have one more question. While extracting data using COPT TO command (in TEXT mode) we have an API, PQunescapeBytea, to convert string representation of binary data(bytea) into binary. Similary we need to convert binary data into it's string representation while loading data into PostgreSQL ,using COPY FROM command in TEXT mode, with PQputCopydata. But there is no API to convert binary data into it's string representation. Could you tell me API or I am misinterpreting something.
Thanks in advance,
Sandeep
----- Original Message ----
From: Sean Davis <sdavis2@mail.nih.gov>
To: pgsql-interfaces@postgresql.org
Cc: Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
Sent: Monday, October 16, 2006 4:04:45 PM
Subject: Re: [INTERFACES] Bulk Load and Extract from PostgreSQL
On Monday 16 October 2006 03:07, Sandeep Khandelwal wrote:
> Hi All.
>
> I want to extract and Load data from PostgreSQL using Libpq C API. Please
> let me know which approach will be good to load large number of rows into
> PostgreSQL(Insert or COPY FROM) and, which approach will be good to extract
> large number of rows from PostgreSQL (COPY TO or SELECT). I want to handle
> all the data types supported in the PostgreSQL.
copy is the faster way to go for a single table.
Sean
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Bulk Load and Extract from PostgreSQL
@ 2006-10-17 12:18 Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
2006-10-17 12:23 ` Re: Bulk Load and Extract from PostgreSQL Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 1 reply; 8+ messages in thread
From: Sandeep Khandelwal @ 2006-10-17 12:18 UTC (permalink / raw)
To: Davis, Sean \(NIH/NCI\) \[E\] <sdavis2@mail.nih.gov>; pgsql-interfaces
Hi Sean.
The reference that you mentioned is just for Inclusion in the SQL commands directly. But.........
I am trying to load coma seperated data using COPY FROM STDIN command (TEXT mode) and using PQputCopydata API. COPY FROM STDIN command requires user to specify coma seperated text version of all the data.
My question is: If I have binary data(bytea) then I have to convert this data into string (char *) form and pass this data to PostgreSQL using PQputCopydata API. And, I don't have any API in Libpq to convert binary data into string.
Thanks,
Sandeep
----- Original Message ----
From: "Davis, Sean (NIH/NCI) [E]" <sdavis2@mail.nih.gov>
To: Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>; pgsql-interfaces@postgresql.org
Sent: Tuesday, October 17, 2006 4:13:56 PM
Subject: Re: [INTERFACES] Bulk Load and Extract from PostgreSQL
Sandeep,
Try looking here:
http://www.postgresql.org/docs/8.1/interactive/libpq-exec.html
Under "Escaping Binary Strings for Inclusion in SQL Commands"? Will that satisfy your needs, or did I miss what you are trying to do?
Sean
-----Original Message-----
From: Sandeep Khandelwal [mailto:sandeep_khandelwal27@yahoo.com]
Sent: Tue 10/17/2006 1:36 AM
To: Davis, Sean (NIH/NCI) [E]; pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] Bulk Load and Extract from PostgreSQL
Hi Sean.
Thanks for the reply.
I have one more question. While extracting data using COPT TO command (in TEXT mode) we have an API, PQunescapeBytea, to convert string representation of binary data(bytea) into binary. Similary we need to convert binary data into it's string representation while loading data into PostgreSQL ,using COPY FROM command in TEXT mode, with PQputCopydata. But there is no API to convert binary data into it's string representation. Could you tell me API or I am misinterpreting something.
Thanks in advance,
Sandeep
----- Original Message ----
From: Sean Davis <sdavis2@mail.nih.gov>
To: pgsql-interfaces@postgresql.org
Cc: Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
Sent: Monday, October 16, 2006 4:04:45 PM
Subject: Re: [INTERFACES] Bulk Load and Extract from PostgreSQL
On Monday 16 October 2006 03:07, Sandeep Khandelwal wrote:
> Hi All.
>
> I want to extract and Load data from PostgreSQL using Libpq C API. Please
> let me know which approach will be good to load large number of rows into
> PostgreSQL(Insert or COPY FROM) and, which approach will be good to extract
> large number of rows from PostgreSQL (COPY TO or SELECT). I want to handle
> all the data types supported in the PostgreSQL.
copy is the faster way to go for a single table.
Sean
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Bulk Load and Extract from PostgreSQL
2006-10-17 12:18 Re: Bulk Load and Extract from PostgreSQL Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
@ 2006-10-17 12:23 ` Sean Davis <sdavis2@mail.nih.gov>
0 siblings, 0 replies; 8+ messages in thread
From: Sean Davis @ 2006-10-17 12:23 UTC (permalink / raw)
To: Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>; +Cc: pgsql-interfaces
On Tuesday 17 October 2006 08:18, Sandeep Khandelwal wrote:
> Hi Sean.
>
> The reference that you mentioned is just for Inclusion in the SQL commands
> directly. But.........
>
> I am trying to load coma seperated data using COPY FROM STDIN command (TEXT
> mode) and using PQputCopydata API. COPY FROM STDIN command requires user to
> specify coma seperated text version of all the data.
>
> My question is: If I have binary data(bytea) then I have to convert this
> data into string (char *) form and pass this data to PostgreSQL using
> PQputCopydata API. And, I don't have any API in Libpq to convert binary
> data into string.
Sandeep,
I see the issue--sorry about the misunderstanding. I'm not sure what the
"correct" answer is, but you could simply do base64 client-side encoding and
decoding of your data.
Sean
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Bulk Load and Extract from PostgreSQL
@ 2006-10-17 12:28 Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Sandeep Khandelwal @ 2006-10-17 12:28 UTC (permalink / raw)
To: sdavis2@mail.nih.gov; +Cc: pgsql-interfaces
Hi Sean.
I will try you option. Thanks a lot for the information.
Thanks,
Sandeep
----- Original Message ----
From: Sean Davis <sdavis2@mail.nih.gov>
To: Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
Cc: pgsql-interfaces@postgresql.org
Sent: Tuesday, October 17, 2006 5:53:14 PM
Subject: Re: [INTERFACES] Bulk Load and Extract from PostgreSQL
On Tuesday 17 October 2006 08:18, Sandeep Khandelwal wrote:
> Hi Sean.
>
> The reference that you mentioned is just for Inclusion in the SQL commands
> directly. But.........
>
> I am trying to load coma seperated data using COPY FROM STDIN command (TEXT
> mode) and using PQputCopydata API. COPY FROM STDIN command requires user to
> specify coma seperated text version of all the data.
>
> My question is: If I have binary data(bytea) then I have to convert this
> data into string (char *) form and pass this data to PostgreSQL using
> PQputCopydata API. And, I don't have any API in Libpq to convert binary
> data into string.
Sandeep,
I see the issue--sorry about the misunderstanding. I'm not sure what the
"correct" answer is, but you could simply do base64 client-side encoding and
decoding of your data.
Sean
^ permalink raw reply [nested|flat] 8+ messages in thread
* Bulk Load and Extract from PostgreSQL
@ 2006-10-18 05:23 Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Sandeep Khandelwal @ 2006-10-18 05:23 UTC (permalink / raw)
To: pgsql-interfaces
Hi All.
I want to read and write bulk data into PostgreSQL using Libpq C API and right now I am under the investigation phase. Also want to delete and update into PostgreSQL using Libpq API.
PLease let me know
Read -
I am planning to use COPY DATA TO command in TEXT mode for reading, I am using PQgetCopydata function to get data one row at a time.
Problem -
a - Not sure whether PQgetCopydata will give NULL terminated string or \n terminated string.
b - I know that PQgetCopydata API will give data of all the rows , seperated by delimiter, in TEXT format. But don't know how to convert this text data into corresponding data type, especially BYTEA.
c - Don't know what are the special characters and how these are handeled by PQgetCopydata and How I should hendel them.
Load -
I am planning to use COPY DATA FROM command in TEXT mode for writing, I am using PQputCopydata function to send data one row at a time.
Problem -
a - Not sure what are the special characters and how I should miodify them.
b - PQputCopydata API takes data of all the rows, seperated by delimiter, ini string representation. Don't know how to convert all the datatypes into string representation, especially BYTEA.
Waiting for reply.
Thanks,
Sandep
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-10-18 05:23 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-16 07:07 Bulk Load and Extract from PostgreSQL Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
2006-10-16 10:34 ` Sean Davis <sdavis2@mail.nih.gov>
2006-10-17 05:36 Re: Bulk Load and Extract from PostgreSQL Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
2006-10-17 10:43 ` Davis, Sean (NIH/NCI) [E] <sdavis2@mail.nih.gov>
2006-10-17 12:18 Re: Bulk Load and Extract from PostgreSQL Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
2006-10-17 12:23 ` Sean Davis <sdavis2@mail.nih.gov>
2006-10-17 12:28 Re: Bulk Load and Extract from PostgreSQL Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
2006-10-18 05:23 Bulk Load and Extract from PostgreSQL Sandeep Khandelwal <sandeep_khandelwal27@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox