Thread: troubles with postgresql
Hi,
There are 2 things that disturb me in postgresql:
- apparently, there is no unsigned number type. why ??? This is very useful,
especially for serials.
- when we get a record containing a fixed sized string rows, we always get a
string that is filled with spaces at the end to match max size. Again, why ?
There is no need for these spaces, and I guess those spaces are sent through
the network, which is not optimal...
Can a developper of PostgreSQL answer my questions ?
Thanks.
Erwan
On Sun, Feb 23, 2003 at 09:22:44AM +0100, erwan ancel wrote: > Hi, > There are 2 things that disturb me in postgresql: > - apparently, there is no unsigned number type. why ??? This is very useful, > especially for serials. Since serial go upto 64 bits, I don't think signed/unsigned will make a difference. > - when we get a record containing a fixed sized string rows, we always get a > string that is filled with spaces at the end to match max size. Again, why ? > There is no need for these spaces, and I guess those spaces are sent through > the network, which is not optimal... You probably defined them char(n). varchar(n) has no padding. text has no limit in size. Choose the appropriate type for your purposes. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Support bacteria! They're the only culture some people have.
Attachment
Hi, On Sun, Feb 23, 2003 at 09:22:44AM +0100, erwan ancel wrote: > - apparently, there is no unsigned number type. why ??? This is very useful, > especially for serials. AFIAK there no unsigned int, but You can define the MINVALUE and START(-value) for the sequence, so this is IMHO not a big problem. > - when we get a record containing a fixed sized string rows, we always get a > string that is filled with spaces at the end to match max size. Again, why ? I bet You're using char(n) instead of varchar(n)?! char(n) is space padded up to the length of n chars. Hope this helps. -tb -- Thomas Beutin tb@laokoon.IN-Berlin.DE Beam me up, Scotty. There is no intelligent live down in Redmond.
On Sun, 2003-02-23 at 11:03, Thomas Beutin wrote:
> Hi,
>
> On Sun, Feb 23, 2003 at 09:22:44AM +0100, erwan ancel wrote:
> > - apparently, there is no unsigned number type. why ??? This is very useful,
> > especially for serials.
> AFIAK there no unsigned int, but You can define the MINVALUE and START(-value)
> for the sequence, so this is IMHO not a big problem.
Furthermore, you can set a check constraint on the column:
CONSTRAINT "thiscol not negative"
CHECK (thiscol >= 0)
> > - when we get a record containing a fixed sized string rows, we always get a
> > string that is filled with spaces at the end to match max size. Again, why ?
> I bet You're using char(n) instead of varchar(n)?! char(n) is space padded up
> to the length of n chars.
And that behaviour of CHAR() is according to the SQL standard.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"All scripture is given by inspiration of God, and is
profitable for doctrine, for reproof, for correction,
for instruction in righteousness; That the man of God
may be perfect, thoroughly furnished unto all good
works." II Timothy 3:16,17
Thanks everyone.
For the char(n): I did not know this was defined by the SQL standard. So ok,
and I will use the varchar type.
For the unsigned values. Well, I would like to make portable applications
between PostgreSQL and MySQL. This means that if unsigned values do not have
the same range for the 2 versions, this can be a problem. And again, I do
not understand why PostgreSQL doesn't deal with unsigned. It should be so
simple... Is it planned for one of the next releases ?
Erwan
----- Original Message -----
From: "Oliver Elphick" <olly@lfix.co.uk>
To: "erwan ancel" <erwan.ancel@free.fr>
Cc: <pgsql-general@postgresql.org>; "Thomas Beutin"
<tyrone@laokoon.IN-Berlin.DE>
Sent: Sunday, February 23, 2003 2:26 PM
Subject: Re: [GENERAL] troubles with postgresql
> On Sun, 2003-02-23 at 11:03, Thomas Beutin wrote:
> > Hi,
> >
> > On Sun, Feb 23, 2003 at 09:22:44AM +0100, erwan ancel wrote:
> > > - apparently, there is no unsigned number type. why ??? This is very
useful,
> > > especially for serials.
> > AFIAK there no unsigned int, but You can define the MINVALUE and
START(-value)
> > for the sequence, so this is IMHO not a big problem.
>
> Furthermore, you can set a check constraint on the column:
>
> CONSTRAINT "thiscol not negative"
> CHECK (thiscol >= 0)
>
> > > - when we get a record containing a fixed sized string rows, we always
get a
> > > string that is filled with spaces at the end to match max size. Again,
why ?
> > I bet You're using char(n) instead of varchar(n)?! char(n) is space
padded up
> > to the length of n chars.
>
> And that behaviour of CHAR() is according to the SQL standard.
>
> --
> Oliver Elphick Oliver.Elphick@lfix.co.uk
> Isle of Wight, UK http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
> ========================================
> "All scripture is given by inspiration of God, and is
> profitable for doctrine, for reproof, for correction,
> for instruction in righteousness; That the man of God
> may be perfect, thoroughly furnished unto all good
> works." II Timothy 3:16,17
>
>
The reason why, probably, is that the signed type is an SQL standard,
and unsigned is not. I've noticed that postgres tries to adhere to the
standard and not do too many extensions, particularly in comparison to
MySQL.
MySQL has some nice extensions, but you have to really change things to
go to another database if you've started on MySQL, (as you are
noticing). That has other drawback. What if you are really successful
with a design and company, and your site gets big and you get money to
make it bigger? You decide to go with DB2 or Oracle then, perhaps? You'd
probably have to make all the same changes to your design to port to
those databases, since MySQL is less standardized than all of them.
Postgres seems to be trying to be the open source, standards compliant
DB.
One thing I don't remember seeing in MySQL that might be an extension
you could use in Postgres is INT8. That's a HUGE number, maybe more than
the number of atoms in existence! (probably not, but it's big). It comes
out to 1.844674407371 * 10^19. (That's over a billion, squared). If
you used that, and forced it unsigned with a check, you'd probably have
LOTS more room than you'd need in that value range.
--
Carpe Dancem ;-)
-----------------------------------------------------------------
Remember your friends while they are alive
-----------------------------------------------------------------
Sincerely, Dennis Gearon
On Sun, Feb 23, 2003 at 15:38:30 +0100, erwan ancel <erwan.ancel@free.fr> wrote: > Thanks everyone. > For the char(n): I did not know this was defined by the SQL standard. So ok, > and I will use the varchar type. For Postgres you are probably better off using text instead of varchar(n) unless there is something in your model that limits the length of the string. If mysql doesn't have text, then you may still want to use varchar anyway for portability. > For the unsigned values. Well, I would like to make portable applications > between PostgreSQL and MySQL. This means that if unsigned values do not have > the same range for the 2 versions, this can be a problem. And again, I do > not understand why PostgreSQL doesn't deal with unsigned. It should be so > simple... Is it planned for one of the next releases ? The simplest option is probably to use a large enough integer type that you can hold the biggest integer you need in either database. In 7.4 check constraints will work with domains. Then you will be able to define something that looks like an unsigned integer type and the postgres specific stuff will be in just one place. However the maximum value probably still won't match mysql because the underlying type will still be signed and you will lose one bit for the sign.
Ok, thanks everyone, I'll deal with it.
Erwan
----- Original Message -----
From: "Dennis Gearon" <gearond@cvc.net>
To: "erwan ancel" <erwan.ancel@free.fr>
Cc: <pgsql-general@postgresql.org>
Sent: Sunday, February 23, 2003 3:59 PM
Subject: Re: [GENERAL] troubles with postgresql
> The reason why, probably, is that the signed type is an SQL standard,
> and unsigned is not. I've noticed that postgres tries to adhere to the
> standard and not do too many extensions, particularly in comparison to
> MySQL.
>
> MySQL has some nice extensions, but you have to really change things to
> go to another database if you've started on MySQL, (as you are
> noticing). That has other drawback. What if you are really successful
> with a design and company, and your site gets big and you get money to
> make it bigger? You decide to go with DB2 or Oracle then, perhaps? You'd
> probably have to make all the same changes to your design to port to
> those databases, since MySQL is less standardized than all of them.
>
> Postgres seems to be trying to be the open source, standards compliant
> DB.
>
> One thing I don't remember seeing in MySQL that might be an extension
> you could use in Postgres is INT8. That's a HUGE number, maybe more than
> the number of atoms in existence! (probably not, but it's big). It comes
> out to 1.844674407371 * 10^19. (That's over a billion, squared). If
> you used that, and forced it unsigned with a check, you'd probably have
> LOTS more room than you'd need in that value range.
> --
>
> Carpe Dancem ;-)
> -----------------------------------------------------------------
> Remember your friends while they are alive
> -----------------------------------------------------------------
> Sincerely, Dennis Gearon
>