Thread: Newbee question "Types"
I'm just starting to build the data structure to
switch over an existing database to postgres and
I keep going back and forth with the column types and
wanted to see if any body had some foresite on this.
1. Using column type "char" vs "varchar" vs "text"
I understand the documented differences, but Why wouldn't
someone just use varchar(x) instead of plain char(x)? It's
definitely more flexable. Or would using char(x) just help one
stay in sql conformity? How about performance differences?
Disk space differences? Would not varchar(45) vs char(45)
consume less space if used with less than 45 characters?
2. Column type money depreciated, so use ie. numeric(6,2)
ok, no problem, but does this mean everytime I do a select
statement I have to "to_char(column, 'FM$9999.99')" to
get a nice formated output? That's ok by me, just wondering if
I'm missing something? It would seem nice to have a currency/money
type that would handle this automagically. Oh, and I guess I missed
even trying to input "$xx.xx" into a numeric field, which I presume I'll
have to convert first from text to integer?
3. Ok, this is not a type question but......
It seems my systems (Rh7.3) postmaster init scripts are not calling the
postgres users ".bashprofile", haven't had time to track
it down yet, but is there any problem with just setting the PG exports
in the system profile as I've been doing for now?
TIA
Ken, 1. Using column type "char" vs "varchar" vs "text" I understand the documented differences, but Why wouldn't someone just use varchar(x) instead of plain char(x)? It's definitely more flexable. Or would using char(x) just help one stay in sql conformity? How about performance differences? Disk space differences? Would not varchar(45) vs char(45) consume less space if used with less than 45 characters? In postgresql, CHAR is supplied for 3 reasons: 1) For compatibility with other database systems that use CHAR. 2) For SQL99 Compliance 3) For compatibility with software that expects space-filled CHAR fields. For a Postgres-native application, there is no reason to ever use CHAR. It does not perform any better, and can lead to significant annoyance. -Josh Berkus
On Thu, 5 Dec 2002, Ken Godee wrote: > 3. Ok, this is not a type question but...... > It seems my systems (Rh7.3) postmaster init scripts are not calling > the postgres users ".bashprofile", haven't had time to track > it down yet, but is there any problem with just setting the PG exports > in the system profile as I've been doing for now? I install postgresql from a tar.gz file, and I start the postmaster with this line on the end of my /etc/rc.d/rc.local script: su - postgres -c 'pg_ctl start | rotatelogs $PGDATA/pglog 86400 2>1&' Note I have rotatelogs from the apache project in the path for the postgres super user so I can have nicely rotated logs with my postgresql.
Josh Berkus wrote: > Ken, > > 1. Using column type "char" vs "varchar" vs "text" > I understand the documented differences, but Why wouldn't > someone just use varchar(x) instead of plain char(x)? It's > definitely more flexable. Or would using char(x) just help one > stay in sql conformity? How about performance differences? > Disk space differences? Would not varchar(45) vs char(45) > consume less space if used with less than 45 characters? > > In postgresql, CHAR is supplied for 3 reasons: > 1) For compatibility with other database systems that use CHAR. > 2) For SQL99 Compliance > 3) For compatibility with software that expects space-filled CHAR > fields. > > For a Postgres-native application, there is no reason to ever use CHAR. > It does not perform any better, and can lead to significant annoyance. I disagree on that one. Char _forces_ a specific length, so it pads to the specified length. For certain applications, this can be a benefit, and often documents that the length is exactly as specified, e.g. CHAR(2) for US state codes. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Joe, Bruce,
> > For a Postgres-native application, there is no reason to ever use CHAR.
> > It does not perform any better, and can lead to significant annoyance.
>
> I disagree on that one. Char _forces_ a specific length, so it pads to
> the specified length. For certain applications, this can be a benefit,
> and often documents that the length is exactly as specified, e.g.
> CHAR(2) for US state codes.
Oops. Replied to the wrong list.
"no reason ever" was probably too strong. A better statement would have been
"almost never a reason".
My viewpoint, which some other developers certainly seem to disagree with:
CHAR(3) does not enforce a 3-character value. It just enforces
that characters which are not entered get filled with spaces. For a real
constraint, you would want something like:
three_code VARCHAR(3) not null,
CONSTRAINT cns_three_code CHECK (three_code ~ '[A-Z]{3}')
While you certainly *could* use CHAR for the above, it makes no difference
whatsoever to Postgres; if the column *must* be exactly 3 characters, then
Postgres will treat CHAR and VARCHAR exactly the same.
Come to think of it, the above declaration could just as easily, and more
efficiently, be done with TEXT. I tend to limit my use of TEXT because it
gives ODBC fits.
Now, I can vaguely imagine hypothetical situations where a developer would
want '___' instead of NULL for a character field. However, I have never run
across one in my application development, *except* for compatibility with
legacy software. After all CHAR originated when databases were unable to
manage VARCHAR.
Bruce gives the example of State Codes, which is a classic example, and
something I did myself in the past. However, I generally found myself
forced to expand the state code field; the abbreviation for some US
Territories is 4 characters, and some countries use 3 for provinces. At
that point, I very much needed to use VARCHAR, since I don't want 'CA__' as
my state.
CHAR can be a reminder to you, the developer, in reading the schema, that you
are expecting a string of a precise length. But I do not see it as an
effective or useful constraint on input in real applications. A constraint
statement, like the above, is far more effective.
Now, if the parser treated CHAR differently, then that would be a good
argument to use it. But Tom Lane has said several times on SQL and HACKERS
that CHAR and VARCHAR are treated the same by the parser.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
On Thu, Dec 05, 2002 at 06:07:44PM -0800, Josh Berkus wrote:
> Bruce gives the example of State Codes, which is a classic example, and
> something I did myself in the past. However, I generally found myself
> forced to expand the state code field; the abbreviation for some US
> Territories is 4 characters, and some countries use 3 for provinces. At
> that point, I very much needed to use VARCHAR, since I don't want 'CA__' as
> my state.
>
> CHAR can be a reminder to you, the developer, in reading the schema, that you
> are expecting a string of a precise length. But I do not see it as an
> effective or useful constraint on input in real applications. A constraint
> statement, like the above, is far more effective.
With some ODBC apps, CHAR can behave in less-than-expected ways.
CREATE TABLE c (c CHAR(5));
INSERT INTO c VALUES ('a');
will put a 'a____' into the field. PG will let you find this with
SELECT * FROM c WHERE c='a';
because it handles the padding v. non-padding fine.
However, an ODBC app like Access won't find it w/a normal,
Access-mediated query. You have to specify WHERE c='a____' to find it.
*Unless* you're writing a pass-through query (which Access doesn't
touch, but hands directly to PostgreSQL). So you can get different
behavior w/o expecting it.
If your app might be used in different environments, I'd think before
using CHAR, even in places that might seem 'safe' or 'obvious'.
- J.
--
Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant