Posts

Showing posts from January, 2019

PostgreSQL 12 : Where in copy from

5 days ago a commit made it to 12devel that implements WHERE clause in COPY FROM. Today we're gonna see how it works and see how someone could achieve the same by using file_fdw. To begin with, lets create a table and put some data in it. create table test ( id int, date timestamp without time zone, ); insert into test (id,date) select generate_series (1,1000000)            ,'2015-01-01'; insert into test (id,date) select generate_series (1000001,2000000),'2016-01-01'; insert into test (id,date) select generate_series (2000001,3000000),'2017-01-01'; insert into test (id,date) select generate_series (3000001,4000000),'2018-01-01'; insert into test (id,date) select generate_series (4000001,5000000),'2019-01-01'; now, lets make this a bit larger than 170MB, dump the data in csv and truncate the table : monkey=# insert into test select * from test; INSERT 0 5000000 monkey=# insert into test select * from test; INSE