PostgreSQL sink does not create table when entity id contains special characters

[jlsiewert]

It appears that cygnus-ngsi and its PosgreSQL sink can't create a postgres table when the entity has an id that contains the - chracter, e.g. urn:ngsi-ld:Motion:001 as recommended in the Fiware tutorials.

I setup a simple stack with Orion, an IoT-Agent as well as Cygnus-NGSI and a PostgreSQL database.

However, PostgreSQL reports the error

2020-11-10 08:39:33.404 UTC [66] ERROR: syntax error at or near "-" at character 44
2020-11-10 08:39:33.404 UTC [66] STATEMENT: CREATE TABLE IF NOT EXISTS openiot.urn_ngsi-ld_motion_001_thing (recvTimeTs text,recvTime text,fiwareServicePath text,entityId text,entityType text,attrName text,attrType text,attrValue text,attrMd text)

The error does not occur when a different entity id is used.
Data is then persisted as expected.

Cygnus should replace non-alphanumeric characters in entity ids with _, however, that does not appear to be happening.
Is this just a bug or do I need some sort of name mapping to solve this?

[fgalan]

Related question in SOF: https://stackoverflow.com/questions/64767280/fiware-cygnus-postgresql-sink-does-not-create-table-when-entity-id-contains-spec

[fgalan]

The limitation should be at least documented. Maybe every sink should have a "Syntax restrictions" section (or a better name :) to describe this kind of things, starting with Postgresqt sink based on this issue.

[jlsiewert]

This limitation should probably be documented in this project as well as in the getting started guide.

Looking at the current encoding this leaves the dash in a weird space, especially because even Unicode seems to be supported in entities id.
Considering that it is recommended to append the literal urn:ngsi-ld: in front of each id it feels like this sink should handle dashes and colons as well.

[fgalan]

Considering that it is recommended to append the literal urn:ngsi-ld: in front of each id it feels like this sink should handle dashes and colons as well.

Maybe the authors of that document should change the recommendation to urn:ngsild.