Export specific rows from a PostgreSQL table as INSERT SQL script

by

Create a table with the set you want to export and then use the command line utility pg_dump to export to a file:

create table export_table as 
select id, name, city
from nyummy.cimory
where city = 'tokyo'

$ pg_dump --table=export_table --data-only --column-inserts my_database > data.sql

--column-inserts will dump as insert commands with column names.

--data-only do not dump schema.

As commented below, creating a view in instead of a table will obviate the table creation whenever a new export is necessary.

More Related Contents:

  1. Save PL/pgSQL output from PostgreSQL to a CSV file
  2. PostgreSQL multi INSERT…RETURNING with multiple columns
  3. INSERT rows into multiple tables in a single query, selecting from an involved table
  4. postgresql: INSERT INTO … (SELECT * …)
  5. Bulk/batch update/upsert in PostgreSQL
  6. COPY with dynamic file name
  7. How to bulk insert only new rows in PostreSQL
  8. SQL Populate table with random data
  9. Select first row in each GROUP BY group?
  10. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  11. How to use RETURNING with ON CONFLICT in PostgreSQL?
  12. PostgreSQL DISTINCT ON with different ORDER BY
  13. PostgreSQL: running count of rows for a query ‘by minute’
  14. Split comma separated column data into additional columns
  15. How to delete duplicate entries?
  16. Transpose latest rows per user to columns
  17. Delete duplicate rows (don’t delete all duplicate)
  18. subquery in FROM must have an alias
  19. Compute percents from SUM() in the same SELECT sql query
  20. SQL Between clause with strings columns
  21. Insert an image in postgresql database
  22. Perform this hours of operation query in PostgreSQL
  23. Does the JDBC spec prevent ‘?’ from being used as an operator (outside of quotes)?
  24. Postgresql Column Not Found, But Shows in Describe
  25. INSERT COMMAND :: ERROR: column “value” does not exist
  26. Return multiple columns and rows from a function PostgreSQL instead of record
  27. Postgres error updating column data
  28. SQL LIKE condition to check for integer?
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment