Thread: Data types mapping
I have a partial mapping of some of the fields with their internal type, e.g.: integer 4 smallint 5 bigint 8 date 9 varchar 12 boolean 16 text 25 What internal table has the complete mapping? I looked around on the Postgres site and thought it might be in pg_attribute, pg_attrdef, or pg_type, but it's not -- or else I'm not performing the right query. Thanks. -doug ps - These values are "set in stone" and don't change with updates, correct?
Doug Silver <dsilver@urchin.com> writes: > I have a partial mapping of some of the fields with their internal type, e.g.: > integer 4 > smallint 5 Some of these are wrong ... from memory, int4 is 23. > What internal table has the complete mapping? SELECT oid, typname FROM pg_type This will give you a lot of things you did not realize were types, too. You might prefer to ignore rows with typtype != 'b'. regards, tom lane
On Thursday 10 April 2003 11:19 am, you wrote: > Doug Silver <dsilver@urchin.com> writes: > > I have a partial mapping of some of the fields with their internal type, > > e.g.: integer 4 > > smallint 5 > > Some of these are wrong ... from memory, int4 is 23. > > > What internal table has the complete mapping? > > SELECT oid, typname FROM pg_type > > This will give you a lot of things you did not realize were types, too. > You might prefer to ignore rows with typtype != 'b'. > > regards, tom lane Hi Tom - That's what I thought too, but when I run the following perl script I get this: \d test Table "public.test" Column | Type | Modifiers --------+----------------------+----------- a | smallint | b | integer | c | bigint | d | numeric(8,2) | e | character varying(5) | f | text | g | character(1) | h | boolean | >cat test.pl use strict; use warnings; use DBI; [snip setting up connection] my $sth = $dbh->prepare("select * from test where 1=2"); my $rc=$sth->execute; my @field_name = @{$sth->{NAME}}; my @field_type = @{$sth->{TYPE}}; for (my $i=0;$i<@field_name;$i++){ print "Field $field_name[$i] = $field_type[$i]\n"; } > ./test.pl Field a = 5 Field b = 4 Field c = 8 Field d = 1700 (correct) Field e = 12 Field f = 25 (correct) Field g = 1 Field h = 16 (correct) So would you think this a problem with the perl module DBI or DBD-Pg (or both)? -doug
Doug Silver <dsilver@urchin.com> writes: > That's what I thought too, but when I run the following perl script I get > this: > for (my $i=0;$i<@field_name;$i++){ > print "Field $field_name[$i] = $field_type[$i]\n"; > } I dunno exactly how DBI defines {TYPE}, but the evidence here suggests that it's got its own code numbers for certain common datatypes. Probably DBD::Pg is converting the OIDs to DBI code numbers for types that DBI defines a code for, and just returning the OID for everything else. You'd have to look at the DBD::Pg code to be sure ... regards, tom lane