Prepare for the PgBouncer and IPv4 deprecations on 26th January 2024

Database

Working With Arrays

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):

  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.

Insert a record with an array value

  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.

View the results

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

You should see:

idtextarray
1["Harry","Larry","Moe"]

Query array data

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

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


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

returns:

textarrayarray_length
Harry3

Resources