How to compare strings as int in EAV model?

Question

I have implemented an EAV db pattern as part of a PoC in Postgres. This is the following ER illustration:

entity:
id, primary key 
firstName, varchar
lastName, varchar
tenantId, int, b-tree indexed

attribute:
id, primary key
label, varchar
type, varchar, (enumerated as string from java enum , NUMERIC, TEXT, DATE)

attribute_value:
id, primary key 
attribute_id, fk reference attribute primary key
entity_id, fk reference entity primary key
value, varchar

Now the problem I am facing is comparing values in the joined tables. For example:

How do I check whether an attribute with label = 'marks12' has value < 100

Comparing the value::int does not solve the problem as there are other attribute such as gender that cannot be cast into int value. How do I perform such value-centric conditions in the above EAV design model?

PS: I am open to other database designs which allow to map/store dynamic attributes in an RDBMS.

Answer 1

easiest is to ignore anything that does not look like an integer.

select e.* , av.value
FROM attribute_value AS av 
JOIN attribute AS a ON av.attribute_id = a.id
JOIN entity AS e ON av.entity_id = e.id
WHERE 
   a.label = 'marks12'
 AND 
   CASE WHEN av.value ~ '[+-]?[0-9]{1,20}' THEN av.value::bigint END < 100;

this is much harder when value might be a date,