Export specific rows from a PostgreSQL table as INSERT SQL script

To export only data 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.

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.

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

SQL Workbench has such a feature.

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

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.

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'

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.

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

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.

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.

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.

I updated it a bit as well, to export multiple rows.

    CREATE OR REPLACE FUNCTION insert_select(IN p_schema text, IN p_table text, IN p_where text)
    RETURNS setof text AS
    $BODY$
    DECLARE
        tablefilter text;
        rowrec record;
        dumpquery text;
        dumpquery_0 text;
        dumpquery_1 text;
        updateclause text;
        selquery text;
        selvalue text;
        valrec record;
        colrec record;
        updaterec record;
        total_rows INT;
        row_number INT := 0;
        row_char_end text;
        primary_key text;
    BEGIN
        -- ------ --
        -- GLOBAL --
        --   build base INSERT
        --   build SELECT array[ ... ]

        tablefilter := format('SELECT * FROM %I.%I %I', p_schema, p_table, p_where);

        FOR rowrec IN EXECUTE tablefilter
        LOOP
            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
                -- raise notice 'tipo%', colrec.data_type;
                IF colrec.data_type = 'text' OR colrec.data_type = 'varchar' OR colrec.data_type = 'character varying' THEN
                    selquery  := selquery    || 'quote_literal(' || colrec.column_name || ')';

                ELSIF colrec.data_type = 'date' THEN
                    selquery  := selquery    || 'quote_literal(to_char(' || colrec.column_name || ', ''YYYY-MM-DD''))';

                ELSIF starts_with(colrec.data_type, 'timestamp') THEN
                    selquery  := selquery    || 'quote_literal(to_char(' || colrec.column_name || ', ''YYYY-MM-DD HH24:MI:SS.MS''))';            
                ELSE 
                    selquery  := selquery    || 'CAST(' || colrec.column_name || ' AS TEXT)';
                END IF;
                selquery  := selquery || ',';

            END LOOP label0;

            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 id = '|| rowrec.id;

            
            -- GLOBAL --
            -- ------ --
            --Get total rows of all primary keys--

            SELECT count(*)
            INTO total_rows
            FROM information_schema.columns
            WHERE table_name = quote_ident(p_table)
            AND table_schema = quote_ident(p_schema)
            AND column_name NOT IN (
                SELECT a.attname 
                FROM pg_index i
                JOIN pg_attribute a ON a.attrelid = i.indrelid 
                                    AND a.attnum = ANY(i.indkey)
                WHERE i.indrelid = quote_ident(p_table)::regclass
                AND i.indisprimary
            );

            --GET PRIMARY KEY COLUMNS
            SELECT c.conname 
            INTO primary_key
            FROM pg_constraint c 
            WHERE c.conrelid::regclass::text = quote_ident(p_table) and c.contype = 'p';
        
            updateclause := ' ON CONFLICT ON CONSTRAINT (' || primary_key || ') DO UPDATE SET ';
            -- --------- --
            -- Get Unique constraints --
            <<label3>>
            FOR updaterec IN SELECT column_name
                            FROM information_schema.columns
                            WHERE table_name = quote_ident(p_table)
                            AND table_schema = quote_ident(p_schema)
                            AND column_name NOT IN (
                                SELECT a.attname 
                                FROM pg_index i
                                JOIN pg_attribute a ON a.attrelid = i.indrelid 
                                                    AND a.attnum = ANY(i.indkey)
                                WHERE i.indrelid = quote_ident(p_table)::regclass
                                AND i.indisprimary
                            )
            LOOP
                updateclause := updateclause || updaterec.column_name || ' = EXCLUDED.' || updaterec.column_name;

                IF row_number = total_rows THEN
                    updateclause := updateclause || ';';
                ELSE
                    updateclause := updateclause || ', ';
                END IF;
                row_number := row_number + 1;

            END LOOP label3;


            row_number := 0;
            EXECUTE 'SELECT COUNT(*) FROM ' ||quote_ident(p_schema)||'.'||quote_ident(p_table) || ' WHERE '||p_where
            INTO total_rows;
            -- ----------- --
            -- 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;
                row_number := row_number + 1;

                IF row_number = total_rows THEN
                row_char_end := ';';
                ELSE
                row_char_end := ',';
                END IF;

                -- ----------- --
                -- LOOP ARRAY (EACH FIELDS) --
                <<label2>>
                FOREACH selvalue in ARRAY valrec.MYARRAY
                LOOP
                    IF selvalue IS NULL
                    THEN selvalue := 'NULL';
                    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;
                dumpquery := dumpquery_0 || dumpquery_1 || updateclause;
                dumpquery_0 := '(';
                RETURN NEXT dumpquery;
            END LOOP label1 ;
            -- SELECT LOOP --
            -- ----------- --
        END LOOP;

    RETURN ;
    END
    $BODY$
    LANGUAGE plpgsql VOLATILE;

I know it is a crap, I will refactor it soon

Improving Vi Shein answer for no generate INSERT INTO c1,c2... for each row:

CREATE OR REPLACE FUNCTION dump(IN p_schema text, IN p_table text, IN p_where text)
   RETURNS setof text AS
 $BODY$
 DECLARE
     dumpquery text;
     dumpquery_0 text;
     dumpquery_1 text;
     selquery text;
     selvalue text;
     valrec record;
     colrec record;
     total_rows INT;
     row_number INT := 0;
     row_char_end text;
 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, is_generated
                   FROM information_schema.columns
                   WHERE table_name = p_table and table_schema = p_schema
                   ORDER BY ordinal_position
     LOOP
         IF colrec.is_generated = 'ALWAYS' THEN
             CONTINUE; -- skip generated columns
         END IF;
         dumpquery_0 := dumpquery_0 || quote_ident(colrec.column_name) || ',';
         raise notice 'tipo%', colrec.data_type;
         IF colrec.data_type = 'text' OR colrec.data_type = 'varchar' OR colrec.data_type = 'character varying' THEN
            selquery  := selquery    || 'quote_literal(regexp_replace(' || colrec.column_name || ', E''[\\n\\r]+'', '' '', ''g''))';

         ELSIF colrec.data_type = 'date' THEN
            selquery  := selquery    || 'quote_literal(to_char(' || colrec.column_name || ', ''YYYY-MM-DD''))';

        ELSIF starts_with(colrec.data_type, 'timestamp') THEN
            selquery  := selquery    || 'quote_literal(to_char(' || colrec.column_name || ', ''YYYY-MM-DD HH24:MI:SS''))';

         ELSE
            selquery  := selquery    || 'CAST(' || colrec.column_name || ' AS TEXT)';

         END IF;
         selquery  := selquery || ',';

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

     EXECUTE 'SELECT COUNT(*) FROM ' ||quote_ident(p_schema)||'.'||quote_ident(p_table) || ' WHERE '||p_where
     INTO total_rows;

     -- ----------- --
     -- 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;
         row_number := row_number + 1;

         IF row_number = total_rows THEN
            row_char_end := ';';
         ELSE
            row_char_end := ',';
         END IF;

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

         -- debug: RETURN NEXT dumpquery_0 || dumpquery_1 || ' --' || selquery;
         -- debug: RETURN NEXT selquery;
         dumpquery := dumpquery_0 || dumpquery_1;
         dumpquery_0 := '(';

         RETURN NEXT dumpquery;

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

 RETURN ;
 END
 $BODY$
   LANGUAGE plpgsql VOLATILE;