sosicon -2psql inconsistent quoting of table names

[chrisvwn]

Hi,

Thanks for this useful tool.

I am trying to import a sosi file into psql using sosicon but receiving errors like below:

ERROR:  relation "elvegsamferdsel_01.tbl0101Elveg_point" does not exist
CONTEXT:  SQL statement "ALTER TABLE elvegsamferdsel_01."tbl0101Elveg_point" ADD COLUMN "tbl0101Elveg_geom" geometry(Point, 4326)"
PL/pgSQL function addgeometrycolumn(character varying,character varying,character varying,character varying,integer,character varying,integer,boolean) line 106 at EXECUTE
SQL statement "SELECT public.AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)"
PL/pgSQL function addgeometrycolumn(character varying,character varying,character varying,integer,character varying,integer,boolean) line 5 at SQL statement

After some digging around it seems that sosicon -2psql is generating SQL where table names are sometimes quoted and sometimes not. In particular the SELECT AddGeometryColumn single-quotes table names which seems to be translated to a double quoted table name in the ALTER TABLE statement. This causes errors for example:

This is a section of the sql file generated using sosicon -2psql showing the create table sections (emphasis is mine):

CREATE TABLE IF NOT EXISTS elvegsamferdsel_01.tbl0101Elveg_point(id_tbl0101Elveg_point INT DEFAULT nextval('elvegsamferdsel_01.tbl0101Elveg_point_serial'),datafangstdato VARCHAR(8),komm VARCHAR(4),kp VARCHAR(1),kvalitet VARCHAR(15),medium VARCHAR(1),nh VARCHAR(24),objtype VARCHAR(12),vegsperringtype VARCHAR(12),vfradato VARCHAR(8),vnr VARCHAR(9),vpa VARCHAR(13));
SELECT AddGeometryColumn( 'elvegsamferdsel_01','tbl0101Elveg_point','tbl0101Elveg_geom',4326,'POINT',2);

Manually running this gives the following:

SELECT AddGeometryColumn( 'elvegsamferdsel_01','tbl0101Elveg_point','tbl0101Elveg_geom',4326,'POINT',2);
ERROR:  relation "elvegsamferdsel_01.tbl0101Elveg_point" does not exist
CONTEXT:  SQL statement "ALTER TABLE elvegsamferdsel_01."tbl0101Elveg_point" ADD COLUMN "tbl0101Elveg_geom" geometry(Point, 4326)"

Which is quite right because the table was generated with a lowercase name which I believe is because it was not quoted in the create table statement whle the ALTER TABLE statement above uses the quoted table name:

       Schema       |          Name           | Type  |  Owner   
--------------------+-------------------------+-------+----------
 elvegsamferdsel_01 | tbl0101elveg_linestring | table | postgres
 elvegsamferdsel_01 | tbl0101elveg_point      | table | postgres
 elvegsamferdsel_01 | tbl0101elveg_polygon    | table | postgres
(3 rows)

I believe this will also affect the geometry column in the insert statements where it is not quoted:

INSERT INTO elvegsamferdsel_01.tbl0101Elveg_point (datafangstdato,komm,kp,kvalitet,medium,nh,objtype,tbl0101Elveg_geom,vegsperringtype,vfradato,vnr,vpa) VALUES

This is with postgres 11 on Debian 4.9.

[espena]

Hi and thanks for submitting this issue. It turned out to be a lot of things going on here. Firstly, the uppercase letters in field-, table- and schema names were not lowercased in the code. However, PostgreSQL will lowercase any non-quoted names, hence - since quoted field names are indeed case sensitive - errors will arise due to the mismatch. Secondly, I also encountered problems with string literal escaping. Sosicon used the old-fashioned backslash character to escape single quotes within text fields. This is now corrected to the recommended double-quoting instead. Thirdly, The initial "SET NAMES [...]" directive at the top of the exported SQL file caused character set problems when the original file was already UTF-8 encoded. Hopefully, these issues have now been fixed and will be commited to the master branch shortly.

[chrisvwn]

Great! Thank you!

[chrisvwn]

Confirming this is fixed! Thanks again!