Working with PostgreSQL data types

author:

Laurence Isla

PostgREST makes use of PostgreSQL string representations to work with data types. Thanks to this, you can use special values, such as now for timestamps, yes for booleans or time values including the time zones. This page describes how you can take advantage of these string representations and some alternatives to perform operations on different PostgreSQL data types.

Arrays

To handle array types you can use string representation or JSON array format.

create table movies (
  id int primary key,
  title text not null,
  tags text[],
  performance_times time[]
);

You can insert a new value using string representation.

curl "http://localhost:3000/movies" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  {
    "id": 1,
    "title": "Paddington",
    "tags": "{family,comedy,not streamable}",
    "performance_times": "{12:40,15:00,20:00}"
  }
EOF

Or you could send the same data using JSON array format:

curl "http://localhost:3000/movies" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  {
    "id": 1,
    "title": "Paddington",
    "tags": ["family", "comedy", "not streamable"],
    "performance_times": ["12:40", "15:00", "20:00"]
  }
EOF

To query the data you can use arrow operators. See Composite / Array Columns.

Multidimensional Arrays

Similarly to one-dimensional arrays, both the string representation and JSON array format are allowed.

-- This new column stores the cinema, floor and auditorium numbers in that order
alter table movies
add column cinema_floor_auditorium int[][][];

You can now update the item using JSON array format:

curl "http://localhost:3000/movies?id=eq.1" \
  -X PATCH -H "Content-Type: application/json" \
  -d @- << EOF
  {
    "cinema_floor_auditorium": [ [ [1,2], [6,7] ], [ [3,5], [8,9] ] ]
  }
EOF

Then, for example, to query the auditoriums that are located in the first cinema (position 0 in the array) and on the second floor (position 1 in the next inner array), we can use the arrow operators this way:

curl "http://localhost:3000/movies?select=title,auditorium:cinema_floor_auditorium->0->1&id=eq.1"
[
  {
    "title": "Paddington",
    "auditorium": [6,7]
  }
]

Bytea

To send raw binary to PostgREST you need a function with a single unnamed parameter of bytea type.

create table files (
  id int primary key generated always as identity,
  file bytea
);

create function upload_binary(bytea) returns void as $$
  insert into files (file) values ($1);
$$ language sql;

Let’s download the PostgREST logo for our test.

curl "https://postgrest.org/en/latest/_images/logo.png" -o postgrest-logo.png

Now, to send the file postgrest-logo.png we need to set the Content-Type: application/octet-stream header in the request:

curl "http://localhost:3000/rpc/upload_binary" \
  -X POST -H "Content-Type: application/octet-stream" \
  --data-binary "@postgrest-logo.png"

To get the image from the database, use Media Type Handlers like so:

create domain "image/png" as bytea;

create or replace get_image(id int) returns "image/png" as $$
  select file from files where id = $1;
$$ language sql;
curl "http://localhost:3000/get_image?id=1" \
  -H "Accept: image/png"

See Providing images for <img> for a step-by-step example on how to handle images in HTML.

Warning

Be careful when saving binaries in the database, having a separate storage service for these is preferable in most cases. See Storing Binary files in the Database.

Composite Types

With PostgREST, you have two options to handle composite type columns.

create type dimension as (
  length decimal(6,2),
  width decimal (6,2),
  height decimal (6,2),
  unit text
);

create table products (
  id int primary key,
  size dimension
);

insert into products (id, size)
values (1, '(5.0,5.0,10.0,"cm")');

On one hand you can insert values using string representation.

curl "http://localhost:3000/products" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  { "id": 2, "size": "(0.7,0.5,1.8,\"m\")" }
EOF

Or you could insert the same data in JSON format.

curl "http://localhost:3000/products" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  {
    "id": 2,
    "size": {
      "length": 0.7,
      "width": 0.5,
      "height": 1.8,
      "unit": "m"
    }
  }
EOF

You can also query the data using arrow operators. See Composite / Array Columns.

Enums

You can handle Enumerated Types using string representations:

create type letter_size as enum ('s','m','l','xl');

create table products (
  id int primary key generated always as identity,
  name text,
  size letter_size
);

To insert or update the value use a string:

curl -X POST "http://localhost:3000/products" \
  -H "Content-Type: application/json" \
  -d @- << EOF
  { "name": "t-shirt", "size": "l" }
EOF

You can then query and filter the enum using the compatible operators. For example, to get all the products larger than m and ordering them by their size:

curl "http://localhost:3000/products?select=name,size&size=gt.m&order=size"
[
  {
    "name": "t-shirt",
    "size": "l"
  },
  {
    "name": "hoodie",
    "size": "xl"
  }
]

hstore

You can work with data types belonging to additional supplied modules such as hstore.

-- Activate the hstore module in the current database
create extension if not exists hstore;

create table countries (
  id int primary key,
  name hstore unique
);

The name column will have the name of the country in different formats. You can insert values using the string representation for that data type:

curl "http://localhost:3000/countries" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  [
    { "id": 1, "name": "common => Egypt, official => \"Arab Republic of Egypt\", native => مصر" },
    { "id": 2, "name": "common => Germany, official => \"Federal Republic of Germany\", native => Deutschland" }
  ]
EOF

Notice that the use of " in the value of the name column needs to be escaped using a backslash \.

You can also query and filter the value of a hstore column using the arrow operators, as you would do for a JSON column. For example, if you want to get the native name of Egypt:

curl "http://localhost:3000/countries?select=name->>native&name->>common=like.Egypt"
[{ "native": "مصر" }]

JSON

To work with a json type column, you can handle the value as a JSON object.

create table products (
  id int primary key,
  name text unique,
  extra_info json
);

You can insert a new product using a JSON object for the extra_info column:

curl "http://localhost:3000/products" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  {
    "id": 1,
    "name": "Canned fish",
    "extra_info": {
      "expiry_date": "2025-12-31",
      "exportable": true
    }
  }
EOF

To query and filter the data see JSON Columns for a complete reference.

PostGIS

You can use the string representation for PostGIS data types such as geometry or geography (you need to install PostGIS first).

-- Activate the postgis module in the current database
create extension if not exists postgis;

create table coverage (
  id int primary key,
  name text unique,
  area geometry
);

To add areas in polygon format, you can use string representation:

curl "http://localhost:3000/coverage" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  [
    { "id": 1, "name": "small", "area": "SRID=4326;POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))" },
    { "id": 2, "name": "big", "area": "SRID=4326;POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))" }
  ]
EOF

Now, when you request the information, PostgREST will automatically cast the area column into a Polygon geometry type. Although this is useful, you may need the whole output to be in GeoJSON format out of the box, which can be done by including the Accept: application/geo+json in the request. This will work for PostGIS versions 3.0.0 and up and will return the output as a FeatureCollection Object:

curl "http://localhost:3000/coverage" \
  -H "Accept: application/geo+json"
{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [[0,0],[1,0],[1,1],[0,1],[0,0]]
        ]
      },
      "properties": {
        "id": 1,
        "name": "small"
      }
    },
    {
      "type": "Feature",
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [[0,0],[10,0],[10,10],[0,10],[0,0]]
        ]
      },
      "properties": {
        "id": 2,
        "name": "big"
      }
    }
  ]
}

If you need to add an extra property, like the area in square units by using st_area(area), you could add a generated column to the table and it will appear in the properties key of each Feature.

alter table coverage
  add square_units double precision generated always as ( st_area(area) ) stored;

In the case that you are using older PostGIS versions, then creating a function is your best option:

create or replace function coverage_geo_collection() returns json as $$
  select
    json_build_object(
      'type', 'FeatureCollection',
      'features', json_agg(
        json_build_object(
          'type', 'Feature',
          'geometry', st_AsGeoJSON(c.area)::json,
          'properties', json_build_object('id', c.id, 'name', c.name)
        )
      )
    )
  from coverage c;
$$ language sql;

Now this query will return the same results:

curl "http://localhost:3000/rpc/coverage_geo_collection"
{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [[0,0],[1,0],[1,1],[0,1],[0,0]]
        ]
      },
      "properties": {
        "id": 1,
        "name": "small"
      }
    },
    {
      "type": "Feature",
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [[0,0],[10,0],[10,10],[0,10],[0,0]]
        ]
      },
      "properties": {
        "id": 2,
        "name": "big"
      }
    }
  ]
}

Ranges

PostgREST allows you to handle ranges.

create table events (
  id int primary key,
  name text unique,
  duration tsrange
);

To insert a new event, specify the duration value as a string representation of the tsrange type:

curl "http://localhost:3000/events" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  {
    "id": 1,
    "name": "New Year's Party",
    "duration": "['2022-12-31 11:00','2023-01-01 06:00']"
  }
EOF

You can use range operators to filter the data. But, in this case, requesting a filter like events?duration=cs.2023-01-01 will return an error, because PostgreSQL needs an explicit cast from string to timestamp. A workaround is to use a range starting and ending in the same date:

curl "http://localhost:3000/events?duration=cs.\[2023-01-01,2023-01-01\]"
[
  {
    "id": 1,
    "name": "New Year's Party",
    "duration": "[\"2022-12-31 11:00:00\",\"2023-01-01 06:00:00\"]"
  }
]

Casting a Range to a JSON Object

As you may have noticed, the tsrange value is returned as a string literal. To return it as a JSON value, first you need to create a function that will do the conversion from a tsrange type:

create or replace function tsrange_to_json(tsrange) returns json as $$
  select json_build_object(
    'lower', lower($1)
  , 'upper', upper($1)
  , 'lower_inc', lower_inc($1)
  , 'upper_inc', upper_inc($1)
  );
$$ language sql;

Then, create the cast using this function:

create cast (tsrange as json) with function tsrange_to_json(tsrange) as assignment;

Finally, do the request casting the range column:

curl "http://localhost:3000/events?select=id,name,duration::json"
[
  {
    "id": 1,
    "name": "New Year's Party",
    "duration": {
      "lower": "2022-12-31T11:00:00",
      "upper": "2023-01-01T06:00:00",
      "lower_inc": true,
      "upper_inc": true
    }
  }
]

Note

If you don’t want to modify casts for built-in types, an option would be to create a custom type for your own tsrange and add its own cast.

create type mytsrange as range (subtype = timestamp, subtype_diff = tsrange_subdiff);

-- define column types and casting function analogously to the above example
-- ...

create cast (mytsrange as json) with function mytsrange_to_json(mytsrange) as assignment;

Timestamps

You can use the time zone to filter or send data if needed.

create table reports (
  id int primary key
  , due_date timestamptz
);

Suppose you are located in Sydney and want create a report with the date in the local time zone. Your request should look like this:

curl "http://localhost:3000/reports" \
  -X POST -H "Content-Type: application/json" \
  -d '[{ "id": 1, "due_date": "2022-02-24 11:10:15 Australia/Sydney" },{ "id": 2, "due_date": "2022-02-27 22:00:00 Australia/Sydney" }]'

Someone located in Cairo can retrieve the data using their local time, too:

curl "http://localhost:3000/reports?due_date=eq.2022-02-24+02:10:15+Africa/Cairo"
[
  {
    "id": 1,
    "due_date": "2022-02-23T19:10:15-05:00"
  }
]

The response has the date in the time zone configured by the server: UTC -05:00 (see Timezone).

You can use other comparative filters and also all the PostgreSQL special date/time input values as illustrated in this example:

curl "http://localhost:3000/reports?or=(and(due_date.gte.today,due_date.lte.tomorrow),and(due_date.gt.-infinity,due_date.lte.epoch))"
[
  {
    "id": 2,
    "due_date": "2022-02-27T06:00:00-05:00"
  }
]