Re: [GENERAL] About COPY command (and probably file fdw too)

[Stefan Stefanov]

Hi, 

Maybe I need to clarify a little.
The suggested option “[SKIP] COLUMNS  ”
 would contain columns' positions in the file so that only some of the columns 
in a text file would be read into a table.
Example: copy the first, second and seventh columns form myfile.txt into table 
"stafflist". myfile.txt has many columns.
COPY stafflist (userid, username, staffid) 
FROM 'myfile.txt' 
WITH (FORMAT text, DELIMITER E'\t', COLUMNS (1, 2, 7), ENCODING 'windows-1250')

BR, 

Stefan








 -------- Оригинално писмо --------

От: Nicolas Paris nipari...@gmail.com

Относно: Re: [GENERAL] About COPY command (and probably file fdw too)

До: Stefan Stefanov  

Изпратено на: 20.05.2015 23:21



 
 
   
    
    

    
    
     
     2015-05-20 22:16 GMT+02:00 Stefan Stefanov 
       stefanov...@abv.bg > :
     

       
        
         
          
          
          Hi,
           
          
          

           
          
          I have been using COPY .. FROM a lot these days for reading in 
tabular data and it does a very good job.
 Still there is an inconvenience when a (large) text file contains more columns 
than the target table or the columns’ order differs. I can imagine three ways 
round and none is really nice -
           
          
          - mount the file as a foreign table with all the text file’s columns 
then insert into the target table a select from the foreign table;
           
          
          - create an intermediate table with all the text file’s columns, copy 
into it from the file then insert into the target table and finally drop the 
intermediate table when no more files are expected;
           
          
          - remove the unneeded columns from the file with a text editor prior 
to COPY-ing.
           
          
          I think that this is happening often in real life and therefore have 
a suggestion to add this option “[SKIP] COLUMNS  ”
 to the WITH clause of COPY .. FROM. It may be very useful in file fdw too.
           
          
          To be able to re-arrange columns’ order would come as a free bonus 
for users.
           
          
          

           
          
          Sincerely,
           
          
          Stefan Stefanov
           
          
          

           
          
          

          
         
        
        
      
     
    

    
    
     
     ​Hi,
     
     
     

     
     
     I guess it already does (from documentation):
     
     
      COPY  table_name  [ (  column_name  [, ...] ) ]
    FROM { ' filename ' | STDIN }
    [ [ WITH ] (  option  [, ...] ) ] 
     
     
     Then you can order the column_name as the source file has.​