OHDSI Home | Forums | Wiki | Github

OFFSET reserved word

Implementers
#1

The current NOTE_NLP table has a column named OFFSET, and OFFSET is a reserved word. We are proposing to change the name of this column. Any objections?

Thanks,
Frank

#2

Yes. That’s expensive. What’s the problem that it is a reserved word?

#3

It crashes DQD and other queries. Also, its a reserved word. Or shall we start naming columns TABLE and DROP? :slight_smile:

2 Likes
#4

I understand. But:

  • Most parsers are smart enough not to take a field name as an instruction, but it could happen.
  • To avoid the problem put it into delimiters (quotes).
  • If you still believe this is a problem it has to be taken to the NLP WG and explain it to them. They will have to make a change proposal.
#5

If it helps, we can use quoted identifiers. I did a little research, and it seems most DB platforms use " (double quotes) as the token to surround keywords that are used in columns, with the exception of MSSQL. However, this article shows how to set the default setting of quoted_identifier=true at the server level.

Using double quotes allows you to create tables/columns with reserved keywords surrounded by ", and I think the only caveat is that any analysis query would have to apply the quoted identifier whenever using that column.

#6

@Chris_Knoll you are correct, and this is the approach we take with the DDLs. One problem I am having, however, is that some DBMS’s (like postgres) are case sensitive. So even when you use quoted identifiers if you do not have the correct case matching the case of the field in the user’s schema it will barf.

2 Likes
#7

If anyone has not seen the XKCD cartoon, little Johnny tables, should go look it up. It is a humorous, but true, look at why being careless with SQL is so dangerous.

Agreed, Postgres expects one to know what one is doing. I have ran into several nightmares with ported TSQL queries into PSQL. I prefer PostgrSQL, as it has a wider range of operations and types, but it does require more precision .

On top of that, one cannot know how tooling will handle reserved words. What may work fine in all of the current tools (from the above posts, this does not seem to be the case), but one cannot predict what future tools will do.

t