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 to perform operations on different PostgreSQL data types.

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:

POST /reports HTTP/1.1
Content-Type: application/json

[{ "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:

GET /reports?due_date=eq.2022-02-24+02:10:15+Africa/Cairo HTTP/1.1

[
  {
    "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.

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

GET /reports?or=(and(due_date.gte.today,due_date.lte.tomorrow),and(due_date.gt.-infinity,due_date.lte.epoch)) HTTP/1.1

[
  {
    "id": 2,
    "due_date": "2022-02-27T06:00:00-05:00"
  }
]

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:

POST /products HTTP/1.1
Content-Type: application/json

{
  "id": 1,
  "name": "Canned fish",
  "extra_info": {
    "expiry_date": "2025-12-31",
    "exportable": true
  }
}

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

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.

POST /movies HTTP/1.1
Content-Type: application/json

{
  "id": 1,
  "title": "Paddington",
  "tags": "{family,comedy,not streamable}",
  "performance_times": "{12:40,15:00,20:00}"
}

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

POST /movies HTTP/1.1
Content-Type: application/json

{
  "id": 1,
  "title": "Paddington",
  "tags": ["family", "comedy", "not streamable"],
  "performance_times": ["12:40", "15:00", "20:00"]
}

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:

PATCH /movies?id=eq.1 HTTP/1.1
Content-Type: application/json

{
  "cinema_floor_auditorium": [ [ [1,2], [6,7] ], [ [3,5], [8,9] ] ]
}

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:

GET /movies?select=title,auditorium:cinema_floor_auditorium->0->1&id=eq.1 HTTP/1.1

[
  {
    "title": "Paddington",
    "auditorium": [6,7]
  }
]

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.

POST /products HTTP/1.1
Content-Type: application/json

{ "id": 2, "size": "(0.7,0.5,1.8,\"m\")" }

Or you could insert the same data in JSON format.

POST /products HTTP/1.1
Content-Type: application/json

  {
    "id": 2,
    "size": {
      "length": 0.7,
      "width": 0.5,
      "height": 1.8,
      "unit": "m"
    }
  }

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

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:

POST /events HTTP/1.1
Content-Type: application/json

{
  "id": 1,
  "name": "New Year's Party",
  "duration": "['2022-12-31 11:00','2023-01-01 06:00']"
}

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:

GET /events?duration=cs.[2023-01-01,2023-01-01] HTTP/1.1

[
  {
    "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:

GET /events?select=id,name,duration::json HTTP/1.1

[
  {
    "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;

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:

POST /rpc/upload_binary HTTP/1.1
Content-Type: application/octet-stream

postgrest-logo.png

To get the image from the database, set the Accept: application/octet-stream header and select only the bytea type column.

GET /files?select=file&id=eq.1 HTTP/1.1
Accept: application/octet-stream

Use more accurate headers according to the type of the files by using the raw-media-types configuration. For example, adding the raw-media-types="image/png" setting to the configuration file will allow you to use the Accept: image/png header:

GET /files?select=file&id=eq.1 HTTP/1.1
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.

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:

POST /countries HTTP/1.1
Content-Type: application/json

[
  { "id": 1, "name": "common => Egypt, official => \"Arab Republic of Egypt\", native => مصر" },
  { "id": 2, "name": "common => Germany, official => \"Federal Republic of Germany\", native => Deutschland" }
]

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:

GET /countries?select=name->>native&name->>common=like.Egypt HTTP/1.1

[{ "native": "مصر" }]

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:

POST /coverage HTTP/1.1
Content-Type: application/json

[
  { "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))" }
]

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:

GET /coverage HTTP/1.1
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:

GET /rpc/coverage_geo_collection HTTP/1.1

{
  "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"
      }
    }
  ]
}