Thread: Sequence name length
Hi, Just a quick one on the length of the name of a sequence. I have some table with long(ish) names like : eselect_maincategory The primary key is named : maincat_id When I create the table the sequence sql looks like nextval('"eselect_maincategory_maincat_id_seq"'::text) However the sequence created is named : eselect_maincategory_maincat_id ( the '_seq' is lopped off ) This looke like a max of 31 characters. Is a sequence name length limited to 31 characters or is it the tool I'm using - phpPgAdmin, I wonder ? Your time and thoughts are very much appreciated. Thank you. Rudi Starcevic.
Hi Rudi, Unless you redefine it before compiling, postgres has a built-in limit of 31 characters for names. Increasing this has a performance penalty, but it might happen for 7.3 due to some improvements in the performance area. BTW, the best way to do a sequence primary key is lik ethis: create table blah (maincat_id SERIAL ); Chris > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Rudi Starcevic > Sent: Wednesday, 17 July 2002 10:39 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] Sequence name length > > > Hi, > > Just a quick one on the length of the name of a sequence. > > I have some table with long(ish) names like : eselect_maincategory > The primary key is named : maincat_id > > When I create the table the sequence sql looks like > nextval('"eselect_maincategory_maincat_id_seq"'::text) > > However the sequence created is named : > eselect_maincategory_maincat_id ( the '_seq' is lopped off ) > > This looke like a max of 31 characters. > > Is a sequence name length limited to 31 characters or is it the tool I'm > using - phpPgAdmin, I wonder ? > > Your time and thoughts are very much appreciated. > Thank you. > Rudi Starcevic. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
Rudi Starcevic <rudi@oasis.net.au> writes: > Just a quick one on the length of the name of a sequence. > ... > However the sequence created is named : > eselect_maincategory_maincat_id ( the '_seq' is lopped off ) What version are you running? Anything recent will keep the '_seq' and lop elsewhere. > This looke like a max of 31 characters. Yeah, the default NAMEDATALEN is 32, allowing for 31 chars plus a terminating null. You can build a private version with larger NAMEDATALEN, and there are recurring discussions about increasing the default length. regards, tom lane
> > However the sequence created is named : > > eselect_maincategory_maincat_id ( the '_seq' is lopped off ) > > What version are you running? Anything recent will keep the '_seq' > and lop elsewhere. Not if he's manually creating a sequence name that's too long - it will just truncate it methinks... Chris
Hi Tom, Here is the output from 'select version()' PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.95.2 Cheers Rudi. Tom Lane wrote: >Rudi Starcevic <rudi@oasis.net.au> writes: > > >>Just a quick one on the length of the name of a sequence. >>... >>However the sequence created is named : >>eselect_maincategory_maincat_id ( the '_seq' is lopped off ) >> >> > >What version are you running? Anything recent will keep the '_seq' >and lop elsewhere. > > > >>This looke like a max of 31 characters. >> >> > >Yeah, the default NAMEDATALEN is 32, allowing for 31 chars plus a >terminating null. You can build a private version with larger >NAMEDATALEN, and there are recurring discussions about increasing >the default length. > > regards, tom lane > > >