0

I am using PostgreSQL with Pentaho Data Integration. I am executing a query:

  SELECT
  t.VAL_1 as Contract,
  t.VAL_2 as Calender,
  t.VAL_3 as MSPProvider,
  t.VAL_4 as MSPCustomer,
  t.VAL_5 as Severity,
  t.VAL_6 as Relation,
  t.VAL_7 as Target,
  t.VAL_8 as TargetUnit,
  t.VAL_9 as TargetPeriod,
  t.VAL_10 as CalculationType,
  t.VAL_11 as working_days,
  t.VAL_12 as time_of_day
FROM t_slalom_outputs t 
WHERE t.is_active = 1 and t.table_name = 'contract_target_mapping';

The error says:

ERROR:  column t.val_1 does not exist

LINE 1: select t.VAL_1 as Contract,
               ^
********** Error **********

ERROR: column t.val_1 does not exist
SQL state: 42703
Character: 8

If I write my query using quoted commas ie.

 SELECT
 't.VAL_1' as Contract,
  't.VAL_2' as Calender,
  't.VAL_3' as MSPProvider,
  't.VAL_4' as MSPCustomer,
  't.VAL_5' as Severity,
  't.VAL_6' as Relation,
  't.VAL_7' as Target,
  't.VAL_8' as TargetUnit,
  't.VAL_9' as TargetPeriod,
  't.VAL_10' as CalculationType,
  't.VAL_11' as working_days,
  't.VAL_12' as time_of_day
 FROM t_slalom_outputs t

The column name (t.val_1,t.val_2.....t.val_12) gets populated in the column values with column names as the alias names (contract, calendar.......Time Of Day). I want to execute the first query but can't get rid of errors. I am noobie at PostgreSQL. I think there's some error with the syntax. Any help will be much appreciated.

Pranjal Kaushik
  • 13
  • 1
  • 10
  • Please show your table (`\d t_slalom_outputs`) should be enough, `CREATE TABLE` statement is better), because this isn't reproducible just yet. – dhke Aug 23 '17 at 18:04
  • By adding quotes, the columns will be interpret as literals, and does not select the actual columns. Without a table definition it is hard to tell. – Fokko Driesprong Aug 23 '17 at 18:19
  • with current query you just dont need any alias - dont specify any prior to column name. instead of `t.VAL_1`, use `VAL_1` – Vao Tsun Aug 23 '17 at 18:24
  • (1) `'t.VAL_1'` is a string literal. (2) PostgreSQL folds unquoted identifiers to lower case so `t.VAL_1` and `t.val_1` are equivalent, hence the lower case column name in the error message. (3) Standard SQL (and PostgreSQL) use double quotes for case sensitive identifiers so you want to say `t."VAL_1"` to preserve case. – mu is too short Aug 23 '17 at 19:22

0 Answers0