Thread: Getting lengths of variable fields
Hi folks, I'm writing a very robust validation script in perl for database entry. One of the things I'd like to do is check how large a field is, and make sure that the entry into that field isn't too big. Problem is, for variable length fields, DBD::Pg returns a -1 size, using the pg_size attribute. Is there something I am missing? Is there a way to get the size of variable length types using DBI/DBD::Pg, in particular, char() and varchar()? Michelle -- ------------ Michelle Murrain, Ph.D. President Norwottuck Technology Resources mpm@norwottuck.com http://www.norwottuck.com
Michelle Murrain writes: > I'm writing a very robust validation script in perl for database entry. One > of the things I'd like to do is check how large a field is, and make sure > that the entry into that field isn't too big. Problem is, for variable length > fields, DBD::Pg returns a -1 size, using the pg_size attribute. > > Is there something I am missing? Is there a way to get the size of variable > length types using DBI/DBD::Pg, in particular, char() and varchar()? Normally, you'd use LENGTH or OCTET_LENGTH. If you want to get the storage size on disk, you could add 4 to what you get as length, but this result seems to be of dubious value, especially with TOAST (compression, out-of-line storage). -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Peter Eisentraut <peter_e@gmx.net> writes:
> Michelle Murrain writes:
>> Is there something I am missing? Is there a way to get the size of variable
>> length types using DBI/DBD::Pg, in particular, char() and varchar()?
> Normally, you'd use LENGTH or OCTET_LENGTH. If you want to get the
> storage size on disk, you could add 4 to what you get as length, but this
> result seems to be of dubious value, especially with TOAST (compression,
> out-of-line storage).
What Michelle seems to want is the declared limit on field width, not
the actual width of any particular value.
This info is stored in the 'atttypmod' field of pg_attribute, but I
don't know whether DBD::Pg provides any handy interface to that. You
might have to get down-and-dirty enough to select it directly out of
pg_attribute ...
regards, tom lane
On Monday 05 March 2001 05:55 pm, Tom Lane wrote: > What Michelle seems to want is the declared limit on field width, not > the actual width of any particular value. Yes, that's exactly what I want. > This info is stored in the 'atttypmod' field of pg_attribute, but I > don't know whether DBD::Pg provides any handy interface to that. You > might have to get down-and-dirty enough to select it directly out of > pg_attribute ... I couldn't seem to find it - and I'm going to show my naivete here - how do I get hold of it if there is no interface in DBD::Pg? THanks!! Michelle ------------ Michelle Murrain, Ph.D. President Norwottuck Technology Resources mpm@norwottuck.com http://www.norwottuck.com
Tom Lane wrote: > > Peter Eisentraut <peter_e@gmx.net> writes: > > Michelle Murrain writes: > >> Is there something I am missing? Is there a way to get the size of variable > >> length types using DBI/DBD::Pg, in particular, char() and varchar()? > > > Normally, you'd use LENGTH or OCTET_LENGTH. If you want to get the > > storage size on disk, you could add 4 to what you get as length, but this > > result seems to be of dubious value, especially with TOAST (compression, > > out-of-line storage). > > What Michelle seems to want is the declared limit on field width, not > the actual width of any particular value. > > This info is stored in the 'atttypmod' field of pg_attribute, but I > don't know whether DBD::Pg provides any handy interface to that. You > might have to get down-and-dirty enough to select it directly out of > pg_attribute ... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org Hi Tom, When I query the pg_attribute table to get the limit on a specific field I get more than one row, because I use the same field name in few tables. However each row has different "attrelid", I suppose that means that every table has its one unique "attrelid". My question is how do I find out the "attrelid" if I only know that table name?? That's the only thing that keeps me from getting max field limit for a specific field in a specific table... Regards, Boulat Khakimov -- Nothing Like the Sun
Join attrelid against the OID column of pg_class ...
regards, tom lane
On Monday 05 March 2001 05:55 pm, Tom Lane wrote: > > This info is stored in the 'atttypmod' field of pg_attribute, but I > don't know whether DBD::Pg provides any handy interface to that. You > might have to get down-and-dirty enough to select it directly out of > pg_attribute ... Ah, I got it. Just do a select. It's a system table I hadn't learned about yet. And, I have to subtract 4 from it to get the real length. Thanks!! Michelle ------------ Michelle Murrain, Ph.D. President Norwottuck Technology Resources mpm@norwottuck.com http://www.norwottuck.com
On Monday 05 March 2001 07:05 pm, Tom Lane wrote: > Join attrelid against the OID column of pg_class ... Um, which column? When I look at that table, I see the following columns... relname | reltype | relowner | relam | relpages | reltuples | rellongrelid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhaspkey | relhasrules | relacl Thanks again! Michelle ------------ Michelle Murrain, Ph.D. President Norwottuck Technology Resources mpm@norwottuck.com http://www.norwottuck.com
Michelle Murrain wrote: > > On Monday 05 March 2001 07:05 pm, Tom Lane wrote: > > Join attrelid against the OID column of pg_class ... > > Um, which column? When I look at that table, I see the following columns... > > relname | reltype | relowner | relam | relpages | reltuples | > rellongrelid | relhasindex | relisshared | relkind | relnatts | relchecks | > reltriggers | relukeys | relfkeys | relrefs | relhaspkey | relhasrules | > relacl > Yup, indeed there is no such field in pg_class. Tom? -- Nothing Like the Sun
On Mon, 5 Mar 2001, Boulat Khakimov wrote: > Michelle Murrain wrote: > > > > On Monday 05 March 2001 07:05 pm, Tom Lane wrote: > > > Join attrelid against the OID column of pg_class ... > > > > Um, which column? When I look at that table, I see the following columns... > > > > relname | reltype | relowner | relam | relpages | reltuples | > > rellongrelid | relhasindex | relisshared | relkind | relnatts | relchecks | > > reltriggers | relukeys | relfkeys | relrefs | relhaspkey | relhasrules | > > relacl > > > > Yup, indeed there is no such field in pg_class. Figure I can field this one in hopes of giving Tom more time for other things :) It's a system column, so it doesn't show up in the column list that you see from a select * query, but if you do a select oid from pg_class; you'll get it.
> > Yup, indeed there is no such field in pg_class.
>
> Figure I can field this one in hopes of giving Tom more time for other
> things :)
>
> It's a system column, so it doesn't show up in the
> column list that you see from a select * query, but if you do a
> select oid from pg_class; you'll get it.
Let me use this system catalog discussion to announce the completion of
my database internals presentation at:
http://candle.pha.pa.us/main/writings/internals.pdf
There is a system catalog diagram near the end.
Comments welcomed.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026