Re: hi all - Mailing list pgsql-general

From David Fetter
Subject Re: hi all
Date
Msg-id 20090217175300.GD6226@fetter.org
Whole thread Raw
In response to Re: hi all  (Sam Mason <sam@samason.me.uk>)
List pgsql-general
On Tue, Feb 17, 2009 at 04:40:58PM +0000, Sam Mason wrote:
> >      user_name varchar(50) NOT NULL,
>
> As a general design question; should user_name have a UNIQUE
> constraint on it?  i.e.
>
>   user_name VARCHAR(50) NOT NULL UNIQUE,

Yes, it's good to have a UNIQUE constraint, but not this one.  To have
a sane one, it needs further constraints, and in 8.4, case-insensitive
text (citext) type.  Here's one that is reasonably sane until citext
is available.

user_name TEXT, -- unless length is an integrity constraint, use TEXT instead of VARCHAR.

then later:

CREATE UNIQUE INDEX unique_user_name_your_table
    ON your_table(LOWER(TRIM(user_name)))

You might also require that whitespace be treated in some consistent
way, one example of which is simply forbidding whitespace in user_name
at all.  This you can do via CHECK constraints or a DOMAIN.

>   creator INT REFERENCES users (user_id),
>
> >      date_created timestamp  NOT NULL default to_timestamp('0000-00-00 00:00:00','YYYY-MM-DD HH24:MI:SS'),
>
> What's this strange 0000-00-00 date you speak of?  As far as I know
> it's not valid; dates go from 1BC to 1AD without a zero in the middle.
> Shouldn't you just remove the NOT NULL check or maybe '-infinity' would
> be better.

Either require a created_date and make the default
sane--CURRENT_TIMESTAMP, e.g.--or don't require one, but making a
nonsense date is Bad(TM).

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

pgsql-general by date:

Previous
From: Mark Roberts
Date:
Subject: Re: Good Delimiter for copy command
Next
From: David Fetter
Date:
Subject: Re: Good Delimiter for copy command