Question about NOT NULL and default values.

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