# PostgreSQL user-defined type

**URL:** <https://www.ruby-forum.com/t/postgresql-user-defined-type/87654>\
**Category:** Rails\
**Created:** [March 5, 2007, 12:00am UTC](https://www.ruby-forum.com/t/postgresql-user-defined-type/87654 "2007-03-05T00:00:01Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ned.c.amazotz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9d8465/32.png) [@ned.c.amazotz](https://www.ruby-forum.com/u/ned.c.amazotz)\
**Post date:** [March 5, 2007, 12:00am UTC](https://www.ruby-forum.com/t/postgresql-user-defined-type/87654/1 "2007-03-05T00:00:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ned.c.amazotz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9d8465/32.png) [@ned.c.amazotz](https://www.ruby-forum.com/u/ned.c.amazotz)\
**Post date:** [March 5, 2007, 12:37am UTC](https://www.ruby-forum.com/t/postgresql-user-defined-type/87654/2 "2007-03-05T00:37:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![ned.c.amazotz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9d8465/32.png) [@ned.c.amazotz](https://www.ruby-forum.com/u/ned.c.amazotz)\
**Post date:** [March 5, 2007, 2:20am UTC](https://www.ruby-forum.com/t/postgresql-user-defined-type/87654/3 "2007-03-05T02:20:16Z")

</div>

On Mar 4, 6:54 pm, “Lugovoi N.” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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.

---

<div class="post-metadata">

**Author:** ![ned.c.amazotz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9d8465/32.png) [@ned.c.amazotz](https://www.ruby-forum.com/u/ned.c.amazotz)\
**Post date:** [March 5, 2007, 2:22am UTC](https://www.ruby-forum.com/t/postgresql-user-defined-type/87654/4 "2007-03-05T02:22:16Z")

</div>

On Mar 4, 6:36 pm, Ashley M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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.

---

<div class="post-metadata">

**Author:** ![ned.c.amazotz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9d8465/32.png) [@ned.c.amazotz](https://www.ruby-forum.com/u/ned.c.amazotz)\
**Post date:** [March 5, 2007, 2:30am UTC](https://www.ruby-forum.com/t/postgresql-user-defined-type/87654/5 "2007-03-05T02:30:17Z")

</div>

On Mar 4, 8:19 pm, “ned.c.amazotz” [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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…

---

<div class="post-metadata">

**Author:** ![ned.c.amazotz](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/9d8465/32.png) [@ned.c.amazotz](https://www.ruby-forum.com/u/ned.c.amazotz)\
**Post date:** [March 5, 2007, 12:55am UTC](https://www.ruby-forum.com/t/postgresql-user-defined-type/87654/6 "2007-03-05T00:55:11Z")

</div>

2007/3/5, ned.c.amazotz [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> 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();