Thread: Re: Are there commands to enquire about table structure?
>PostgreSQL mavens, can I ask the database, in a normal database query
>command, or in other words,
>essentially using the same environment where I'd say...
>
> SELECT count(*) FROM mytable [WHERE myconditions_obtain];
>
>...to get # records in my table,
>
>o How many fields mytable has as in:
> SELECT fieldcount(*) from mytable
>
In 7.4 anyhow, I bet you can do much of this with the
information_schema. This one would be ...
SELECT count(*)
FROM information_schema.columns
WHERE table_name='mytable';
_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail
On Sat, 31 Jan 2004 02:22:09 +0000, Lee Harr wrote: > In 7.4 anyhow, I bet you can do much of this with the > information_schema. This one would be ... > > SELECT count(*) > FROM information_schema.columns > WHERE table_name='mytable'; Sounds very good; however, the DB in question is 7.3.2 - no such namespace exists there. If we can get the IP people to sign off on upgrading, I'll look into this mechanism. We've got megabytes of SQL designed for Postgres, though, and any changes have to be vetted against the whole system before we can use them, something that takes many months. --Ben
"Ben" <reply@to-the-newsgroup.com> writes: > On Sat, 31 Jan 2004 02:22:09 +0000, Lee Harr wrote: > >> In 7.4 anyhow, I bet you can do much of this with the >> information_schema. This one would be ... >> >> SELECT count(*) >> FROM information_schema.columns >> WHERE table_name='mytable'; > > Sounds very good; however, the DB in question is 7.3.2 - no such namespace > exists there. If we can get the IP people to sign off on upgrading, I'll > look into this mechanism. We've got megabytes of SQL designed for > Postgres, though, and any changes have to be vetted against the whole > system before we can use them, something that takes many months. For 7.3, the info you need is available in the system catalogs, which have a somewhat hairier layout than the SQL-standard information_schema. -Doug
On Sat, 31 Jan 2004 23:41:54 -0500, Doug McNaught wrote: > For 7.3, the info you need is available in the system catalogs, which > have a somewhat hairier layout than the SQL-standard information_schema. Doug, thanks - do you know if the system catalogs retain the same abilities in 7.4? So that if I implement this, will it still work later? I don't mind "hairy", but "temporary" is a concern, at least. Thanks a million! --Ben
"Ben" <reply@to-the-newsgroup.com> writes: > On Sat, 31 Jan 2004 23:41:54 -0500, Doug McNaught wrote: > >> For 7.3, the info you need is available in the system catalogs, which >> have a somewhat hairier layout than the SQL-standard information_schema. > > Doug, thanks - do you know if the system catalogs retain the same > abilities in 7.4? So that if I implement this, will it still work later? I > don't mind "hairy", but "temporary" is a concern, at least. The system catalog layouts are not guaranteed to stay the same between major versions. You will certainly be *able* to get column layout information from the syscats but your queries might have to change when you upgrade. I don't actually know whether anything major changed between 7.3 and 7.4 that would break what you're trying to do, but I doubt it. -Doug
Doug McNaught <doug@mcnaught.org> writes:
> "Ben" <reply@to-the-newsgroup.com> writes:
>> Doug, thanks - do you know if the system catalogs retain the same
>> abilities in 7.4? So that if I implement this, will it still work later? I
>> don't mind "hairy", but "temporary" is a concern, at least.
> The system catalog layouts are not guaranteed to stay the same between
> major versions. You will certainly be *able* to get column layout
> information from the syscats but your queries might have to change
> when you upgrade.
Right. If you like, you can get a feeling for the sort of hacks you
might need by looking at the source code for pg_dump. Here's pg_dump
trying to extract information about column default expressions of a
particular table --- it needs different queries for 7.0, 7.1, 7.2,
and 7.3 (so far 7.4 and HEAD haven't diverged from 7.3):
if (g_fout->remoteVersion >= 70300)
{
appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
"pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc "
"FROM pg_catalog.pg_attrdef "
"WHERE adrelid = '%u'::pg_catalog.oid",
tbinfo->dobj.catId.oid);
}
else if (g_fout->remoteVersion >= 70200)
{
/* 7.2 did not have OIDs in pg_attrdef */
appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, adnum, "
"pg_get_expr(adbin, adrelid) AS adsrc "
"FROM pg_attrdef "
"WHERE adrelid = '%u'::oid",
tbinfo->dobj.catId.oid);
}
else if (g_fout->remoteVersion >= 70100)
{
/* no pg_get_expr, so must rely on adsrc */
appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, adsrc "
"FROM pg_attrdef "
"WHERE adrelid = '%u'::oid",
tbinfo->dobj.catId.oid);
}
else
{
/* no pg_get_expr, no tableoid either */
appendPQExpBuffer(q, "SELECT "
"(SELECT oid FROM pg_class WHERE relname = 'pg_attrdef') AS tableoid, "
"oid, adnum, adsrc "
"FROM pg_attrdef "
"WHERE adrelid = '%u'::oid",
tbinfo->dobj.catId.oid);
}
This particular aspect of the system catalogs has changed more than the
core aspects like getting the column names of a table ... but on the
other hand this is by no means the hairiest bit of pg_dump. It all
depends on what you need to extract.
regards, tom lane
On Sat, 31 Jan 2004 23:41:54 -0500, Doug McNaught wrote:
> For 7.3, the info you need is available in the system catalogs, which
> have a somewhat hairier layout than the SQL-standard information_schema.
Using Doug's pointer, I came up with this for 7.3...
SELECT
a.relname,b.attname,c.typname,b.attlen,b.atttypmod
FROM
pg_class AS a
LEFT OUTER JOIN pg_attribute AS b ON (b.attrelid = a.oid)
LEFT OUTER JOIN pg_type AS c ON (b.atttypid = c.oid)
WHERE
c.typname ILIKE '%XX%'
AND b.attname ILIKE '%YY%'
AND a.relname ILIKE '%ZZ%'
AND b.attisdropped=false
ORDER BY
a.relname,b.attname
...you replace XX, YY and ZZ with a substring you want to find in the
field, and/or table, and/or type. The select will return all matching
fields in a reasonble fashion.
What I've not figured out yet is how this relates to a particular
database; if a table and field match in two databases, you'll see them
both, which (probably) isn't what you'd want.
Further pointers are welcome, otherwise if I figure it out myself, I'll
post the results.
--Ben
> Further pointers are welcome, otherwise if I figure it out myself, I'll > post the results. Just an opinion here, but I would implement these queries as views similar to the information schema. That way you won't have to port everything when the database changes. Once you upgrade to 7.4 you can just keep on chugging away. Greg
"Ben" <reply@to-the-newsgroup.com> writes:
> Using Doug's pointer, I came up with this for 7.3...
> SELECT
> a.relname,b.attname,c.typname,b.attlen,b.atttypmod
> FROM
> pg_class AS a
> LEFT OUTER JOIN pg_attribute AS b ON (b.attrelid = a.oid)
> LEFT OUTER JOIN pg_type AS c ON (b.atttypid = c.oid)
> WHERE
> c.typname ILIKE '%XX%'
> AND b.attname ILIKE '%YY%'
> AND a.relname ILIKE '%ZZ%'
> AND b.attisdropped=false
> ORDER BY
> a.relname,b.attname
> What I've not figured out yet is how this relates to a particular
> database; if a table and field match in two databases, you'll see them
> both, which (probably) isn't what you'd want.
No, you won't, because each database has its own copy of pg_class et al.
Tables that are in other databases simply won't be in the copy of the
catalogs that you are looking at.
It is true that this query will produce multiple hits if you have
similarly named tables in different schemas of one database. To deal
with that, you probably want to extend the thing to join against
pg_namespace and show the schema name.
regards, tom lane