troubles with postgresql

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/

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

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