- 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;
|