2019년 11월 19일 화요일

GPDB6. 자동 형 변환



Greenplum 6 자동 형변환 및 Operator 변환

 Greenplum 4에서는 자동으로 Data Type 형변환이 있었지만, Greenplum 5, 6에서는 명시적으로 형 변환을 해야합니다.
자동으로 형 변환하기 위해서는 아래의 함수를 생성하시면 됩니다.

 Soruce: https://gpdb.docs.pivotal.io/6-1/install_guide/43x_to_5x.html
        https://gist.github.com/ioguix/4dd187986c4a1b7e1160

 1. Type Case 에러 유형

    1) 데이터 Type이 맞지 않은 조인의 경우

       CREATE TABLE foo (a int) DISTRIBUTED RANDOMLY ;
      CREATE TABLE bar (b text) DISTRIBUTED RANDOMLY ;
      
      
      SELECT * FROM foo, bar WHERE foo.a = bar.b;
      ERROR:  operator does not exist: integer = text
      LINE 1: SELECT * FROM foo, bar WHERE foo.a = bar.b;
                                                 ^
      HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
      --> 2.데이터 타입 자동 형변환 함수 생성 스크립트  수행하면 자동 형변환 됨.

   2) Equal = 수행 에러

       CREATE TABLE foo (a int) DISTRIBUTED RANDOMLY ;
      CREATE TABLE bar (b text) DISTRIBUTED RANDOMLY ;
      insert into foo values (1);
      insert into bar values ('1');      
      
      testdb=# SELECT foo.a = bar.b FROM foo, bar WHERE foo.a = bar.b;
      ERROR:  operator does not exist: integer = text
      LINE 1: SELECT foo.a = bar.b FROM foo, bar WHERE foo.a = bar.b;
                           ^
      HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
                                                 ^
      --> 3. = 오퍼레이터 자동 형 변환 함수 생성
       
   3) concat || 수행 에러
      - Greenplum 5에서는 에러 발생
      - Greenplum 6에서는 에러 발생하지 않음   
      --> 4. || 오퍼레이터 자동 형 변환 함수 생성
      
      
2. 데이터 타입 자동 형변환 함수 생성 스크립트

 ## Type cast
CREATE FUNCTION pg_catalog.text(integer) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int4out($1));';
CREATE CAST (integer AS text) WITH FUNCTION pg_catalog.text(integer) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(smallint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int2out($1));';
CREATE CAST (smallint AS text) WITH FUNCTION pg_catalog.text(smallint) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(oid) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(oidout($1));';
CREATE CAST (oid AS text) WITH FUNCTION pg_catalog.text(oid) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(date) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(date_out($1));';
CREATE CAST (date AS text) WITH FUNCTION pg_catalog.text(date) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(double precision) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(float8out($1));';
CREATE CAST (double precision AS text) WITH FUNCTION pg_catalog.text(double precision) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(real) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(float4out($1));';
CREATE CAST (real AS text) WITH FUNCTION pg_catalog.text(real) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(time with time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(timetz_out($1));';
CREATE CAST (time with time zone AS text) WITH FUNCTION pg_catalog.text(time with time zone) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(time without time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(time_out($1));';
CREATE CAST (time without time zone AS text) WITH FUNCTION pg_catalog.text(time without time zone) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(timestamp with time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(timestamptz_out($1));';
CREATE CAST (timestamp with time zone AS text) WITH FUNCTION pg_catalog.text(timestamp with time zone) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(interval) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(interval_out($1));';
CREATE CAST (interval AS text) WITH FUNCTION pg_catalog.text(interval) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(bigint) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(int8out($1));';
CREATE CAST (bigint AS text) WITH FUNCTION pg_catalog.text(bigint) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(numeric) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(numeric_out($1));';
CREATE CAST (numeric AS text) WITH FUNCTION pg_catalog.text(numeric) AS IMPLICIT;
CREATE FUNCTION pg_catalog.text(timestamp without time zone) RETURNS text STRICT IMMUTABLE LANGUAGE SQL AS 'SELECT textin(timestamp_out($1));';
CREATE CAST (timestamp without time zone AS text) WITH FUNCTION pg_catalog.text(timestamp without time zone) AS IMPLICIT;
3. = 오퍼레이터 자동 형 변환
CREATE FUNCTION pg_catalog.texteqint(text, integer) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int4out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqint2(text, smallint) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int2out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqoid(text, oid) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(oidout($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqdate(text, date) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(date_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqfloat8(text, double precision) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(float8out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqreal(text, real) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(float4out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqtimetz(text, time WITH time zone) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timetz_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqtime(text, time without time zone) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(time_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqtimestamptz(text, timestamp WITH time zone) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timestamptz_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqinterval(text, interval) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(interval_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqint8(text, bigint) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int8out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqnumeric(text, numeric) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(numeric_out($2)) = $1;$$;
CREATE FUNCTION pg_catalog.texteqtimestamp(text, timestamp without time zone) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timestamp_out($2)) = $1;$$;

 CREATE FUNCTION pg_catalog.inteqtext(integer, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int4out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.int2eqtext(smallint, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int2out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.oideqtext(oid, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(oidout($1)) = $2;$$;
CREATE FUNCTION pg_catalog.dateeqtext(date, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(date_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.float8eqtext(double precision, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(float8out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.realeqtext(real, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(float4out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.timetzeqtext(time WITH time zone, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timetz_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.timeeqtext(time without time zone, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(time_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.timestamptzeqtext(timestamp WITH time zone, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timestamptz_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.intervaleqtext(interval, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(interval_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.int8eqtext(bigint, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(int8out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.numericeqtext(numeric, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(numeric_out($1)) = $2;$$;
CREATE FUNCTION pg_catalog.timestampeqtext(timestamp without time zone, text) RETURNS BOOLEAN STRICT IMMUTABLE LANGUAGE SQL AS $$SELECT textin(timestamp_out($1)) = $2;$$;

 CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqint, LEFTARG=text, RIGHTARG=integer, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqint2, LEFTARG=text, RIGHTARG=smallint, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqoid, LEFTARG=text, RIGHTARG=oid, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqdate, LEFTARG=text, RIGHTARG=date, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqfloat8, LEFTARG=text, RIGHTARG=double precision, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqreal, LEFTARG=text, RIGHTARG=real, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqtimetz, LEFTARG=text, RIGHTARG=time WITH time zone, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqtime, LEFTARG=text, RIGHTARG=time without time zone, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqtimestamptz, LEFTARG=text, RIGHTARG=timestamp WITH time zone, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqinterval, LEFTARG=text, RIGHTARG=interval, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqint8, LEFTARG=text, RIGHTARG=bigint, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqnumeric, LEFTARG=text, RIGHTARG=numeric, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.texteqtimestamp, LEFTARG=text, RIGHTARG=timestamp without time zone, COMMUTATOR=OPERATOR(pg_catalog.=));

 CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.inteqtext, LEFTARG=integer, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.int2eqtext, LEFTARG=smallint, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.oideqtext, LEFTARG=oid, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.dateeqtext, LEFTARG=date, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.float8eqtext, LEFTARG=double precision, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.realeqtext, LEFTARG=real, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.timetzeqtext, LEFTARG=time WITH time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.timeeqtext, LEFTARG=time without time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.timestamptzeqtext, LEFTARG=timestamp WITH time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.intervaleqtext, LEFTARG=interval, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.int8eqtext, LEFTARG=bigint, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.numericeqtext, LEFTARG=numeric, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
CREATE OPERATOR pg_catalog.= ( PROCEDURE = pg_catalog.timestampeqtext, LEFTARG=timestamp without time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.=));
4. Concat, || 오퍼레이터 자동 형 변환

CREATE FUNCTION pg_catalog.textconcatint(text, integer)                           RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatint2(text, smallint)                         RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatoid(text, oid)                               RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatdate(text, date)                             RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatfloat8(text, double precision)               RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatreal(text, real)                             RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcattimetz(text, time WITH time zone)            RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcattime(text, time without time zone)           RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcattimestamptz(text, timestamp WITH time zone)  RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatinterval(text, interval)                     RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatint8(text, bigint)                           RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcatnumeric(text, numeric)                       RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.textconcattimestamp(text, timestamp without time zone) RETURNS text AS 'SELECT $1 || $2::pg_catalog.text' LANGUAGE sql IMMUTABLE STRICT;

 CREATE FUNCTION pg_catalog.intconcattext(integer, text)                           RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.int2concattext(smallint, text)                         RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.oidconcattext(oid, text)                               RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.dateconcattext(date, text)                             RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.float8concattext(double precision, text)               RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.realconcattext(real, text)                             RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.timetzconcattext(time WITH time zone, text)            RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.timeconcattext(time without time zone, text)           RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.timestamptzconcattext(timestamp WITH time zone, text)  RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.intervalconcattext(interval, text)                     RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.int8concattext(bigint, text)                           RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.numericconcattext(numeric, text)                       RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;
CREATE FUNCTION pg_catalog.timestampconcattext(timestamp without time zone, text) RETURNS text AS 'SELECT $1::pg_catalog.text || $2' LANGUAGE sql IMMUTABLE STRICT;

 CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatint, LEFTARG=text, RIGHTARG=integer, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatint2, LEFTARG=text, RIGHTARG=smallint, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatoid, LEFTARG=text, RIGHTARG=oid, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatdate, LEFTARG=text, RIGHTARG=date, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatfloat8, LEFTARG=text, RIGHTARG=double precision, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatreal, LEFTARG=text, RIGHTARG=real, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcattimetz, LEFTARG=text, RIGHTARG=time WITH time zone, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcattime, LEFTARG=text, RIGHTARG=time without time zone, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcattimestamptz, LEFTARG=text, RIGHTARG=timestamp WITH time zone, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatinterval, LEFTARG=text, RIGHTARG=interval, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatint8, LEFTARG=text, RIGHTARG=bigint, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcatnumeric, LEFTARG=text, RIGHTARG=numeric, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.textconcattimestamp, LEFTARG=text, RIGHTARG=timestamp without time zone, COMMUTATOR=OPERATOR(pg_catalog.||));

 CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.intconcattext,     LEFTARG=integer, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.int2concattext,    LEFTARG=smallint, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.oidconcattext,     LEFTARG=oid, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.dateconcattext,    LEFTARG=date, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.float8concattext,  LEFTARG=double precision, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.realconcattext,    LEFTARG=real, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.timetzconcattext,  LEFTARG=time WITH time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.timeconcattext,    LEFTARG=time without time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.timestamptzconcattext, LEFTARG=timestamp WITH time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.intervalconcattext,    LEFTARG=interval, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.int8concattext,        LEFTARG=bigint, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.numericconcattext,     LEFTARG=numeric, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));
CREATE OPERATOR pg_catalog.|| ( PROCEDURE = pg_catalog.timestampconcattext,   LEFTARG=timestamp without time zone, RIGHTARG=text, COMMUTATOR=OPERATOR(pg_catalog.||));

시간:
라벨:

댓글 없음:

댓글 쓰기

피드 구독하기: 댓글 (Atom)

Greenplum Backup & Restore

Greenplum에서는 gpbackup과 gprestore를 이용해서 대량의 데이터를 병렬로 백업/병렬로 복구를 지원하고 있습니다. Full 백업이외에도 incremental 백업을 지원하고 있습니다.  - incremental 백업시에는 반드시 사전...

  • Greenplum에서 리소스 관리
    보통 분석 시스템에서 항상 나오는 이야기가 배치는 빠른데, 일반 조회성 쿼리의 성능을 맞출 수 있느냐라는 질문을 많이 받습니다. 전사에서 보는 일별 현황이 있고, 배치도 수행하고, 머신러링 형태로 분석하는 것이 동시에 수행될 때 어느 업무가 중...
  • Greenplum 소개
    1. Greenplum 소개 Greenplum은 대용량 분석 작업에 최적화된 병렬처리(MPP) 기반의 오픈소스 데이터 플랫폼입니다. Greenplum 처음 프로젝트에서는 데이터웨어하우스를 목적으로 시작되었습니다. 국내 시장에서 ...
  • Greenplum DDL(테이블/인덱스 생성)
    1.       테이블 & 파티션 테이블 1)         Data Type            -        Greenplum 의 Character Length 는 Byte 수가 아닌 Character ...