表和视图

所有公开模式中的表和视图，以及当前数据库角色可访问的表和视图，都可以用于查询。它们以单层深度路由的方式公开。

例如，表 people 的完整内容将在以下地址返回

curl "http://localhost:3000/people"

没有深层/嵌套/路由。每个路由根据数据库权限提供 OPTIONS、GET、HEAD、POST、PATCH 和 DELETE 方法。

注意

为什么不提供嵌套路由？许多 API 允许嵌套以检索相关信息，例如 /films/1/director。我们提供了一种更灵活的机制（受 GraphQL 启发）来嵌入相关资源。这将在资源嵌入中介绍。

读取

GET 和 HEAD

使用 GET 方法，您可以检索表和视图的行。默认的响应格式为 JSON。

HEAD 方法的行为与 GET 相同，只是不返回响应主体 (RFC 2616)。作为优化，生成的查询不会执行聚合（以避免不必要的数据传输）。

水平过滤

您可以通过在列上添加条件来过滤结果行。例如，要返回年龄小于 13 岁的人员

curl "http://localhost:3000/people?age=lt.13"

您可以通过添加更多查询字符串参数来评估列上的多个条件。例如，要返回年龄在 18 岁或以上 **并且** 是学生的人员

curl "http://localhost:3000/people?age=gte.18&student=is.true"

运算符

这些运算符可用

缩写	在 PostgreSQL 中	含义
eq	`=`	等于
gt	`>`	大于
gte	`>=`	大于或等于
lt	`<`	小于
lte	`<=`	小于或等于
neq	`<>` 或 `!=`	不等于
like	`LIKE`	LIKE 运算符（为了避免 URL 编码，您可以使用 `*` 作为百分号 `%` 的别名来表示模式）
ilike	`ILIKE`	ILIKE 运算符（为了避免 URL 编码，可以使用 `*` 作为百分号 `%` 的别名）
匹配	`~`	~ 运算符，参见模式匹配
imatch	`~*`	~* 运算符，参见模式匹配
in	`IN`	值列表中的一个，例如 `?a=in.(1,2,3)` - 也支持带引号字符串中的逗号，例如 `?a=in.("hi,there","yes,you")`
is	`IS`	检查精确相等（null、true、false、unknown）
isdistinct	`IS DISTINCT FROM`	不相等，将 `NULL` 视为可比较的值
fts	`@@`	使用 to_tsquery 进行全文搜索
plfts	`@@`	使用 plainto_tsquery 进行全文搜索
phfts	`@@`	使用 phraseto_tsquery 进行全文搜索
wfts	`@@`	使用 websearch_to_tsquery 进行全文搜索
cs	`@>`	包含，例如 `?tags=cs.{example, new}`
cd	`<@`	包含于，例如 `?values=cd.{1,2,3}`
ov	`&&`	重叠（有共同点），例如 `?period=ov.[2017-01-01,2017-06-30]` - 也支持数组类型，使用大括号代替方括号，例如：code: ?arr=ov.{1,3}
sl	`<<`	严格左于，例如 `?range=sl.(1,10)`
sr	`>>`	严格右于
nxr	`&<`	不扩展到右侧，例如 `?range=nxr.(1,10)`
nxl	`&>`	不扩展到左侧
adj	`-\|-`	相邻，例如 `?range=adj.(1,10)`
not	`NOT`	否定另一个运算符，参见逻辑运算符
or	`OR`	逻辑 `OR`，参见逻辑运算符
and	`AND`	逻辑 `AND`，参见逻辑运算符
all	`ALL`	比较匹配列表中的所有值，参见运算符修饰符
any	`ANY`	比较匹配列表中的任何值，参见运算符修饰符

对于更复杂的过滤器，您需要在数据库中创建一个新视图，或使用函数。例如，以下是一个显示“今日故事”的视图，其中可能包含较旧的固定故事

CREATE VIEW fresh_stories AS
SELECT *
  FROM stories
 WHERE pinned = true
    OR published > now() - interval '1 day'
ORDER BY pinned DESC, published DESC;

该视图将提供一个新的端点

curl "http://localhost:3000/fresh_stories"

逻辑运算符

默认情况下，列上的多个条件使用 AND 评估，但您可以使用 OR 运算符使用 or 将它们组合起来。例如，要返回年龄小于 18 岁 **或** 大于 21 岁的用户

curl "http://localhost:3000/people?or=(age.lt.18,age.gt.21)"

要 **否定** 任何运算符，可以在其前面加上 not，例如 ?a=not.eq.2 或 ?not.and=(a.gte.0,a.lte.100) 。

您还可以对条件应用复杂的逻辑

# curl "http://localhost:3000/people?grade=gte.90&student=is.true&or=(age.eq.14,not.and(age.gte.11,age.lte.17))"

curl --get "http://localhost:3000/people" \
  -d "grade=gte.90" \
  -d "student=is.true" \
  -d "or=(age.eq.14,not.and(age.gte.11,age.lte.17))"

如果过滤器值包含保留字符，则需要将其用双引号括起来

curl -g 'http://localhost:3000/survey?or=(age_range.adj."[18,21)",age_range.cs."[30,35]")'

运算符修饰符

您可以使用 any/all 修饰符进一步简化 eq,like,ilike,gt,gte,lt,lte,match,imatch 的逻辑。

例如，为了避免重复相同的列用于 or，可以使用 any 来获取姓氏以 O 或 P 开头的用户。

curl -g "http://localhost:3000/people?last_name=like(any).{O*,P*}"

类似地，您可以使用 all 来避免重复相同的列用于 and。要获取姓氏以 O 开头并以 n 结尾的用户。

curl -g "http://localhost:3000/people?last_name=like(all).{O*,*n}"

模式匹配

模式匹配运算符（like、ilike、match、imatch）用于支持使用模式而不是具体字符串来过滤数据，如 PostgreSQL 文档中所述。

为了确保在大型数据集上获得最佳性能，应使用适当的索引，即使这样，查询计划程序是否使用现有索引也取决于模式值和实际数据统计信息。

全文搜索

上面提到的 fts 过滤器有许多选项来支持灵活的文本查询，即选择纯文本搜索与短语搜索以及用于词干提取的语言。假设 tsearch 是一个包含类型为 tsvector 的列 my_tsv 的表。以下示例说明了这些可能性。

curl "http://localhost:3000/tsearch?my_tsv=fts(french).amusant"

curl "http://localhost:3000/tsearch?my_tsv=plfts.The%20Fat%20Cats"

curl "http://localhost:3000/tsearch?my_tsv=not.phfts(english).The%20Fat%20Cats"

curl "http://localhost:3000/tsearch?my_tsv=not.wfts(french).amusant"

使用 websearch_to_tsquery 需要 PostgreSQL 版本至少为 11.0，在更早版本的数据库中会引发错误。

垂直过滤

当某些列很宽（例如那些保存二进制数据的列）时，服务器在响应中保留它们效率更高。客户端可以使用 select 参数指定需要哪些列。

curl "http://localhost:3000/people?select=first_name,age"

[
  {"first_name": "John", "age": 30},
  {"first_name": "Jane", "age": 20}
]

默认值为 *，表示所有列。此值将在下面资源嵌入中变得更加重要。

重命名列

您可以通过在列名前添加别名，然后使用冒号 : 运算符来重命名列。

curl "http://localhost:3000/people?select=fullName:full_name,birthDate:birth_date"

[
  {"fullName": "John Doe", "birthDate": "04/25/1988"},
  {"fullName": "Jane Doe", "birthDate": "01/12/1998"}
]

JSON 列

为了进一步减少传输的数据量，您可以使用箭头运算符（-> 或 ->>）指定 json 或 jsonb 列的路径，具体请参考 PostgreSQL 文档。

CREATE TABLE people (
  id int,
  json_data json
);

curl "http://localhost:3000/people?select=id,json_data->>blood_type,json_data->phones"

[
  { "id": 1, "blood_type": "A-", "phones": [{"country_code": "61", "number": "917-929-5745"}] },
  { "id": 2, "blood_type": "O+", "phones": [{"country_code": "43", "number": "512-446-4988"}, {"country_code": "43", "number": "213-891-5979"}] }
]

curl "http://localhost:3000/people?select=id,json_data->phones->0->>number"

[
  { "id": 1, "number": "917-929-5745"},
  { "id": 2, "number": "512-446-4988"}
]

这在过滤器中也适用。

curl "http://localhost:3000/people?select=id,json_data->blood_type&json_data->>blood_type=eq.A-"

[
  { "id": 1, "blood_type": "A-" },
  { "id": 3, "blood_type": "A-" },
  { "id": 7, "blood_type": "A-" }
]

请注意，->> 用于将 blood_type 作为 text 进行比较。要与整数值进行比较，请使用 ->。

curl "http://localhost:3000/people?select=id,json_data->age&json_data->age=gt.20"

[
  { "id": 11, "age": 25 },
  { "id": 12, "age": 30 },
  { "id": 15, "age": 35 }
]

排序也受支持。

curl "http://localhost:3000/people?select=id,json_data->age&order=json_data->>age.desc"

[
  { "id": 15, "age": 35 },
  { "id": 12, "age": 30 },
  { "id": 11, "age": 25 }
]

复合/数组列

箭头运算符（->，->>）也可以用于访问复合字段和数组元素。

CREATE TYPE coordinates (
  lat decimal(8,6),
  long decimal(9,6)
);

CREATE TABLE countries (
  id int,
  location coordinates,
  languages text[]
);

# curl "http://localhost:3000/countries?select=id,location->>lat,location->>long,primary_language:languages->0&location->lat=gte.19"

curl --get "http://localhost:3000/countries" \
  -d "select=id,location->>lat,location->>long,primary_language:languages->0" \
  -d "location->lat=gte.19"

[
  {
    "id": 5,
    "lat": "19.741755",
    "long": "-155.844437",
    "primary_language": "en"
  }
]

重要

在复合和数组列上使用 -> 和 ->> 运算符时，PostgREST 会使用类似 to_jsonb(<col>)->'field' 的查询。为了在这些嵌套字段上进行过滤和排序，需要使用索引，索引需要在相同的表达式上创建，包括 to_jsonb(...) 调用。

CREATE INDEX ON mytable ((to_jsonb(data) -> 'identification' ->> 'registration_number'));

强制转换列

可以通过在列后面添加双冒号 :: 加上所需类型来强制转换列。

curl "http://localhost:3000/people?select=full_name,salary::text"

[
  {"full_name": "John Doe", "salary": "90000.00"},
  {"full_name": "Jane Doe", "salary": "120000.00"}
]

注意

为了防止使索引使用无效，不允许在水平过滤上进行强制转换。要执行此操作，您可以使用计算字段。

排序

保留字 order 用于重新排序响应行。它使用逗号分隔的列和方向列表。

curl "http://localhost:3000/people?order=age.desc,height.asc"

如果没有指定方向，则默认为升序。

curl "http://localhost:3000/people?order=age"

如果您关心空值排序的位置，请添加 nullsfirst 或 nullslast。

curl "http://localhost:3000/people?order=age.nullsfirst"

curl "http://localhost:3000/people?order=age.desc.nullslast"

您也可以对复合/数组列或 JSON 列的字段进行排序。

curl "http://localhost:3000/countries?order=location->>lat"

索引使用

在使用水平过滤、垂直过滤和排序时，索引会透明地工作。例如，当有

create index salary_idx on employees (salary);

我们可以通过获取执行计划来确认对员工的过滤使用了索引。

curl 'localhost:3000/employees?salary=eq.36000' -H "Accept: application/vnd.pgrst.plan"

Aggregate  (cost=9.52..9.54 rows=1 width=144)
  ->  Bitmap Heap Scan on employees  (cost=4.16..9.50 rows=2 width=136)
        Recheck Cond: (salary = '$36,000.00'::money)
        ->  Bitmap Index Scan on salary_idx  (cost=0.00..4.16 rows=2 width=0)
              Index Cond: (salary = '$36,000.00'::money)

在那里我们可以看到“Index Cond”，它确认索引被查询规划器使用。

插入

所有表和自动可更新视图都可以通过 API 修改，但需遵守请求者数据库角色的权限。

要在数据库表中创建一行，请发布一个 JSON 对象，其键是您要创建的列的名称。如果适用，缺失的属性将设置为默认值。

curl "http://localhost:3000/table_name" \
  -X POST -H "Content-Type: application/json" \
  -d '{ "col1": "value1", "col2": "value2" }'

HTTP/1.1 201 Created

默认情况下不会返回任何响应主体，但您可以使用返回表示来获取受影响的资源，并使用资源嵌入来添加相关资源。

x-www-form-urlencoded

可以使用 Content-Type: application/x-www-form-urlencoded 发布 URL 编码的有效负载。

curl "http://localhost:3000/people" \
  -X POST -H "Content-Type: application/x-www-form-urlencoded" \
  -d "name=John+Doe&age=50&weight=80"

注意

插入行时，必须发布 JSON 对象，而不是带引号的 JSON。

Yes
{ "a": 1, "b": 2 }

No
"{ \"a\": 1, \"b\": 2 }"

如果您不小心，一些 JavaScript 库会错误地发布数据。为了获得最佳效果，请尝试使用为 PostgREST 构建的客户端库之一。

重要

建议您使用触发器而不是规则。由于 PostgREST 使用了 CTE，因此在具有复杂规则的视图上进行插入操作可能无法开箱即用。如果您想继续使用规则，则可以使用一个变通方法，将视图插入操作包装在一个函数中，并通过函数作为 RPC 接口调用它。有关更多详细信息，请参阅此github 问题。

批量插入

批量插入的工作原理与单行插入完全相同，只是您需要提供一个具有统一键的 JSON 对象数组，或者提供 CSV 格式的行。这不仅可以最大限度地减少所需的 HTTP 请求，而且还可以使用后端上的单个 INSERT 语句来提高效率。

要批量插入 CSV，只需将 Content-Type: text/csv 发送到表路由，并将列名作为第一行包含在内。例如

curl "http://localhost:3000/people" \
  -X POST -H "Content-Type: text/csv" \
  --data-binary @- << EOF
name,age,height
J Doe,62,70
Jonas,10,55
EOF

空字段 (,,) 将被强制转换为空字符串，保留字 NULL 将被映射到 SQL 空值。请注意，列名和逗号之间不应有空格。

要批量插入 JSON，请发布一个具有所有匹配键的对象数组

curl "http://localhost:3000/people" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  [
    { "name": "J Doe", "age": 62, "height": 70 },
    { "name": "Janus", "age": 10, "height": 55 }
  ]
EOF

使用默认值进行批量插入

有效载荷中任何缺失的列都将被插入为 null 值。要使用 DEFAULT 列值，请使用 Prefer: missing=default 标头。

有

create table foo (
  id bigint generated by default as identity primary key
, bar text
, baz int default 100
);

一个请求

curl "http://localhost:3000/foo?columns=id,bar,baz" \
  -H "Content-Type: application/json" \
  -H "Prefer: missing=default, return=representation" \
  -d @- << EOF
    [
      { "bar": "val1" },
      { "bar": "val2", "baz": 15 }
    ]
EOF

将导致

[
  { "id":  1, "bar": "val1", "baz": 100 },
  { "id":  2, "bar": "val2", "baz": 15 }
]

指定列

通过使用 columns 查询参数，可以指定将要插入的有效载荷键，并忽略有效载荷的其余部分。

curl "http://localhost:3000/datasets?columns=source,publication_date,figure" \
  -X POST -H "Content-Type: application/json" \
  -d @- << EOF
  {
    "source": "Natural Disaster Prevention and Control",
    "publication_date": "2015-09-11",
    "figure": 1100,
    "location": "...",
    "comment": "...",
    "extra": "...",
    "stuff": "..."
  }
EOF

在这种情况下，只有 **source**、**publication_date** 和 **figure** 将被插入。JSON 键的其余部分将被忽略。

使用此方法也有一个副作用，即对于批量插入来说效率更高，因为 PostgREST 不会处理 JSON，而是会将其直接发送到 PostgreSQL。

更新

要更新表中的一行或多行，请使用 PATCH 方法。使用水平过滤指定要更新的记录。以下是一个将 category 列设置为所有年龄低于某个年龄的人的子项的示例查询。

curl "http://localhost:3000/people?age=lt.13" \
  -X PATCH -H "Content-Type: application/json" \
  -d '{ "category": "child" }'

更新也支持返回表示、资源嵌入和垂直过滤。

警告

小心不要意外地更新表中的每一行。要了解如何防止这种情况，请参阅阻止全表操作。

Upsert

您可以使用 POST 和 Prefer: resolution=merge-duplicates 头部进行 upsert 操作。

curl "http://localhost:3000/employees" \
  -X POST -H "Content-Type: application/json" \
  -H "Prefer: resolution=merge-duplicates" \
  -d @- << EOF
  [
    { "id": 1, "name": "Old employee 1", "salary": 30000 },
    { "id": 2, "name": "Old employee 2", "salary": 42000 },
    { "id": 3, "name": "New employee 3", "salary": 50000 }
  ]
EOF

默认情况下，upsert 操作基于主键列，您必须指定所有主键列。您也可以选择使用 Prefer: resolution=ignore-duplicates 忽略重复项。当主键是自然主键时，此方法最有效，但如果主键是代理主键（例如：“id serial primary key”），也可以使用它。有关更多详细信息，请阅读此问题。

重要

在创建表或更改其主键后，您必须刷新 PostgREST 架构缓存，才能使 upsert 正确工作。要了解如何刷新缓存，请参阅架构缓存重新加载。

冲突时

通过指定 on_conflict 查询参数，您可以使 upsert 操作在具有唯一约束的列上工作。

curl "http://localhost:3000/employees?on_conflict=name" \
  -X POST -H "Content-Type: application/json" \
  -H "Prefer: resolution=merge-duplicates" \
  -d @- << EOF
  [
    { "name": "Old employee 1", "salary": 40000 },
    { "name": "Old employee 2", "salary": 52000 },
    { "name": "New employee 3", "salary": 60000 }
  ]
EOF

PUT

可以使用 PUT 并使用 eq 过滤主键列来执行单行 upsert 操作。

curl "http://localhost/employees?id=eq.4" \
  -X PUT -H "Content-Type: application/json" \
  -d '{ "id": 4, "name": "Sara B.", "salary": 60000 }'

请求主体中必须指定所有列，包括主键列。

删除

要删除表中的行，请使用 DELETE 动词加上水平过滤。例如，删除非活动用户

curl "http://localhost:3000/user?active=is.false" -X DELETE

删除操作也支持返回表示、资源嵌入和垂直过滤。

curl "http://localhost:3000/user?id=eq.1" -X DELETE \
  -H "Prefer: return=representation"

{"id": 1, "email": "johndoe@email.com"}

警告

小心不要意外地删除表中的所有行。要了解如何防止这种情况，请参阅阻止全表操作。

有限更新/删除

您可以使用 limit 查询参数来限制更新或删除影响的行数。为此，您必须在唯一列上添加显式的 order。

curl -X PATCH "/users?limit=10&order=id&last_login=lt.2020-01-01" \
  -H "Content-Type: application/json" \
  -d '{ "status": "inactive" }'

curl -X DELETE "http://localhost:3000/users?limit=10&order=id&status=eq.inactive"

如果您的表没有唯一列，您可以使用 ctid 系统列。

也可以使用 offset 来定位不同的行子集。

注意

PostgreSQL 中没有原生 UPDATE...LIMIT 或 DELETE...LIMIT 支持；生成的查询模拟了这种行为，并且基于这篇 Crunchy Data 博客文章。