[postgis-users] SQL Dump Question

[Markus Schaber]

Hi, Sean,

First: Please don't reply to an existing message when posting a new
topic. Most mailreaders will keep the References: header in the mail,
and most mailreaders will then sort your message into the middle of the
existing discussion. This has two side effects: It annoys readers, and
it increases the likelyhood for your message to get lost.

Sean Montague wrote:
> This is more of a straight postgreSQL question, but does pertain to a
> postGIS table.

Questions of this kind are best-placed at the PostgreSQL SQL list at
pgsql-sql at postgresql.org, subscription information can be found at
http://www.postgresql.org/community/lists/

> I want to dump a very large table into separate tables by
> year. For instance, SELECT * FROM dg WHERE date = '2002';, and dump the
> result into a sql file, preferably with inserts into a table named dg2002.

The easiest way is to issue

CREATE TABLE dg2002 AS SELECT * FROM dg WHERE date = '2002';

and then use pg_dump to extract just this table via the --table switch.

Afterwards, you can DROP TABLE dg2002; again.

For some applications, a view and a partial index may also be helpful in
this case, to avoid data duplication in the same database.

PostgreSQL 8.2 will support the COPY ... SELECT statement that will be
helpful in your case, too.

HTH,
Markus
-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org