4

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.

Improve this question
1
  • 1
    What you're looking for is referred to as either an "upsert" or "merge" Commented Jun 16, 2010 at 22:26

1 Answer 1

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.