arrays.mdx

id	title	description
arrays	Working With Arrays	How to use arrays in PostgreSQL and the Supabase API.

PostgreSQL supports flexible array types. These arrays are also supported in the Supabase Dashboard and in the JavaScript API.

Create a table with an array column

Create a test table with a text array (an array of strings):

<Tabs scrollable size="small" type="underlined" defaultActiveId="dashboard" queryGroup="database-method"

1. Go to the Table editor page in the Dashboard.
2. Click New Table and create a table with the name arraytest.
3. Click Save.
4. Click New Column and create a column with the name textarray, type text, and select Define as array.
5. Click Save.

create table arraytest (
  id integer not null,
  textarray text array
);

Insert a record with an array value

<Tabs scrollable size="small" type="underlined" defaultActiveId="dashboard" queryGroup="database-method"

1. Go to the Table editor page in the Dashboard.
2. Select the arraytest table.
3. Click Insert row and add ["Harry", "Larry", "Moe"].
4. Click Save.

INSERT INTO arraytest (id, textarray) VALUES (1, ARRAY['Harry', 'Larry', 'Moe']);

Insert a record from the JavaScript client:

const { data, error } = await supabase
  .from('arraytest')
  .insert([{ id: 2, textarray: ['one', 'two', 'three', 'four'] }])

Insert a record from the Swift client:

struct ArrayTest: Encodable {
  let id: Int
  let textarray: [String]
}

try await supabase
  .from("arraytest")
  .insert(
    [
      ArrayTest(
        id: 2,
        textarray: ["one", "two", "three", "four"]
      )
    ]
  )
  .execute()

View the results

<Tabs scrollable size="small" type="underlined" defaultActiveId="dashboard" queryGroup="database-method"

1. Go to the Table editor page in the Dashboard.
2. Select the arraytest table.

You should see:

id	textarray
1	["Harry","Larry","Moe"]

select * from arraytest;

You should see:

id	textarray
1	["Harry","Larry","Moe"]

Query array data

PostgreSQL uses 1-based indexing (e.g., textarray[1] is the first item in the array).

<Tabs scrollable size="small" type="underlined" defaultActiveId="sql" queryGroup="language"

To select the first item from the array and get the total length of the array:

SELECT textarray[1], array_length(textarray, 1) FROM arraytest;

returns:

textarray	array_length
Harry	3

This returns the entire array field:

const { data, error } = await supabase.from('arraytest').select('textarray')
console.log(JSON.stringify(data, null, 2))

returns:

[
  {
    "textarray": ["Harry", "Larry", "Moe"]
  }
]

This returns the entire array field:

struct Response: Decodable {
  let textarray: [String]
}

let response: [Response] = try await supabase.from("arraytest").select("textarray").execute().value
dump(response)

returns:

[
  Response(
    textarray: ["Harry", "Larry", "Moe"],
  )
]

Resources

• Supabase JS Client
• Supabase - Get started for free
• PostgreSQL Arrays