Thread: Table name lengths...
Hi All, I've noticed that in the pg_type system table, there is a data type called "name", would that represent the definition of the table name space, including the max length a talbe name space could be? If so where would I find the same definition for the max name space for a sequence, or index... -- Chris Bowlby <excalibur@hub.org> Hub.Org Networking Services
On Tuesday 02 September 2003 17:55, Chris Bowlby wrote: > Hi All, > > I've noticed that in the pg_type system table, there is a data type > called "name", would that represent the definition of the table name > space, including the max length a talbe name space could be? If so where > would I find the same definition for the max name space for a sequence, > or index... All names are the same length. This is 64 characters, but I _think_ it can be changed with a recompile. -- Richard Huxton Archonet Ltd
On Tue, 2003-09-02 at 11:55, Chris Bowlby wrote: > Hi All, > > I've noticed that in the pg_type system table, there is a data type > called "name", would that represent the definition of the table name > space, including the max length a talbe name space could be? If so where > would I find the same definition for the max name space for a sequence, > or index... No matter how long PostgreSQL lets you make table names, I'd stick with ANSI standard 31 characters. -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA Causation does NOT equal correlation !!!!!!!!
On Tue, Sep 02, 2003 at 06:24:55PM +0100, Richard Huxton wrote: > On Tuesday 02 September 2003 17:55, Chris Bowlby wrote: > > I've noticed that in the pg_type system table, there is a data type > > called "name", would that represent the definition of the table name > > space, including the max length a talbe name space could be? If so where > > would I find the same definition for the max name space for a sequence, > > or index... > > All names are the same length. This is 64 characters, but I _think_ it can be > changed with a recompile. 63 characters IIRC (the 64th is used for a trailing \0, I think). -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "The first of April is the day we remember what we are the other 364 days of the year" (Mark Twain)
On Tue, 2003-09-02 at 14:24, Richard Huxton wrote: I'm working with an application that needs to be able to determine this depending on the version of PostgreSQL that is running. I know what anything less then 7.3 was 32 characters and anything newer is currently 64, but I'm hoping to be able to check the lengths from my application before creating a table/sequence/index, etc.. The names have to be generated from the application and as such by allowing a dynamic means to search for them I wont need to "hard code" it.. > On Tuesday 02 September 2003 17:55, Chris Bowlby wrote: > > Hi All, > > > > I've noticed that in the pg_type system table, there is a data type > > called "name", would that represent the definition of the table name > > space, including the max length a talbe name space could be? If so where > > would I find the same definition for the max name space for a sequence, > > or index... > > All names are the same length. This is 64 characters, but I _think_ it can be > changed with a recompile. -- Chris Bowlby <excalibur@hub.org> Hub.Org Networking Services
Chris Bowlby <excalibur@hub.org> writes:
> I'm working with an application that needs to be able to determine this
> depending on the version of PostgreSQL that is running. I know what
> anything less then 7.3 was 32 characters and anything newer is currently
> 64, but I'm hoping to be able to check the lengths from my application
> before creating a table/sequence/index, etc..
I'd do
SELECT 1 AS "some really long string here";
and see how many characters come back in the column title ...
regards, tom lane
On Tue, Sep 02, 2003 at 02:33:00PM -0300, Chris Bowlby wrote:
> On Tue, 2003-09-02 at 14:24, Richard Huxton wrote:
>
> I'm working with an application that needs to be able to determine this
> depending on the version of PostgreSQL that is running. I know what
> anything less then 7.3 was 32 characters and anything newer is currently
> 64, but I'm hoping to be able to check the lengths from my application
> before creating a table/sequence/index, etc..
Cast a long string to the name type and measure its length:
test=> SELECT length(repeat('xyzzy', 100)::name);
length
--------
63
(1 row)
--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos / con todos los humanos acabaré (Bender)
Ron Johnson <ron.l.johnson@cox.net> writes:
> No matter how long PostgreSQL lets you make table names, I'd stick
> with ANSI standard 31 characters.
"ANSI standard"? SQL92 specifies that names can be up to 128 characters
long. Perhaps there was a shorter limit in SQL89, but that ranks as
ancient history now.
(In fact, I just now realized that it says *characters*, not *bytes*,
which means that in a multibyte encoding you could need quite a bit more
than 128 bytes to meet the spec's requirement...)
regards, tom lane
On Tue, 2003-09-02 at 14:46, Alvaro Herrera wrote:
> Cast a long string to the name type and measure its length:
>
> test=> SELECT length(repeat('xyzzy', 100)::name);
> length
> --------
> 63
> (1 row)
Cool, thanks that will work fairly well..
--
Chris Bowlby <excalibur@hub.org>
Hub.Org Networking Services
On Tue, 2003-09-02 at 12:47, Tom Lane wrote: > Ron Johnson <ron.l.johnson@cox.net> writes: > > No matter how long PostgreSQL lets you make table names, I'd stick > > with ANSI standard 31 characters. > > "ANSI standard"? SQL92 specifies that names can be up to 128 characters > long. Perhaps there was a shorter limit in SQL89, but that ranks as > ancient history now. > > (In fact, I just now realized that it says *characters*, not *bytes*, > which means that in a multibyte encoding you could need quite a bit more > than 128 bytes to meet the spec's requirement...) Ok, color me erroneous. The 31 octet length is on Rdb/VMS, and was picked because that's how long VMS file names were/are. Also, Oracle has an object limit of 30 characters. -- ----------------------------------------------------------------- Ron Johnson, Jr. ron.l.johnson@cox.net Jefferson, LA USA Regarding war zones: "There's nothing sacrosanct about a hotel with a bunch of journalists in it." Marine Lt. Gen. Bernard E. Trainor (Retired)