Export specific rows from a PostgreSQL table as INSERT SQL script

Question

I have a database schema named: nyummy and a table named cimory:

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)
);

I want to export the cimory table's data as insert SQL script file. However, I only want to export records/data where the city is equal to 'tokyo' (assume city data are all lowercase).

How to do it?

It doesn't matter whether the solution is in freeware GUI tools or command line (although GUI tools solution is better). I had tried pgAdmin III, but I can't find an option to do this.

Coopersmith

PostgreSQL cannot select across databases. At least, older versions cannot and neither can Greenplum, don't know about 9.x. – Hime

I realize this is old, but I just wanted to mention that it is possible to select across databases using dblink, which has been available since at least v8.3. It makes use of foreign servers and foreign data wrappers to connect to "remote" databases. This works whether those databases exist on the same instance or entirely different hosts. I've used it fairly extensively to create materialized views into other databases to facilitate certain reporting and such and it works great. – Noto

Answer 1

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.

Lipoid

Alright, so far your solution works. One thing missed is I need to add "-U user_name". I also almost succeed with ToraSQL tool, it's just that it has error in date-time data in the script result. If none can give GUI tool solution in 2 days, your answer will be accepted – Coopersmith

Just want to share to other people, you can also use this free GUI tool: SQL Workbench/J (with postgreSQL jdbc4 driver), to do the same thing. – Coopersmith

This would be much better with create view export_view..., since the view would remain up-to-date with changes to the base table. The docs say --table=table: Dump only tables (or **views**... so I had some hope this would work, but dumping a view sadly yields no data. :P – Oruntha

@Oruntha It works for me in 9.5. What exactly did you try? – Lipoid

@ClodoaldoNeto oh, OK great! I hope I can also get it working. I used pg_dump --table=my_schema.my_view --data-only --inserts my_db > data.sql, version 9.5.3, and my create statement was the same as yours except create view.... All I get in the output is the usual pg_dump comments and SET statements. Not sure where I'm going wrong. – Oruntha

@Oruntha If the table is empty or the where condition filters all rows out then no rows will be int the pg_dump output. – Lipoid

@ClodoaldoNeto there's definitely data in the table, and selecting from the view in PG Admin yields data. Changing my_view in my pg_dump statement above to my_table (upon which my_view is based) also works. I'm not sure what I'm doing wrong. Knowing it CAN work is great though. I'll keep trying and report back when I work it out. – Oruntha

This solution is work. From this solution in sql file, table name is "export_table". But the original table name is nyummy.cimory. Lets take production DB and UAT DB server. In both DB server I have the nyummy schema and cimory table. I need to take dump for particular rows from production cimory table to UAT cimory table. For that I need to create one "export_table" table, then I change the table name in sql file. Then I need to copy the data to UAT table. So is there any way to take dump for particular rows from a table. – Buffy

So is there any way to take dump for particular rows from a production table. If it is possible we can copy the data to UAT easily. No need to create extra table in production database. – Buffy

I want to do this for multiple tables and want to store their insert statements in same file. so if i dynamically generate pg_dump command for each table and then execute it will it keep appending to same file or overwrite it? – Waltner

ohk. answering my own question.. you can dump multiple table By using multiple --table clauses. – Waltner

add -U xxx to specify user. – Immigration

Answer 2

To export data only use COPY:

COPY (SELECT * FROM nyummy.cimory WHERE city = 'tokio') TO '/path/to/file.csv';

You can export a whole table, only selected columns, or the result of a query as demonstrated. No need to create a table explicitly.

You get a file with one table row per line as plain text (not INSERT commands). Smaller and faster than INSERT commands.

To import the same to another Postgres table of matching structure anywhere (columns in same order, data types compatible!):

COPY other_tbl FROM '/path/to/file.csv';

COPY writes and reads files local to the server, unlike client programs like pg_dump or psql which read and write files local to the client. If both run on the same machine, it doesn't matter much, but it does for remote connections.

There is also the \copy command of psql:

Performs a frontend (client) copy. This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required.

Same syntax as above. Just replace COPY with \copy.

Metallo

The OP calls specifically for data as insert sql script file. I guess he is talking about insert commands, don't you? – Lipoid

@Clodoaldo: You may be right, in which case your answer would be a better fit. One could also copy the CREATE script in pgAdmin separately (as the OP mentions GUIs). – Metallo

STDIN and STDOUT can be used in place of file path, useful for small data exports. – Paedo

Without the --column-inserts flag, pg_dump uses a COPY from STDIN for each of the tables in the SQL code it generates. – Finnish

Take care that the order of the columns you SELECT matches the order of the columns in the destination database. If it doesn't, this could fail, or worse, succeed but insert bad data. – Lucilelucilia

There is an issue where the order of the columns on the source and the destination Postgres database should be the same as the copy command does not tell which column the data should be added to. If the columns are in the wrong order on the destination DB, there will be issues when importing the data. Insert queries don't have that issue. – Bordelon

Indeed, make sure the column order matches in the source and destination tables. In my case the destination was a development instance, so I changed migrations locally to make it work. The other option is to specify columns explicitly. | @IsmailIqbal With COPY you can also specify columns explicitly. – Gracielagracile

Lifesaver when you can't do regular COPY because of lame AWS security policies! – Peccavi

Answer 3

This is an easy and fast way to export a table to a script with pgAdmin manually without extra installations:

1. Right click on target table and select "Backup".
2. Select a file path to store the backup. As Format choose "Plain".
3. Open the tab "Dump Options #2" at the bottom and check "Use Column Inserts".
4. Click the Backup-button.
5. If you open the resulting file with a text reader (e.g. notepad++) you get a script to create the whole table. From there you can simply copy the generated INSERT-Statements.

This method also works with the technique of making an export_table as demonstrated in @Clodoaldo Neto's answer.

Mientao

When I do this, there is no "Bakckup" option. This is pgAdmin III v1.18.1 connecting to Greenplum 4.3.4.1 (based on PostgreSQL 8.2.15). – Hime

I installed pgAdmin III v1.18.1 and there was the "backup" option. I connected to a PostgreSQL 9.5. So the problem is most probably between pgAdmin and the Greenplum. – Mientao

Works as intended in pgAdmin4 – Yield

Worked perfectly, thanks for posting this option! – Kieffer

Answer 4

For my use-case I was able to simply pipe to grep.

pg_dump -U user_name --data-only --column-inserts -t nyummy.cimory | grep "tokyo" > tokyo.sql

Stefa

One must consider about having 'tokyo' in other field. – Dinnage

@BuyutJokoRivai since it's a table only dump in most cases it should be fine – Bordelon

Though with big table, you will dump all rows for the grep which is the pitfall case for your solution. Then, the way we query and store result to a table to dump as here https://mcmap.net/q/28987/-export-specific-rows-from-a-postgresql-table-as-insert-sql-script is more suiteable – Immolate

Answer 5

SQL Workbench has such a feature.

After running a query, right click on the query results and choose "Copy Data As SQL > SQL Insert"

Priestley

It works great. When you choose 'postgres' as the 'driver', It's probable that you'll have to download the JDBC drivers yourself: jdbc.postgresql.org/download.html (it's a .jar file - java binary) and add it as the 'driver' of the postgresql conneciton. The connection string (or URL as in the interface) should look like that: jdbc:postgresql://127.0.0.1:5432/db_name – Smutchy

DBVisualizer has a similar and excellent feature that can copy to a file or straight to the clipboard. – Brooklynese

Answer 6

I tried to write a procedure doing that, based on @PhilHibbs codes, on a different way. Please have a look and test.

 CREATE OR REPLACE FUNCTION dump(IN p_schema text, IN p_table text, IN p_where text)
   RETURNS setof text AS
 $BODY$
 DECLARE
     dumpquery_0 text;
     dumpquery_1 text;
     selquery text;
     selvalue text;
     valrec record;
     colrec record;
 BEGIN

     -- ------ --
     -- GLOBAL --
     --   build base INSERT
     --   build SELECT array[ ... ]
     dumpquery_0 := 'INSERT INTO ' ||  quote_ident(p_schema) || '.' || quote_ident(p_table) || '(';
     selquery    := 'SELECT array[';

     <<label0>>
     FOR colrec IN SELECT table_schema, table_name, column_name, data_type
                   FROM information_schema.columns
                   WHERE table_name = p_table and table_schema = p_schema
                   ORDER BY ordinal_position
     LOOP
         dumpquery_0 := dumpquery_0 || quote_ident(colrec.column_name) || ',';
         selquery    := selquery    || 'CAST(' || quote_ident(colrec.column_name) || ' AS TEXT),';
     END LOOP label0;

     dumpquery_0 := substring(dumpquery_0 ,1,length(dumpquery_0)-1) || ')';
     dumpquery_0 := dumpquery_0 || ' VALUES (';
     selquery    := substring(selquery    ,1,length(selquery)-1)    || '] AS MYARRAY';
     selquery    := selquery    || ' FROM ' ||quote_ident(p_schema)||'.'||quote_ident(p_table);
     selquery    := selquery    || ' WHERE '||p_where;
     -- GLOBAL --
     -- ------ --

     -- ----------- --
     -- SELECT LOOP --
     --   execute SELECT built and loop on each row
     <<label1>>
     FOR valrec IN  EXECUTE  selquery
     LOOP
         dumpquery_1 := '';
         IF not found THEN
             EXIT ;
         END IF;

         -- ----------- --
         -- LOOP ARRAY (EACH FIELDS) --
         <<label2>>
         FOREACH selvalue in ARRAY valrec.MYARRAY
         LOOP
             IF selvalue IS NULL
             THEN selvalue := 'NULL';
             ELSE selvalue := quote_literal(selvalue);
             END IF;
             dumpquery_1 := dumpquery_1 || selvalue || ',';
         END LOOP label2;
         dumpquery_1 := substring(dumpquery_1 ,1,length(dumpquery_1)-1) || ');';
         -- LOOP ARRAY (EACH FIELD) --
         -- ----------- --

         -- debug: RETURN NEXT dumpquery_0 || dumpquery_1 || ' --' || selquery;
         -- debug: RETURN NEXT selquery;
         RETURN NEXT dumpquery_0 || dumpquery_1;

     END LOOP label1 ;
     -- SELECT LOOP --
     -- ----------- --

 RETURN ;
 END
 $BODY$
   LANGUAGE plpgsql VOLATILE;

And then :

-- for a range
SELECT dump('public', 'my_table','my_id between 123456 and 123459'); 
-- for the entire table
SELECT dump('public', 'my_table','true');

tested on my postgres 9.1, with a table with mixed field datatype (text, double, int,timestamp without time zone, etc).

That's why the CAST in TEXT type is needed. My test run correctly for about 9M lines, looks like it fail just before 18 minutes of running.

ps : I found an equivalent for mysql on the WEB.

Overpower

Answer 7

You can make view of the table with specifit records and then dump sql file

CREATE VIEW foo AS
SELECT id,name,city FROM nyummy.cimory WHERE city = 'tokyo'

Higginson

I tried it in pgAdmin III, but for View object, there is no option for dumping. – Coopersmith

Try navicat. I'm using it and it has export sql script option – Higginson

@Giorgi: is there freeware version? – Coopersmith

It is not possible using Postgres 9.1 – Marvelmarvella

Answer 8

I was in need of a way where I can generate insert statements without creating a temp table (in production). I did see some useful arguments to the the pg_dump above but still ended up devising for a way to

generate the insert statements and dump into a file

The below statement did the trick which I feel will be useful for people who end up here for similar answer.

$ /usr/local/bin/pg_dump -h the_db_host_address -d the_db_name --table=schema_name.table_name --data-only --column-inserts -U postgres -p the_port_number -v -f /Users/the_user/folder_name/insrt_stmts_file_name.sql

The next thing is a prompt which asks the password, in the case above I provided the postgres password but any other user should work fine which has the read capability.

I ran the just the insert statements from the file named: insrt_stmts_file_name.sql in DBeaver client.

But the same can be run from the command prompt as well using the following script:

/usr/local/bin/psql -h the_db_host_address -d the_db_name -v -U postgres -f /Users/the_user/folder_name/insrt_stmts_file_name.sql

Reference for pg_dump / psql flags: -h = host -d = db name -v = verbose (it'll output as it progresses) -U = db user name -f = file / path

Elenaelenchus

Answer 9

I just knocked up a quick procedure to do this. It only works for a single row, so I create a temporary view that just selects the row I want, and then replace the pg_temp.temp_view with the actual table that I want to insert into.

CREATE OR REPLACE FUNCTION dv_util.gen_insert_statement(IN p_schema text, IN p_table text)
  RETURNS text AS
$BODY$
DECLARE
    selquery text; 
    valquery text; 
    selvalue text; 
    colvalue text; 
    colrec record;
BEGIN

    selquery := 'INSERT INTO ' ||  quote_ident(p_schema) || '.' || quote_ident(p_table);

    selquery := selquery || '(';

    valquery := ' VALUES (';
    FOR colrec IN SELECT table_schema, table_name, column_name, data_type
                  FROM information_schema.columns 
                  WHERE table_name = p_table and table_schema = p_schema 
                  ORDER BY ordinal_position 
    LOOP
      selquery := selquery || quote_ident(colrec.column_name) || ',';

      selvalue := 
        'SELECT CASE WHEN ' || quote_ident(colrec.column_name) || ' IS NULL' || 
                   ' THEN ''NULL''' || 
                   ' ELSE '''' || quote_literal('|| quote_ident(colrec.column_name) || ')::text || ''''' || 
                   ' END' || 
        ' FROM '||quote_ident(p_schema)||'.'||quote_ident(p_table);
      EXECUTE selvalue INTO colvalue;
      valquery := valquery || colvalue || ',';
    END LOOP;
    -- Replace the last , with a )
    selquery := substring(selquery,1,length(selquery)-1) || ')';
    valquery := substring(valquery,1,length(valquery)-1) || ')';

    selquery := selquery || valquery;

RETURN selquery;
END
$BODY$
  LANGUAGE plpgsql VOLATILE;

Invoked thus:

SELECT distinct dv_util.gen_insert_statement('pg_temp_' || sess_id::text,'my_data') 
from pg_stat_activity 
where procpid = pg_backend_pid()

I haven't tested this against injection attacks, please let me know if the quote_literal call isn't sufficient for that.

Also it only works for columns that can be simply cast to ::text and back again.

Also this is for Greenplum but I can't think of a reason why it wouldn't work on Postgres, CMIIW.

Hime

Answer 10

Here are the BASH functions that I'm using to do this easily from the CLI:

function psql_oneoff() {
    local query=$1

    PGPASSWORD=$POSTGRES_PASSWORD "psql" \
        -d "$PG_DB_NAME" \
        -h "${PG_HOST:-localhost}" \
        -U "${POSTGRES_USER:-$USER}" \
        -p "$PG_PORT" \
        -c "$query"
}

function dump_sql_query_as_insert() {
    local query=$1
    local temp_table_name="_tmp_dump_as_inserts"
    psql_oneoff "DROP TABLE IF EXISTS $temp_table_name"
    full_query="CREATE TABLE $temp_table_name AS $query"
    psql_oneoff "$full_query"
    PGPASSWORD=$POSTGRES_PASSWORD pg_dump \
        --host "${PG_HOST:-localhost}" \
        --user "${POSTGRES_USER:-$USER}" \
        --port "$PG_PORT" \
        --column-inserts \
        --data-only \
        --table="$temp_table_name" \
        "$PG_DB_NAME"
    psql_oneoff "DROP TABLE $temp_table_name"
}

# Can be used like this:
dump_sql_query_as_insert "SELECT * FROM important_table WHERE clever_condition = true"

Just for context, I mainly use this for creating essential seed data in testing and local development environments.

Mia

Answer 11

Just to add on a simple way but manual method.

1)Using PGADMIN 4, after querying the data, download the data in csv.

2. open the csv in any notepad and copy and paste the data in an online csv to sql converter. For example: https://www.convertcsv.com/csv-to-sql.htm. You can set the name of the target table.The output is sql insert scripts.

3. Copy the INSERT scripts back into PGADMIN4 in the targeted db query window.

Ferrand

Answer 12

You can create an insert script using two methods.

1. using select query
2. from GUI in backup the table. Also, you can apply the where condition directly and create the insert script for particular rows only.

Method 1:

SELECT 'INSERT INTO nyummy.cimory (id, name, city) VALUES (''' || id || ''', ''' || name || ''', ''' || city || ''' );' FROM nyummy.cimory where your_condition ;

The above example only has character varying for all columns. If you want a number value, I'm adding that to here.

For example, the table has another column named "pin, which is an integer type in that scenario.

SELECT 'INSERT INTO nyummy.cimory (pin) VALUES ( ' || pin || ' );'

FROM nyummy.cimory where your_condition ;

*You can download the all-row select script result in CSV using the download option available in Postgresql. Sometime, while copying and pasting the script, it contains double quotes. Just replace the double quotes with an empty value using the text editor.

Method 2:

Right-click the table in Pgadmin, then click backup. backup option popup open there choose location and file name and in data/object tab toggle only data unselect all options and in object tab toggle use inserts commands option, it generate individual insert script for each row in the table.

Sogdiana

Answer 13

have u tried in pgadmin executing query with " EXECUTE QUERY WRITE RESULT TO FILE " option

its only export the data, else try like

pg_dump -t view_name DB_name > db.sql

-t option used for ==> Dump only tables (or views or sequences) matching table, refer

Barbarism

This will only export a create view statement – Brisling