From: news.postgresql.org <jlim@natsoft.com.my>
To: pgsql-sql@postgresql.org
Subject: CHAR(n) always trims trailing spaces in 7.4
Date: Mon, 16 Feb 2004 11:29:42 +0800
Message-ID: <c0pdei$ho6$1@news.hub.org> (raw)
References: <20040212000610.13913.qmail@web60806.mail.yahoo.com>
Hello,
I just discovered the following change to CHAR(n) (taken from varlena.com,
general bits, issue 62).
This will cause me serious heart-ache, back-ache and bug-ache if we upgrade
to 7.4. Is there any compatibility flag available to use pre-7.4 behaviour
for CHAR(n)?
Regards, John
==================
In 7.4, one of the changes was that the char(n) type now truncates trailing
spaces. This causes a problem for those of us using ::char(n) to create
fixed length fields. Creating fixed length field batch files are usually
required to interface with legacy systems.
In the example below, psql is called from a shell with tuples only (t) and
unaligned (A). The result of the selection creates a fixed width file.
One of the techniques used to create fixed length fields in 7.3 and earlier
was to cast the value to char(n). This along with the various to_char()
functions used to be able to create fixed length records. For example:
% psql -At > batch.out << END
select accountid::char(30),
to_char( transaction_amount, 'FM000.00'),
(lastname || ',' || firstname )::char(40),
bankid::char(15),
to_char( now(), 'YYYYMMDD');
END
In 7.4 this no longer works. The fields created with the cast are no longer
fixed length. Instead of using the cast to make fixed length fields, use
rpad() and lpad() to do the same thing.
rpad(string text, length integer [, fill text])
lpad(string text, length integer [, fill text])
The previous selection should now be written as follows.
% psql -At > batch.out << END
select rpad(accountid,30),
to_char( transaction_amount, 'FM000.00'),
rpad( (lastname || ',' || firstname ), 40),
rpad(bankid, 15),
to_char( now(), 'YYYYMMDD');
END
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jlim@natsoft.com.my
Subject: Re: CHAR(n) always trims trailing spaces in 7.4
In-Reply-To: <c0pdei$ho6$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox