'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?

arrayspostgresqldatabase-designtypesmultivalue


Solution 1:[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");

Solution 2:[2]

There are several:

Solution 3:[3]

I know that this question had been asked 10 years ago but since Postgres is evolving there are new types that are optimized and could be used to address this issue

  1. json
  2. jsonb aka binary json

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 mu is too short
Solution 3 Bentech

Related Questions