arrayspostgresqldatabase-designtypesmultivalue

Is there a multivalued field type available in PostgreSQL?

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?

Solution

  • 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");