Thread

1. RE: [Solved] SQL Server to PostgreSQL

   Franck Martin <franck@sopac.org> — 2000-08-23T03:36:40Z

   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
   
   

2. Re: [Solved] SQL Server to PostgreSQL

   Martijn van Oosterhout <kleptog@cupid.suninternet.com> — 2000-08-23T11:38:05Z

   Franck Martin wrote:
   > 
   > 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.
   
   does substr() not handle this?
   
   > 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?).
   
   One thing I've thought about is creating an escape char to delimit this
   sort
   of thing. Maybe a control-A, followed by four bytes giving the length
   followed
   by that many byes of data. Escape \0's with ^A0 and a real ^A with ^A^A.
   
   I would love something like this because I always get tripped up by
   fields 
   I'm inserting containing quotes and other characters that need to be
   escaped.
   
   > 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...
   
   Heh, I know what you mean...
   
   -- 
   Martijn van Oosterhout <kleptog@cupid.suninternet.com>
   http://cupid.suninternet.com/~kleptog/