Thread: Data length and data precision
<p><font size="2">Hi all,<br /><br /> I'm trying to write some code to make a 'fingerprint' of a database. This to comparea customer database with a reference database of our own.<br /> Therefore I'm trying to retrieve information likethis:<br /> -Table name<br /> pg_class.relname where relkind = 'r'<br /> -Column name<br /> pg_attribute where attrelid= pg_class.oid<br /> -Data type<br /> pg_type where oid = pg_attribute.atttypid<br /> -Data length<br /> if pg_attribute.atttypmod= -1 then pg_type.typlen<br /> else pg_attribute.atttypmod<br /> -Data precision<br /> No clue<br/> -Nullable<br /> pg_attribute.attnotnull<br /><br /> As you can see I'm missing the data precision (e.g. numeric(19,2)) and I'm not entirely sure about the data length.<br /><br /> Can anyone tell me where to find the data precisionof a column?<br /> And can anyone tell me if I get the data length from the correct places?<br /><br /> Thanks inadvance,<br /><br /> Bart</font>
In response to Bart van Houdt : > Hi all, > > I'm trying to write some code to make a 'fingerprint' of a database. This to > compare a customer database with a reference database of our own. > Therefore I'm trying to retrieve information like this: > -Table name > pg_class.relname where relkind = 'r' > -Column name > pg_attribute where attrelid = pg_class.oid > -Data type > pg_type where oid = pg_attribute.atttypid > -Data length > if pg_attribute.atttypmod = -1 then pg_type.typlen > else pg_attribute.atttypmod > -Data precision > No clue > -Nullable > pg_attribute.attnotnull > > As you can see I'm missing the data precision (e.g. numeric (19,2)) and I'm not > entirely sure about the data length. > > Can anyone tell me where to find the data precision of a column? > And can anyone tell me if I get the data length from the correct places? information_schema.columns, numeric_precision. http://www.postgresql.org/docs/current/static/infoschema-columns.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
<p><font size="2">Just what I needed, thx!<br /><br /><br /> -----Oorspronkelijk bericht-----<br /> Van: pgsql-sql-owner@postgresql.orgnamens A. Kretschmer<br /> Verzonden: vr 6-2-2009 11:04<br /> Aan: pgsql-sql@postgresql.org<br/> Onderwerp: Re: [SQL] Data length and data precision<br /><br /> In response to Bart van Houdt:<br /> > Hi all,<br /> ><br /> > I'm trying to write some code to make a 'fingerprint' of a database. Thisto<br /> > compare a customer database with a reference database of our own.<br /> > Therefore I'm trying to retrieveinformation like this:<br /> > -Table name<br /> > pg_class.relname where relkind = 'r'<br /> > -Columnname<br /> > pg_attribute where attrelid = pg_class.oid<br /> > -Data type<br /> > pg_type where oid= pg_attribute.atttypid<br /> > -Data length<br /> > if pg_attribute.atttypmod = -1 then pg_type.typlen<br />> else pg_attribute.atttypmod<br /> > -Data precision<br /> > No clue<br /> > -Nullable<br /> > pg_attribute.attnotnull<br/> ><br /> > As you can see I'm missing the data precision (e.g. numeric (19,2)) and I'mnot<br /> > entirely sure about the data length.<br /> ><br /> > Can anyone tell me where to find the data precisionof a column?<br /> > And can anyone tell me if I get the data length from the correct places?<br /><br /> information_schema.columns,numeric_precision.<br /><a href="http://www.postgresql.org/docs/current/static/infoschema-columns.html">http://www.postgresql.org/docs/current/static/infoschema-columns.html</a><br /><br/><br /> Andreas<br /> --<br /> Andreas Kretschmer<br /> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr:-> Header)<br /> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA <a href="http://wwwkeys.de.pgp.net">http://wwwkeys.de.pgp.net</a><br/><br /> --<br /> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql">http://www.postgresql.org/mailpref/pgsql-sql</a><br/><br /></font>