Newlines in shapefiles not handled correctly by shp2pgsql

[dmiranda]

Shp2pgsql will not escape the newlines on text attributes of shapefiles when translating to postgis. The dump will not load because the insert statements get split between several lines.

Tested with postgis SVN R7856, Last Changed Date: 2011-09-15 14:43:57

Looks like Ticket #203 reappearing on trunk.

[dmiranda]

Minimal shapefile to reproduce the problem

[dmiranda]

Result of shp2pgql on the problem file. Notice the insert statement split into several lines.

[mcayland]

I'm afraid I can't reproduce this here as the following file works under plain PostgreSQL for me:

postgres@zeno:~$ cat /tmp/mca.sql SET CLIENT_ENCODING TO UTF8; SET STANDARD_CONFORMING_STRINGS TO ON; BEGIN; CREATE TABLE "newline" (gid serial, "id" numeric(10,0), "content" varchar(80)); ALTER TABLE "newline" ADD PRIMARY KEY (gid); INSERT INTO "newline" ("id","content") VALUES ('1','bla bla bla'); COMMIT;

postgres@zeno:~$ psql -d foo -f /tmp/mca.sql -U foo -h localhost Password for user foo: SET SET BEGIN psql:/tmp/mca.sql:6: NOTICE: CREATE TABLE will create implicit sequence "newline_gid_seq" for serial column "newline.gid" CREATE TABLE psql:/tmp/mca.sql:7: NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "newline_pkey" for table "newline" ALTER TABLE INSERT 0 1 COMMIT

postgres@zeno:~$ psql -d foo -U foo -h localhost Password for user foo: psql (8.4.8) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help.

foo=> select * from newline;

gid | id | content

---

1 | 1 | bla

: bla : bla

(1 row)

The use of standard_conforming_strings should be enough so that multi-line INSERT statements are accepted correctly without having to escape anything other than as dictated by the SQL standard.

[dmiranda]

Indeed. I just updated postgresql to 9.1 (before was 9.1 rc1) and tested some known bad files and now everything seems ok. Thanks for taking a look at this though.