I get the following error:
PGError: ERROR: null value in column “person_id” violates not-null
constraint
…after I generate a basic scaffold and try to insert a record into a
PostgreSQL table that looks like:
CREATE SEQUENCE people_id_seq;
CREATE TABLE people (
id
INTEGER
NOT NULL
DEFAULT nextval( ‘people_id_seq’ )
PRIMARY KEY,
person_id
y_octet_16
NOT NULL
DEFAULT encode( y_uuid_generate_random(), ‘hex’ )::y_octet_16,
etc…
The issue here, I believe, is that my ‘person_id’ field is based on my
own user-defined type.
The log file shows rails trying to insert a NULL value into this
field. Ideally I’d like rails to automatically understand that it
shouldn’t attempt to insert NULL into a NOT NULL field with a DEFAULT
value. Short of that, I’d like to know how to tell my model to ignore
this field.
TIA.
On Mar 04, 2007, at 10:59 pm, ned.c.amazotz wrote:
The issue here, I believe, is that my ‘person_id’ field is based on my
own user-defined type.
Hmm, long time since I’ve looked at the code but there was a patch
submitted to make ActiveRecord treat Postgres domains as their
underlying types. Have a look on the Rails Trac. I meant to update
it a while back - if you find it maybe leave a comment on it and
suggest it’s included in the adapter. I can’t remember what
ActiveRecord does with custom domains currently. I think they’re a
nice feature of Postgres and should probably be used more than they are.
Ashley.
On Mar 4, 6:54 pm, “Lugovoi N.” [email protected] wrote:
IIRC, custom types should be treated as strings (can’t check right now).
String would be o.k. in this case, as my output function converts the
binary storage format to hexadecimal text. This field doesn’t show up
in the list view (or any other view) at all, though.
END IF;
RETURN NEW;
END$$ language ‘plpgsql’;
CREATE TRIGGER person_uuid_trg BEFORE INSERT ON people
FOR EACH ROW EXECUTE PROCEDURE gen_person_uuid();
I guess I’ll do that then for now. (this needs a ‘THEN’ in front
of ‘NEW.person_id := …’ btw.)
Thanks.
On Mar 4, 6:36 pm, Ashley M. [email protected] wrote:
On Mar 04, 2007, at 10:59 pm, ned.c.amazotz wrote:
The issue here, I believe, is that my ‘person_id’ field is based on my
own user-defined type.
Hmm, long time since I’ve looked at the code but there was a patch
submitted to make ActiveRecord treat Postgres domains as their
underlying types. Have a look on the Rails Trac. I meant to update
it a while back - if you find it maybe leave a comment on it and
suggest it’s included in the adapter.
Thanks, I’ll take a look.
On Mar 4, 8:19 pm, “ned.c.amazotz” [email protected] wrote:> >
IIRC, custom types should be treated as strings (can’t check right
now).
String would be o.k. in this case, as my output function converts the
binary storage format to hexadecimal text. This field doesn’t show up
in the list view (or any other view) at all, though.
Phghgt. The ‘id’ suffix is special. I’ll catch on soon enough…
2007/3/5, ned.c.amazotz [email protected]:
The issue here, I believe, is that my ‘person_id’ field is based on my
own user-defined type.
The log file shows rails trying to insert a NULL value into this
field. Ideally I’d like rails to automatically understand that it
shouldn’t attempt to insert NULL into a NOT NULL field with a DEFAULT
value. Short of that, I’d like to know how to tell my model to ignore
this field.
IIRC, custom types should be treated as strings (can’t check right now).
But this your problem is caused by inability of ActiveRecord to handle
inserts where not-null columns have default values based on function
calls (such like now() ).
One solution would be just to set up a trigger on that table:
CREATE FUNCTION gen_person_uuid() RETURNS trigger AS $$
BEGIN
IF NEW.person_id IS NULL
NEW.person_id := encode( y_uuid_generate_random(), ‘hex’
)::y_octet_16;
END IF;
RETURN NEW;
END$$ language ‘plpgsql’;
CREATE TRIGGER person_uuid_trg BEFORE INSERT ON people
FOR EACH ROW EXECUTE PROCEDURE gen_person_uuid();