Re: Name limitation question

Hi all -

I have a quick question. Is there a limit to the length of a table name / column name / procedure name? For example, Sybase has a limit of 32 characters for stored procedure names, or some such. Is there such a limit in PostgreSQL?

Thank you.

Laura
Total Novice!!

Laura,

> I have a quick question. Is there a limit to the length of a table name /
> column name / procedure name? For example, Sybase has a limit of 32
> characters for stored procedure names, or some such. Is there such a limit
> in PostgreSQL?

We had a limit of first 31, then 63 characters. Either in 7.3 or in 7.4
(beta) that was extended to 128 characters, which should be enough for
anybody. Unfortunately, I'm not sure what versions of Postgres each of these
limits is associated with, but I'm sure that someone else on this list will
speak up.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> We had a limit of first 31, then 63 characters. Either in 7.3 or in 7.4
> (beta) that was extended to 128 characters, which should be enough for
> anybody.

No, the default is still 63, and will probably stay there. You can
however alter NAMEDATALEN when building the software, if you are hot
to have a different limit.

regards, tom lane

>>We had a limit of first 31, then 63 characters. Either in 7.3 or in 7.4
>>(beta) that was extended to 128 characters, which should be enough for
>>anybody.
>
>
> No, the default is still 63, and will probably stay there. You can
> however alter NAMEDATALEN when building the software, if you are hot
> to have a different limit.

Isn't the SQL standard 128 chars?

Kaarel <kaarel(at)future(dot)ee> writes:
>> No, the default is still 63, and will probably stay there.

> Isn't the SQL standard 128 chars?

Yes. We tested that a few months ago when we raised the limit from 31
to 63, and determined that there was a nontrivial additional speed and
space penalty to raising it to 128. Since nobody could muster a
real-world use case that actually required 128, we didn't go there.
But if you feel you need to check off that particular SQL-compliance
box, see NAMEDATALEN in postgres_ext.h.

regards, tom lane

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

> > Isn't the SQL standard 128 chars?
>
> Yes. We tested that a few months ago when we raised the limit from 31
> to 63, and determined that there was a nontrivial additional speed and
> space penalty to raising it to 128. Since nobody could muster a
> real-world use case that actually required 128, we didn't go there.
> But if you feel you need to check off that particular SQL-compliance
> box, see NAMEDATALEN in postgres_ext.h.

Would it make more sense to put this in as a configure option? It
might be rarely changed, but it does seem like the right place for it.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200306251101

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE++b0LvJuQZxSWSsgRAiMxAKD3GEYVETU++nq6ye4iR8MEsmUBpgCfRngF
CW1ypGDmDFUsGCEkaTd6+PU=
=bmFP
-----END PGP SIGNATURE-----

Added to TODO:

* Allow the identifier length to be increased via a configure
option

---------------------------------------------------------------------------

greg(at)turnstep(dot)com wrote:
[ There is text before PGP section. ]
>
[ PGP not available, raw data follows ]
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
> > > Isn't the SQL standard 128 chars?
> >
> > Yes. We tested that a few months ago when we raised the limit from 31
> > to 63, and determined that there was a nontrivial additional speed and
> > space penalty to raising it to 128. Since nobody could muster a
> > real-world use case that actually required 128, we didn't go there.
> > But if you feel you need to check off that particular SQL-compliance
> > box, see NAMEDATALEN in postgres_ext.h.
>
>
> Would it make more sense to put this in as a configure option? It
> might be rarely changed, but it does seem like the right place for it.
>
>
> - --
> Greg Sabino Mullane greg(at)turnstep(dot)com
> PGP Key: 0x14964AC8 200306251101
>
> -----BEGIN PGP SIGNATURE-----
> Comment: http://www.turnstep.com/pgp.html
>
> iD8DBQE++b0LvJuQZxSWSsgRAiMxAKD3GEYVETU++nq6ye4iR8MEsmUBpgCfRngF
> CW1ypGDmDFUsGCEkaTd6+PU=
> =bmFP
> -----END PGP SIGNATURE-----
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>
[ Decrypting message... End of raw data. ]

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073