Thread: Question about NOT NULL and default values.
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? Thanks.
Tim Uckun 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. Not sure how you do this in rails but if you use DEFAULT in the query it works: > create table a(a int default 5); CREATE TABLE > insert into a(a) values (DEFAULT); INSERT 0 1 > SELECT * from a; a --- 5 (1 row) -- Postgresql & php tutorials http://www.designmagick.com/
On Thu, Oct 16, 2008 at 9:09 PM, Tim Uckun <timuckun@gmail.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.
> Not sure how you do this in rails but if you use DEFAULT in the query it > works: > >> create table a(a int default 5); > CREATE TABLE > >> insert into a(a) values (DEFAULT); > INSERT 0 1 Unfortunately the SQL is being generated by the ORM. I really don't want to bypass the ORM that would be way too painful.
On Thu, Oct 16, 2008 at 9:26 PM, Tim Uckun <timuckun@gmail.com> wrote: >> Not sure how you do this in rails but if you use DEFAULT in the query it >> works: >> >>> create table a(a int default 5); >> CREATE TABLE >> >>> insert into a(a) values (DEFAULT); >> INSERT 0 1 > > Unfortunately the SQL is being generated by the ORM. I really don't > want to bypass the ORM that would be way too painful. Are you using the ruby-pg interface? I was under the impression it handled this properly.
On Thu, Oct 16, 2008 at 9:09 PM, Tim Uckun <timuckun@gmail.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; Hold on, when did you assign a sequence to this column? When you created it as a serial? Or is there none assigned?
> > Are you using the ruby-pg interface? I was under the impression it > handled this properly. > I am using postgres-pr
On Fri, Oct 17, 2008 at 4:40 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Thu, Oct 16, 2008 at 9:09 PM, Tim Uckun <timuckun@gmail.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; > > Hold on, when did you assign a sequence to this column? When you > created it as a serial? Or is there none assigned? > There is no sequence. It's a foreign key.
On Fri, 17 Oct 2008, Tim Uckun wrote: > 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? I don't think so specifically with default, but you could use a before trigger instead that would put in a value in the new row if NULL was given.
On Thu, Oct 16, 2008 at 9:58 PM, Tim Uckun <timuckun@gmail.com> wrote:
> On Fri, Oct 17, 2008 at 4:40 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Thu, Oct 16, 2008 at 9:09 PM, Tim Uckun <timuckun@gmail.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;
>>
>> Hold on, when did you assign a sequence to this column? When you
>> created it as a serial? Or is there none assigned?
>>
>
> There is no sequence. It's a foreign key.
Not sure what being a FK means here. Postgresql uses sequences and
default to make an autoincrementing column.
Old fashioned way (which doesn't work well with ruby):
create sequence test_id_seq;
create table test (id int primary key default nextval('test_id_seq'),
info text);
Easy method, which should work with ruby-pg:
create table test (id serial primary key, info text);
On Thu, Oct 16, 2008 at 10:01 PM, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > On Fri, 17 Oct 2008, Tim Uckun wrote: > >> 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? > > I don't think so specifically with default, but you could use a before > trigger instead that would put in a value in the new row if NULL was > given. I'm pretty sure that will fail as the primary key or not null constraint comes first.
On Thu, 16 Oct 2008, Scott Marlowe wrote: > On Thu, Oct 16, 2008 at 10:01 PM, Stephan Szabo > <sszabo@megazone.bigpanda.com> wrote: > > On Fri, 17 Oct 2008, Tim Uckun wrote: > > > >> 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? > > > > I don't think so specifically with default, but you could use a before > > trigger instead that would put in a value in the new row if NULL was > > given. > > I'm pretty sure that will fail as the primary key or not null > constraint comes first. Well, since he said that he'd removed the not null constraint in his testing, I figured that was a viable option.
On Thu, Oct 16, 2008 at 10:06 PM, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote: > > On Thu, 16 Oct 2008, Scott Marlowe wrote: > >> On Thu, Oct 16, 2008 at 10:01 PM, Stephan Szabo >> <sszabo@megazone.bigpanda.com> wrote: >> > On Fri, 17 Oct 2008, Tim Uckun wrote: >> > >> >> 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? >> > >> > I don't think so specifically with default, but you could use a before >> > trigger instead that would put in a value in the new row if NULL was >> > given. >> >> I'm pretty sure that will fail as the primary key or not null >> constraint comes first. > > Well, since he said that he'd removed the not null constraint in his > testing, I figured that was a viable option. Yeah, then it might. But I get the feeling the OP just wasn't assigning a sequence as a defult.
Scott Marlowe wrote: > On Thu, Oct 16, 2008 at 10:06 PM, Stephan Szabo > <sszabo@megazone.bigpanda.com> wrote: >> On Thu, 16 Oct 2008, Scott Marlowe wrote: >> >>> On Thu, Oct 16, 2008 at 10:01 PM, Stephan Szabo >>> <sszabo@megazone.bigpanda.com> wrote: >>>> On Fri, 17 Oct 2008, Tim Uckun wrote: >>>> >>>>> 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? >>>> I don't think so specifically with default, but you could use a before >>>> trigger instead that would put in a value in the new row if NULL was >>>> given. >>> I'm pretty sure that will fail as the primary key or not null >>> constraint comes first. >> Well, since he said that he'd removed the not null constraint in his >> testing, I figured that was a viable option. > > Yeah, then it might. But I get the feeling the OP just wasn't > assigning a sequence as a defult. > I don't think the OP wants this column to have a sequence. It looked to me that he wants it to default to zero. I think the confusion was caused by a comment about MySQL's autoincrement handling. It seems to me that the problem lies with postgres-pr and the best thing to do until that's fixed would be to use a trigger, as someone else suggested. b