使用 PostgreSQL 数据类型

作者:

Laurence Isla

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

数组

要处理 数组类型，您可以使用字符串表示或 JSON 数组格式。

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

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

curl "https://#: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 "https://#: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 "https://#: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 "https://#:3000/movies?select=title,auditorium:cinema_floor_auditorium->0->1&id=eq.1"

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

Bytea

要将原始二进制数据发送到 PostgREST，您需要一个具有单个未命名参数的函数，该参数的类型为 bytea。

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.postgresql.ac.cn/en/latest/_images/logo.png" -o postgrest-logo.png

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

curl "https://#: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 "https://#:3000/get_image?id=1" \
  -H "Accept: image/png"

请参阅 为 <img> 提供图像，了解有关如何在 HTML 中处理图像的分步示例。

警告

在将二进制数据保存到数据库时要小心，在大多数情况下，最好为这些数据使用单独的存储服务。请参阅 在数据库中存储二进制文件。

复合类型

使用 PostgREST，您可以使用两种方法来处理 复合类型列。

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 "https://#:3000/products" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  { "id": 2, "size": "(0.7,0.5,1.8,\"m\")" }
EOF

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

curl "https://#: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

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

枚举

您可以使用字符串表示法处理 枚举类型

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 "https://#:3000/products" \
  -H "Content-Type: application/json" \
  -d @- << EOF
  { "name": "t-shirt", "size": "l" }
EOF

然后，您可以使用兼容的 运算符 查询和过滤枚举。例如，要获取所有大于 m 的产品并按其大小排序

curl "https://#: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 "https://#: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 列 所做的那样。例如，如果您想获取埃及的本地名称

curl "https://#: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
);

您可以使用 extra_info 列的 JSON 对象插入新产品

curl "https://#: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

您可以使用字符串表示形式来表示 PostGIS 数据类型，例如 geometry 或 geography（您需要先 安装 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 "https://#: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 对象

curl "https://#: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) 的平方单位面积，您可以向表中添加一个生成的列，它将出现在每个 Feature 的 properties 键中。

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 "https://#: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 允许您处理 范围。

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

要插入一个新的事件，请将 duration 值指定为 tsrange 类型的字符串表示形式

curl "https://#: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

您可以使用范围 运算符 来过滤数据。但是，在这种情况下，请求类似 events?duration=cs.2023-01-01 的过滤器将返回错误，因为 PostgreSQL 需要从字符串到时间戳的显式转换。解决方法是使用一个在同一日期开始和结束的范围

curl "https://#: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;

最后，执行请求 强制转换范围列

curl "https://#: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
    }
  }
]

注意

如果您不想修改内置类型的强制转换，可以选择 创建自定义类型 用于您自己的 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;

时间戳

您可以使用 **时区** 来过滤或发送数据（如果需要）。

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

假设您位于悉尼，并希望创建一个使用当地时区的日期报告。您的请求应如下所示

curl "https://#: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 "https://#: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 特殊日期/时间输入值，如本示例所示

curl "https://#: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"
  }
]