By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,541 Members | 1,427 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,541 IT Pros & Developers. It's quick & easy.

Table name lengths...

P: n/a
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

Nov 11 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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

Nov 11 '05 #2

P: n/a
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

Nov 11 '05 #3

P: n/a
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

Nov 11 '05 #4

P: n/a
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

Nov 11 '05 #5

P: n/a
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

Nov 11 '05 #6

P: n/a
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

Nov 11 '05 #7

P: n/a
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)

Nov 11 '05 #8

P: n/a
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)

Nov 11 '05 #9

P: n/a
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

Nov 11 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.