Casting to char as Integer

February 21, 2022

I’m trying to do some casting but cannot understand where I’m making a mistake. The error occurs in this line:

set  datekey = cast(to_char((event_time)::TIMESTAMP,'yyyymmdd') as integer)

ERROR: Syntax error at »yyyymmdd«
LINE 16: … datekey = cast(to_char((event_time)::TIMESTAMP,’yyyymmdd’)…

And here is my whole query:

do $$
  declare
    arow record;
    curtable varchar(50);
    number_ int = 0;
  begin
    for arow in
      SELECT table_name as fact_table FROM information_schema.tables WHERE table_name like 'fact_entriesexits%' 
    loop
      curtable := arow.fact_table;

     RAISE NOTICE 'Updating partition(%)', curtable;
      execute ('
     
            Update ' || curtable || ' e
            set  datekey = cast(to_char((event_time)::TIMESTAMP,'yyyymmdd') as integer),
             timekey = cast(to_char((event_time)::TIMESTAMP,'hhmiss') as integer),
             dateinserted = odb_created_at,
             devicetype = device_type,
             cardnumber = card_nr,
             manufacturerkey = 
                 case when manufacturer like '%DESIGNA%' THEN 2 
                 WHEN manufacturer like '%SKIDAT%' THEN 3
                 WHEN manufacturer like '%IPCP%' THEN 1  
                 WHEN manufacturer like '%SCHEIDT%' THEN 4 END,
             eventdate = event_time;

        Update ' || curtable || ' e  set  ticket_type  = 14 where ticket_type in (2,15);
        Update ' || curtable || ' e  set  ticket_type  = 41 where ticket_type = 8;
        Update ' || curtable || ' e  set  ticket_type  = 9  where ticket_type = 30;
        Update ' || curtable || ' e  set  ticket_type  = 21 where ticket_type = 20;
        Update ' || curtable || ' e  set  ticket_type  = 33 where ticket_type = 18;

        Update ' || curtable || ' e  set  tickettypekey      = dt.key  from dim_tickettype dt 
        where dt.tickettypeid = e.ticket_type and event_time >= dt.scd_start AND (event_time < dt.scd_end OR dt.scd_end IS NULL);
        
        Update ' || curtable || ' e  set  eventtypekey = et.key from dim_eventtype et
        where et.eventtypeid = e.event_type and event_time >= et.scd_start AND (event_time < et.scd_end OR et.scd_end IS NULL);
        
        Update ' || curtable || ' e  set  facilitykey  = et.key from dim_facility et
        where et.facilityid = e.carpark_id and event_time >= et.scd_start AND (event_time < et.scd_end OR et.scd_end IS NULL);');
    end loop;
  end;
$$;

>Solution :

Use two single quote instead of one single quote when using dynamic query. All static text except variable/parameter inside preparing query must use two single quote.

cast(to_char((event_time)::TIMESTAMP,''yyyymmdd'') as integer)

case when manufacturer like ''%DESIGNA%'' THEN 2 
                 WHEN manufacturer like ''%SKIDAT%'' THEN 3
                 WHEN manufacturer like ''%IPCP%'' THEN 1  
                 WHEN manufacturer like ''%SCHEIDT%''

N.B.: Two single quote ”test” converts in ‘test’ but one single quote ‘test’ converts test. That’s throw error.