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.

Search Discussions

  • Chris at Oct 17, 2008 at 3:13 am

    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/
  • Tim Uckun at Oct 17, 2008 at 3:26 am

    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.
  • Scott Marlowe at Oct 17, 2008 at 3:36 am

    On Thu, Oct 16, 2008 at 9:26 PM, Tim Uckun 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.
  • Tim Uckun at Oct 17, 2008 at 3:58 am

    Are you using the ruby-pg interface? I was under the impression it
    handled this properly.
    I am using postgres-pr
  • Scott Marlowe at Oct 17, 2008 at 3:18 am

    On Thu, Oct 16, 2008 at 9:09 PM, 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.

    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.
  • Scott Marlowe at Oct 17, 2008 at 3:40 am

    On Thu, Oct 16, 2008 at 9:09 PM, 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;
    Hold on, when did you assign a sequence to this column? When you
    created it as a serial? Or is there none assigned?
  • Tim Uckun at Oct 17, 2008 at 3:58 am

    On Fri, Oct 17, 2008 at 4:40 PM, Scott Marlowe wrote:
    On Thu, Oct 16, 2008 at 9:09 PM, 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;
    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.
  • Scott Marlowe at Oct 17, 2008 at 4:04 am

    On Thu, Oct 16, 2008 at 9:58 PM, Tim Uckun wrote:
    On Fri, Oct 17, 2008 at 4:40 PM, Scott Marlowe wrote:
    On Thu, Oct 16, 2008 at 9:09 PM, 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;
    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);
  • Stephan Szabo at Oct 17, 2008 at 4:01 am

    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.
  • Scott Marlowe at Oct 17, 2008 at 4:05 am

    On Thu, Oct 16, 2008 at 10:01 PM, Stephan Szabo 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.
  • Stephan Szabo at Oct 17, 2008 at 4:06 am

    On Thu, 16 Oct 2008, Scott Marlowe wrote:

    On Thu, Oct 16, 2008 at 10:01 PM, Stephan Szabo
    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.
  • Scott Marlowe at Oct 17, 2008 at 4:12 am

    On Thu, Oct 16, 2008 at 10:06 PM, Stephan Szabo wrote:
    On Thu, 16 Oct 2008, Scott Marlowe wrote:

    On Thu, Oct 16, 2008 at 10:01 PM, Stephan Szabo
    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.
  • Brian Ally at Oct 17, 2008 at 5:18 pm

    Scott Marlowe wrote:
    On Thu, Oct 16, 2008 at 10:06 PM, Stephan Szabo
    wrote:
    On Thu, 16 Oct 2008, Scott Marlowe wrote:

    On Thu, Oct 16, 2008 at 10:01 PM, Stephan Szabo
    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedOct 17, '08 at 3:09a
activeOct 17, '08 at 5:18p
posts14
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase