SQL question. need help

Posted on
Fri Jun 16, 2017 5:18 pm
kw123 offline
User avatar
Posts: 4861
Joined: May 12, 2013
Location: dallas, tx

SQL question. need help

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

Posted on
Fri Jun 16, 2017 5:32 pm
jay (support) offline
Site Admin
User avatar
Posts: 13254
Joined: Mar 19, 2008
Location: Austin, Texas

Re: SQL question. need help

Try back single quotes:

Code: Select all
`column-name`

Jay (Indigo Support)
Twitter | Facebook | LinkedIn

Posted on
Fri Jun 16, 2017 6:23 pm
kw123 offline
User avatar
Posts: 4861
Joined: May 12, 2013
Location: dallas, tx

Re: SQL question. need help

` ignores the column

Posted on
Fri Jun 16, 2017 6:32 pm
kw123 offline
User avatar
Posts: 4861
Joined: May 12, 2013
Location: dallas, tx

Re: SQL question. need help

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...

Posted on
Fri Jun 16, 2017 6:58 pm
RogueProeliator offline
User avatar
Posts: 1780
Joined: Nov 13, 2012
Location: Baton Rouge, LA

Re: SQL question. need help

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

Posted on
Fri Jun 16, 2017 7:05 pm
kw123 offline
User avatar
Posts: 4861
Joined: May 12, 2013
Location: dallas, tx

Re: SQL question. need help

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

Posted on
Fri Jun 16, 2017 8:28 pm
kwijibo007 offline
Posts: 151
Joined: Sep 27, 2013
Location: Melbourne, Australia

Re: SQL question. need help

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

Posted on
Fri Jun 16, 2017 8:44 pm
kw123 offline
User avatar
Posts: 4861
Joined: May 12, 2013
Location: dallas, tx

Re: SQL question. need help

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 ...
               ^

Posted on
Fri Jun 16, 2017 9:10 pm
kw123 offline
User avatar
Posts: 4861
Joined: May 12, 2013
Location: dallas, tx

Re: SQL question. need help

.. 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

Posted on
Fri Jun 16, 2017 9:17 pm
kw123 offline
User avatar
Posts: 4861
Joined: May 12, 2013
Location: dallas, tx

Re: SQL question. need help

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

Posted on
Fri Jun 16, 2017 9:45 pm
RogueProeliator offline
User avatar
Posts: 1780
Joined: Nov 13, 2012
Location: Baton Rouge, LA

Re: SQL question. need help

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

Page 1 of 1

Who is online

Users browsing this forum: No registered users and 0 guests