What column type is required when using ElementCollection with jpa?

Question

I have a simple class with a column that is technically a List

@Entity
@Table(name='hat')
class Hat {
    @Id
    String id = UUID.randomUUID()

    @ElementCollection
    List<String> wat
}

Right now when I use either a varchar(500) or character varying(500) the PSQL code that pulls this entity out of the database explodes

org.postgresql.util.PSQLException: ERROR: relation "hat_wat" does not exist

Answer 1

An element collection is not stored in a single column. It's stored in a separate table, just as if you have a OneToMany, except there is no identifier for the many side. Suppose the Hat foo has 2 wats "bar" and "baz":

hat table:             hat_wat table:

id                     hat_id    wat
-----                  ---------------
foo                    foo       bar
                       foo       baz

That allows querying the hats to find, for example, all the hats having "bar" in their list of wats. Trying to store multiple string in a single column is really not a good idea. It violates the first normal form rules.