RE: [Solved] SQL Server to PostgreSQL - Mailing list pgsql-general

As we are talking about 7.1 and huge field size...

MS-SQL has a function that allows you to retreive part of a field a kind of
mid$(field, start, length). This would be a good addition indeed.

last problem. PG does not allow to store binary data. I'm not talking about
the current implementation of BLOB, but what will happen in 7.1...

If I want to store an image in a field. I cannot do that in 7.1 because the
data sent by 7.1 and received in libpq must be formated in ASCII. I haven't
play around to see if I could create a user type called varbinary(n), which
will output via varbinary_out just the content of a buffer... May be varchar
does it already (even if there is a \0?).

I know I should submit this problem to the hacker list, but I don't want to
subscribe to hacker just to submit one message...

BTW is there an alpha/beta release of PG 7.1 ?

Franck Martin
Database Development Officer
SOPAC South Pacific Applied Geoscience Commission
Fiji
E-mail: franck@sopac.org <mailto:franck@sopac.org>
Web site: http://www.sopac.org/ <http://www.sopac.org/>

This e-mail is intended for its recipients only. Do not forward this
e-mail without approval. The views expressed in this e-mail may not be
neccessarily the views of SOPAC.



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, August 23, 2000 3:11 PM
To: Jeffrey A. Rhines
Cc: PostgreSQL::General List
Subject: Re: [GENERAL] [Solved] SQL Server to PostgreSQL


"Jeffrey A. Rhines" <jrhines@email.com> writes:
>> Uh ... what's wrong with varchar(n) ?
>
> I've wondered that myself, actually.  What are the benefits and
> drawbacks to going with one over the other, besides the obvious 255-char
> field length limit for varchar?

AFAIK there has *never* been a 255-char limit on char or varchar in
pgsql ... you must be thinking of Some Other DBMS.

The limit for these datatypes in 7.0 and before is BLCKSZ less some
overhead --- ~8000 bytes in a default setup.  Beginning in 7.1 it's
an essentially arbitrary number.  I set it at 10Mb in current sources,
but there's no strong reason for that number over any other.  In theory
it could be up to 1Gb, but as Jan Wieck points out in a nearby thread,
you probably wouldn't like the performance of shoving gigabyte-sized
text values around.  We need to think about offering API functions that
will allow reading and writing huge field values in bite-sized chunks.

There's no essential performance difference between char(n), varchar(n),
and text in Postgres, given the same-sized data value.  char(n)
truncates or blank-pads to exactly n characters; varchar(n) truncates
if more than n characters; text never truncates nor pads.  Beyond that
they are completely identical in storage requirements.  Pick one based
on the semantics you want for your application.

            regards, tom lane