COPY not handling BLOBs

[David Rysdam]

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

[Tom Lane]

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)

[David Rysdam]

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

[Jan Wieck]

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

[Doug McNaught]

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