ERROR: function pg_catalog.extract(unknown, integer) does not exist

Question

I am writing an SQL query for creating the partitions which looks like:

DO
$$
    DECLARE
        table_name             text     := 'table_1';
        start_date             date     := (SELECT MIN(create_date)
                                            FROM db.table);
        end_date               date     := (SELECT MAX(create_date)
                                            FROM db.table);
        partition_interval     interval := '1 day';
        partition_column_value text;
    BEGIN
        FOR partition_column_value IN SELECT start_date +
                                             (generate_series * extract(day from partition_interval)::integer)::date
                                      FROM generate_series(0, extract(day from end_date - start_date::date) /
                                                              extract(day from partition_interval))
            LOOP
                EXECUTE format(
                        'create table if not exists %1$s_%2$s partition of %1$s for values in (%2$s) partition by list (create_date)',
                        table_name, partition_column_value::date);
            END LOOP;
    END
$$;

I get an error:

[42883] ERROR: function pg_catalog.extract(unknown, integer) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Where: PL/pgSQL function inline_code_block line 9 at FOR over SELECT rows

Answer 1

The immediate cause of the error msg is this:

extract(day from end_date - start_date::date)

It's nonsense to cast start_date::date, start_date being type date to begin with. More importantly, date - date yields integer (not interval like you might assume). And extract() does not operate on integer input.

I removed more confusion and noise to arrive at this:

DO
$do$
DECLARE
   table_name             text    := 'table_1';
   partition_interval     integer := 1;  -- given in days!!
   start_date             date;
   end_date               date;
   partition_column_value text;
BEGIN
   SELECT INTO start_date, end_date  -- two assignments for the price of one
          min(create_date), max(create_date)
   FROM   db.table;
   
   FOR partition_column_value IN
      SELECT start_date + g * partition_interval         -- date + int → date
      FROM   generate_series(0, (end_date - start_date)  -- date - date → int
                              / partition_interval) g
   LOOP
      EXECUTE format(
         'CREATE TABLE IF NOT EXISTS %1$I PARTITION OF %1$I
          FOR VALUES IN (%3$L) PARTITION BY LIST (create_date)'
        , table_name || to_char(partition_column_value, '"_"yyyymmdd')  -- !
        , table_name
        , partition_column_value::text  -- only covers single day!!
          );
   END LOOP;
END
$do$;

This should work.
But it only makes sense for the example interval of '1 day'. For longer intervals, concatenate the list of days per partition or switch to range partitioning ...