Is there a multivalued field type available in PostgreSQL?

Question

I want to know if it is possible to store multiple values in a field in PostgreSQL.

I have a table called Token with the columns id, text and category. category is a multivalued field. Is it necessary to create a separate table for it or is there a way to store it in the Token table?

Answer 1

There are arrays in PostgreSQL. For example:

CREATE TABLE "token" (
  "id"       integer PRIMARY KEY,
  "text"     text,
  "category" text[]
);

Now you can insert multiple categories for each row into token:

INSERT INTO "token" ("id", "text", "category")
VALUES (1, 'some text', ARRAY['cate1', 'cate2']);

You can find the rows like:

SELECT * FROM "token" WHERE 'cate1' = ANY ("category");

Answer 2

There are several:

• arrays
• hstore (associative tables)
• composite types (similar to structs in C, more or less)