17

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Renato Dinhani
  • 35,057
  • 55
  • 139
  • 199

3 Answers3

23

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");
minhee
  • 5,688
  • 5
  • 43
  • 81
  • If I do a query like `SELECT * FROM token WHERE category = 'cate1'`, the query will search inside the array? – Renato Dinhani Oct 28 '11 at 04:20
  • @RenatoDinhaniConceição No, you can use `@>` operator or [`ANY`](http://www.postgresql.org/docs/9.0/interactive/functions-comparisons.html#AEN16871) hyperoperator instead. – minhee Oct 28 '11 at 04:26
13

There are several:

mu is too short
  • 426,620
  • 70
  • 833
  • 800
1

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
Bentech
  • 468
  • 5
  • 14
  • Even though JSON fields are way more flexible in terms of performance are not as good as multi-value fields. This is due to some indexing limitations. As always it depends on each case and your needs in every use case you implement. – Albert Jun 16 '23 at 12:36