Arrays

PostgreSQL supports array types, allowing one field to store multiple values of the same type. Arrays are suitable for tags, options, and small permission lists.

If the collection needs frequent joins, statistics, or separate updates, use a relational table instead of an array field.

Create array fields

Append [] to a base type to define an array.

create table public.articles (
  id bigint generated by default as identity primary key,
  title text not null,
  tags text[] not null default '{}',
  scores int[] default '{}'
);

Insert data with array literals or array[...].

insert into public.articles (title, tags, scores)
values
  ('PostgreSQL basics', array['database', 'postgres'], array[90, 95]),
  ('CloudBase practice', array['cloudbase', 'serverless'], array[88]);

Query arrays

Common array operators:

Operator	Description	Example
@>	Left array contains right array	tags @> array['postgres']
<@	Left array is contained by right array	array['postgres'] <@ tags
&&	Arrays overlap	tags && array['cloudbase', 'postgres']

select id, title
from public.articles
where tags @> array['postgres'];

Use cardinality to query array length.

select id, title, cardinality(tags) as tag_count
from public.articles
where cardinality(tags) > 1;

Query through HTTP API

The HTTP API is based on PostgREST and supports compatible filters for array fields.

curl -X GET 'https://<envId>.api.tcloudbasegateway.com/v1/rdb/rest/articles?tags=cs.{postgres}' \
  -H 'Authorization: Bearer <access_token>'

Use Query data and the HTTP API reference as the source of truth for supported operators.

Update arrays

Use array_append to append values and array_remove to remove values.

update public.articles
set tags = array_append(tags, 'tutorial')
where id = 1;

update public.articles
set tags = array_remove(tags, 'tutorial')
where id = 1;

If clients update array fields directly, use RLS WITH CHECK policies to ensure users can only modify their own rows.

Index optimization

Array containment and overlap queries work well with GIN indexes.

create index articles_tags_gin_idx
on public.articles
using gin (tags);

Indexes improve reads but add write cost. Create them only when the array field is frequently used for filtering.

Arrays or join tables

Arrays work for small, weakly related, whole-value updates, such as article tags, product highlights, or config flags.

Use a join table when elements need permissions, ordering, statistics, foreign keys, or frequent single-item updates. For example, an article-tag many-to-many relation can use an article_tags table.