Export specific rows from a PostgreSQL table as INSERT SQL script

📝 Exporting specific rows from a PostgreSQL table as INSERT SQL script

Are you struggling to export specific rows from a PostgreSQL table as an INSERT SQL script? Don't worry, I've got you covered! In this blog post, I'll guide you through some easy solutions to this common problem.

But before we dive into the solutions, let's understand the context of the question. We have a PostgreSQL database schema called "nyummy" and a table named "cimory." The table structure looks like this:

create table nyummy.cimory (
  id numeric(10,0) not null,
  name character varying(60) not null,
  city character varying(50) not null,
  CONSTRAINT cimory_pkey PRIMARY KEY (id)
);

Now, let's address the main question: How can we export data from the "cimory" table as an insert SQL script, but only for records where the city is equal to 'tokyo'?

1. Using pgAdmin III:

Unfortunately, the question states that pgAdmin III doesn't have an option to export specific rows based on a condition. But don't worry, we have other solutions!

1. Using pgAdmin 4:

If you have pgAdmin 4 installed, you can easily export specific rows from the "cimory" table. Here are the steps:

• Open pgAdmin 4 and connect to your PostgreSQL server.

• Navigate to the "Object Browser" on the left panel and expand the "Servers" and your server.

• Expand your database schema "nyummy" and the "Tables" section.

• Right-click on the "cimory" table and select "Backup..."

• In the "Backup Options" tab, make sure to select the "Plain" format.

• In the "Dump Options" tab, scroll down to the "Dump Only" section.

• In the "Where" clause, type city = 'tokyo'.

• Click the "Backup" button to save the INSERT SQL script file.

1. Using the command line:

If you prefer the command line interface, you can use the pg_dump utility to export specific rows. Here's an example command:

pg_dump -t nyummy.cimory --where="city = 'tokyo'" > output.sql

This command exports the "cimory" table from the "nyummy" schema, including only the rows where the city is 'tokyo'. The output is redirected to a file named "output.sql".

Feel free to replace "output.sql" with the desired file name.

Now that you have learned a couple of solutions, go ahead and choose the one that suits you best. Export your specific rows from the PostgreSQL table, be it using pgAdmin or the command line.

🔥 Call-to-Action:

Did you find this blog post helpful? Let me know in the comments section below! If you have any other questions or need further assistance, feel free to ask. And don't forget to share this blog post with your fellow PostgreSQL enthusiasts. Happy exporting! 🚀