By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 915 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Exporting tables from Postgres to SQL

P: 6

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.


May 22 '07 #1
Share this Question
Share on Google+
7 Replies

Expert 100+
P: 534
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.
May 22 '07 #2

P: 6
I'm trying to get a .sql output, which in turn I want to convert to a shapefile (.shp).
May 22 '07 #3

Expert 100+
P: 534
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.
May 22 '07 #4

P: 6
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.

Expand|Select|Wrap|Line Numbers
  1. BEGIN;
  2. CREATE TABLE "txt_barrio" (gid serial PRIMARY KEY,
  3. "nombre" varchar(254),
  4. "izena" varchar(254),
  5. "text_angle" float8,
  6. "text_size" float8,
  7. "textstring" varchar(254));
  8. SELECT AddGeometryColumn('','txt_barrio','the_geom','23030','POINT',2);
  9. 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');
  10. ..... 
  11. .....;
  12. END;
May 23 '07 #5

Expert 100+
P: 534
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.
May 24 '07 #6

P: 6
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:

Expand|Select|Wrap|Line Numbers
  2. rem %1 database - %2 table
  3. pgsql2shp.exe -h <server_name> -u postgres <DB_name> <table_name>

May 24 '07 #7

Expert 100+
P: 534
Matt, thanks for the update.

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

Post your reply

Sign in to post your reply or Sign up for a free account.