Форматирование внешних таблиц в Greenplum (PostgreSQL)

Оказывается, вы можете:

SET DATESTYLE = 'YMD';

перед SELECTing из таблицы. Однако это повлияет на интерпретацию всех дат, а не только дат из файла. Если вы постоянно используете однозначные даты ISO в другом месте, это будет хорошо, но это может быть проблемой, если (например) вам нужно также принимать литералы даты «D/M/Y» в том же запросе.

Это характерно для CREATE EXTERNAL TABLE GreenPlum и не применяется к стандартным SQL-оболочкам SQL/MED внешних данных, как показано ниже.

Что меня удивляет, так это то, что собственно PostgreSQL (в котором нет этой функции CREATE EXTERNAL TABLE) всегда принимает даты YYYY-MM-DD и YYYYMMDD в стиле ISO, независимо от DATESTYLE. Наблюдать:

regress=> SELECT '20121229'::date, '2012-12-29'::date, current_setting('DateStyle');
    date    |    date    | current_setting 
------------+------------+-----------------
 2012-12-29 | 2012-12-29 | ISO, MDY
(1 row)

regress=> SET DateStyle = 'DMY';
SET
regress=> SELECT '20121229'::date, '2012-12-29'::date, current_setting('DateStyle');
    date    |    date    | current_setting 
------------+------------+-----------------
 2012-12-29 | 2012-12-29 | ISO, DMY
(1 row)

... поэтому, если GreenPlum ведет себя так же, вам не нужно ничего делать, чтобы эти YYYYMMDD дат правильно читались из входного файла.

Вот как это работает с file_fdw SQL/MED внешней обёрткой данных PostgreSQL:

CREATE EXTENSION file_fdw;

COPY (SELECT '20121229', '2012-12-29') TO '/tmp/dates.csv' CSV;

SET DateStyle = 'DMY';

CREATE SERVER csvtest FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE csvtest (
    date1 date,
    date2 date
) SERVER csvtest OPTIONS ( filename '/tmp/dates.csv', format 'csv' );

SELECT * FROM csvtest ;
   date1    |   date2    
------------+------------
 2012-12-29 | 2012-12-29
(1 row)

Содержимое файла CSV:

20121229,2012-12-29

поэтому вы можете видеть, что Pg всегда будет принимать даты ISO для CSV, независимо от стиля даты.

Если GreenPlum этого не делает, отправьте сообщение об ошибке. Идея DateStyle изменить способ чтения внешней таблицы после ее создания безумна.

Да, ты можешь.

Вы делаете это, указывая, что поле во внешней таблице имеет текстовый тип. Затем используйте преобразование в операторе вставки. Вы также можете использовать gpload и определить преобразование. Оба решения аналогичны решению, описанному выше.

Вот простой файл с целым числом и датой, выраженной в виде года, месяца, дня, разделенных пробелом:

дата1.txt

1|2012 10 12
2|2012 11 13

Запустите gpfdist:

gpfdist -p 8010 -d ./ -l ./gpfdist.log &

Используйте psql для создания внешней таблицы, целевой таблицы и загрузки данных:

psql test

test=# create external table ext.t2( i int, d text ) 
  location ('gpfdist://walstl-mbp.local:8010/date1.txt') 
  format 'TEXT' ( delimiter '|' )
;


test=# select * from ext.t2; i |     d      
---+------------
  1 | 2012 10 12
  2 | 2012 11 13
(2 rows)

Теперь создайте таблицу, в которую будут загружены данные:

test=# create table test.t2 ( i int, d date ) 
;

И загрузите таблицу:

test=# insert into test.t2 select i, to_date(d,'YYYY MM DD') from ext.t2 ;

test=# select * from test.t2;
 i |     d      
---+------------
 1 | 2012-10-12
 2 | 2012-11-13