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. Let’s use this table as an example:

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. For instance, let’s use this table:

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

Now, 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. For instance, let’s create the following table:

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

To insert a new value you can use 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 data using a JSON array format. The following request sends the same data as the example above:

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 the arrow operators. See Composite / Array Columns.

Multidimensional Arrays

Handling multidimensional arrays is no different than handling one-dimensional ones: both the string representation and the JSON array format are allowed. For example, let’s add a new column to the table:

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

Now, let’s update the row we inserted before 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] ] ]
}

Now, 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. On one hand you can use string representation and on the other you can handle it as you would a JSON column. Let’s create a type and a table for this example:

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")');

Now, you could insert values using string representation as seen in the example above.

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

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

Or, you could insert the data in JSON format. The following request is equivalent to the previous one:

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 data using the arrow operators. See Composite / Array Columns.

Ranges

To illustrate how to work with ranges, let’s use the following table as an example:

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

Now, to insert a new event, specify the duration value as a string representation of the tsrange type, for example:

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 what if you need get the events for the New Year 2023? Doing this filter events?duration=cs.2023-01-01 will return an error because PostgreSQL needs an explicit cast to timestamp of the string value. A workaround would be to use a range starting and ending in the same date, like this:

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. For example, let’s create a table that will save some files and a function that inserts data to that table:

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;

Next, let’s use 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, you will need to set the Accept: application/octet-stream header in the request and select only the bytea column.

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

You can also use more accurate headers depending on 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. Let’s use the following table:

-- 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, for instance:

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, the query would be:

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. As an example, let’s create a table using the geometry type (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
);

Say you want to add areas in polygon format. The request using string representation would look like:

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. For example:

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