Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2016
    Posts
    18
    Provided Answers: 1

    Answered: data format of pg_dump

    Hi all,
    running command
    Code:
      pg_dump  -h localhost  -U postgres -W -F p  wprods > /_wwwroot/wprods/__SQL/DUMPS/wprods_db_pd_product_bookmark.sql   --table pd_product_bookmark --format=c
    In resulting file I see rows like :
    Code:
    COPY pd_product_bookmark (id, product_id, user_id, is_featured, info, created_at) FROM stdin;
    8	53	1	t		2017-02-27 14:15:48.7069
    9	43	1	f	Interesting  Ninja	2017-02-27 14:18:54.849934
    7	54	1	t	Cool Monitor...	2017-02-27 14:15:40.381489
    12	25	1	t	Ninja Silhouette ерштп 3	2017-03-04 18:24:07.3093
    13	45	1	t	Flying Ninja 45	2017-03-04 18:24:55.383167
    If there is a way for pg_dump to export data rows in more common INSERT INTO rows, which could be used in sql editors ?

    Are there some other tools for this ?

    Thanks!

  2. Best Answer
    Posted by shammat

    "From the pg_dump manual

    Quote Originally Posted by The Manual
    --inserts

    Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safe against column order changes, though even slower.
    But using inserts is a lot slower during restore compared to the COPY command."


  3. #2
    Join Date
    Nov 2003
    Posts
    2,993
    Provided Answers: 23
    From the pg_dump manual

    Quote Originally Posted by The Manual
    --inserts

    Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safe against column order changes, though even slower.
    But using inserts is a lot slower during restore compared to the COPY command.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

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