Opened 13 years ago

Closed 13 years ago

#1197 closed defect (invalid)

Newlines in shapefiles not handled correctly by shp2pgsql

Reported by: dmiranda Owned by: mcayland
Priority: high Milestone: PostGIS 2.0.0
Component: postgis Version: master
Keywords: newline shp2pgsql Cc:

Description

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.

Attachments (2)

newline.zip (975 bytes ) - added by dmiranda 13 years ago.
Minimal shapefile to reproduce the problem
newline.sql (387 bytes ) - added by dmiranda 13 years ago.
Result of shp2pgql on the problem file. Notice the insert statement split into several lines.

Download all attachments as: .zip

Change History (4)

by dmiranda, 13 years ago

Attachment: newline.zip added

Minimal shapefile to reproduce the problem

by dmiranda, 13 years ago

Attachment: newline.sql added

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

comment:1 by mcayland, 13 years ago

Owner: changed from pramsey to 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.

comment:2 by dmiranda, 13 years ago

Resolution: invalid
Status: newclosed

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.

Note: See TracTickets for help on using tickets.