3

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.

closed as off-topic by MDCCL, Philᵀᴹ, Andriy M, mustaccio, Tom V Nov 11 '16 at 15:21

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – MDCCL, Philᵀᴹ, Andriy M, mustaccio, Tom V
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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
2

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

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