Working with PostgreSQL data types
- author:
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" }]
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:
GET /reports?due_date=eq.2022-02-24+02:10:15+Africa/Cairo HTTP/1.1
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
.
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
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"
}
]
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
}
}
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.
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}"
}
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:
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"]
}
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:
PATCH /movies?id=eq.1 HTTP/1.1
Content-Type: application/json
{
"cinema_floor_auditorium": [ [ [1,2], [6,7] ], [ [3,5], [8,9] ] ]
}
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:
GET /movies?select=title,auditorium:cinema_floor_auditorium->0->1&id=eq.1 HTTP/1.1
curl "http://localhost:3000/movies?select=title,auditorium:cinema_floor_auditorium->0->1&id=eq.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\")" }
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.
POST /products HTTP/1.1
Content-Type: application/json
{
"id": 2,
"size": {
"length": 0.7,
"width": 0.5,
"height": 1.8,
"unit": "m"
}
}
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.
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']"
}
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:
GET /events?duration=cs.[2023-01-01,2023-01-01] HTTP/1.1
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:
GET /events?select=id,name,duration::json HTTP/1.1
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;
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
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, 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
curl "http://localhost:3000/files?select=file&id=eq.1" \
-H "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
curl "http://localhost:3000/files?select=file&id=eq.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.
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" }
]
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:
GET /countries?select=name->>native&name->>common=like.Egypt HTTP/1.1
curl "http://localhost:3000/countries?select=name->>native&name->>common=like.Egypt"
[{ "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))" }
]
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:
GET /coverage HTTP/1.1
Accept: application/geo+json
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:
GET /rpc/coverage_geo_collection HTTP/1.1
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"
}
}
]
}