Table name lengths...

**Richard Huxton** · Nov 11 '05, 11:37 PM

Re: Table name lengths...

On Tuesday 02 September 2003 17:55, Chris Bowlby wrote:[color=blue]
> 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...[/color]

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?

PostgreSQL: Not Found

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

**Ron Johnson** · Nov 11 '05, 11:37 PM

Re: Table name lengths...

On Tue, 2003-09-02 at 11:55, Chris Bowlby wrote:[color=blue]
> 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...[/color]

No matter how long PostgreSQL lets you make table names, I'd stick
with ANSI standard 31 characters.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ron.l.johnson@c ox.net
Jefferson, LA USA

Causation does NOT equal correlation !!!!!!!!

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

PostgreSQL: Not Found

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

**Alvaro Herrera** · Nov 11 '05, 11:37 PM

Re: Table name lengths...

On Tue, Sep 02, 2003 at 06:24:55PM +0100, Richard Huxton wrote:[color=blue]
> On Tuesday 02 September 2003 17:55, Chris Bowlby wrote:[/color]
[color=blue][color=green]
> > 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...[/color]
>
> All names are the same length. This is 64 characters, but I _think_ it can be
> changed with a recompile.[/color]

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** · Nov 11 '05, 11:37 PM

Re: Table name lengths...

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..
[color=blue]
> On Tuesday 02 September 2003 17:55, Chris Bowlby wrote:[color=green]
> > 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...[/color]
>
> All names are the same length. This is 64 characters, but I _think_ it can be
> changed with a recompile.[/color]
--
Chris Bowlby <excalibur@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** · Nov 11 '05, 11:37 PM

Re: Table name lengths...

Chris Bowlby <excalibur@hub. org> writes:[color=blue]
> 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..[/color]

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** · Nov 11 '05, 11:37 PM

Re: Table name lengths...

On Tue, Sep 02, 2003 at 02:33:00PM -0300, Chris Bowlby wrote:[color=blue]
> 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..[/color]

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** · Nov 11 '05, 11:37 PM

Re: Table name lengths...

Ron Johnson <ron.l.johnson@ cox.net> writes:[color=blue]
> No matter how long PostgreSQL lets you make table names, I'd stick
> with ANSI standard 31 characters.[/color]

"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 YourEmailAddres sHere" to majordomo@postg resql.org)

**Chris Bowlby** · Nov 11 '05, 11:37 PM

Re: Table name lengths...

On Tue, 2003-09-02 at 14:46, Alvaro Herrera wrote:
[color=blue]
> Cast a long string to the name type and measure its length:
>
> test=> SELECT length(repeat(' xyzzy', 100)::name);
> length
> --------
> 63
> (1 row)[/color]

Cool, thanks that will work fairly well..

--
Chris Bowlby <excalibur@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 YourEmailAddres sHere" to majordomo@postg resql.org)

**Ron Johnson** · Nov 11 '05, 11:37 PM

Re: Table name lengths...

On Tue, 2003-09-02 at 12:47, Tom Lane wrote:[color=blue]
> Ron Johnson <ron.l.johnson@ cox.net> writes:[color=green]
> > No matter how long PostgreSQL lets you make table names, I'd stick
> > with ANSI standard 31 characters.[/color]
>
> "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...)[/color]

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@c ox.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

Table name lengths...

Table name lengths...

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment