使用PostgreSQL数据类型

作者:

Laurence Isla

PostgREST使用PostgreSQL字符串表示来处理数据类型。多亏了这一点，您可以使用特殊值，例如 now 对于时间戳， yes 用于布尔值或包括时区的时间值。本页介绍如何利用这些字符串表示法和一些替代方法来对不同的PostgreSQL数据类型执行操作。

数组

处理 array types 您可以使用字符串表示法或JSON数组格式。

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

您可以使用字符串表示法插入新值。

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

或者，您可以使用JSON数组格式发送相同的数据：

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

要查询数据，可以使用箭头运算符。看见 复合/数组列 。

多维数组

与一维数组类似，字符串表示和JSON数组格式都是允许的。

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

现在可以使用JSON数组格式更新项目：

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

然后，例如，要查询位于第一个电影院(数组中的位置0)和二楼(下一个内部数组中的位置1)的观众席，我们可以这样使用箭头运算符：

curl "http://localhost:3000/movies?select=title,auditorium:cinema_floor_auditorium->0->1&id=eq.1"

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

Bytea

要将原始二进制文件发送到PostgREST，您需要一个带有单个未命名参数的函数 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;

让我们为我们的测试下载PostgREST徽标。

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

现在，要发送文件 postgrest-logo.png 我们需要设置 Content-Type: application/octet-stream 请求中的Header：

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

要从数据库中获取图像，请使用 媒体类型处理程序 如下所示：

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"

看见 为以下项目提供图像 <img> 获取有关如何在HTML中处理图像的分步示例。

警告

在数据库中保存二进制文件时要小心，在大多数情况下，最好为这些文件提供单独的存储服务。看见 Storing Binary files in the Database 。

复合类型

使用PostgREST，您有两个选项需要处理 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")');

一方面，您可以使用字符串表示法插入值。

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

或者，您可以以JSON格式插入相同的数据。

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

您还可以使用箭头运算符查询数据。看见 复合/数组列 。

枚举

你可以处理的 Enumerated Types 使用字符串表示法：

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

要插入或更新值，请使用字符串：

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

然后，您可以使用兼容的 operators 。例如，要获得所有大于 m 并按其大小进行排序：

curl "http://localhost:3000/products?select=name,size&size=gt.m&order=size"

[
  {
    "name": "t-shirt",
    "size": "l"
  },
  {
    "name": "hoodie",
    "size": "xl"
  }
]

Hstore

您可以使用属于其他提供的模块的数据类型，例如 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
);

这个 name 列将以不同的格式显示国家/地区名称。您可以使用该数据类型的字符串表示形式插入值：

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

请注意，使用 " 在价值上， name 列需要使用反斜杠进行转义 \ 。

您还可以查询和筛选 hstore 列使用箭头运算符，就像对 JSON column 。例如，如果要获取埃及的本地名称：

curl "http://localhost:3000/countries?select=name->>native&name->>common=like.Egypt"

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

JSON

要使用 json 类型列中，您可以将该值作为JSON对象进行处理。

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

可以使用的JSON对象插入新产品 extra_info 专栏：

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

要查询和筛选数据，请参见 JSON列 以获得完整的参考资料。

邮政地理信息系统

您可以使用字符串表示形式 PostGIS 数据类型如 geometry 或 geography (您需要 install PostGIS 第一个)。

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

要以多边形格式添加区域，可以使用字符串表示法：

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

现在，当您请求信息时，PostgREST将自动将 area 列放入一个 Polygon 几何图形类型。尽管这很有用，但您可能需要将整个输出放在 GeoJSON 开箱即用格式，这可以通过包括 Accept: application/geo+json 在请求中。这将适用于PostGIS版本3.0.0及更高版本，并将以 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"
      }
    }
  ]
}

如果需要添加额外的属性，如使用平方单位的面积 st_area(area) ，您可以将生成的列添加到表中，它将出现在 properties 每个键的密钥 Feature 。

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

如果您使用的是较旧的PostGIS版本，则创建函数是您的最佳选择：

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;

现在，此查询将返回相同的结果：

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

范围

PostgREST允许您处理 ranges 。

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

若要插入新事件，请指定 duration 属性的字符串表示形式。 tsrange 类型：

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

您可以使用Range operators 以筛选数据。但是，在本例中，请求像这样的过滤器 events?duration=cs.2023-01-01 将返回错误，因为PostgreSQL需要从字符串到时间戳的显式转换。解决方法是使用开始日期和结束日期相同的范围：

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

将范围强制转换为JSON对象

正如您可能已经注意到的， tsrange 值作为字符串文字返回。要将其作为JSON值返回，首先需要创建一个函数，该函数将从 tsrange 类型：

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;

然后，使用此函数创建强制转换：

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

最后，执行请求 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
    }
  }
]

备注

如果不想修改内置类型的强制转换，可以选择 create a custom type 为你自己 tsrange 并增加自己的演员阵容。

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;

时间戳

您可以使用 time zone 如有需要，可过滤或发送数据。

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

假设您位于悉尼，并且想要创建一个日期为当地时区的报告。您的请求应如下所示：

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

位于开罗的人也可以使用他们当地的时间检索数据：

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

响应具有服务器配置的时区中的日期： UTC -05:00 (见 时区 )。

您可以使用其他比较筛选器以及所有 PostgreSQL special date/time input values 如本例所示：

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