pg_dump enhancement ? - Mailing list pgsql-general

Let's assume there's a table dem.address

    CREATE TABLE address (
        id integer NOT NULL,
        id_street integer NOT NULL,
        aux_street text,
        number text NOT NULL,
        subunit text,
        addendum text,
        lat_lon point
    )
    INHERITS (audit.audit_fields);

    (the INHERITS part is not relevant here)

Given the following pg_dump invocation

    pg_dump --inserts --data-only -d ... -U ... -t dem.address

we will get a dump like:

    --
    -- PostgreSQL database dump
    --

    SET statement_timeout = 0;
    SET lock_timeout = 0;
    SET client_encoding = 'UTF8';
    SET standard_conforming_strings = on;
    SET check_function_bodies = false;
    SET client_min_messages = warning;

    SET search_path = dem, pg_catalog;

    --
    -- Data for Name: address; Type: TABLE DATA; Schema: dem; Owner: gm-dbo
    --

    INSERT INTO address VALUES (12638, 0, '2013-11-19 13:51:03.641023+00', 'gm-dbo', 1, 10, NULL, '117', NULL, NULL,
NULL);
    INSERT INTO address VALUES (12641, 1, '2013-11-19 13:51:03.690905+00', 'gm-dbo', 2, 11, NULL, '31', NULL,
'typicallyin Space', NULL); 
    INSERT INTO address VALUES (12646, 0, '2013-11-19 13:51:05.651142+00', 'gm-dbo', 3, 12, NULL, '65', 'Parterre',
NULL,NULL); 


    --
    -- Name: address_id_seq; Type: SEQUENCE SET; Schema: dem; Owner: gm-dbo
    --

    SELECT pg_catalog.setval('address_id_seq', 3, true);


    --
    -- PostgreSQL database dump complete
    --

The man page says:

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

Given the "loaded into non-PostgreSQL databases" part I
wonder whether pg_dump --verbose might add a commented out
line listing the column order (say, above the INSERTS):

    -- (pk_audit, row_version, modified_when, modified_by, id, id_street, aux_street, number, subunit, addendum,
lat_lon)
    INSERT INTO address VALUES (12638, 0, '2013-11-19 13:51:03.641023+00', 'gm-dbo', 1, 10, NULL, '117', NULL, NULL,
NULL);
    INSERT INTO address VALUES (12641, 1, '2013-11-19 13:51:03.690905+00', 'gm-dbo', 2, 11, NULL, '31', NULL,
'typicallyin Space', NULL); 
    INSERT INTO address VALUES (12646, 0, '2013-11-19 13:51:05.651142+00', 'gm-dbo', 3, 12, NULL, '65', 'Parterre',
NULL,NULL); 

    (don't worry about the actual columns they are not
    relevant, some may also not seem very normalized)

or even show the *first* line for each table as if it was run
with --column-inserts ?

That would help not having to refer back to some other means
of schema discovery when trying to load such dumps into
another database.

Thanks,
Karsten
--
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346