Skip to content

Updated

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

  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

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

Insert a record from the C# client:

[Table("arraytest")]
class ArrayTest : BaseModel
{
    [PrimaryKey("id", false)]
    public int Id { get; set; }
    [Column("textarray")]
    public List<string> Textarray { get; set; }
}
await supabase
  .From<ArrayTest>()
  .Insert(new ArrayTest { Id = 2, Textarray = new List<string> { "one", "two", "three", "four" } });

View the results

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

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"],
  )
]

This returns the entire array field:

var result = await supabase
  .From<ArrayTest>()
  .Select(x => new object[] { x.Textarray })
  .Get();

Resources