I'm trying to implement the accepted answer of this question:

[How to model a (Java) Enum in a Database (with SQL92)](https://stackoverflow.com/questions/9978089/how-to-model-a-java-enum-in-a-database-with-sql92)

And here's my current implementation:

Gender enum:

```
public enum Gender {

    MALE,
    FEMALE

}

```

Gender table:

```
create table gender (
    gender_id bigserial not null,
    name text not null,
    primary key(gender_id)
);

```

User table:

```
create table system_user (
    user_id bigserial not null,
    username text not null,
    gender_id bigint references gender(gender_id),
    primary key(user_id)
);

create trigger update_gender_enum_table before insert or update on system_user
    for each row execute procedure update_gender_enum_table();

```

Trigger:

```
create or replace function update_gender_enum_table() returns trigger as $update_gender_enum_table$
    begin
        if not exists(select name from gender g where g.name = new.gender_id::text limit 1)
        then
             insert into gender (name) values (new.gender_id);
        end if;

        new.gender_id = (select gender_id from gender g where g.name = new.gender_id::text limit 1);

        return new;

    end;
$update_gender_enum_table$ language plpgsql;

```

At the moment insert statement is failing because second parameter is text and not integer:

```
insert into system_user (username, gender_id) values ('my_username', 'MALE');

ERROR: invalid input syntax for integer: "MALE"

```

Is there any way to pass string as value to integer field? Or am I approaching this in a wrong way.