pg_dump enhancement ?

[Karsten Hilbert <Karsten.Hilbert@xxxxxxx>]

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, 'typically in 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, 'typically in 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