Home > Software design >  PostgreSQL: Cannot use numbers as tags for dollar quoted strings
PostgreSQL: Cannot use numbers as tags for dollar quoted strings

Time:04-30

How do we use numbers as tags for dollar quoted strings?

INSERT INTO table(user_id,user_data) 
values (22176,to_jsonb($123${"name": "Helo. $ what is this $"}$123$::jsonb))

The above query fails, however if I replace numeric tags with alphabetic then it works. I didn't find anything in the documentation against using numbers for tags.

I need to make my tags as unique as possible, since I'm trying to avoid a situation where user content inside the jsonb matches my tags, for example

$abc${"name": "hello $abc$"}$abc$

I was trying to use UUIDs but it's not accepting numbers as tags.

Note: It's an example query, I have a lot of ' in my json values.

CodePudding user response:

You cannot use $123$, because PostgreSQL uses $1, $2 etc. as placeholders in prepared statements. $a1$ would be ok.

To get a rare string to avoid collisions, drop on the keyboard a few times and make sure not to hit a digit first.

  • Related