I have an enum type with default value as the following:
app_type apps_status NOT NULL DEFAULT 'undeployed'::apps_status
But now whenever I try to insert I get an error app_type cannot be empty. Also when you continue trying you will get error: Number has already been taken.
Any idea why this is happening?
The issue is the NOT NULL constraint, which is filled by the ORM, and not deferrable.
First off, try setting the value to DEFAULT on the ORM side.
If it's not a workable solution in your ORM, try adding a trigger that sets the default value:
create function make_default() returns trigger as $$
begin
new.app_status := 'undeployed'::apps_status
return null;
end;
$$ language plpgsql;
create trigger before insert or delete on yourtable
for each row when (new.app_type is null)
execute procedure make_default();
If that doesn't work either, drop the NOT NULL constraint altogether, and enforce it with a constraint trigger instead:
create function reject_row() returns trigger as $$
begin
raise exception 'rejected!'
return null;
end;
$$ language plpgsql;
create constraint trigger after insert or delete on yourtable
for each row when (new.app_type is null)
execute procedure reject_row();
before_validation :initialize_stuff, :if => :new_record? (where the initialize_stuff method would set the initial value if needed) combined with a validation to ensure that it is set should do the trick.Thanks to everyone who helped. However after thorough research I realized that PostgreSQL ENUM doesn't support default but domains do so I use domain instead. Domain is working fine and is inserting default value as well. See the following link: http://www.sqlines.com/postgresql/how-to/create_user_defined_type
DEFAULTor simply omitting the column)NULLvalue, or remove the column alltogether from the insert, or use theDEFAULTkeyword. All that will also work for MySQL