Thread: Column name truncation

Column name truncation

From
Frank Voellmann
Date:
I've upgraded postgresl from 7.1 to 7.3 and am having an issue with the
following:

7.1 would truncate column names greater than 31 characters to 31.

7.3 does not do this, so that backups previously done via pg_dump in 7.1
don't play nicely with my code accessing 7.3


Is there a way to force 7.3 to truncate at 31 characters too?
Or,
Is there a way to test whether a (truncated) column name is in a table? and
if true, rename the column name to it's full (pre-truncated) name?

Thanks


Re: Column name truncation

From
Oliver Fromme
Date:
Frank Voellmann wrote:
 > I've upgraded postgresl from 7.1 to 7.3 and am having an issue with the
 > following:
 >
 > 7.1 would truncate column names greater than 31 characters to 31.
 >
 > 7.3 does not do this, so that backups previously done via pg_dump in 7.1
 > don't play nicely with my code accessing 7.3

If your code relies on the truncation to 31 characters, then
your code is broken and should be fixed, I think.

 > Is there a way to force 7.3 to truncate at 31 characters too?
 > Or,
 > Is there a way to test whether a (truncated) column name is in a table?

Yup.  All column definitions are in the pg_attribute table
(in the pg_catalog system schema).  The column "attname"
contains all column names.  You should select those where
the column "attrelid" is the OID of your table(s), and
"attnum" is > 0 (those < 0 are system columns such as OID,
current transaction ID etc.).

For example:

   SELECT c.oid FROM pg_catalog.pg_class c
   WHERE c.relname = 'foo'

will give you the OID of the table "foo".  Suppose it is
42, then you do:

   SELECT a.attname FROM pg_catalog.pg_attribute a
   WHERE a.attrelid = 42 AND a.attnum > 0

That'll give you the names of all columns in the table.
Within the psql client program, you can use the "\d"
meta command to display the definitions of your tables,
of course.

 > and
 > if true, rename the column name to it's full (pre-truncated) name?

Sure, "alter table ... rename column ...", see the docs.

Best regards
   Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"Python tricks" is a tough one, cuz the language is so clean. E.g.,
C makes an art of confusing pointers with arrays and strings, which
leads to lotsa neat pointer tricks; APL mistakes everything for an
array, leading to neat one-liners; and Perl confuses everything
period, making each line a joyous adventure <wink>.
        -- Tim Peters