Working With Arrays

Postgres 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()

Insert a record from the Python client:

supabase.from_('arraytest').insert(
  [
    {
      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

Postgres 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
• Postgres Arrays