[Resolved] Checking PostgreSQL column characteristics

lobobr

Greetings.

I'm developing a tool similar to phppgadmin, but with some differences.

When my tool fetches the characterisrics of a table, it's easy to tell wich datatype goes into each column with pg_field_type().

I'm stuck with these questions, and I hope someone here can help me:

1) How can I tell the maximum size of a - let's say for an example - varchar column?

2) How can I tell whether a column accepts NULL values?

3) How can I tell whether a column has a unique constraint or not?

I've browsed through PHP's manual, but couldn't find these answers anywhere.

Can somene please give some clues?

Thanks a lot in advance,

Sxooter

This kind of stuff tends to be a but version dependant, but there are two basic approaches you can take, one is to use the information_schema, which is a part of the SQL spec, and therefore not likely to change in future versions, or the system catalogs.

While the information_schema is a bit obtuse at first when you're trying to figure it out, it's not too bad. From the psql prompt, just do this:

set search_path='information_schema';
\d

and you'll see a long list of the things in the information schema. Schemas, and therefore the information_schema, were introduced in 7.3, so if you're on an older version of postgresql this won't work.

To use the built in system catalogs, which are almost guaranteed to change a fair bit from version to version, start psql with the -E switch, which will make it display all the backend queries used by the \ series commands, like \d etc...

lobobr

Thanks, Sxooter.

Could you please clarify a little bit on how can I implement this on PHP?

There is no possibility of using "\d" on a query variable, right? So I would like to know if there's a SQL form of this.

Thanks again,

Sxooter

Originally posted by lobobr
Thanks, Sxooter.

Could you please clarify a little bit on how can I implement this on PHP?

There is no possibility of using "\d" on a query variable, right? So I would like to know if there's a SQL form of this.

Thanks again,

The information_schema is bog standard SQL and you can do things like:

select * from information_schema.tables

from php and iterate over the list of elements returned. One day, when all databases implement this standard feature you could do the exact same thing to any ANSI compliant database and get back the same answer.

As for the stuff hidden away behind the \d switch, that's what you start psql with a -E switch for, like so:

psql -E
mydatabase=# \d
****** QUERY *******
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;

                 List of relations

and so on...

lobobr

Sxooter:

I can't thank you enough. I've found the answer.

All the data I need is accessible via:

select * from information_schema.columns where table_schema='public'

Thanks a lot!

Sxooter

You're welcome! Hope you enjoy exploring the information_schema. It's a pretty cool addition.

csn

Originally posted by Sxooter
You're welcome! Hope you enjoy exploring the information_schema. It's a pretty cool addition.

Definitely 🙂