CODEWITHSUNDEEP
postgresql
nicolinho66
nicolinho66

Reputation: 107

How can I use a WHERE BETWEEN clause in an INSERT query?

I have the following table.

CREATE TABLE public.ad
(
  id integer NOT NULL DEFAULT nextval('ad_id_seq'::regclass),
  uuid uuid NOT NULL DEFAULT uuid_generate_v4(),
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  cmdb_id integer,
  platform character varying(100),
  bidfloor numeric(15,6),
  views integer NOT NULL DEFAULT 1,
  year integer,
  month integer,
  day integer,
  CONSTRAINT ad_pkey PRIMARY KEY (id),
  CONSTRAINT ad_cmdb_id_foreign FOREIGN KEY (cmdb_id)
      REFERENCES public.cmdb (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT ad_id_unique UNIQUE (uuid)
)
WITH (
  OIDS=FALSE
);

Without going in too much detail, this table logs all the requests and impressions of advertisements on electronic screens throughout the country. This table is also being used to generate reports and consists of +- 50 million records.

Currently, the reports are filtered on the created_at timestamp. You can imagine that with +- 50 million records the query will get slow, even with an index on the created_at column. The reports are generated by selecting between which dates you want to request the data on the UI of the system.

The year, month and day columns are new columns that I just added to make the reporting more efficient. Instead of indexing on the date, I want the system to index on a year, month and day, all separate values.

The newly added columns are still empty. I want to run a query that inserts a value where the created_at column is between two dates. For example:

INSERT INTO ad (year) VALUES (2016) WHERE created_at BETWEEN '2016-01-01 00:00:00' AND '2016-12-31 23:59:59';

This doesn't work of course. I cannot seem to find anything on the internet where an INSERT statement makes use of a WHERE BETWEEN clause. I also tried using subqueries and the WITH clausule to generate a series of years between 2012 and 2020 using generate_series. It all didn't work out.

Upvotes: 0

Views: 990

Answers (1)

lubilis
lubilis

Reputation: 4170

You don't want to insert new rows, you should update your table.

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE column_name BETWEEN value1 AND value2;

Otherwise you'll have 100 milions rows ;)

Upvotes: 4

Related Questions