Обсуждение: copy question - fixed width?

Is there any way to make copy work with fixed width files?

eg
  create table t1 (code char(5), description char(30));
  copy t1 from '/tmp/afile' delimiter as <nothing?>

where afile looks something like
00001test16789012345678901234567890
00002test26789012345678901234567890
00003test36789012345678901234567890

at the moment i have a function that does
  create temp table tmp1 (line text);
  copy tmp1 from '/tmp/afile';
  <bunch of code to create a view of tmp1 with lots of substrings from
looking at information schema for t1 definition>
  <it used to do the substrings in the plpgsql but it was orders of
magnitude quicker to use the view>
  insert into t1 select * from tmpview;

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

Klint Gore <kgore4@une.edu.au> writes:
> Is there any way to make copy work with fixed width files?

I'd suggest using a simple sed script to convert the data into the
format COPY understands.

            regards, tom lane

Le lundi 11 février 2008, Klint Gore a écrit :
> Is there any way to make copy work with fixed width files?

I'll try to see about implementing this in pgloader, shouldn't be complex. But
we have some other things on the TODO (which could get formalized by now...).

So at the moment the preprocessing sed script is a better idea than waiting
for the pgloader release which will be able to process no-separator
fixed-field length input files.

Regards,
--
dim