Thread: 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) >