Split PostgreSQL table column values into multiple columns-CodePudding

I am working with PostgreSQL and I have a table with 2 columns manager_id and manager_name. I want to split manager_name column (with values such as: ANGLO EASTERN SHIPMANAGEMENT - HONG KONG, CHINA) to Manager name| city | country. delimiter "-"and "," .

I tried this query but it just show the result, not saved that columns permanently

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

Is there any other way to split this column and saved it in that table permanently ??

CodePudding user response：

Perhaps a bit verbose, but the following seems to do it. Your SPLIT_PART() to retrieve the city from manager_name needed some tweaking; also wrap them in TRIM() to remove leading and trailing spaces.

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);

Table after update:

 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