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

User · Accepted Answer

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.

User · Answer

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           lt  lt label0 gt  gt       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       lt  lt label1 gt  gt       FOR valrec IN  EXECUTE  selquery      LOOP          dumpquery 1                 IF not found THEN              EXIT            END IF            -- ----------- --          -- LOOP ARRAY  EACH FIELDS  --           lt  lt label2 gt  gt           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

User · 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   gt  tokyo sql

User · Answer

For a data-only export use COPY  You get a file with one table row per line as plain text  not INSERT commands   it s smaller and faster   COPY  SELECT   FROM nyummy cimory WHERE city    tokio   TO   path to file csv     Import the same to another table of the same structure anywhere with   COPY other tbl FROM   path to file csv     COPY writes and read 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 that      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

User · Answer

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

User · Answer

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  gt  db sql   -t option used for     Dump only tables  or views or sequences  matching table  refer

User · Answer

This is an easy and fast way to export a table to a script with pgAdmin manually without extra installations    Right click on target table and select  Backup   Select a file path to store the backup  As Format choose  Plain   Open the tab  Dump Options  2  at the bottom and check  Use Column Inserts   Click the Backup-button   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

User · Answer

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

User · Answer

SQL Workbench has such a feature   After running a query  right click on the query results and choose  Copy Data As SQL   SQL Insert

[postgresql] Export specific rows from a PostgreSQL table as INSERT SQL script

Examples related to postgresql

Examples related to insert

Examples related to export

Examples related to sql-scripts

Examples related to postgresql-copy