deleting data in tables

Answers

• Options

  mosheg Vertica Employee Administrator

  May 2020 edited May 2020

  Consider the following example:

                  CREATE TABLE public.my_table
              (
                  order_no int,
                  order_date timestamp NOT NULL,
                  shipper varchar(20),
                  ship_date date
              )
              PARTITION BY DATE(order_date);
              CREATE TABLE
  
          \set DEMO_ROWS 10
  
          INSERT INTO public.my_table
          WITH myrows AS (SELECT
          row_number() over()    AS order_no,
          NOW() - RANDOMINT(30) AS order_date
          FROM ( SELECT 1 FROM ( SELECT NOW() as se union all
          SELECT NOW() + :DEMO_ROWS - 1 AS se) a timeseries ts AS '1 day' OVER (ORDER BY se)) b)
          SELECT order_no, order_date, 'something', order_date AS ship_date
          FROM myrows
          ORDER BY order_no;
  
               OUTPUT
              --------
                   10
              (1 row)
  
              COMMIT;
              COMMIT
  
  SELECT date(order_date),
         CASE WHEN DATE(order_date) <= DATE(now() -7) THEN 'Will be droped' ELSE 'Will keep' END as Status
  FROM public.my_table ORDER BY 1;
      date    |     Status
  ------------+----------------
   2020-04-23 | Will be droped
   2020-04-24 | Will be droped
   2020-04-28 | Will be droped
   2020-04-30 | Will be droped
   2020-05-03 | Will be droped
   2020-05-10 | Will be droped
   2020-05-10 | Will be droped
   2020-05-12 | Will be droped
   2020-05-14 | Will keep
   2020-05-20 | Will keep
  (10 rows)
  
  SELECT DROP_PARTITIONS ('my_table', DATE(NOW()-365), DATE(now() -7));
    DROP_PARTITIONS
  -------------------
   Partition dropped
  (1 row)
  
  SELECT date(order_date),
         CASE WHEN DATE(order_date) <= DATE(now() -7) THEN 'Will be droped' ELSE 'Will keep' END as Status
  FROM public.my_table ORDER BY 1;
      date    |  Status
  ------------+-----------
   2020-05-14 | Will keep
   2020-05-20 | Will keep
  (2 rows)
  
  SELECT COUNT (partition_key) NumPartitions, ros_id, node_name, SUM(ROS_SIZE_BYTES) as ROS_SIZE_BYTES, SUM(ROS_ROW_COUNT) as ROS_ROW_COUNT
  FROM PARTITIONS
  WHERE projection_name ilike 'my_table%'
  GROUP BY ros_id,node_name
  ORDER BY ROS_SIZE_BYTES
  LIMIT 20;
   NumPartitions |      ros_id       |     node_name      | ROS_SIZE_BYTES | ROS_ROW_COUNT
  ---------------+-------------------+--------------------+----------------+---------------
               1 | 54043195528615460 | v_eon6vdb_node0003 |             99 |             1
               1 | 54043195528615464 | v_eon6vdb_node0003 |             99 |             1
  (2 rows)
  

  0
• Options

  maks Community Edition User ✭

  May 2020

  How can I divide the table by days into partitions?

  0
• Options

  marcothesane - Select Field - Administrator

  May 2020

  As ship_date is a DATE type - meaning one different value per possible day -

    ... PARTITION BY ship_date
   ;
  

  will do.

  0