Thread: 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.
David Rysdam <drysdam@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 -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
Doug McNaught wrote: >David Rysdam <drysdam@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.
David Rysdam <drysdam@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
Tom Lane wrote: >David Rysdam <drysdam@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.
On 8/4/2004 1:58 PM, David Rysdam wrote: > Doug McNaught wrote: > >>David Rysdam <drysdam@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. # #================================================== JanWieck@Yahoo.com #
Jan Wieck <JanWieck@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