External table def not showing data type in 1.14.3

From: "Little, Douglas" <DOUGLAS(dot)LITTLE(at)orbitz(dot)com>
To: "pgadmin-support(at)postgresql(dot)org" <pgadmin-support(at)postgresql(dot)org>
Subject: External table def not showing data type in 1.14.3
Date: 2012-06-12 14:40:21
Message-ID: 8585BA53443004458E0BAA6134C5A7FB9F1B6476@EGEXCMB01.oww.root.lcl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgadmin-support

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 Content-Type Size
external_table_data.csv application/octet-stream 9.6 KB

Responses

Browse pgadmin-support by date

  From Date Subject
Next Message Guillaume Lelarge 2012-06-13 19:09:55 Re: External table def not showing data type in 1.14.3
Previous Message Dave Page 2012-06-11 17:02:22 Re: [pgadmin-support] Missing defaults for function arguments