Обсуждение: Querying the schema for column widths - what syntax do I use?
I am working on some upgrades to the MyDNS open source product. I have some expertise in MySQL but am not overly familiar with PostgreSQL and need some guidance on how to query the schema for the maximum size of data a column can hold. In MySQL I can do either: a "DESCRIBE" command or do "SELECT COLUMNS" command. And then parse the result for the length in the type column of the row returned. How would I do a similar function using PostgreSQL - I have tried to find this in the manuals and in this mailing list but not found any pointers to get me started. Apologies for asking such a simple question but I am being a bit lazy as I want to get on with releasing the MyDNS code. Regards, Howard.
Howard Wilkinson wrote: > I am working on some upgrades to the MyDNS open source product. I have > some expertise in MySQL but am not overly familiar with PostgreSQL and > need some guidance on how to query the schema for the maximum size of > data a column can hold. Unless you're after PG-specific stuff, it's probably best to use the information-schema. http://www.postgresql.org/docs/8.3/static/information-schema.html This has a standard layout cross-database, so will help you to extend you app across other systems. I think it's supported in the latest version of MySQL too. -- Richard Huxton Archonet Ltd
am Thu, dem 21.02.2008, um 10:44:32 +0000 mailte Howard Wilkinson folgendes: > I am working on some upgrades to the MyDNS open source product. I have > some expertise in MySQL but am not overly familiar with PostgreSQL and > need some guidance on how to query the schema for the maximum size of > data a column can hold. > > In MySQL I can do either: a "DESCRIBE" command or do "SELECT COLUMNS" > command. And then parse the result for the length in the type column of > the row returned. How would I do a similar function using PostgreSQL - I > have tried to find this in the manuals and in this mailing list but not > found any pointers to get me started. http://www.alberton.info/postgresql_meta_info.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Richard Huxton wrote:
select character_maximum_length from information_schema.columns where table_name='rr' and column_name='data';
Yes?
Howard Wilkinson wrote:SO I can do something like this?I am working on some upgrades to the MyDNS open source product. I have some expertise in MySQL but am not overly familiar with PostgreSQL and need some guidance on how to query the schema for the maximum size of data a column can hold.
Unless you're after PG-specific stuff, it's probably best to use the information-schema.
http://www.postgresql.org/docs/8.3/static/information-schema.html
This has a standard layout cross-database, so will help you to extend you app across other systems. I think it's supported in the latest version of MySQL too.
select character_maximum_length from information_schema.columns where table_name='rr' and column_name='data';
Yes?
--
Howard Wilkinson | Phone: | +44(20)76907075 |
Coherent Technology Limited | Fax: |
|
23 Northampton Square, | Mobile: | +44(7980)639379 |
United Kingdom, EC1V 0HL | Email: |
am Thu, dem 21.02.2008, um 12:42:51 +0000 mailte Howard Wilkinson folgendes: > SO I can do something like this? > > select character_maximum_length from information_schema.columns where > table_name='rr' and column_name='data'; > > Yes? Yes. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hi Howard, Howard Wilkinson wrote: > I am working on some upgrades to the MyDNS open source product. I have > some expertise in MySQL but am not overly familiar with PostgreSQL and > need some guidance on how to query the schema for the maximum size of > data a column can hold. > > In MySQL I can do either: a "DESCRIBE" command or do "SELECT COLUMNS" > command. And then parse the result for the length in the type column of > the row returned. How would I do a similar function using PostgreSQL - I > have tried to find this in the manuals and in this mailing list but not > found any pointers to get me started. > > Apologies for asking such a simple question but I am being a bit lazy as > I want to get on with releasing the MyDNS code. beside the correct answers you got relating the informational_schema, since I do not know what MyDNS is and what you are doing with the maximum size of the column, are you aware that postgresql bails out if you put in a string which exceeds the column size (so you can just try rather then check beforehand if thats what you do) or you also get the description in the cursor when you do the select on a table. Also, text type could be used to hold potentially large strings without harm (so if the string is short, its no difference but you can easily go up to over a gig) Regards Tino
Tino Wildenhain wrote: > Hi Howard, > > Howard Wilkinson wrote: >> I am working on some upgrades to the MyDNS open source product. I >> have some expertise in MySQL but am not overly familiar with >> PostgreSQL and need some guidance on how to query the schema for the >> maximum size of data a column can hold. >> >> In MySQL I can do either: a "DESCRIBE" command or do "SELECT COLUMNS" >> command. And then parse the result for the length in the type column >> of the row returned. How would I do a similar function using >> PostgreSQL - I have tried to find this in the manuals and in this >> mailing list but not found any pointers to get me started. >> >> Apologies for asking such a simple question but I am being a bit lazy >> as I want to get on with releasing the MyDNS code. > > beside the correct answers you got relating the informational_schema, > since I do not know what MyDNS is and what you are doing with the > maximum size of the column, are you aware that postgresql bails out > if you put in a string which exceeds the column size (so you can just > try rather then check beforehand if thats what you do) or you also > get the description in the cursor when you do the select on a table. > > Also, text type could be used to hold potentially large strings without > harm (so if the string is short, its no difference but you can easily go > up to over a gig) > > Regards > Tino The package is a DNS server originally hosted on top of a MySQL data base. I am extending it in a number of ways, but this particular need arises as I need to store some data (binary in nature) in a field that is part of the key for an index. The data is < 65536 in length. However, most of the time it is <<<<<<65536 e.g. 4 bytes. I also need to support backwards compatibility with the previous releases which had limited storage capacity in this field. I have therefore chosen to detect when the field overflows the maximum storage capacity for the 'data' column and split the data into 'data' (truncated) and 'edata' (the rest). As I do not know what size the 'data' field is I needed to detect it dynamically and do the split before storing the data/edata. I have defined a further column 'edatakey' which takes an MD5 has of 'edata' when present and is included in the index on the table. It all seems to be working on MySQL 5.0.46 - have not tested the MD5 code yet - and I hope will with PostgreSQL when somebody tries it. Howard.
On Fri, Feb 22, 2008 at 11:38:33AM +0000, Howard Wilkinson wrote: > The package is a DNS server originally hosted on top of a MySQL data > base. I am extending it in a number of ways, but this particular need > arises as I need to store some data (binary in nature) in a field that > is part of the key for an index. The data is < 65536 in length. However, > most of the time it is <<<<<<65536 e.g. 4 bytes. I also need to support > backwards compatibility with the previous releases which had limited > storage capacity in this field. Just remember that if you're going to index the data that a btree index will handle a maximum of 2700 *bytes* which, depending on your encoding might be many less characters. This is independant of the maximum size of the datatype. How you deal with this depends on why exactly you want it indexed. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy