Thread: Table name lengths...

Table name lengths...

From
Chris Bowlby
Date:
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


Re: Table name lengths...

From
Richard Huxton
Date:
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

Re: Table name lengths...

From
Ron Johnson
Date:
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 !!!!!!!!


Re: Table name lengths...

From
Alvaro Herrera
Date:
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)

Re: Table name lengths...

From
Chris Bowlby
Date:
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


Re: Table name lengths...

From
Tom Lane
Date:
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

Re: Table name lengths...

From
Alvaro Herrera
Date:
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)

Re: Table name lengths...

From
Tom Lane
Date:
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

Re: Table name lengths...

From
Chris Bowlby
Date:
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


Re: Table name lengths...

From
Ron Johnson
Date:
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)