469,642 Members | 1,124 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,642 developers. It's quick & easy.

COPY not handling BLOBs

I have a bunch of data in Sybase and some of it is in image fields. We
use bcp on this data transparently all the time without major issues in
character mode. Is there a fundamental technical reason that BLOBs
can't be COPY'd in postgresql or is it just that nobody has ever wanted
to before?

(If I was starting from scratch I'd probably be happy with
pg_dump/pg_restore, but I'd like to migrate in easy stages over from
Sybase, so I'd like the bcp files to be interchangable.)

Right now I'm having to write a program to create all the large objects
up front and record the OIDs in a file which I will then COPY in with
the rest of my data.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
4 1359
David Rysdam <dr*****@ll.mit.edu> writes:
Doug McNaught wrote:
You might consider using "bytea" instead of large objects, unless you
need the lo_read()/lo_seek() API in your client app... I'm not
bytea will only go up to "several thousand bytes" according to the
docs.


[ raises eyebrow... ] According to *what* docs?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2
Tom Lane wrote:
David Rysdam <dr*****@ll.mit.edu> writes:

Doug McNaught wrote:

You might consider using "bytea" instead of large objects, unless you
need the lo_read()/lo_seek() API in your client app... I'm not

bytea will only go up to "several thousand bytes" according to the
docs.


[ raises eyebrow... ] According to *what* docs?

regards, tom lane

I was using the general search feature and found something like that. I
only just now noticed that you can choose a doc version and search
within only that.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #3
On 8/4/2004 1:58 PM, David Rysdam wrote:
Doug McNaught wrote:
David Rysdam <dr*****@ll.mit.edu> writes:
Right now I'm having to write a program to create all the large
objects up front and record the OIDs in a file which I will then COPY
in with the rest of my data.
You might consider using "bytea" instead of large objects, unless you
need the lo_read()/lo_seek() API in your client app... I'm not
super-familiar with how bytea and COPY work together, though.

-Doug

bytea will only go up to "several thousand bytes" according to the
docs. I assume it's not very precise because the maximum is 8196 -
$other_fields_in_row. My binary data could be a couple times that or
even much bigger for other apps.


I suggest upgrading to PostgreSQL 7.1 or newer.
Jan

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
#================================================= =====================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================= = Ja******@Yahoo.com #

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #4
Jan Wieck <Ja******@Yahoo.com> writes:
On 8/4/2004 1:58 PM, David Rysdam wrote:
bytea will only go up to "several thousand bytes" according to the
docs. I assume it's not very precise because the maximum is 8196 -
$other_fields_in_row. My binary data could be a couple times that
or even much bigger for other apps.


I suggest upgrading to PostgreSQL 7.1 or newer.


Or reading the right set of docs. :)

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
--T. J. Jackson, 1863

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Kirby Urner | last post: by
reply views Thread by Ole Hansen | last post: by
7 posts views Thread by Howard Lowndes | last post: by
7 posts views Thread by Nilabhra Banerjee | last post: by
2 posts views Thread by Jerry LeVan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.