1

I have an sql script that creates some tables from the default planet_osm_point, planet_osm_line, planet_osm_polygon tables which are created with the osm2pgsql command line tool.

 DROP TABLE IF EXISTS "aero-poly";

 CREATE TABLE "aero-poly" AS ( 

  SELECT way,aeroway 

  FROM planet_osm_polygon

 WHERE "aeroway" IN ('apron','runway','taxiway','helipad')

 ORDER BY z_order ASC );

CREATE INDEX "aero-poly_way_idx" ON "aero-poly" USING gist (way);


DROP TABLE IF EXISTS "agriculture";

CREATE TABLE "agriculture" AS ( 

 SELECT way,landuse FROM planet_osm_polygon

 WHERE "landuse" IN    ('allotments','farm','farmland','farmyard','orchard','vineyard')

ORDER BY z_order ASC );

 CREATE INDEX "agriculture_way_idx" ON "agriculture" USING gist (way);

and more ....

Some columns in the default tables mentioned above, have names that contain a colon (:) like this: seamark:type.

How may I use this name (seamark:type) in the sql script?

Is there a way to escape the colon (:)?

  • Just to be clear, you want to pass a table name or a column name with a colon in as a parameter to a plpgsql script? – John Powell Feb 29 '16 at 16:21
  • Valid SQL identifiers start with an alpha, and permit alphanumeric and underscore as subsequent characters. Some RDBMSes may allow exceptions, but utilizing those exceptions is a violation of best practice -- It's far better to map the colon (and any other non-conformant character) to underscore. – Vince Feb 29 '16 at 17:37
  • Aren't these values used for key value pairs, and using postgres HSTORE, can actually be useful... is there a reason you want to get rid of it? – DPSSpatial Feb 29 '16 at 18:18
  • Try wrapping in backticks, like `seamark:type` – elrobis Feb 29 '16 at 19:13
  • 2
    Easy, use double quotes like "seamark:type" read more here – Mike T Mar 1 '16 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.