Как разбить текст на несколько полей с помощью Postgresql?

У меня есть таблица со столбцом, который нужно разделить и вставить в новую таблицу. Имя столбца — location, а данные могут выглядеть как Detroit, MI, USA;Chicago, IL, USA или просто как USA.

В конечном итоге я хочу вставить данные в новую таблицу измерений, которая выглядит так:

City   | State | Country|
Detroit  MI      USA
Chicago  IL      USA
NULL     NULL    USA

Я наткнулся на функцию string_to_array и могу разбить большой пример (Detroit, MI, USA; Chicago, IL, USA) на 2 строки Detroit, MI, USA и Chicago, IL, USA.

Теперь я в тупике, как снова разбить эти строки, а затем вставить их. Поскольку есть две строки, разделенные запятой, работает ли снова использование string_to_array? Кажется, это не работает в Sqlfiddle.

Примечание. Я использую Sqlfiddle прямо сейчас, так как в данный момент у меня нет доступа к моей таблице Redshift.

Это для Redshift, который, к сожалению, все еще использует PostGresql 8.0.2 и поэтому не имеет функции unnest


postgresql amazon-redshift dimensional-modeling
person simplycoding    schedule 22.09.2015    source источник

Ответы (1)


arrow_upward
2
arrow_downward 

postgres=# select v[1] as city, v[1] as state, v[2] as country
              from (select string_to_array(unnest(string_to_array(
           'Detroit, MI, USA;Chicago, IL, USA',';')),',')) s(v);
┌─────────┬─────────┬─────────┐
│  city   │  state  │ country │
╞═════════╪═════════╪═════════╡
│ Detroit │ Detroit │  MI     │
│ Chicago │ Chicago │  IL     │
└─────────┴─────────┴─────────┘
(2 rows)

Протестировано на Postgres, не уверен, что он будет работать и на Redshift.

Следующий запрос должен работать на каждом Postgres

select  v[1] as city, v[1] as state, v[2] as country 
    from (select string_to_array(v, ',') v 
             from unnest(string_to_array(
'Detroit, MI, USA;Chicago, IL, USA',';')) g(v)) s;

Он использует старый трюк PostgreSQL - использование производной таблицы.

SELECT v[1], v[2] FROM (SELECT string_to_array('1,2',',')) g(v)

Негнездовая функция:

CREATE OR REPLACE FUNCTION _unnest(anyarray)
RETURNS SETOF anyelement AS '
BEGIN
  FOR i IN array_lower($1,1) .. array_upper($1,1) LOOP
    RETURN NEXT $1[i];
  END LOOP;
  RETURN;
END;
' LANGUAGE plpgsql;
person Pavel Stehule    schedule 22.09.2015
comment
Я не думаю, что это решит проблему, учитывая пример массива первого уровня, который содержит только код страны, такой как США. - person Ron Dunn; 23.09.2015
comment
Только что понял, что Redshift основан на Postgres 8.0... Полностью думал, что это 9.0. Вы знаете альтернативу unnest? - person simplycoding; 23.09.2015
comment
@RonDunn на самом деле данные могут быть стандартизированы в трехуровневые массивы для всех записей. - person simplycoding; 23.09.2015
comment
@simplycoding см. обновленный текст — есть исходный код для unnest. 8.0 довольно старый, но я надеюсь, что он должен работать. - person Pavel Stehule; 23.09.2015
comment
Привет, @Pavel, я пытаюсь выполнить функцию unnest, которую ты написал. Кажется, это не работает, когда я просто копирую + вставляю + выполняю. Я получаю сообщение об ошибке: неопределенная строка в долларовых кавычках на уровне "$$ BEGIN FOR i IN array_lower($1,1) .. array_upper($1,1) LOOP RETURN NEXT $1[i] или рядом с ним - person simplycoding; 06.10.2015
comment
@simplycoding хм .. перепроверил, все работает. Попробуйте использовать один апостроф вместо $$ - person Pavel Stehule; 06.10.2015
comment
Спасибо. Оказывается, Redshift даже не поддерживает создание функций, если только это не Python... Тьфу. - person simplycoding; 07.10.2015