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.