Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2009

    Post Unanswered: create SQL insert statement from pg_dump using a SELECT query


    pg_dump references the -D

    but does anyone know how to feed pg_dump a specific query to produce a more focused dump?

    and other front ends can manually produce an output similar to

    insert into testingtable (t1_f1, t1_f2, t1_f3, t2_f1, t2_f2, etc...) values ('1', '2', '3', '4', '5', etc...);

    from a SELECT t1_f1, t1_f2, t1_f3, t2_f1, t2_f2, etc...
    FROM t1, t2

    does anyone know of a way to feed a select statement to pg_dump to get a focused output instead of the entire database????

    I am aware of the tables option in pg_dump
    but what I am looking to do is automate the query>dump so I can move specific cross-table data into a single report table on a different database
    specifically postgresql to mysql
    and postgresql to oracle

    if pg_dump does not do this
    is there a standard postgres command for pulling this data

    or is the conclusion to write php to pull the data and format the INSERTs???

    and by sqldeveloper I mean these guys (solyp dot com)
    the tool works great, but it is not open source and I can not find any further information on how they are doing the column export to INSERT output

    any ideas would be greatly appreciated.

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    You could, I suppose, construct a SQL statement to do this, by concatenating the non 'variable' portion of the results (as string constants) with the field values.
    For example, I have a table named 'test,' defined as follows:
      groupid integer,
      actionid serial NOT NULL,
      act_date date DEFAULT now(),
      CONSTRAINT test_pk PRIMARY KEY (actionid)
    It's simple, and contains just a few dozen rows.

    If I want to create insert statements from this data, I could use the following query to construct the data for a file
    select 'INSERT INTO schema.test_table (groupid, actionid, act_date) VALUES (' || groupid || ', ' || actionid || ', ''' || act_date || ''')' 
    FROM test
    WHERE actionid <= 5
    ORDER BY groupid, actionid
    With the test data currently in the table, this query would return the following results:

    INSERT INTO schema.test_table (groupid, actionid, act_date) VALUES (12, 1, '2009-02-10')
    INSERT INTO schema.test_table (groupid, actionid, act_date) VALUES (12, 2, '2009-02-10')
    INSERT INTO schema.test_table (groupid, actionid, act_date) VALUES (13, 3, '2009-02-11')
    INSERT INTO schema.test_table (groupid, actionid, act_date) VALUES (13, 4, '2009-02-11')
    INSERT INTO schema.test_table (groupid, actionid, act_date) VALUES (13, 5, '2009-02-11')
    Note how the doubling of apostrophes in the query (around act_date) result in single apostrophes in the result. pgAdmin offers the Query-Execute to File menu option in the SQL query window.

    Note that this approach does not attempt to retrieve the field names/types from the database (as a generic solution.) Instead, we assume that you know the field names and data types in advance, as they are included in the field list, and you specifically include the apostrophes needed to wrap text and date data types in your "constant" strings used in the concatenation.

    A truly generic solution, where you wouldn't need to know anything about the table metadata, would probably require a stored function.
    Last edited by loquin; 11-19-09 at 16:30.
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

  3. #3
    Join Date
    Nov 2009
    I think I just figured this out simply
    hopefully someone else can benefit from this

    step 1
    psql# CREATE TABLE specialtable AS SELECT t1_f1, t1_f2, t1_f3, t2_f1, t2_f2, etc...
    FROM t1, t2

    step 2
    pg_dump DBdata -t specialtable -a -D > specialtable.inserts.sql

    step 3
    cat specialtable.inserts.sql | grep INSERT > specialtable.inserts.ONLY.sql

    psql command
    and pg_dump command

    this is scriptable and ready to be automated now

    thank you for your ideas

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts