Re: Extracting metadata about attributes from catalog

Do 'psql -E ...', it will display actual queries used by psql.

Your particular query is:
SELECT a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull,
a.atthasdef, a.attnum
FROM pg_class c, pg_attribute a, pg_type t
WHERE c.relname = '...tablename...'
AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
ORDER BY a.attnum

And pg_type has all information you need.

On Fri, 22 Jun 2001, Bernardo Pons wrote:

>
> I make queries on catalog tables in order get metadata about table
> attributes. I need this metadata in order to help me controlling the data
> that users enter using html forms dynamically generated with PHP.
>
> The problem I've found is that the attribute that stores the info about data
> length (attribute atttypmod of catalog table pg_attribute) is some kind of
> internal coding. For example, for an attribute varchar(100) atttypmod value
> is 104; for an attribute numeric(6,0) atttypmod value is 393220.
>
> I guess I would need some kind of function in order to get the actual lenght
> for the attributes. Does this function exist? Where can I find it?
>
> Any help will be appreciated.
>
> --
> Bernardo Pons
>
>
> P.S.
>
> For example, typical output of \d <tablename> in psql is:
>
> Attribute | Type | Modifier
> -----------------+--------------+----------
> CustomerId | numeric(6,0) | not null
> Name | varchar(100) |
> Series | numeric(2,0) | not null
> Number | numeric(6,0) | not null
> ObjectId | numeric(6,0) |
> ObjectType | numeric(3,0) |
> Quantity | numeric(8,2) | not null
> Price | numeric(8,2) | not null
>
> Using a query like
>
> SELECT a.attname, t.typname, a.atttypmod, a.attnum FROM pg_class c,
> pg_attribute a, pg_type t WHERE c.relname = <tablename> AND a.attnum > 0 AND
> a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum;
>
> on system catalog tables I get:
>
> attname | typname | atttypmod | attnum
> -----------------+---------+-----------+--------
> CustomerId | numeric | 393220 | 1
> Name | varchar | 104 | 2
> Series | numeric | 131076 | 1
> Number | numeric | 393220 | 2
> ObjectId | numeric | 393220 | 3
> ObjectType | numeric | 196612 | 4
> Quantity | numeric | 524294 | 7
> Price | numeric | 524294 | 8
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>