PostgREST 中处理 PostgreSQL 数据类型的完整指南

PostgREST 中处理 PostgreSQL 数据类型的完整指南

postgrest PostgREST是一个开源的RESTful API服务器，用于将PostgreSQL数据库暴露为RESTful API。 - 功能：RESTful API服务器；PostgreSQL数据库；RESTful API。 - 特点：易于使用；轻量级；支持多种编程语言；高性能。 项目地址: https://gitcode.com/gh_mirrors/po/postgrest

PostgREST 是一个强大的工具，它能够将 PostgreSQL 数据库直接转换为 RESTful API。在使用过程中，正确处理各种 PostgreSQL 数据类型是关键。本文将全面介绍如何在 PostgREST 中高效处理各种 PostgreSQL 数据类型。

数组类型处理

PostgreSQL 数组类型在 PostgREST 中有两种表示方式：

基本数组操作

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 '{
    "id": 1,
    "title": "Paddington",
    "tags": "{family,comedy,not streamable}",
    "performance_times": "{12:40,15:00,20:00}"
  }'

插入数据方式二（JSON数组格式）：

curl "http://localhost:3000/movies" \
  -X POST -H "Content-Type: application/json" \
  -d '{
    "id": 1,
    "title": "Paddington",
    "tags": ["family", "comedy", "not streamable"],
    "performance_times": ["12:40", "15:00", "20:00"]
  }'

多维数组处理

ALTER TABLE movies
ADD COLUMN cinema_floor_auditorium INT[][][];

插入多维数组数据：

curl "http://localhost:3000/movies?id=eq.1" \
  -X PATCH -H "Content-Type: application/json" \
  -d '{
    "cinema_floor_auditorium": [ [ [1,2], [6,7] ], [ [3,5], [8,9] ] ]
  }'

查询特定维度的数据：

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

二进制数据(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;

上传二进制文件：

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 FUNCTION 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"

注意：虽然可以在数据库中存储二进制数据，但在生产环境中建议使用专门的存储服务。

复合类型处理

复合类型可以表示复杂的数据结构：

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

插入方式一（字符串表示法）：

curl "http://localhost:3000/products" \
  -X POST -H "Content-Type: application/json" \
  -d '{
    "id": 2,
    "size": "(0.7,0.5,1.8,\"m\")"
  }'

插入方式二（JSON对象格式）：

curl "http://localhost:3000/products" \
  -X POST -H "Content-Type: application/json" \
  -d '{
    "id": 2,
    "size": {
      "length": 0.7,
      "width": 0.5,
      "height": 1.8,
      "unit": "m"
    }
  }'

枚举类型处理

枚举类型提供了一种限制字段取值的方式：

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 '{ "name": "t-shirt", "size": "l" }'

查询和过滤：

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

JSON类型处理

PostgreSQL 原生支持 JSON 数据类型：

CREATE TABLE products (
  id INT PRIMARY KEY,
  name TEXT UNIQUE,
  extra_info JSON
);

插入 JSON 数据：

curl "http://localhost:3000/products" \
  -X POST -H "Content-Type: application/json" \
  -d '{
    "id": 1,
    "name": "Canned fish",
    "extra_info": {
      "expiry_date": "2025-12-31",
      "exportable": true
    }
  }'

PostGIS地理空间数据处理

PostGIS 扩展提供了丰富的地理空间数据处理能力：

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

获取 GeoJSON 格式数据：

curl "http://localhost:3000/coverage" \
  -H "Accept: application/geo+json"

范围类型处理

范围类型可以表示一个连续的值区间：

CREATE TABLE events (
  id INT PRIMARY KEY,
  name TEXT UNIQUE,
  duration TSRANGE
);

插入范围数据：

curl "http://localhost:3000/events" \
  -X POST -H "Content-Type: application/json" \
  -d '{
    "id": 1,
    "name": "New Year'\''s Party",
    "duration": "['\''2022-12-31 11:00'\'','\''2023-01-01 06:00'\'']"
  }'

查询范围数据：

curl "http://localhost:3000/events?duration=cs.\[2023-01-01,2023-01-01\]"

时间戳与时区处理

正确处理时间戳和时区对于全球化应用至关重要：

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

使用时区查询：

curl "http://localhost:3000/reports?due_date=eq.2022-02-24+02:10:15+Africa/Cairo"

使用特殊时间值查询：

curl "http://localhost:3000/reports?or=(and(due_date.gte.today,due_date.lte.tomorrow),and(due_date.gt.-infinity,due_date.lte.epoch))"

通过本文的介绍，您应该已经掌握了在 PostgREST 中处理各种 PostgreSQL 数据类型的方法。正确使用这些数据类型可以大大提高应用的灵活性和数据处理能力。

postgrest PostgREST是一个开源的RESTful API服务器，用于将PostgreSQL数据库暴露为RESTful API。 - 功能：RESTful API服务器；PostgreSQL数据库；RESTful API。 - 特点：易于使用；轻量级；支持多种编程语言；高性能。 项目地址: https://gitcode.com/gh_mirrors/po/postgrest