Thread: External table def not showing data type in 1.14.3

External table def not showing data type in 1.14.3

From
"Little, Douglas"
Date:

In gpadmin running on win7/32bit against greenplum 4.1.2.4   external tables columns are showing without a data type

CREATE EXTERNAL TABLE etl_stage_tbls.ext_dim_sitevisit

(

  md5hash ,

  visitid ,

  visit1ofacctdayind ,

  visit1ofacctind ,

  visit1oftdayind ,

  intentpurchaseind ,

  intentsearchind ,

  intentsvcactind ,

  intentsvcitinind ,

  ip ,

 

This is the query being executed by pgadmin

The results show the datatype.   Is there possibly a display problem?

Attaching the query output.

 

Thanks

Doug

 

 

SELECT att.*, def.*, pg_catalog.pg_get_expr(def.adbin, def.adrelid) AS defval

, CASE WHEN att.attndims > 0 THEN 1 ELSE 0 END AS isarray, format_type(ty.oid,NULL) AS typname

, format_type(ty.oid,att.atttypmod) AS displaytypname

, tn.nspname as typnspname

, et.typname as elemtypname

,  ty.typstorage AS defaultstorage

, cl.relname, na.nspname, att.attstattarget

, description, cs.relname AS sername, ns.nspname AS serschema,

  (SELECT count(1) FROM pg_type t2 WHERE t2.typname=ty.typname) > 1 AS isdup, indkey,

  EXISTS(SELECT 1 FROM  pg_constraint WHERE conrelid=att.attrelid AND contype='f' AND att.attnum=ANY(conkey)) As isfk

  FROM pg_attribute att

  JOIN pg_type ty ON ty.oid=atttypid

  JOIN pg_namespace tn ON tn.oid=ty.typnamespace

  JOIN pg_class cl ON cl.oid=att.attrelid

  JOIN pg_namespace na ON na.oid=cl.relnamespace

  LEFT OUTER JOIN pg_type et ON et.oid=ty.typelem

  LEFT OUTER JOIN pg_attrdef def ON adrelid=att.attrelid AND adnum=att.attnum

  LEFT OUTER JOIN pg_description des ON des.objoid=att.attrelid AND des.objsubid=att.attnum

  LEFT OUTER JOIN (pg_depend JOIN pg_class cs ON objid=cs.oid AND cs.relkind='S') ON refobjid=att.attrelid AND refobjsubid=att.attnum

  LEFT OUTER JOIN pg_namespace ns ON ns.oid=cs.relnamespace

  LEFT OUTER JOIN pg_index pi ON pi.indrelid=att.attrelid AND indisprimary

WHERE att.attrelid = 18073137::oid

   AND att.attnum > 0

   AND att.attisdropped IS FALSE

ORDER BY att.attnum

 

Doug Little

 

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide

 

Attachment

Re: External table def not showing data type in 1.14.3

From
Guillaume Lelarge
Date:
On Tue, 2012-06-12 at 09:40 -0500, Little, Douglas wrote:
> In gpadmin running on win7/32bit against greenplum 4.1.2.4   external tables columns are showing without a data type
> CREATE EXTERNAL TABLE etl_stage_tbls.ext_dim_sitevisit
> (
>   md5hash ,
>   visitid ,
>   visit1ofacctdayind ,
>   visit1ofacctind ,
>   visit1oftdayind ,
>   intentpurchaseind ,
>   intentsearchind ,
>   intentsvcactind ,
>   intentsvcitinind ,
>   ip ,
> ...
> 
> This is the query being executed by pgadmin
> The results show the datatype.   Is there possibly a display problem?

According to the code, it should show the columns datatypes. I have no
idea why it doesn't and I don't have a Greenplum database to check that.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com