Обсуждение: Problem - PostgreSQL Truncating Column Names to 63 Characters

Hello everyone,

I guess I'm not typing in the right search string for my question
because I can't find any answer on the Internet. So I'm coming to all
of you for help.

I'm performing a cross-tab type query in PostgreSQL where the returned
data in rows become my column names. The problem I'm facing is that
PostgreSQL is truncating the column names down to 63 characters, and
some of my names need to be a little larger than that. Is a 63 column
name length a limit in PostgreSQL, or is there a
setting/function/compile time option I can enact to overcome this
problem?

Thank you for any help,

Darren H.

Darren Houston <darren.houston@gmail.com> writes:
> PostgreSQL is truncating the column names down to 63 characters, and
> some of my names need to be a little larger than that. Is a 63 column
> name length a limit in PostgreSQL, or is there a
> setting/function/compile time option I can enact to overcome this
> problem?

See NAMEDATALEN ... the pghackers archives contain some traffic about
the performance penalty for increasing it ...

            regards, tom lane

On Fri, May 13, 2005 at 10:07:43AM -0600, Darren Houston wrote:
> Hello everyone,
>
> I guess I'm not typing in the right search string for my question
> because I can't find any answer on the Internet. So I'm coming to all
> of you for help.
>
> I'm performing a cross-tab type query in PostgreSQL where the returned
> data in rows become my column names. The problem I'm facing is that
> PostgreSQL is truncating the column names down to 63 characters, and
> some of my names need to be a little larger than that. Is a 63 column
> name length a limit in PostgreSQL, or is there a
> setting/function/compile time option I can enact to overcome this
> problem?

Yes, you can change it at compile time.  It's the NAMEDATALEN
definition.  Don't expect the resulting data directory to be compatible
with vanilla sources though -- you will only be able to use that with
patches sources.  Of course, if you produce dump files with long names,
they will be truncated if exported into nonpatched systems too, so take
caution.

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)

Hello Tom and Alvaro,

The solution you guys provided is exactly the answer I needed.

Thank you for your help and saving me time,

Darren H.

On 5/13/05, Alvaro Herrera <alvherre@surnet.cl> wrote:
> On Fri, May 13, 2005 at 10:07:43AM -0600, Darren Houston wrote:
> > Hello everyone,
> >
> > I guess I'm not typing in the right search string for my question
> > because I can't find any answer on the Internet. So I'm coming to all
> > of you for help.
> >
> > I'm performing a cross-tab type query in PostgreSQL where the returned
> > data in rows become my column names. The problem I'm facing is that
> > PostgreSQL is truncating the column names down to 63 characters, and
> > some of my names need to be a little larger than that. Is a 63 column
> > name length a limit in PostgreSQL, or is there a
> > setting/function/compile time option I can enact to overcome this
> > problem?
>
> Yes, you can change it at compile time.  It's the NAMEDATALEN
> definition.  Don't expect the resulting data directory to be compatible
> with vanilla sources though -- you will only be able to use that with
> patches sources.  Of course, if you produce dump files with long names,
> they will be truncated if exported into nonpatched systems too, so take
> caution.
>
> --
> Alvaro Herrera (<alvherre[a]surnet.cl>)
> "La primera ley de las demostraciones en vivo es: no trate de usar el sistema.
> Escriba un guión que no toque nada para no causar daños." (Jakob Nielsen)
>