Thread: Re: [GENERAL] 'a' == 'a '
> -----Original Message----- > From: Richard_D_Levine@raytheon.com [mailto:Richard_D_Levine@raytheon.com] > Sent: Thursday, October 20, 2005 2:12 PM > To: Tom Lane > Cc: Chris Travers; Dann Corbit; Greg Stark; josh@agliodbs.com; pgsql- > general@postgresql.org; pgsql-hackers@postgresql.org; Marc G. Fournier; > Stephan Szabo; Terry Fielder; Tino Wildenhain > Subject: Re: [GENERAL] [HACKERS] 'a' == 'a ' > > > > Tom Lane <tgl@sss.pgh.pa.us> wrote on 10/20/2005 03:11:23 PM: > <snip> > > The hard part would be in figuring out how > > the output routine could know how many spaces to add back. > > The length is in the metadata for the column, or am I being dense? I guess that what Tom is saying is that it would be nice to store everything as VARCHAR. But with (for instance) BPCHAR, the returned string is blank padded. So if you store 'Danniel' in BPCHAR(20), you will get back:'Danniel ' But if you store 'Danniel' In VARCHAR(20) You will get back exactly what you put in. I guess that additional ambiguity arises if you add additional spaces to the end. Many database systems solve this by trimming the characters from the end of the string upon storage and the returned string will not have any trailing blanks. I am not arguing pro nor con this way of doing things.
"Dann Corbit" <DCorbit@connx.com> writes:
> I guess that additional ambiguity arises if you add additional spaces to
> the end. Many database systems solve this by trimming the characters
> from the end of the string upon storage and the returned string will not
> have any trailing blanks.
Can you document that? ISTM that that would effectively make char(n)
and varchar(n) exactly equivalent, which is ... um ... a bit stupid.
regards, tom lane
"Dann Corbit" <DCorbit@connx.com> wrote on 10/20/2005 04:24:26 PM: > > -----Original Message----- > > From: Richard_D_Levine@raytheon.com > [mailto:Richard_D_Levine@raytheon.com] > > Sent: Thursday, October 20, 2005 2:12 PM > > To: Tom Lane > > Cc: Chris Travers; Dann Corbit; Greg Stark; josh@agliodbs.com; pgsql- > > general@postgresql.org; pgsql-hackers@postgresql.org; Marc G. > Fournier; > > Stephan Szabo; Terry Fielder; Tino Wildenhain > > Subject: Re: [GENERAL] [HACKERS] 'a' == 'a ' > > > > > > > > Tom Lane <tgl@sss.pgh.pa.us> wrote on 10/20/2005 03:11:23 PM: > > <snip> > > > The hard part would be in figuring out how > > > the output routine could know how many spaces to add back. > > > > The length is in the metadata for the column, or am I being dense? > > I guess that what Tom is saying is that it would be nice to store > everything as VARCHAR. <snip> I get that part, but he asked how to determine the proper output length based on the PAD semantics, and I was saying to just pad whatever is stored to the length available in the column metadata for those collations that require padding. > I guess that additional ambiguity arises if you add additional spaces to > the end. Many database systems solve this by trimming the characters > from the end of the string upon storage and the returned string will not > have any trailing blanks. I am not arguing pro nor con this way of > doing things. For PAD correlations, Oracle stores the blanks. Interbase does not. Going from Interbase to Oracle I switched from CHAR to VARCHAR2 because of this, shot my self in the foot by not understanding PAD, whined about it, and you know the rest. As to how it is output, I know the language interface has an effect. Embedded SQL in C will put the null terminator in different places for different databases and different switch settings particular to each database.
Is there any TODO here? --------------------------------------------------------------------------- Tom Lane wrote: > "Dann Corbit" <DCorbit@connx.com> writes: > > I guess that additional ambiguity arises if you add additional spaces to > > the end. Many database systems solve this by trimming the characters > > from the end of the string upon storage and the returned string will not > > have any trailing blanks. > > Can you document that? ISTM that that would effectively make char(n) > and varchar(n) exactly equivalent, which is ... um ... a bit stupid. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073