Is there a multivalued field type available in PostgreSQL?

arrays postgresql database-design types multivalue
Renato Dinhani picture Renato Dinhani · Oct 28, 2011 · Viewed 12.7k times · Source

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

minhee picture minhee · Oct 28, 2011

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

Related questions

INSERT into table with array of composite type

So I am trying to copy values from temporary tables into a table with an array type and I get the following error which I can't find a way to overcome. ERROR: cannot assign to field "addressid" of column "address" …

Read More
Can PostgreSQL have a uniqueness constraint on array elements?

I'm trying to come up with a PostgreSQL schema for host data that's currently in an LDAP store. Part of that data is the list of hostnames a machine can have, and that attribute is generally the key that most …

Read More
Check if value exists in Postgres array

Using Postgres 9.0, I need a way to test if a value exists in a given array. So far I came up with something like this: select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int) But I keep thinking there …

Read More