Replace into equivalent for postgresql and then autoincrementing an int

Question

Okay no seriously, if a PostgreSQL guru can help out I'm just getting started.

Basically what I want is a simple table like such:

CREATE TABLE schema.searches
(
  search_id serial NOT NULL,
  search_query character varying(255),
  search_count integer DEFAULT 1,
  CONSTRAINT pkey_search_id PRIMARY KEY (search_id)
)
WITH (
  OIDS=FALSE
);

I need something like REPLACE INTO for MySQL. I don't know if I have to write my own procedure or something?

Basically:

check if the query already exists
if so, just add 1 to the count
it not, add it to the db

I can do this in my php code but I'd rather all that be done in postgres C engine

What you're looking for is referred to as either an "upsert" or "merge" — OMG Ponies, Jun 16 '10 at 22:26
This is not a duplicate per se. `REPLACE INTO` is delete-then-insert, which has different implications than upsert. — Timo, Nov 28 '19 at 17:33

score 1 · Answer 1 · answered Oct 12 '21 at 13:46

You have to add a unique constraint first.

ALTER TABLE schema.searches ADD UNIQUE (search_query);

The insert/replace command looks like this.

INSERT INTO schema.searches(search_query) VALUES ('a search query')
ON CONFLICT (search_query)
DO UPDATE SET search_count = schema.searches.search_count + 1;

Replace into equivalent for postgresql and then autoincrementing an int

1 Answers1