Table name lengths...

[Chris Bowlby]

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 <ex*******@hub.org>
Hub.Org Networking Services
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Ron Johnson]

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. ro***********@cox.net
Jefferson, LA USA

Causation does NOT equal correlation !!!!!!!!
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Alvaro Herrera]

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)

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Chris Bowlby]

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 <ex*******@hub.org>
Hub.Org Networking Services
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Chris Bowlby <ex*******@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Alvaro Herrera]

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)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Tom Lane]

Ron Johnson <ro***********@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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Chris Bowlby]

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 <ex*******@hub.org>
Hub.Org Networking Services
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Ron Johnson]

On Tue, 2003-09-02 at 12:47, Tom Lane wrote:

Ron Johnson <ro***********@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. ro***********@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)
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster