Thread: [GENERAL] Length of field names.
Dear All, I think I'm having problems with PG because of the length of my fields names. I try: brecard2=> create table malignant_pathologies_invasive ( rep_mal_invasive_carcinoma_rep_number char(15) primary key, rep_mal_invasive_carcinoma_pat_id char(15), rep_mal_invasive_carcinoma bool, rep_mal_invasive_carcinoma_type_other text, rep_mal_invasive_carcinoma_grade int2, rep_mal_invasive_carcinoma_tubules int2, rep_mal_invasive_carcinoma_pleomorphism int2, rep_mal_invasive_carcinoma_mitoses int2 ); ERROR: CREATE TABLE/PRIMARY KEY multiple keys for table malignant_pathologies_invasive are not legal However, if I make the name of the table smaller as in: brecard2=> create table mal_paths_invasive (...); it works fine. When I have a look at the table produce I get: brecard2=> \d mal_paths_invasive +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | rep_mal_invasive_carcinoma_rep_ | char() not null | 15 | | rep_mal_invasive_carcinoma_pat_ | char() | 15 | | rep_mal_invasive_carcinoma | bool | 1 | | rep_mal_invasive_carcinoma_type | text | var | | rep_mal_invasive_carcinoma_grad | int2 | 2 | | rep_mal_invasive_carcinoma_tubu | int2 | 2 | | rep_mal_invasive_carcinoma_pleo | int2 | 2 | | rep_mal_invasive_carcinoma_mito | int2 | 2 | +----------------------------------+----------------------------------+-------+ So my guess is that with the original name, malignant_pathologies_invasive and the primary key created by it (normally malignant_pathologies_invasive_pkey) end up having the same name because of the cut-off? Does this seem right? If so, what is the max length of field names and can anyone suggest a way around this problem (obviously I could just give the tables shorter names but they are computer generated by a Perl script and if at all possible, I'd like to keep the filed and table names in this long format). Cheers, S. PS. For those that have been following the \dt <regex> discussion, in this case I would have like a '\d \dt' kind of query to get PG to give me the type and length of the Field field (which is more of an SQL query type of question so probably would have required SQL access to catalog tables as Herouth suggested). +-------------------------+--------------------------------------+ | Stuart Rison | Ludwig Institute for Cancer Research | +-------------------------+ University College London | | Tel. (0171) 878 4041 | 91 Riding House Street | | Fax. (0171) 878 4040 | London, W1P 8BT, UNITED KINGDOM. | +-------------------------+--------------------------------------+ | stuart@NOJUNK_ludwig.ucl.ac.uk [Remove NOJUNK_ for it to work] | +----------------------------------------------------------------+
> So my guess is that with the original name, malignant_pathologies_invasive > and the primary key created by it (normally > malignant_pathologies_invasive_pkey) end up having the same name because of > the cut-off? > > Does this seem right? If so, what is the max length of field names and can > anyone suggest a way around this problem (obviously I could just give the > tables shorter names but they are computer generated by a Perl script and > if at all possible, I'd like to keep the filed and table names in this long > format). max length is 31. > PS. For those that have been following the \dt <regex> discussion, in this > case I would have like a '\d \dt' kind of query to get PG to give me the > type and length of the Field field (which is more of an SQL query type of > question so probably would have required SQL access to catalog tables as > Herouth suggested). Huh? Doesn't \d \dt do that if you supply the table name? -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
>> So my guess is that with the original name, malignant_pathologies_invasive
>> and the primary key created by it (normally
>> malignant_pathologies_invasive_pkey) end up having the same name because of
>> the cut-off?
>>
>> Does this seem right? If so, what is the max length of field names and can
>> anyone suggest a way around this problem (obviously I could just give the
>> tables shorter names but they are computer generated by a Perl script and
>> if at all possible, I'd like to keep the filed and table names in this long
>> format).
>
>max length is 31.
>
>> PS. For those that have been following the \dt <regex> discussion, in this
>> case I would have like a '\d \dt' kind of query to get PG to give me the
>> type and length of the Field field (which is more of an SQL query type of
>> question so probably would have required SQL access to catalog tables as
>> Herouth suggested).
>
>Huh? Doesn't \d \dt do that if you supply the table name?
Yeah I guess I didn't explain that very well. Suppose you did
\d test
you'd get
Field | Type | Length
------------------------------
blah | text | var
number | int2 | 2
code | char() | 15
[hand-made table :)]
now what is was -badly- trying to say was that is you did a \d of that
table you'd get:
Field | Type | Length
-----------------------------------
Field | varchar() | 31
Type | varchar() | 10
Length | int2 | 2
[or something like that, I'm guessing the numbers/types]
so a sort of \d on a \d which would have given me the max length of field.
If I understood Herouth's mail properly, all this information (i.e. catalog
table) are store as 'hidden' tables in the DBMS. In Oracle it appears
these are actually 'usable' in SQL statements -if you know their names and
he was suggesting that the same could be of postgreSQL (I'm sorry if I got
that all wrong Herouth).
cheers,
Stuart.
PS. That leaves me with giving tables and fields smaller names... I
suppose names of over 31 are pretty silly anyway... no wonder I keep
exceeding the query buffer!!
+-------------------------+--------------------------------------+
| Stuart Rison | Ludwig Institute for Cancer Research |
| Tel. (0171) 878 4041 | Courtauld Building |
| Fax. (0171) 878 4040 | 91 Riding House Street |
+-------------------------+ London, W1P 8BT |
| stuart@ludwig.ucl.ac.uk | UNITED KINGDOM. |
+-------------------------+--------------------------------------+
> Yeah I guess I didn't explain that very well. Suppose you did > > \d test > > you'd get > > Field | Type | Length > ------------------------------ > blah | text | var > number | int2 | 2 > code | char() | 15 > > [hand-made table :)] > > now what is was -badly- trying to say was that is you did a \d of that > table you'd get: > > > Field | Type | Length > ----------------------------------- > Field | varchar() | 31 > Type | varchar() | 10 > Length | int2 | 2 > > [or something like that, I'm guessing the numbers/types] > > so a sort of \d on a \d which would have given me the max length of field. > If I understood Herouth's mail properly, all this information (i.e. catalog > table) are store as 'hidden' tables in the DBMS. In Oracle it appears > these are actually 'usable' in SQL statements -if you know their names and > he was suggesting that the same could be of postgreSQL (I'm sorry if I got > that all wrong Herouth). That is \dT, no? -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)