Error Creating Indexes for PDT

Looker Q&A Looker
,
1

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?

2

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