Exporting tables from Postgres to SQL

[mattdoughty]

Hi,

How can I export some tables from Postgres to SQL scripts? I've tried the COPY...TO command but it's not really what I was looking for as it creates CSV files.

Cheers,

Matt

[michaelb]

It does not have to be a CSV, the COPY command allows you to specify any delimiter you want.

I'm not sure though what you mean by "copy to SQL scripts"
It may help if you elaborate on what output format you're trying to create, and how you intend to use it.

[mattdoughty]

I'm trying to get a .sql output, which in turn I want to convert to a shapefile (.shp).

[michaelb]

I'm trying to get a .sql output, which in turn I want to convert to a shapefile (.shp).

Sorry, but this does not tell me anything.
.sql is just an extension, which does not necessarily imply any specific standard or format, it only suggests that this file probably contains sql commands.

What you really need is to identify the exact format of what you call an .sql file.

[mattdoughty]

Sorry for being a bit vague, I hadn't realised there were various types of SQL files. I checked the files and they're 'Microsoft server SQL query files'- does that help? Below I've copied a the text from the header and footer of a file similar to what I'd like to output.

Thanks

BEGIN;
CREATE TABLE "txt_barrio" (gid serial PRIMARY KEY,
"nombre" varchar(254),
"izena" varchar(254),
"text_angle" float8,
"text_size" float8,
"textstring" varchar(254));
SELECT AddGeometryColumn('','txt_barrio','the_geom','23030','POINT',2);
INSERT INTO "txt_barrio" ("nombre","izena","text_angle","text_size","textstring",the_geom) VALUES ('Poligono Industrial  La Coba','La Coba industrialdea','0.000000','31.123845','La Coba industrialdea / Poligono Industrial  La Coba','SRID=23030;0101000000316948FDC0F01E415D47207DAC095241');
..... 
.....;
END;
 

[michaelb]

Thanks for clarification.
You need to look at pg_dump utility.
Give it a try, make sure to use the desired command line parameters.
I assume you want to dump data in a plain text format and probably want to use INSERT instead of COPY.

[mattdoughty]

Thanks for the link. Although I managed to solve the problem just before going to lunch. A friend passed me a BAT file called postgistoshp.bat, which works, although I'm not sure how(!).

This is the code:

 
rem %1 database - %2 table
pgsql2shp.exe -h <server_name> -u postgres <DB_name> <table_name>
 

Cheers,

Matt

[michaelb]

Matt, thanks for the update.

I think pgsql2shp.exe comes with PostGIS Spatial Extensions package, something I wasn't aware of until now...

http://www.bostongis.com/PrinterFrie...=postgis_tut01