PostegreSQL. Как правильно создать структурированную таблицу с select?

Question

[NIKA_R]

Есть две таблицы с данными , все данные в одну строчку не разбиты по атрибутам .
То есть
Таблица 1

101.130.92.131			20170216044929	http://news.yandex.ru/2962142	1418	405	Firefox/5.0 (compatible; MSIE 9.0; Windows NT 6.1; WOW64; Trident/5.0; chromeframe/12.0.742.112)n'

...
Таблица 2.
101.130.92.131 Воронеж

Требуется на базе этих таблиц создать новую с распарсенными данными

create table  LOG( 
DT DATE,          
LINK VARCHAR(50),  
USER_AGENT VARCHAR(200),  
REGION VARCHAR(30)  
);

Как создавать таблицы на базе других при помощи select , понимаю.

CREATE TABLE new_table AS (SELECT column_1, column2, ... column_n FROM old_table_1, old_table_2, ... old_table_n)

Создавать таблицу со структурой заранее нельзя... Вью создавать нельзя...
Как в одном запросе распарсить и сджоинить две таблицы?
===================================================
20140216044939 - как переформатировать в дату и время ?
2014 02 16 04 49 39 ?

Comments

[mayton2019]

Есть две таблицы с данными , все данные в одну строчку не разбиты по атрибутам .

Непонятно. Нужно делать парсинг неструктурированной информации на SQL? Так не делают. Вообще язык
SQL предполагает что данные у нас уже лежат хотябы в первой нормально форме.

А что в таблице 2? Там 3 миллиарда всех IP адресов с привязкой к городам? Так не делают. Это нерационально.

Создавать таблицу со структурой заранее нельзя... Вью создавать нельзя...

Очень хочется позвать в топик того эффективного менеджера который надиктовал такие ограничения. Что он этим хотел сказать? Что это олимпиадная задача? Или тестовое задание? Что ожидается получить в качестве решения? Одноразовый парсинг? Или работа на регулярной основе. Во втором варианте код должен быть красив и supportable,

Что я хочу сказать. - Что это очень плохое задание. Оно - нерациональное и его не стоит делать в том виде как его выдали. Тем более так вот радикально.

[NIKA_R]

mayton2019, "партия сказала надо, комсомол ответил - есть... " . Могу поспорить, при помощи SQL можно делать парсинг неструктурированной информации, но на это нет времени ... Пока у меня следующие мысли... Убойные по производительности , но на другое мое мышление пока не сподвиглось. Решить через функциии парсинг и сделать вложенный подзапрос для создания таблицы... =) Безумие.

[NIKA_R]

Akina, регулярками ещё порекомендовали не пользоваться ....

Answer 1

[Akina]

CREATE TABLE log AS (
  WITH cte AS (
    SELECT STRING_TO_ARRAY(txt, ' ') parsed
    FROM t1
  )
  SELECT LEFT(parsed[2], 8) :: DATE dt,
         parsed[3] link,
         ARRAY_TO_STRING(parsed[6:100], ' ') agent,
         t2.city
  FROM cte
  LEFT JOIN t2 ON cte.parsed[1] = t2.ip
  );

fiddle

PS. Нужны конкретные типы данных в итоговой таблице - добавь соотв. приведение типов в SELECT, как сделано для поля dt.