Question

Error Creating Indexes for PDT

  • 20 July 2022
  • 1 reply
  • 12 views

I’m using a PDT in my application. I get a warning that I’m not using an index for my PDT, and so to commit my changes I need to add an index (or remove the requirement that 0 warnings must exist, which I don’t want to do). I’ve tried to add an index, but I get the following error message. 

The PostgreSQL 9.5+ database encountered an error while running this query.
ERROR, index creation 'public.looker_view_contact.cured_id' for derived table contact_main_pdt failed: ERROR: syntax error at or near ")" Position: 148' 'CREATE INDEX IDXMZBN71658343989912_contact_main_pdt_0 ON looker_scratch.LR$MZBN71658343989912_contact_main_pdt (public.looker_view_contact.cured_id)'

My PDT looks like this: 

view: contact_main_pdt {
derived_table: {
sql: SELECT
contact."cured_id" AS "contact.cured_id",
SUBSTRING ( (contact."email") , POSITION('@' in (contact."email")) + 1) AS "contact.domain",
contact."zip_code" AS "contact.zip_code",
contact."gender" AS "contact.gender",
contact_status."status" AS "contact_status.status",
contact_status."channel" AS "contact_status.channel",
contact_status."cured_id" AS "contact_status.cured_id",
COUNT(DISTINCT ( contact."cured_id" ) ) AS "contact.total_customers_count"
FROM public.submit_event_fdw AS submit_event_fdw
INNER JOIN public.looker_view_contact AS contact ON CAST((contact."cured_id") as VARCHAR) = submit_event_fdw.cured_id
INNER JOIN public.contact_status AS contact_status ON (contact."cured_id") = (contact_status."cured_id")
WHERE (submit_event_fdw.client_id ) = 'socratic'
GROUP BY
1,
2,
3,
4,
5,
6,
7
ORDER BY
8 DESC
;;
sql_trigger_value: SELECT COUNT(*) FROM public.submit_event_fdw ;;
indexes: ["public.looker_view_contact.cured_id"]
}

measure: count {
type: count
drill_fields: [detail*]
}

Why can’t I create the index, and how do I work around this?


1 reply

Hi @thomas-cured ! Looks like you defined your indexes incorrectly. In the quotes just refer to the column name you aliased in the SQL above which would be: “contact.cured_id”.

Reply