Обсуждение: pad column with leading zeros or space
Howdy:
I'm running Postgres 7.1.3 on Mandrake Linux 8.0 kernel
version 2.4.16.
I am trying to create a table where I need to pad certain
columns. For example, I have a column where it's defined
as an integer 10 but has to be padded with leading zeros,
so the results should be:
0000200201
instead of
[space]200201
At the same time, I have to define a column similar to char (25),
but left justify it with trailing bytes filled with spaces.
In the end, the entire output of a dump should have a specific
format (like COBOL) where there is a start and end position.
Yeah, well, I don't know why a delimited format wouldn't
help the clients, but it seems that they've hardcode
their process so that the text dump will look *exactly*
as specified.
Can someone point me to the right docs / examples?
Thanks!
-X
"Johnson, Shaunn" <SJohnson6@bcbsm.com> writes:
> I am trying to create a table where I need to pad certain
> columns. ...
> Yeah, well, I don't know why a delimited format wouldn't
> help the clients, but it seems that they've hardcode
> their process so that the text dump will look *exactly*
> as specified.
Rather than hacking the database representation into some unnatural
choice of datatypes, I'd suggest you keep the table declaration clean
and work on producing the requested output format during the dump.
A simple sed, awk, or Perl script to postprocess a dump file might
well be the cleanest answer.
regards, tom lane
--You're right, Tom.
--I've been toying around with the perl aspect of
padding what I needed with zeros and spaces, but either
way, it seems like I'm just going to be pained by this
exercise.
--For example, I'm testing the following:
[snip test table]
drop table t_devel;
CREATE TABLE t_devel (
"bp_member_id" int,
"bp_disease_id" int,
"bp_measure_id" int,
"bp_end_perios_id" int
);
grant select on t_devel to public;
[/snip]
[snip from command]
bcn=> insert into t_devel values (009834,23,3445,523);
INSERT 689881850 1
bcn=> select * from t_devel;
bp_member_id | bp_disease_id | bp_measure_id | bp_end_perios_id
--------------+---------------+---------------+------------------
9834 | 23 | 3445 | 523
(1 row)
bcn=> select lpad(bp_member_id:text, 10, '0') as bp_member_id from t_devel;
ERROR: parser: parse error at or near ":"
bcn=> select lpad(bp_member_id::text, 10, '0') as bp_member_id from t_devel;
bp_member_id
--------------
0000009834
(1 row)
[/snip]
This is the results that I want, but how do you *make* the table
keep the column attribute of things like lpad, rpad, etc ...
I mean, when I define the table, can't I just say something like:
[a guess]
create table foo (
member_id lpad(10, '0')
);
[/snip guess]
I know this doesn't work, but that's sorta what I'm going for.
Or, if it's not a current function by itself, can I develop
something that will always do that for each type, function and
attribute and return what I want?
Yeah ... seems like I'm on the 'gimme, gimmie, gimme' kick today ...
sorry 'bout that ...
Thanks all!
-X
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> I am trying to create a table where I need to pad certain
> columns. ...
> Yeah, well, I don't know why a delimited format wouldn't
> help the clients, but it seems that they've hardcode
> their process so that the text dump will look *exactly*
> as specified.
Rather than hacking the database representation into some unnatural
choice of datatypes, I'd suggest you keep the table declaration clean
and work on producing the requested output format during the dump.
A simple sed, awk, or Perl script to postprocess a dump file might
well be the cleanest answer.
regards, tom lane
On Wed, 23 Jan 2002, Johnson, Shaunn wrote: In general, Tom's right that it's probably best to do this in the front end, but... > create table foo ( > member_id lpad(10, '0') > ); > > [/snip guess] > > I know this doesn't work, but that's sorta what I'm going for. If you want it to really be stored with all the 0's, a trigger would probably do what you want. If you want it converted to the text string with 0's when a select is done, you'd probably want a view to do the manipulation for you. (trigger example - mostly untested:) create table foo ( member_id text ); create function foofunc() returns opaque as 'begin NEW.member_id := lpad(NEW.member_id, 10, ''0''); return NEW; end;' language 'plpgsql'; create trigger t1 before insert or update on foo for each row execute procedure foofunc();
Use a view:
create view v_devel as
select lpad(bp_member_id::text, 10, '0') as bp_member_id, ... from
t_devel;
-----Original Message-----
From: Johnson, Shaunn [mailto:SJohnson6@bcbsm.com]
Sent: Wednesday, January 23, 2002 12:32 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pad column with leading zeros or space
--You're right, Tom.
--I've been toying around with the perl aspect of
padding what I needed with zeros and spaces, but either
way, it seems like I'm just going to be pained by this
exercise.
--For example, I'm testing the following:
[snip test table]
drop table t_devel;
CREATE TABLE t_devel (
"bp_member_id" int,
"bp_disease_id" int,
"bp_measure_id" int,
"bp_end_perios_id" int
);
grant select on t_devel to public;
[/snip]
[snip from command]
bcn=> insert into t_devel values (009834,23,3445,523);
INSERT 689881850 1
bcn=> select * from t_devel;
bp_member_id | bp_disease_id | bp_measure_id | bp_end_perios_id
--------------+---------------+---------------+------------------
9834 | 23 | 3445 | 523
(1 row)
bcn=> select lpad(bp_member_id:text, 10, '0') as bp_member_id from
t_devel;
ERROR: parser: parse error at or near ":"
bcn=> select lpad(bp_member_id::text, 10, '0') as bp_member_id from
t_devel;
bp_member_id
--------------
0000009834
(1 row)
[/snip]
This is the results that I want, but how do you *make* the table
keep the column attribute of things like lpad, rpad, etc ...
I mean, when I define the table, can't I just say something like:
[a guess]
create table foo (
member_id lpad(10, '0')
);
[/snip guess]
I know this doesn't work, but that's sorta what I'm going for.
Or, if it's not a current function by itself, can I develop
something that will always do that for each type, function and
attribute and return what I want?
Yeah ... seems like I'm on the 'gimme, gimmie, gimme' kick today ...
sorry 'bout that ...
Thanks all!
-X
-----Original Message-----
From: Tom Lane [ mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us> ]
> I am trying to create a table where I need to pad certain
> columns. ...
> Yeah, well, I don't know why a delimited format wouldn't
> help the clients, but it seems that they've hardcode
> their process so that the text dump will look *exactly*
> as specified.
Rather than hacking the database representation into some unnatural
choice of datatypes, I'd suggest you keep the table declaration clean
and work on producing the requested output format during the dump.
A simple sed, awk, or Perl script to postprocess a dump file might
well be the cleanest answer.
regards, tom lane
Thanks all (special thanks to Tom, Tom and Stephan):
I have two views down and one more to figure out. So far, the
results is pretty much what is required. Only time will tell
if 'The Powers That Be' change their minds.
If anyone is interested, this is what I did: I derived this from
a table that I had already created and populated with data.
Since creating a table with fix-length characters and padded
values wasn't going to work, I did:
[snip sql]
/*
--creating special view of bp_disease
drop view v_bp_disease;
create view v_bp_disease as
select
lpad (bp_disease_id::text,10,'0') as bp_disease_id,
rpad (bp_dis_label::text,30,' ') as bp_dis_label
from bp_disease
;
*/
[/snip]
What's interesting is that in the original table, I had to have
a column that had to increment with each new row. That would
return a regular number (I think I might have used serial
for this instance).
The view makes it look like the specs; zeros in front of
incrementing numbers, space behind the right places.
I have more questions, but I think I'll quit for
now ...
Thanks again!
-X
-----Original Message-----
From: Tom Pfau [mailto:T.Pfau@emCrit.com]
Use a view:
create view v_devel as
select lpad(bp_member_id::text, 10, '0') as bp_member_id, ... from
t_devel;
[snip]