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]

Re: COPY not handling BLOBs

David Rysdam <drysdam@ll.mit .edu> writes:[color=blue]
> Doug McNaught wrote:[color=green]
>> 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[/color][/color]
[color=blue]
> bytea will only go up to "several thousand bytes" according to the
> docs.[/color]

[ 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 YourEmailAddres sHere" to majordomo@postg resql.org)

[David Rysdam]

Re: COPY not handling BLOBs

Tom Lane wrote:
[color=blue]
>David Rysdam <drysdam@ll.mit .edu> writes:
>
>[color=green]
>>Doug McNaught wrote:
>>
>>[color=darkred]
>>>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
>>>
>>>[/color][/color]
>
>
>[color=green]
>>bytea will only go up to "several thousand bytes" according to the
>>docs.
>>
>>[/color]
>
>[ raises eyebrow... ] According to *what* docs?
>
> regards, tom lane
>
>
>
>[/color]
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]

Re: COPY not handling BLOBs

On 8/4/2004 1:58 PM, David Rysdam wrote:
[color=blue]
> Doug McNaught wrote:
>[color=green]
>>David Rysdam <drysdam@ll.mit .edu> writes:
>>
>>
>>[color=darkred]
>>>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.
>>>
>>>[/color]
>>
>>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
>>
>>[/color]
> 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_i n_row. My binary data could be a couple times that or
> even much bigger for other apps.[/color]

I suggest upgrading to PostgreSQL 7.1 or newer.


Jan
[color=blue]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster[/color]


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

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

[Doug McNaught]

Re: COPY not handling BLOBs

Jan Wieck <JanWieck@Yahoo .com> writes:
[color=blue]
> On 8/4/2004 1:58 PM, David Rysdam wrote:
>[color=green]
>> 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_i n_row. My binary data could be a couple times that
>> or even much bigger for other apps.[/color]
>
> I suggest upgrading to PostgreSQL 7.1 or newer.[/color]

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