Exporting partial dataset from PostgreSQL

partial-export.sql

-- 1. Select the proper subset of data and create a new table. 
-- Data types automatically inferred by PostgreSQL. Foreign keys and some other constraints are lost.
create table k_ryhma as 
 select * from kysymysryhma where valtakunnallinen = true;

-- Select referenced data using .. in (select ..) 
create table k_kys as 
 select * from kysymys where kysymysryhmaid in (select kysymysryhmaid from k_ryhma);

create table k_mv as 
  select * from monivalintavaihtoehto where kysymysid in (select kysymysid from k_kys);
  
-- 2. Dump the data as SQL insert statements  (aipal is the database name in this example)
-- pg_dump --column-inserts --data-only --table=k_ryhma --table=k_kys --table=k_mv aipal > testdata.sql

-- 3. replace table names in inserts. (obviously this could match something in varchar columns, careful here) 
-- sed 's/k_mv/monivalintavaihtoehto/g' testdata.sql | sed 's/k_ryhma/kysymysryhma/g' | sed 's/k_kys/kysymys/g' > fixed_testdata.sql