# Arrays in Postgres

DevFeed: [Arrays in Postgres](<https://devfeed.tech/articles/arrays-in-postgres-41117.md>)

Original publisher: [Read original article](<https://www.craigkerstiens.com/2012/08/20/Arrays-in-Postgres/>)

Author: Map

Published: 2012-08-20T20:55:56Z

Content type: tutorial

Language: en

Sources: [Craig Kerstiens](<https://devfeed.tech/sources/craig-kerstiens.md>)

Topics: [data](<https://devfeed.tech/topics/data.md>), [Data structures](<https://devfeed.tech/topics/data-structures.md>), [App](<https://devfeed.tech/topics/app.md>)

Tags: [application](<https://devfeed.tech/tags/application.md>), [arrays](<https://devfeed.tech/tags/arrays.md>), [development](<https://devfeed.tech/tags/development.md>), [gin](<https://devfeed.tech/tags/gin.md>), [gist](<https://devfeed.tech/tags/gist.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [postgres](<https://devfeed.tech/tags/postgres.md>), [query](<https://devfeed.tech/tags/query.md>)

## AI overview

This tutorial explains how PostgreSQL arrays can store multiple values in a single column or row. It discusses modeling data with arrays, gives purchase and tagging examples, and notes that GIN and GiST indexes can support searches for array contents.

## Source excerpt

Postgres out of the box has an abundance of datatypes, from standard numeric datatypes to geometric or even network datatypes. With extensions you can get even more out of it as earlier discussed with hStore. Though with all of the datatypes its easy to miss out on some of them that are there, in fact one of my favorites is often missed entirely. The Array datatype lets you do just as you'd expect, store an array inside Postgres. With this you can often get some of the functionality you'd want in a single table when you might traditionally have expanded to multiple tables. The broader question may be why you'd actually want to use an array. One good reason may be if you're an application developer its how you think of your data, so why not model it the same way. As you'll see below it can be easier than joining and aggregating across a set of rows. Also depending on your case you performance could be improved, though mileage may vary here as it does depend on the data you're storing. First a bit of a hacky example... Lets say you have a basic website that sells stuff, and instead of having a purchase ID and a total you want to include the quantity, id, and price of each item in a single row. With a bit of a messy foreign key (using a decimal) you could store all of this within a single row: CREATE TABLE purchases ( id integer NOT NULL, user_id integer, items decimal(10,2) [100][1], occurred_at timestamp ); With this table I could have an array that holds multiple records of: The item purchased The quantity The price An insert to this table would look something like: INSERT INTO purchases VALUES (1, 37, '\{\{15.0, 1.0, 25.0\}, \{15.0, 1.0, 25.0\}\}', now()); INSERT INTO purchases VALUES (2, 2, '{{11.0, 1.0, 4.99}}', now()); You can see a full example with UDF's to compute total here A more practical example may actually be using an array for tags. If you were to tag your purchases: CREATE TABLE products ( id integer NOT NULL, title character varying(255), description t