Product Documentation

Appearance

\copy Examples ​

The following example uses the ENCODING, FORMAT, DELIMITER, and HEADER options:

premdb=# \copy season from '/home/premdata/season.csv' 
with (encoding 'latin9',format text, delimiter ',',header 'false')
COPY 25

Copy the season table to a csv file:

premdb=# \copy season to '/home/premdata/seasonout.csv' with (format csv)
COPY 25

Copy the results of a query to a file:

premdb=# premdb=# \copy (select * from team where teamid>25 order by teamid) to '/home/premdata/team25plus' with (delimiter ',');
COPY 25
premdb=# \q
$ more team25plus
26,27,76,Middlesbrough,Boro,Middlesbrough,Riverside Stadium,34742,0.000
27,28,77,Newcastle United,Magpies,Newcastle,St. James Park,52405,49.754
28,29,78,Norwich City,Canaries,Norwich,Carrow Road,27244,26.972
29,30,79,Nottingham Forest,Forest,Nottingham,City Ground,30445,0.000
30,32,80,Oldham Athletic,Latics,Oldham,Boundary Park,13309,0.000
...

The following example copies the match table to and from a binary file:

premdb=# \copy(select * from match) to '/home/premdata/matchout' with(format binary);
COPY 8606
premdb=# delete from match;
DELETE 8606
premdb=# \copy match from '/home/premdata/matchout' with(format binary);
COPY 8606

The following example copies quoted output to a csv file:

premdb=# \copy(select * from team) to '/home/premdata/teamout' with(format csv, force_quote *);
COPY 50
premdb=# \q
$ more teamout
"1","2","51","Arsenal","Gunners","London","Emirates Stadium","60260","59.944"
"2","3","52","Aston Villa","Villains","Birmingham","Villa Park","42785","33.690"
"3","4","53","Barnsley","Tykes","Barnsley","Oakwell Stadium","23009","0.000"
...

Assume that the source file season_with_nulls.csv contains this line, where the character string null is in the winners field:

25,2016-2017,20,null

The following \copy command loads the season table, setting null as the null string and forcing the null value to be loaded:

premdb=# \copy season from '/home/brumsby/season_with_nulls.csv' with(format csv, null 'null', force_null(winners));
COPY 25

The following query returns the row in the table that now contains null:

premdb=# select * from season where winners is null;
 seasonid | season_name | numteams | winners 
----------+-------------+----------+---------
      25 | 2016-2017   |       20 |

Parent topic:ybsql \copy Command