PostgreSQL: How do I split/export table records into year wise slices (CSV)?

Question

I have a table in my PostgreSQL 9.5 database containing two columns i.e., start_time (timestamp without time zone) and values (records) like this.

Start_time              Values
2003-06-07 00:00:00     12
2004-02-03 00:00:00     16
2005-07-09 00:00:00     14
2003-07-07 00:00:00     17
2004-01-31 00:00:00     11
2005-05-02 00:00:00     10

For start_time, I need to export my_table records so that it generates CSV files for each year slice like this (separating the records for each year in a separate CSV file).

Expected output:

results_2003.csv
results_2004.csv
results_2005.csv
and so on...

How to do this?

Answer 1

Use copy command in a dynamic execute format inside a plpgsql DO block, e.g.:

do $$
declare
    y int;
begin
    for y in
        select distinct extract(year from start_time)
        from my_table
    loop
        execute format($ex$
            copy (
                select * 
                from my_table 
                where extract(year from start_time) = %1$s
                )
            to '\data\%1$s.csv'
            $ex$, y);
    end loop;
end $$;

Answer 2

Of several possible alternative ways to do this, I would use execsql.py (https://pypi.python.org/pypi/execsql/ -- disclaimer: I wrote it) and this script:

select distinct 
    extract(year from start_time) as start_year,
    False as exported
into temporary table tt_years
from interval_table;

create temporary view unexported as
select * from tt_years
where exported = False
limit 1;

-- !x! begin script export_year
-- !x! select_sub unexported
-- !x! if(sub_defined(@start_year))
    create temporary view export_data as
    select * from interval_table
    where extract(year from start_time) = !!@start_year!!;
    -- !x! export export_data to results_!!@start_year!!.csv as csv
    update tt_years
    set exported = True
    where start_year = !!@start_year!!;
    -- !x! execute script export_year
-- !x! endif
-- !x! end script

-- !x! execute script export_year

The !x! tokens identify metacommands to execsql, which allows looping (through end recursion) and exporting to CSV.