1

Hi guys this is hopefully a simple one. I have only taken a simple SQL course and am now trying to apply it to a real postgres database. The immediate difficulty I am having is dealing with things at the schema level (multiple schemas) whereas everything I had learned only dealt with querying between multiple tables in a single schema.

I am trying to go one step more than this questioner and filter by schema, but things are behaving very weirdly to me

This is what I mean. This query returns the result I expect (and is lifted straight from this SO example):

this is fine so far but the syntax highlighting is perhaps pointing to a problem

so given my sql training so far I figure I can take the output and further refine it. 

select table_name
from (select table_name, table_schema from information_schema.columns where column_name = 'user_id')
where table_schema = "web_app"

doing this gives a very weird error:

enter image description here

why is it treating what is obviously a field as a column?? what is this derived_table1? (presumably its the compounded query?)

in the extremely unlikely case that it is a software issue, i am using Navicat premium 9.1.2 and connecting to amazon redshift.

Community
  • 1
  • 1
swyx
  • 2,378
  • 5
  • 24
  • 39
  • 1
    `"web_app"` is an identifier. String constants need to be put into single quotes: `'web_app'`. https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS –  Jun 12 '16 at 09:26
  • by god. nobody ever said double quotes were different from single quotes. that still makes no sense to me syntactically but its the right answer. thanks so much. – swyx Jun 12 '16 at 09:46
  • Well, this is how the SQL language was specified back in the 70's –  Jun 12 '16 at 09:47

2 Answers2

0
  • Related to the colours, I suppose it is related to your tool and the keywords are coloured like this. 'table_name' seems considered as a keyword. Check the list of keywords and correct it if necessary.
  • The query you have written is wrong. Please remove 'table_schema'.
  • If you select from a select, then the database engine considers the list of tables returned as derived. The list is computed at runtime and it is not easy to optimize those queries.
Rudy Vissers
  • 5,267
  • 4
  • 35
  • 37
  • thanks Rudy, but the other answer was the more immediate correct answer. marked you right anyway since the guy just left it as a comment. – swyx Jun 12 '16 at 09:48
0

hi future people with the same issue, see a_horse_with_no_name's comment. thats the right answer for my issue.

swyx
  • 2,378
  • 5
  • 24
  • 39