Thread: The Information Schema vs the PG Catalog

<div class="Section1"><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">I’m writing PL/pgSQL routines that generate triggers, functions, and rules based on design
characteristicsof tables, columns, and other database objects.  These routines need to be able to look up the
definitionsof these objects.  I see that there are two places available to look up this info: the Information Schema
andin the PG Catalog.  </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">Which source is preferable?  Or if that answer isn’t absolute, what are the reasons or conditions
forpreferring one over the other?  </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">Also, a specific question:  Does the Information Schema offer any way to list the sequences that
existand their attributes?  I can’t seem to find any.</span></font><p class="MsoNormal"><font face="Verdana"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Verdana"> </span></font><p class="MsoNormal"><font face="Verdana" size="2"><span style="font-size:10.0pt;
font-family:Verdana">~ TIA</span></font><p class="MsoNormal"><font face="Verdana" size="2"><span
style="font-size:10.0pt;
font-family:Verdana">~ Ken</span></font></div>

I would say that pg_catalog is the more complete one whereas the
information_schema the more generic, standards-conformant place. I would
stick with the information_schema unless that becomes inadequate. A case
in point may be sequences. Apart from
information_schema.columns.column_default I haven't seen them
represented anywhere there (please someone correct me if I am wrong).
You can get more information about sequences from pg_catalog.pg_class
(look for pg_class.relkind='S') and various views that sit on top of
that (e.g. pg_statio_all_sequences).

George


> I'm writing PL/pgSQL routines that generate triggers, functions,
> and rules based on design characteristics of tables, columns, and
> other database objects.  These routines need to be able to look up
> the definitions of these objects.  I see that there are two places
> available to look up this info: the Information Schema and in the
> PG Catalog.
>
> Which source is preferable?  Or if that answer isn't absolute,
> what are the reasons or conditions for preferring one over the
> other?
>
> Also, a specific question:  Does the Information Schema offer any
> way to list the sequences that exist and their attributes?  I
> can't seem to find any.
>

Thanks, George.  What you say fits with what I was finding.  I think that's
the way I will go.

~ Ken

> -----Original Message-----
> From: George Pavlov [mailto:gpavlov@mynewplace.com]
> Sent: Tuesday, December 27, 2005 12:11 PM
> To: pgsql-sql@postgresql.org
> Cc: ken@sunward.org
> Subject: Re: The Information Schema vs the PG Catalog
> 
> I would say that pg_catalog is the more complete one whereas the
> information_schema the more generic, standards-conformant place. I would
> stick with the information_schema unless that becomes inadequate. A case
> in point may be sequences. Apart from
> information_schema.columns.column_default I haven't seen them
> represented anywhere there (please someone correct me if I am wrong).
> You can get more information about sequences from pg_catalog.pg_class
> (look for pg_class.relkind='S') and various views that sit on top of
> that (e.g. pg_statio_all_sequences).
> 
> George
> 
> 
> > I'm writing PL/pgSQL routines that generate triggers, functions,
> > and rules based on design characteristics of tables, columns, and
> > other database objects.  These routines need to be able to look up
> > the definitions of these objects.  I see that there are two places
> > available to look up this info: the Information Schema and in the
> > PG Catalog.
> >
> > Which source is preferable?  Or if that answer isn't absolute,
> > what are the reasons or conditions for preferring one over the
> > other?
> >
> > Also, a specific question:  Does the Information Schema offer any
> > way to list the sequences that exist and their attributes?  I
> > can't seem to find any.
> >