SQL question. need help

[kw123]

I am trying do execute an sql statement with a column name that contains "-". e.g. xx-yy

have tried 'xx-yy' [xx-yy]
all give either an error or print string 'xx-yy' instead of the contents

any suggestions?


Karl

[jay (support)]

Try back single quotes:

Code: Select all

`column-name`

[kw123]

` ignores the column

[kw123]

tried '"[]
==>

Code: Select all

~:psql indigo_history -U postgres  -c  "SELECT `rx-tx-RateWiFi` from device_history_1066129470  LIMIT 10";
-bash: rx-tx-RateWiFi: command not found
--
(10 rows)

~:psql indigo_history -U postgres  -c  "SELECT [rx-tx-RateWiFi] from device_history_1066129470  LIMIT 10";
ERROR:  syntax error at or near "["
LINE 1: SELECT [rx-tx-RateWiFi] from device_history_1066129470  LIMI...
               ^
~:psql indigo_history -U postgres  -c  "SELECT 'rx-tx-RateWiFi' from device_history_1066129470  LIMIT 10";
    ?column?    
----------------
 rx-tx-RateWiFi
 rx-tx-RateWiFi
 rx-tx-RateWiFi
 rx-tx-RateWiFi
 rx-tx-RateWiFi
 rx-tx-RateWiFi
 rx-tx-RateWiFi
 rx-tx-RateWiFi
 rx-tx-RateWiFi
 rx-tx-RateWiFi
(10 rows)

~:
~:psql indigo_history -U postgres  -c  "SELECT \"rx-tx-RateWiFi\" from device_history_1066129470  LIMIT 10";
ERROR:  column "rx-tx-RateWiFi" does not exist
LINE 1: SELECT "rx-tx-RateWiFi" from device_history_1066129470  LIMI...

[RogueProeliator]

I think you want a standard double-quote around the column...

SELECT "rx-tx-RateWiFi" from device_history_1066129470 LIMIT 10

Which, of course, would require you use single quotes or elsewise escape in certain circumstances / languages.

Adam

[kw123]

Code: Select all

SELECT "rx-tx-RateWiFi" from device_history_1066129470  LIMIT 10;                                                                           
ERROR:  syntax error at or near "SELECT"
LINE 2: SELECT rx
        ^
indigo_history=# 

nop

so ' " [ ]` does not work .. also tried to escape the "-" sign

[kwijibo007]

Have you tried no quote at all?

Code: Select all

SELECT rx-tx-RateWiFi from device_history_1066129470  LIMIT 10

Sent from my iPhone using Tapatalk

[kw123]

yes that is were I started:

Code: Select all

 SELECT rx-tx-RateWiFi from device_history_1066129470  LIMIT 10;
ERROR:  column "rx" does not exist
LINE 1: SELECT rx-tx-RateWiFi from device_history_1066129470  LIMIT ...
               ^

[kw123]

.. kind of giving up.. changing the state names to _ instead of - ... tooo many problems also down the road

but still interested in how to do it

[kw123]

SURPRISE!!!!

Indigo replaces the - with a _ when storing the state in the database .. so replacing the - with _ in the state name has no impact on the database , all history is still there .. that was an easy change !!

Karl

[RogueProeliator]

FWIW, if a similar need to escape comes up in the future, it looks like double quotes is indeed the Postgre escape sequence... from the docs:

There is a second kind of identifier: the delimited identifier or quoted identifier. It is formed by enclosing an arbitrary sequence of characters in double-quotes ("). A delimited identifier is always an identifier, never a key word. So "select" could be used to refer to a column or table named "select", whereas an unquoted select would be taken as a key word and would therefore provoke a parse error when used where a table or column name is expected.

Of course, if Indigo changes your characters that won't help too much - but perhaps if you have a similar-but-not-exact issue in the future this will be here for posterity...

Adam