From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | William ZHANG <uniware(at)zedware(dot)org> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Re: [BUGS] 7.4: CHAR padding inconsistency |
Date: | 2003-11-20 16:35:26 |
Message-ID: | 200311201635.hAKGZQM00514@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
Well, that certainly is interesting. Oracle and MS-SQL preserve the
trailing space when concatenating. Does anyone remember the logic for
trimming space with ||?
---------------------------------------------------------------------------
William ZHANG wrote:
>
> Bruce said:
> > How do other databases handle this?
>
> I have tried on MS SQL Server 2000 and Oracle 9i for Windows.
> SQL Server doesn't like character_length and || , so use len and + instead.
> Oracle doesn't like character_length either, use length.
> Hope the result may help.
>
> create table chartest(col char(10) not null);
> insert into chartest values ('AAA');
>
> PostgreSQL:
> select character_length(col) from chartest;
> 10
> SQL Server
> select len(col) from chartest;
> 3
> Oracle
> select length(col) from chartest;
> 10
>
> PostgreSQL:
> select character_length(col || 'hey') from chartest;
> 6
> SQL Server:
> select len(col + 'hey') from chartest;
> 13
> Oracle:
> select length(col || 'hey') from chartest;
> 13
>
> PostgreSQL:
> select 'aaa ' || 'bb';
> aaa bb
> SQL Server:
> select 'aaa ' + 'bb';
> aaa bb
> Oracle:
> select 'aaa ' || 'bb' from dual;
> aaa bb
>
> PostgreSQL:
> select cast('aa ' as char(10)) || 'b';
> aab
> SQL Server:
> select cast('aa ' as char(10)) + 'b';
> aa b
> Oracle:
> select cast('aa ' as char(10)) || 'b' from dual;
> aa b
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-11-20 17:40:30 | Re: [BUGS] 7.4: CHAR padding inconsistency |
Previous Message | krishna kumar | 2003-11-20 14:54:45 | Installation problem |
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2003-11-20 16:45:20 | Re: 4 Clause license? |
Previous Message | Matthew T. O'Connor | 2003-11-20 16:32:24 | Re: [PERFORM] More detail on settings for pgavd? |