[Prev] [Menu] [Next]

COPY command


  • COPY command original with Pgbash

    In the standard functionality of PostgreSQL, only a postgres super user can specify a file name in a COPY command. In this case, the backend performs actual file I/O processing.
    On the other hand, Pgbash has an original COPY command which can specify a file name by a general user and which can specify a column name. The syntax of a COPY command is shown below.

    COPY table ( [col1[,col2..]] ) {FROM | TO}
    {filename | STDIN | STDOUT}
    [ USING DELIMITERS 'delimiter' ] [ WITH NULL AS 'nullstring' ];

    Pgbash COPY command attaches () behind a table name. All columns should be specified if column names were not specified in ( ). In addition, when there is no () behind a table name, it performs as a standard COPY command.

  • Copy TABLE from a file

    If "copy tbname(col1,col2) from /tmp/oo;" is specified, Pgbash converts as follows.
      begin;
      insert into tbname(col1,col2) values( data11, data12, data13,.. )
      insert into tbname(col1,col2) values( data21, data22, data23,.. )
      ...
      end;
    
    If the error occurred in writing the table, data are rollbacked.

  • Copy TABLE to a file

    If "copy tbname(col1,col2) to /tmp/oo;" is specified, Pgbash converts as follows.
      begin;
      declare copy_cur00 cursor for select col1,col2 from tbname;
      fetch 300 in copy_cur00 >> /tmp/oo
      fetch 300 in copy_cur00 >> /tmp/oo
      ..
      end;
    


[Prev] [Menu] [Next]