表和视图#

的所有表和视图 exposed schema 并可由 active database role 可供查询。它们暴露在一级深的路径中。

例如，表格的全部内容 people 返回的地址为

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

没有深度/嵌套/路线。每条路由完全根据数据库权限提供GET、HEAD、POST、PATCH和DELETE等选项。

备注

为什么不提供嵌套路线呢？许多API允许嵌套来检索相关信息，例如 /films/1/director 。我们提供了一种更灵活的机制(灵感来自GraphQL)来嵌入相关资源。有关此内容，请参阅资源嵌入。

朗读#

拿起头来#

使用Get方法，您可以检索表和视图行。默认设置响应格式是JSON。

Head方法的行为与GET相同，不同之处在于不会返回响应正文 (RFC 2616 )。作为优化，生成的查询不会执行聚合(以避免不必要的数据传输)。

水平滤波#

您可以通过在列上添加条件来筛选结果行。例如，遣返13岁以下的人：

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

通过添加更多查询字符串参数，可以对列计算多个条件。例如，将18岁或以上的人送回 and 是否有学生：

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

操作员#

这些运算符可用：

缩略语	在PostgreSQL中	含义
情商	`=`	相等
燃气轮机	`>`	大于
GTE	`>=`	大于或等于
它	`<`	少于
LTE	`<=`	小于或等于
NEQ	`<>` or `!=`	不相等
喜欢	`LIKE`	LIKE运算符(避免 URL encoding 您可以使用 `*` 作为百分号的别名 `%` 对于图案)
我喜欢	`ILIKE`	ILike运算符(避免 URL encoding 您可以使用 `*` 作为百分号的别名 `%` 对于图案)
匹配	`~`	~操作符，请参见模式匹配
IMatch	`~*`	~*操作符，请参见模式匹配
在……里面	`IN`	值列表中的一个，例如 `?a=in.(1,2,3)` -还支持带引号的字符串中的逗号，如 `?a=in.("hi,there","yes,you")`
是	`IS`	检查是否完全相等(NULL、TRUE、FALSE、UNKNOWN)
是不同的	`IS DISTINCT FROM`	不平等，对待 `NULL` 作为可比价值
FTS	`@@`	全文搜索使用TO_T查询
普洛夫茨	`@@`	全文搜索使用普通查询
PHFTS	`@@`	全文搜索使用短语to_tsquery
WFTS	`@@`	全文搜索使用WebSearch_to_tSquery
政务司司长	`@>`	包含例如 `?tags=cs.{example, new}`
CD	`<@`	包含在例如 `?values=cd.{1,2,3}`
奥夫	`&&`	重叠(有共同点)，例如 `?period=ov.[2017-01-01,2017-06-30]` -也支持数组类型，使用大括号而不是方括号，例如：代码： ?arr=ov.{1,3}
服务级别	`<<`	严格地留在…的左边，例如 `?range=sl.(1,10)`
高级	`>>`	严格地说是正确的
NXR	`&<`	没有延伸到…的右边，例如 `?range=nxr.(1,10)`
NXL	`&>`	不会延伸到
调整	`-\|-`	毗邻，例如 `?range=adj.(1,10)`
不	`NOT`	取反另一个运算符，请参见逻辑运算符
或	`OR`	逻辑化 `OR` ，见逻辑运算符
和	`AND`	逻辑化 `AND` ，见逻辑运算符
全	`ALL`	比较与列表中的所有值匹配，请参见运算符修饰符
任何	`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岁以下的人送回 or 21岁以上：

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

至 negate 任何运算符，您都可以在其前面加上 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))"

运算符修饰符#

您可以使用 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 docs 。

为确保在较大数据集上实现最佳性能， appropriate index 即使在那时，也取决于模式值和实际数据统计信息，查询规划器是否将使用现有索引。

全文搜索#

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

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"

vbl.使用 websearch_to_tsquery 需要至少11.0版的PostgreSQL，并且会在较早版本的数据库中引发错误。

垂直过滤#

当某些列很宽时(例如那些保存二进制数据的列)，服务器在响应中保留它们会更有效。属性指定需要哪些列。 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 or jsonb column using the arrow operators(-> or ->>) as per the PostgreSQL docs 。

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

[
  {
    "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" ，它确认查询规划器正在使用该索引。

插入#

所有表格和 auto-updatable views 可以通过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-urlended#

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

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而建。

重要

建议您 use triggers instead of rules 。在复杂的视图上插入 rules 由于使用了CTE，PostgREST可能无法开箱即用。如果希望继续使用规则，一种解决办法是将视图插入包装在函数中，并通过作为RPC的功能界面。有关更多详细信息，请参阅此 github issue 。

大容量插入#

除了提供具有统一键的JSON对象数组或CSV格式的行之外，BULK INSERT的工作方式与单行INSERT完全相同。这不仅最大限度地减少了所需的HTTP请求，而且为了提高效率，在后端使用了一条INSERT语句。

要批量插入CSV，只需使用以下命令将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。

更新#

若要更新表中的一行或多行，请使用补丁动词。使用水平滤波指定要更新的记录(S)。下面是一个设置 category 列到孩子，为所有低于一定年龄的人。

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

更新还支持返回表示法，资源嵌入和垂直过滤。

警告

注意不要意外更新表中的每一行。要学会防止这种情况发生，请参阅数据块满表操作。

向上插入#

你可以用来做个插曲 POST 以及 Prefer: resolution=merge-duplicates 标题：

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序列主键”)，也可以使用它。有关更多详细信息，请阅读 this issue 。

重要

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

论冲突#

通过指定 on_conflict 查询参数，您可以在具有唯一约束的列(S)上进行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 ：

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 在一个独特的专栏上(S)。

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 系统列。

vbl.使用 offset 也可以以不同的行子集为目标。

备注

没有原住民 UPDATE...LIMIT 或 DELETE...LIMIT 在PostgreSQL中支持；生成的查询模拟该行为，并基于 this Crunchy Data blog post 。