Обсуждение: 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