There was a Question put by one of user about loading data in PostgreSQL skipping column which has default value using Copy Command.

With above requirement, he has also mentioned that table’s column get changed and default values too.

So, he wanted a plpgsql function code, which can be use for any table and file to load the data.

Following is a sample plpgsql code which can be use to make things happen:

CREATE OR REPLACE FUNCTION Copy_test(text,text,text) returns boolean  
AS  
$$  
 DECLARE  
    rec record;  
    cmd text;  
    colstring text;  
 BEGIN  
        cmd:='select array_to_string(array(select column_name::text from information_schema.columns where table_name='||''''||$2||''''||' and table_schema='||''''||$1||''''||' and column_default is null),'||''''||','||''''||')';  
         EXECUTE  cmd into colstring;  
 cmd:='COPY "'||$1||'"."'||$2||'"('||colstring||') from '||''''||$3||'''';  
        EXECUTE  cmd;  
     EXCEPTION when others then  
 return false;  
 Return true;  
  END;  
$$ language plpgsql;  
Usage:  
select copy_test('schemaname','tablename','filename');  

Usage Example is given below:

postgres=# select * from test_copy;  
 id | id1   
----+-----  
(0 rows)  
  
postgres=#select Copy_test('public','test_copy','/Users/postgres/test_data');  
 copy_test   
-----------  
 t  
(1 row)  
  
postgres=# select * from test_copy;                                                                                                                                      id | id1   
----+-----  
  1 |   1  
  2 |   2  
  3 |   3  
  4 |   4  
  5 |   5  
(5 rows)  

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s