Thread: 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:

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.

SO I can do something like this?

select character_maximum_length from information_schema.columns where table_name='rr' and column_name='data';

Yes?

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