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