title: PostgreSQL data types excluding those for the row structure of tables
body: <p>We're using Ruby on Rails 4.2.6 and PostgreSQL 9.3.10 for our multi-tenant application which creates a schema for each of our customers.</p>
  
  <p>Within ActiveRecord there is code which executes the following SQL to fetch the data types:</p>
  
  <pre><code>SELECT t.oid, t.typname, t.typelem, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype
  FROM pg_type as t
  LEFT JOIN pg_range as r ON oid = rngtypid
  WHERE t.typname IN ('int2', 'int4', 'int8', 'oid', 'float4', 'float8', 'text', 'varchar', 'char', 'name', 'bpchar', 'bool', 'bit', 'varbit', 'timestamptz', 'date', 'time', 'money', 'bytea', 'point', 'hstore', 'json', 'jsonb', 'cidr', 'inet', 'uuid', 'xml', 'tsvector', 'macaddr', 'citext', 'ltree', 'interval', 'path', 'line', 'polygon', 'circle', 'lseg', 'box', 'timestamp', 'numeric')
  OR t.typtype IN ('r', 'e', 'd')
  OR t.typinput = 'array_in(cstring,oid,integer)'::regprocedure
  OR t.typelem != 0
  </code></pre>
  
  <p>As this query returns a record relating to the row structure of every table in every schema, the number of records exceeds 150k for our database. For example, for our tags table there is a record for every schema with a typname of "_tags", typinput of "array_in" and typtype of "b".</p>
  
  <p>Could anyone recommend a way to alter this query to exclude these row structure records?</p>
  
  <p>We have considered using the DISTINCT statement below but this would not cater for two "true" data types with the same name in different schemas. </p>
  
  <pre><code>DISTINCT ON (t.typname, t.typdelim, t.typinput, r.rngsubtype, t.typtype, t.typbasetype)
  </code></pre>