Table name lengths...

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 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

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 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 !!!!!!!!

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)