Обсуждение: psql \d command
Hi all, I see now \d shows indexes, I tried to display index structure but it show the wrong length of fields: Table = figure +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | azienda | char() not null | 11 | | tipo | char() not null | 2 | | gruppo | char() not null | 2 | | inizio_attivita | date not null | 4 | | fine_attivita | date | 4 | | codice_figura | char() not null | 11 | +----------------------------------+----------------------------------+-------+ Index: figure_pkey hygea=> \d figure_pkey Table = figure_pkey +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ it should be: | azienda | char() | -4 |<<-- 11 | tipo | char() | -4 |<<-- 02 | gruppo | char() | -4 |<<-- 02 | inizio_attivita | date | 4 |<<-- 04 +----------------------------------+----------------------------------+-------+ Jose'
>
> Hi all,
>
> I see now \d shows indexes, I tried to display index structure but it
> show the wrong
> length of fields:
>
> [...]
>
> hygea=> \d figure_pkey
>
> Table = figure_pkey
> +----------------------------------+----------------------------------+-------+
> | Field | Type |
> Length|
> +----------------------------------+----------------------------------+-------+
> it should be:
> | azienda | char()
> | -4 |<<-- 11
> | tipo | char()
> | -4 |<<-- 02
> | gruppo | char()
> | -4 |<<-- 02
> | inizio_attivita | date
> | 4 |<<-- 04
> +----------------------------------+----------------------------------+-------+
The atttypmod value off all index attributes is 0 in
pg_attribute. That's the reason why \d shows this. This
information is not required for indices because the datum
given to the index access methods comes from the heap tuples
and it must already have the correct size if it reaches
there.
For data types of variable size, an atttypmod value of -1
means variable, a value of n means size = n-4 (4 is the size
of the variable length datum header VARHDRSZ). Don't worry,
you cannot select from an index directly, so it doesn't
matter. But psql might get enhanced for 6.5 to lookup the
atttypmod of the indexed field in the table instead of using
that from the index.
BTW: You might also want to look at the pg_indexes view.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Hello Jan, giovedì, 22 ottobre 98, you wrote: >> >> Hi all, >> >> I see now \d shows indexes, I tried to display index structure but it >> show the wrong >> length of fields: >> >> [...] >> >> hygea=> \d figure_pkey >> >> Table = figure_pkey >> +----------------------------------+----------------------------------+-------+ >> | Field | Type | >> Length| >> +----------------------------------+----------------------------------+-------+ >> it should be: >> | azienda | char() >> | -4 |<<-- 11 >> | tipo | char() >> | -4 |<<-- 02 >> | gruppo | char() >> | -4 |<<-- 02 >> | inizio_attivita | date >> | 4 |<<-- 04 >> +----------------------------------+----------------------------------+-------+ JW> The atttypmod value off all index attributes is 0 in JW> pg_attribute. That's the reason why \d shows this. This JW> information is not required for indices because the datum JW> given to the index access methods comes from the heap tuples JW> and it must already have the correct size if it reaches JW> there. JW> For data types of variable size, an atttypmod value of -1 JW> means variable, a value of n means size = n-4 (4 is the size JW> of the variable length datum header VARHDRSZ). Don't worry, JW> you cannot select from an index directly, so it doesn't JW> matter. But psql might get enhanced for 6.5 to lookup the JW> atttypmod of the indexed field in the table instead of using JW> that from the index. I'm not worry, only a aesthetical question. Thanks for reply.
> Hi all, > > I see now \d shows indexes, I tried to display index structure but it > show the wrong > length of fields: > > Table = figure > +----------------------------------+----------------------------------+-------+ > | Field | Type | > Length| > +----------------------------------+----------------------------------+-------+ > | azienda | char() not null > | 11 | > | tipo | char() not null > | 2 | > | gruppo | char() not null > | 2 | > | inizio_attivita | date not null > | 4 | > | fine_attivita | date > | 4 | > | codice_figura | char() not null > | 11 | > +----------------------------------+----------------------------------+-------+ > Index: figure_pkey > hygea=> \d figure_pkey > > Table = figure_pkey > +----------------------------------+----------------------------------+-------+ > | Field | Type | > Length| > +----------------------------------+----------------------------------+-------+ > it should be: > | azienda | char() > | -4 |<<-- 11 > | tipo | char() > | -4 |<<-- 02 > | gruppo | char() > | -4 |<<-- 02 > | inizio_attivita | date > | 4 |<<-- 04 > +----------------------------------+----------------------------------+-------+ Added to TODO: * psql \d on index with char()/varchar() fields shows improper length -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026