I am trying to figure out how to store the id column of a table into a hstore column. That way the id is stored in two places. Going forward the table will be moved to another database and the current id will need to be preserved.

I have tried:

update new_table set hstore_col = hstore_col || hstore("id",id::text) where id = 1

This does not work Any other suggestions adding the id column of a database table to a hstore column it has.

  • Double quoted "id" is an identifier, use 'id' instead to specify the string literal: hstore('id', id::text) – Abelisto Nov 10 '16 at 20:19
  • @Abelisto can you make that an answer, so he can make the question as answered? – Evan Carroll Nov 10 '16 at 22:38
  • @EvanCarroll There are already a tons of Q&A about the same issue. However, done. – Abelisto Nov 10 '16 at 23:11

Usually in SQL double quotes used to specify the identifiers like tables, columns, functions and other DB objects which name contains "non-conventional" characters or have to be case-sensitive. For example it is correct PostgreSQL statement:

create table "This is#my...%TABLE" ("And&Field" int);

(It is not recommended to use such names however because it may cause a lot of problems)

So double quoted literals meanings the DB objects and in the your statement "id" meanings the id field.

On the other hand to specify the string literal in the standard SQL you have to use single quotes. For example:

select 'foo' as "bar field";

Here 'foo' is a text constant and "bar field" is a column alias where double quotes is required because of the space character inside it.

According to the above you have to change your statement to

update new_table set hstore_col = hstore_col || hstore('id',id::text) where id = 1
| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.