Partition 2

CREATE TABLE sales.orders_new3 (
  created_at  timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  order_id    uuid NOT NULL DEFAULT gen_random_uuid(),
  customer_id bigint NOT NULL,
  amount      numeric(12,2) NOT NULL,

  PRIMARY KEY (created_at, order_id)
) PARTITION BY RANGE (created_at);

-- Helpful for point lookups by order_id
CREATE INDEX ON sales.orders_new3 (order_id);

# Create past and future partitions
DO $$
DECLARE
  start_ts timestamp(3) := '2023-01-01 00:00:00';
  -- create through next Sunday + 2 weeks for breathing room
  end_ts   timestamp(3) := (
              date_trunc('day', now())  -- today at midnight (local server time, but it's timestamp)
              - (extract(dow from now())::int) * interval '1 day'  -- go back to Sunday 00:00
              + interval '3 weeks'  -- this Sunday + 3 weeks (i.e., two weeks ahead from next boundary)
            )::timestamp(3);

  cur      timestamp(3);
  nxt      timestamp(3);
  partname text;
BEGIN
  cur := start_ts;

  WHILE cur < end_ts LOOP
    nxt := (cur + interval '1 week')::timestamp(3);

    -- partitions named like orders_new3_p20230101, orders_new_p20230108, ...
    partname := format('orders_new3_p%s', to_char(cur, 'YYYYMMDD'));

    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS sales.%I
         PARTITION OF sales.orders_new3
         FOR VALUES FROM (%L) TO (%L);',
      partname, cur, nxt
    );

    cur := nxt;
  END LOOP;
END $$;

-- Add the job to add new partitions
SELECT partman.create_parent(
  p_parent_table := 'sales.orders_new3',
  p_control      := 'created_at',
  p_type         := 'range',
  p_interval     := '1 week'
);

-- Verify the partitions
SELECT
  child.relname AS partition,
  pg_get_expr(child.relpartbound, child.oid) AS bound
FROM pg_inherits i
JOIN pg_class parent ON parent.oid = i.inhparent
JOIN pg_class child  ON child.oid  = i.inhrelid
WHERE parent.oid = 'sales.orders_new3'::regclass
ORDER BY child.relname;