title: How to create dynamic partition table in postgres with bigint column?
body: <p>I have a master table such as</p>
  
  <pre><code>CREATE TABLE public.user_event_firebase
  (
      user_id character varying(32) COLLATE pg_catalog."default" NOT NULL,
      event_name character varying(255) COLLATE pg_catalog."default" NOT NULL,
      "timestamp" bigint NOT NULL,
      platform character varying(255) COLLATE pg_catalog."default" NOT NULL,
      created_at timestamp without time zone DEFAULT now()
  )
  WITH (
      OIDS = FALSE
  )
  TABLESPACE pg_default;
  </code></pre>
  
  <p><strong>GOAL</strong></p>
  
  <p>I want to partition this table by year_month table with "<strong>timestamp</strong>" column such as user_event_firebase_2018_04 , user_event_firebase_2018_05, user_event_firebase_2018_06. The rows will automation redirect to insert into partition table with timestamp condition. </p>
  
  <p>I created function create partition such as:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION partition_uef_table( bigint, bigint )
  returns void AS $$
  DECLARE
      create_query text;
      index_query text;
  BEGIN
      FOR create_query, index_query IN SELECT
              'create table user_event_firebase_'
              || TO_CHAR( d, 'YYYY_MM' )
              || ' ( check( timestamp &gt;= bigint '''
              || TO_CHAR( d, 'YYYY-MM-DD' )
              || ''' and timestamp &lt; bigint '''
              || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD' )
              || ''' ) ) inherits ( user_event_firebase );',
              'create index user_event_firebase_'
              || TO_CHAR( d, 'YYYY_MM' )
              || '_time on user_event_firebase_' 
              || TO_CHAR( d, 'YYYY_MM' )
              || ' ( timestamp );'
          FROM generate_series( $1, $2, '1 month' ) AS d
      LOOP
          EXECUTE create_query;
          EXECUTE index_query;
      END LOOP;
  END;
  $$
  language plpgsql;
  
  CREATE OR REPLACE FUNCTION test_partition_function_uef()
  RETURNS TRIGGER AS $$
  BEGIN
      EXECUTE 'insert into user_event_firebase_'
          || to_char( NEW.timestamp, 'YYYY_MM' )
          || ' values ( $1, $2, $3, $4 )' USING NEW.user_id, NEW.event_name, NEW.timestamp, NEW.platform;
      RETURN NULL;
  END;
  $$
  LANGUAGE plpgsql;
  </code></pre>
  
  <p>with trigger </p>
  
  <pre><code>CREATE TRIGGER test_partition_trigger_uef
      BEFORE INSERT
      ON user_event_firebase
      FOR each ROW
      EXECUTE PROCEDURE test_partition_function_uef() ;
  </code></pre>
  
  <p>I trying with example </p>
  
  <pre><code>SELECT partition_uef_table(1518164237,1520583437) ;
  </code></pre>
  
  <p><strong>PROBLEM :</strong></p>
  
  <pre><code>ERROR:  invalid input syntax for integer: "1 month"
  LINE 14:   FROM generate_series( $1, $2, '1 month' ) AS d
                                           ^
  QUERY:  SELECT
              'create table user_event_firebase_'
              || TO_CHAR( d, 'YYYY_MM' )
              || ' ( check( timestamp &gt;= bigint '''
              || TO_CHAR( d, 'YYYY-MM-DD' )
              || ''' and timestamp &lt; bigint '''
              || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD' )
              || ''' ) ) inherits ( user_event_firebase );',
              'create index user_event_firebase_'
  </code></pre>
  
  <p><strong>QUESTION:</strong></p>
  
  <p>How to create range for generate_series function in ' 1 month ' , set <code>step</code> property such int or bigint suck because of day of month is diffirence ( 2nd - 28 days, 3rd - 30 days ).</p>
  
  <p>Thank you.</p>