retrieve data type,size,precision and scale of postgresql columns

Question

How to retreive data type, size, precision and scale of all columns of a particular table in postgresql with a single sql statement?

The table can have columns of the data types ( 'int2','int4','int8','float4','float8','numeric','varchar','char','date','timestamp'). If precision/scale are not applicable for a particular data type we can leave it as null.

I should not use INFORMATION_SCHEMA for this because though this schema is built-in we can drop this schema. SO i wrote code using this schema and if some how the customer drops this schema my code breaks. I just want to use pg_catalog tables/views.

i used pg_attribute,pg_class and pg_type but i didn't get exactly what i wanted in single sql statement.. So, i posted here. Ok, anybody else has thoughts on my question? By the way, What is RentACoder.com? — vchitta, Commented Jul 6, 2011 at 20:20
I wonder how one can drop the INFORMATION_SCHEMA. Can someone explain? — ypercubeᵀᴹ, Commented Jul 6, 2011 at 20:47
Since in PostgreSQL 8.4, this schema is droppable. There is no special purpose of dropping it but if some how this schema is dropped then my sqlstatement which extracts data type,size,precision and scale should not cause my application to break. That's why i want to use only pg_catalog shema and not INFORMATION_SCHEMA. — vchitta, Commented Jul 6, 2011 at 21:07
You can also drop a table, or delete the database, or turn off the computer. Are you going to work around that as well? Seriously, use the information schema. Or just include a copy of information_schema.sql into your application if you are desperate. — Peter Eisentraut, Commented Jul 7, 2011 at 9:48
@Peter: While I gave an answer, your advice is best. As the quote goes: "Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning." (Rick Cook, The Wizardry Compiled) — Matthew Wood, Commented Jul 7, 2011 at 16:14

Matthew Wood · Accepted Answer · 2011-07-07 16:11:45Z

1

The tables in information_schema are views on the system tables. If you use this command in psql, you will get the view definition that generates the columns view:

\dS+ information_schema.columns

You'll still have some work to do as the type casts to user-friendly outputs are also based on information_schema, but that shouldn't be too hard.

EDIT: In versions prior to 8.4, you have to use \d+, not \dS+

edited Jul 7, 2011 at 16:11

answered Jul 6, 2011 at 20:44

Matthew Wood

16.5k6 gold badges48 silver badges35 bronze badges

ok, but this also based on information_schema..Can any body write only with pg_catalog tables/views?
– vchitta
Commented Jul 7, 2011 at 5:42
1

Actually, the view definition is based on the pg_catalog tables/views with the exception of the make-it-friendly-for-output typecasts. You can copy that view definition and modify it to remove the info schema typecasts and now you have query that is independent of the existence of information_schema.
– Matthew Wood
Commented Jul 7, 2011 at 16:09

Add a comment |

Collectives™ on Stack Overflow

retrieve data type,size,precision and scale of postgresql columns

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related