3

I have been looking at this SO post as a basis for this trigger.

I need to do a large import of several tables with several UUID fields, which were formerly stored as char(35) in MySQL, some of which are blank ('').

I'd like to automatically change blanks to nil UUIDs using a BEFORE INSERT TRIGGER.

Currently I have the trigger function simplified to this:

CREATE OR REPLACE FUNCTION trg_uc_on_insert()
  RETURNS trigger AS
$func$
BEGIN
    RAISE NOTICE 'in function';
    NEW.id=uuid_generate_v1();
    RETURN NEW;
END
$func$ LANGUAGE plpgsql;

And I'm running

create table uuidtest (id uuid);
insert into uuidtest values ('');

but I am still getting

[Err] ERROR:  invalid input syntax for uuid: ""
LINE 1: insert into uuidtest values ('');

I have too much data to import and I would much rather let PostgreSQL change the values before the insert than do it myself.

Is this possible using this mechanism, and if so, what am I doing wrong?

Community
  • 1
  • 1
arnaldop
  • 333
  • 6
  • 17
  • Sure. You just need a `BEFORE` trigger that tests for `NEW.id = ''` and if so, sets `NEW.id := uuid_generate_v4()` before returning `NEW`. See the trigger examples in the PostgreSQL manual for details on `BEFORE` triggers. – Craig Ringer Sep 15 '15 at 03:12
  • 1
    @CraigRinger The error is apparently generated before the trigger function is entered. Without knowing the details of the code, this presumably is because the `NEW` record has the `uuid` type for id and can thus not be populated with an empty string. – Patrick Sep 15 '15 at 03:23
  • @Patrick Ah, that makes sense. Because it's not `NULL`, it's an invalid UUID `''`. – Craig Ringer Sep 15 '15 at 03:31

1 Answers1

3

In PostgreSQL a uuid is stored in binary form, hence an empty string is invalid as a uuid value.

The easiest approach is probably to import the tables with a char(35) column, then UPDATE blank values to a text representation of of a nil uuid and then ALTER COLUMN to convert the column to the uuid data type.

Assuming you have your import done, do:

UPDATE my_table SET id = '00000000000000000000000000000000' -- that's 32 0's
WHERE id = '';

The above is a true nil uuid.

Now you can change the column to the uuid type. Provided all text values are in a format PostgreSQL can understand, the values will be converted to uuid automatically:

ALTER TABLE my_table ALTER id TYPE uuid;
Patrick
  • 29,357
  • 6
  • 62
  • 90
  • Not the answer I was hoping for, but the answer was right! I was afraid of some performance impact after changing table column types. – arnaldop Sep 16 '15 at 03:24