Dump a PostgreSQL table as insert statements


FYI: Like the previous post, this is a really quick tip.

This week I’m working closely to the “front-end guy”. Not that I don’t know how to front end, but he is helping me.

We are developing an internal tool, that, for this first version, will use only a few tables of one of our databases.

Doing the “back end” part of it, I created tons of rows in my local database, and, in order to properly test the front end part, the “front end guy” needed some data. He could just create a lot of rows in that table. That would be easy. And boring. And inaccurate.

Dump an entire database is also boring, and usually takes a lot of time. Besides, he only needs one table.

So, I dumped that one table in the form of inserts. pg_dump, with some parameters, can easily do that:

$ pg_dump \
  -h localhost \
  -p 5432 \
  -U user -W \
  --table="table-name" \
  --data-only \
  --column-inserts \
  database-name > table.sql

So, I just had to send him this table.sql file somehow, and he had to execute that SQL file in his database, which can be easily done with the pg command:

$ psql \
  -h localhost \
  -p 5432 \
  -U user \
  database-name \
  -f table.sql

That’s it. A quick and useful tip that I have used many times and will probably use many more.

Related Posts

Writing cli applications with Golang

Measuring production code coverage with JaCoCo

From Travis Enterprise to BuildKite in 50 repositories