将postgresql表列值分为多列

Question

我正在与PostgreSQL一起工作，并且有一个带有2列的表Manager_id和Manager_name。我想拆分manager_name列（带有诸如：盎格鲁东部船舶管理 - 中国香港）的值|城市|国家。定界器“ - ”和“，”。

我尝试了此查询，但它只是显示了结果，而不是永久保存的列

select 
    manager_name,
    split_part(manager_name, '-', 1) m_name,
    split_part(manager_name, ',', 2) city,
    split_part(manager_name, ',', 2) country 
from 
    manager2

是否有其他方法可以将本列分开并将其永久保存在该表中？

Answer 1

也许有点冗长，但以下内容似乎是这样做的。您的split_part（）从Manager_name检索城市需要进行一些调整；还将它们包裹在trim（）中以删除前导和落后空间。

CREATE TABLE manager2
(
  manager_id SERIAL PRIMARY KEY,
  manager_name VARCHAR(250)
);

INSERT INTO manager2 (manager_name)
VALUES ('ANGLO EASTERN SHIPMANAGEMENT - HONG KONG, CHINA');
INSERT INTO manager2 (manager_name)
VALUES ('HARTMANN REEDEREI - LEER, GERMANY');

-- add columns to table
ALTER TABLE manager2 ADD COLUMN m_name VARCHAR(250);
ALTER TABLE manager2 ADD COLUMN city VARCHAR(250);
ALTER TABLE manager2 ADD COLUMN country VARCHAR(250);

-- populate tables with vals
WITH results AS (
  SELECT manager_id AS id, TRIM(SPLIT_PART(manager_name, '-', 1)) AS m_name, 
  TRIM(SPLIT_PART(SPLIT_PART(manager_name, '-', 2), ',', 1)) AS city,  
  TRIM(SPLIT_PART(manager_name, ',', 2)) AS country
  FROM manager2
  )
UPDATE manager2
SET 
m_name = (SELECT m_name FROM results WHERE manager_id = results.id),
city = (SELECT city FROM results WHERE manager_id = results.id),
country = (SELECT country FROM results WHERE manager_id = results.id);

更新后表：

 manager_id |                  manager_name                   |            m_name            |   city    | country
------------+-------------------------------------------------+------------------------------+-----------+---------
          1 | ANGLO EASTERN SHIPMANAGEMENT - HONG KONG, CHINA | ANGLO EASTERN SHIPMANAGEMENT | HONG KONG | CHINA
          2 | HARTMANN REEDEREI - LEER, GERMANY               | HARTMANN REEDEREI            | LEER      | GERMANY

---

更新以处理诸如psa之类的案例| null |新加坡。您可以使用以下代码：结果：而不是上面使用的CTE（），

WITH results AS (
  SELECT manager_id AS id, TRIM(SPLIT_PART(manager_name, '-', 1)) AS m_name,
    CASE
      WHEN SPLIT_PART(manager_name, '-', 2) ~ ',' THEN
        TRIM(SPLIT_PART(SPLIT_PART(manager_name, '-', 2), ',', 1))
      ELSE
        NULL
    END AS city,
    CASE 
      WHEN SPLIT_PART(manager_name, '-', 2) ~ ',' THEN 
        TRIM(SPLIT_PART(SPLIT_PART(manager_name, '-', 2), ',', 2)) 
      ELSE
        TRIM(SPLIT_PART(SPLIT_PART(manager_name, '-', 2), ',', 1)) 
    END AS country
  FROM manager2
  )

结果：结果：

 manager_id |                  manager_name                   |            m_name            |   city    |  country
------------+-------------------------------------------------+------------------------------+-----------+-----------
          1 | ANGLO EASTERN SHIPMANAGEMENT - HONG KONG, CHINA | ANGLO EASTERN SHIPMANAGEMENT | HONG KONG | CHINA
          2 | HARTMANN REEDEREI - LEER, GERMANY               | HARTMANN REEDEREI            | LEER      | GERMANY
          3 | PSA - SINGAPORE                                 | PSA                          |           | SINGAPORE

Answer 2

您可以通过我知道的两种方式来执行此操作。您可以使用split_part和alter表或创建视图语句将查询永久存储在另一个表中。然后将表用作主表作为创建视图的

  --The ALTER TABLE 
  SELECT *,
   SPLIT_PART(manager_name,'-',1) AS manager_name1, 
   SPLIT_PART(manager_name,',',2) AS city,
   SPLIT_PART(manager_name,',',3) AS country
   FROM table_name;
   
  -- NOW lets update the table

  ALTER TABLE table_name 
  ADD manager_name1 varchar(500);
  UPDATE manager_name1 
  SET manager_name1 = SPLIT_PART(manager_name,'-',1);

  ALTER TABLE table_name 
  ADD city varchar(500);
  UPDATE city 
  SET city = SPLIT_PART(manager_name,',',2);

  ALTER TABLE table_name 
  ADD country varchar(500);
  UPDATE country 
  SET country = SPLIT_PART(manager_name,',',3);

创建

CREATE VIEW new_table_name AS
SELECT SPLIT_PART(manager_name, '-', '1') AS manager_name1,
SPLIT_PART(manager_name, '-', '2') AS city
SPLIT_PART(manager_name, ',', '3') AS country;

三列将在您的视图中创建三列，然后您可以删除拆分的原始Manager_name。