'Replace into equivalent for postgresql and then autoincrementing an int

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

sqlpostgresqlmergeupsert


Solution 1:[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;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Michas

Related Questions