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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
