使用regex拆分PostgreSQL行

Question

我有一个表，它有列ID、名称和描述。表看起来如下：

ID    |  Name | Model
1     |  Ford | Focus 3
2.1-3 |  Opel | 1. Astra 2
      |       | 2. Vectra 2
      |       | 3. Vectra 3
3.1-2 | Toyota| Avensis 2; Micra 
4.1-2 |  Opel | (various versions) 1. Astra
      |       | (various versions) 2. Vectra
5.1-3 | Mazda | MX5; GTR; MX4

我希望使用"regexp_split_to_table“将其拆分为新的行，以获得如下结果：

ID    |  Name | Description
1     |  Ford | Focus 3
2.1   |  Opel | Astra 2
2.2   |  Opel | Vectra 2
2.3   |  Opel | Vectra 3
3.1   | Toyota| Avensis
3.2   | Toyota| Micra
4.1   |  Opel | Astra
4.2   |  Opel | Vectra
5.1   | Mazda | MX5
5.2   | Mazda | GTR
5.3   | Mazda | MX4

如何使用postresql并在此之后更新主表？

非常感谢你的帮助！

Answer 1

一般来说，由于以下几个原因，这是不可能的：

您的数据是没有排序的，对于您的示例来说，有可能有类似的

ID    |  Name | Description
      |       | (year)  2. 2004
1     |  Ford | Some text
5.1-3 | Mazda | Petrol; 1.9; 3-doors
      |       | 2. Diesel
2.1-3 |  Opel | 1. Astra
      |       | 3. 2005
3     | Toyota| 2001; Petrol; 1.8 TDI
4.1-2 |  Opel | (model) 1. Vectra

就试着像这样

drop table if exists my_ugly_table;
create table my_ugly_table as select generate_series(1,3) as x, generate_series(1,3) as y;
select * from my_ugly_table;
update my_ugly_table set y = 4 where x = 2;
select * from my_ugly_table;

你就会有

第一项结果：

 x | y 
---+---
 1 | 1
 2 | 2
 3 | 3
(3 rows)

第二项结果：

 x | y 
---+---
 1 | 1
 3 | 3
 2 | 4
(3 rows)

如您所见，行顺序发生了更改。

接下来，您的目标是错误的，您希望在ID中保存一些有价值的数据，如1.2, 1.3等等。ID只能是行的唯一标识符，而不是其他任何标识符。理想情况下，您对ID的值没有任何了解-它们只是存在。

但是，您可以尝试使用plpgsql对原始数据执行一些操作，或者类似于：

首先，创建表，在该表中我们将对数据执行一些糟糕的操作：

create table models_t as select * from models m where 1=2;

它将创建结构与表models_t相同的空表models。

最后，让我们为他们创建真正的PK：

alter table models_t add mt_id serial not null primary key; 

接下来，让我们用数据填充它：

do language plpgsql $$
declare
  p_rec models;
  c_rec models;
begin
  p_rec := null;
  for c_rec in (select * from models) loop
    p_rec.id := coalesce(c_rec.id, p_rec.id);
    p_rec.name := coalesce(c_rec.name, p_rec.name);
    p_rec.description := c_rec.description;
    insert into models_t values (p_rec.id, p_rec.name, p_rec.description);
    raise notice '% %', c_rec.id, c_rec.name;
  end loop;
end; $$;

这样做的一个结果是，我们的桌子没有空隙，例如：

postgres=# select * from models_t;
  id   |  name  |     description      | mt_id 
-------+--------+----------------------+-------
 1     | Ford   | Some text            |     1
 2.1-3 | Opel   | 1. Astra             |     2
 2.1-3 | Opel   | 2. Diesel            |     3
 2.1-3 | Opel   | 3. 2005              |     4
 3     | Toyota | 2001; Ptrol; 1.8 TDI |     5
 4.1-2 | Opel   | (model) 1. Vectra    |     6
 4.1-2 | Opel   | (year) 2. 2004       |     7
 5.1-3 | Mazda  | Petrol; 1.9; 3-doors |     8
(8 rows)

实际上，这就足够了。但是，让我们解析我们的最后一个数据：

select 
  *, 
  substring(id from '(\d*)\.?.*') as main_id, -- First number before dot
  row_number() over (partition by substring(id from '(\d*)\.?.*')) as secondary_id -- Order inside previous value
from models_t;

结果：

  id   |  name  |     description      | mt_id | main_id | secondary_id 
-------+--------+----------------------+-------+---------+--------------
 1     | Ford   | Some text            |     1 | 1       |            1
 2.1-3 | Opel   | 1. Astra             |     2 | 2       |            1
 2.1-3 | Opel   | 2. Diesel            |     3 | 2       |            2
 2.1-3 | Opel   | 3. 2005              |     4 | 2       |            3
 3     | Toyota | 2001; Ptrol; 1.8 TDI |     5 | 3       |            1
 4.1-2 | Opel   | (model) 1. Vectra    |     6 | 4       |            1
 4.1-2 | Opel   | (year) 2. 2004       |     7 | 4       |            2
 5.1-3 | Mazda  | Petrol; 1.9; 3-doors |     8 | 5       |            1
(8 rows)

此时，我们可以使用列main_id和secondary_id来构建想要的ID，比如1.1或2.3。

其他的一切都取决于你。

祝你好运，玩得开心。