From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "Tim Uckun" <timuckun(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Question about NOT NULL and default values. |
Date: | 2008-10-17 03:18:15 |
Message-ID: | dcc563d10810162018y26b71142jbf0c62568fd39983@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Oct 16, 2008 at 9:09 PM, Tim Uckun <timuckun(at)gmail(dot)com> wrote:
> Hey all.
>
> I am using postgres 8.3 with a rails application. I have a column
> defined like this.
>
> ALTER TABLE provisions ADD COLUMN provider_id integer;
> ALTER TABLE provisions ALTER COLUMN provider_id SET STORAGE PLAIN;
> ALTER TABLE provisions ALTER COLUMN provider_id SET NOT NULL;
> ALTER TABLE provisions ALTER COLUMN provider_id SET DEFAULT 0;
>
> In rails if I don't specify the provider_id the ORM passes a NULL in
> the SQL to insert or update and the query blows up.
>
> In order to get around that problem and removed the NOT NULL
> constraint but postgres just puts the NULL value in the column instead
> of the default value.
>
> Is there a way to change this behavior so that an attempt to set the
> column to NULL will result in the default value being put in the
> field?
MySQL implements autoincrements this way. It's complete broken by the
SQL spec and no other database I know of does this. You use DEFAULT
keywork not NULL in postgresql to get the DEFAULT value. That's how
the SQL spec says to do it I believe.
There should be some kind of personality setting in rails that tells
it you're using pgsql and fixes this, unless you're using some
ancient, early mysql only version.
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Uckun | 2008-10-17 03:26:37 | Re: Question about NOT NULL and default values. |
Previous Message | Chris | 2008-10-17 03:13:01 | Re: Question about NOT NULL and default values. |