Replace into then autoincrement an int [duplicate]

Question

I have a table:

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 from MySQL. I don't know if I have to write my own procedure or something. Basically:

Check if the row already exists.
If so, just add 1 to the count.
If not, insert it into the database.

I can do this in PHP but rather have that be done in PostgreSQL's C engine.

What you're looking for is referred to as either an "upsert" or "merge" — OMG Ponies
– OMG Ponies, Commented Jun 16, 2010 at 22:26

Michas · Accepted Answer · 2021-10-12 13:46:18Z

1

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;

answered Oct 12, 2021 at 13:46

Michas

9,5966 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Replace into then autoincrement an int [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related