时间戳

首页 > 解决方案 > 如何从数组中删除特定值？

postgresql - 如何从数组中删除特定值？

问题描述

我有一个表，其中有一列存储数组值。我最近被要求从数组中删除一个特定的值，我认为这是一项简单的工作，但我最终迷失在复杂的函数和答案中。

这是表的结构和数据。

CREATE TABLE contacts (
   id serial PRIMARY KEY,
   name VARCHAR (100),
   phones TEXT []
);


INSERT INTO contacts (name, phones)
VALUES
  (
      'John Doe',
      '{"(408)-589-5846","(408)-589-5555"}'
   ),   
   (
      'Lily Bush',
      '{"(408)-589-5841"}'
   ),
   (
      'William Gate',
      '{"(408)-589-5842","(408)-589-58423"}'
   );

这是有人帮助我获取数组的索引值的查询，我希望能够基于该索引以某种方式删除该值，但我找不到方法。因此，例如我想删除John Doe第二个索引处的电话号码，我该如何通过在 where 子句或其他方式中提供索引号来做到这一点？

select c.name, 
       p.phone,
       p.idx
from contacts c
  left join lateral unnest(phones) with ordinality as p(phone, idx) on true;

标签： postgresql

解决方案

您可以使用“切片”表示法获取数组的一部分。phone[2:2]返回第二个元素，如果要删除，则需要将切片“之前”和切片“之后”连接起来，例如phones[1:1]||phones[3:]，如果要删除第 4 个元素，则为phones[1:3]||phones[5:]

如果要更改表，请使用带有该表达式的 UPDATE

update contacts
  set phones = phones[1:1]||phones[3:]
where name = 'John Doe';

如果您愿意，可以将其放入函数中：

create function array_remove_index(p_input anyarray, p_index int)
  returns anyarray
as
$$
  select p_input[1:p_index - 1]||p_input[p_index + 1:];
$$
language sql;

update contacts
  set phones = array_remove_index(phones, 2)
where name = 'John Doe';

像这样的要求通常表明对模型进行非规范化的决定并不是一个好的选择（并且需要通过索引访问事物是一个强烈的迹象）。

从手册中引用

数组不是集合；搜索特定的数组元素可能是数据库设计错误的标志。考虑使用一个单独的表，其中每个项目将是一个数组元素。这将更容易搜索，并且对于大量元素可能会更好地扩展。

数组有时非常方便，但特别是如果您不熟悉 SQL、关系数据库和 Postgres，特别是您应该坚持经过验证的良好规范化数据模型的设计原则。

推荐阅读