Postgresql – How to insert if key not exist with PostgreSQL

insertpostgresql

I have a table:

CREATE TABLE mytable (id SERIAL, name VARCHAR(10) PRIMARY KEY)

Now I want to add names to this table, but only if they not exist in the table already, and in both cases return the id. How can I do this with PostgreSQL?

I have seen a few scripts for this, but is there no single SQL-statement to do it?

E.g. I can INSERT and return id with:

INSERT INTO mytable (name) VALUES ('Jonas') RETURNING id

it works the first time and returns id. But it fails if Jonas already exist in the table, but I want to return the id even if the Insert fails. Is this possible to do with PostgreSQL?

Best Answer

Upsert statements used to be planned for 9.1 but have been postponed to 9.2, so until then, your only choice is to test if the value already exists before inserting.

Alternatively, if your intention is merely to have a unique identifier, you could simply use a sequence + nextval.

If you want to create a function to do that, this should get you started:

CREATE OR REPLACE FUNCTION upsert_tableName(arg1 type, arg2 type) RETURNS VOID AS $$ 
DECLARE 
BEGIN 
    UPDATE tableName SET col1 = value WHERE colX = arg1 and colY = arg2; 
    IF NOT FOUND THEN 
    INSERT INTO tableName values (value, arg1, arg2); 
    END IF; 
END; 
$$ LANGUAGE 'plpgsql';

Related Solutions

PostgreSQL – How to Insert Values into a Table from a Select Query

Column order does matter so if (and only if) the column orders match you can for example:

insert into items_ver
select * from items where item_id=2;

Or if they don't match you could for example:

insert into items_ver(item_id, item_group, name)
select * from items where item_id=2;

but relying on column order is a bug waiting to happen (it can change, as can the number of columns) - it also makes your SQL harder to read

There is no good 'shortcut' - you should explicitly list columns for both the table you are inserting into and the query you are using for the source data, eg:

insert into items_ver (item_id, name, item_group)
select item_id, name, item_group from items where item_id=2;

dbfiddle here

MySQL Insert into table, where name = id, if not exist, then insert name and use that ID

Assuming you have some tables for Persons, Animals:

CREATE TABLE Person
  ( PersonID INT UNSIGNED NOT NULL AUTO_INCREMENT
  , PersonName VARCHAR(255) NOT NULL
  , CONSTRAINT Person_PK
      PRIMARY KEY (PersonID)
  , CONSTRAINT PersonName_UQ 
      UNIQUE (PersonName)
  ) ;

CREATE TABLE Animal
  ( AnimalID INT UNSIGNED NOT NULL AUTO_INCREMENT
  , AnimalName VARCHAR(255) NOT NULL
  , CONSTRAINT Animal_PK
      PRIMARY KEY (AnimalID)
  , CONSTRAINT AnimalName_UQ 
      UNIQUE (AnimalName)
  ) ;

and results:

CREATE TABLE Result
  ( RaceID INT UNSIGNED NOT NULL
  , Position INT UNSIGNED NOT NULL
  , PersonID INT UNSIGNED NOT NULL 
  , AnimalID INT UNSIGNED NOT NULL
  , Errors INT UNSIGNED NOT NULL DEFAULT 0
  , CompletionTime Time NULL DEFAULT NULL

  , CONSTRAINT Result_PK
      PRIMARY KEY (RaceID, Position)

  , CONSTRAINT Race_Person_UQ           -- assuming a Person cannot enter
      UNIQUE (RaceID, PersonID)                 -- a race twice

  , CONSTRAINT Race_Animal_UQ           -- assuming an Animal cannot enter
      UNIQUE (RaceID, AnimalID)                 -- a race twice

  , INDEX PersonID_IX (PersonID)                -- indexes for the Foreign Key
  , INDEX AnimalID_IX (AnimalID)                -- constraints:

  , CONSTRAINT Person_Result_FK     
      FOREIGN KEY (PersonID)
      REFERENCES Person (PersonID)
  , CONSTRAINT Animal_Result_FK     
      FOREIGN KEY (AnimalID)
      REFERENCES Animal (AnimalID)
  ) ;

I suggest you first bulk load the data (possibly with LOAD DATA from .txt or .csv files) in a table in MySQL (supplying race IDS. If you can't supply raceIDs but you have race names, the tables should be adjusted accordingly). You should have a Race table as well, this is just a sample procedure:

CREATE TABLE BulkData
  ( RaceID INT UNSIGNED NOT NULL
  , Position INT UNSIGNED NOT NULL
  , PersonName VARCHAR(255) NOT NULL
  , AnimalName VARCHAR(255) NOT NULL
  , Errors INT UNSIGNED NOT NULL DEFAULT 0          -- adjust datatypes according
  , CompletionTime Time NULL DEFAULT NULL           -- to your data
  ) ;

LOAD DATA INFILE '/results.txt' 
    INTO TABLE BulkData
    FIELDS TERMINATED BY ',' 
           ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n' ;

Then you can manipulate them and insert them into the 2-3 tables. For Person:

INSERT INTO Person
    (PersonName)
SELECT DISTINCT
    b.PersonName
FROM
    BulkData AS b
WHERE NOT EXISTS
    ( SELECT 1
      FROM Person AS p
      WHERE p.PersonName = b.PersonName
    ) ;

Similar for Animal:

INSERT INTO Animal
    (AnimalName)
SELECT DISTINCT
    b.AnimalName
FROM
    BulkData AS b
WHERE NOT EXISTS
    ( SELECT 1
      FROM Animal AS a
      WHERE a.AnimalName = b.AnimalName
    ) ;

And then in Result:

INSERT INTO Result
    (RaceID, Position, PersonID, AnimalID, Errors, CompletionTime)
SELECT 
    b.RaceID, b.Position, p.PersonID, a.AnimalID, b.Errors, b.CompletionTime
FROM
    BulkData AS b
  JOIN
    Person AS p  ON p.PersonName = b.PersonName
  JOIN 
    Animal AS a  ON a.AnimalName = b.AnimalName
WHERE NOT EXISTS
    ( SELECT 1
      FROM Result AS r
      WHERE r.RaceID = b.RaceID
        AND r.PositionID = b.PositionID
    ) ;

If the importing results are satisfying, then you can empty the BulkData table and repeat the procedure with more files. The NOT EXISTS conditions will take care and not allow duplicates even if you try to load same data twice.

Best Answer

Related Solutions

PostgreSQL – How to Insert Values into a Table from a Select Query

MySQL Insert into table, where name = id, if not exist, then insert name and use that ID

Related Question