1

Lets say I have a table new_data like this:

ndx    | someval | somemachine
serial | integer | integer
----------------------------

And let's say I have a reference table new_data_someval_ref:

someval | someval_str
integer | varchar
---------------------

(Where, obviously, new_data_someval_ref.someval is foreign key on new_data.someval)

And I want do inserts like so:

INSERT INTO new_data
  (someval, machine)
VALUES (
  (SELECT someval
   FROM new_data_someval_ref
   WHERE someval_str = ?),
  ?)

BUT, if there is no match in the reference table, I would like to add a row containing the new string and an incremented someval, then do the insert above.

So my question is there clean, possibly even constraint-based way to do this?

(I use both Postgres and SQL Server regularly, so an answer addressing both would be ideal, but not necessary.)

4
  • 3
    Just add the referenced value in the same transaction? Also, your example is not very clear. We don't know what sort of type someval_str is, but it is certainly not an integer. Probably some actual CREATE TABLE statements would help. Oct 28, 2014 at 15:45
  • It's labeled as a varchar in the second table block
    – anthropomo
    Oct 28, 2014 at 16:58
  • Yeah, that's true. What this shows is that a CREATE TABLE would be much more understandable... Oct 28, 2014 at 17:11
  • 3
    "Obviously"? It seems to be the other way round: new_data.someval is a foreign key on new_data_someval_ref.someval. To avoid such ambiguities, it is always better to provide complete CREATE TABLE scripts. Oct 29, 2014 at 2:08

2 Answers 2

2

Assuming someval is actually an auto-generated column like a serial.

In Postgres this data-modifying CTE (writeable CTE) would do the job:

WITH sel AS (
   SELECT r.someval, i.someval_str, i.machine
   FROM  (SELECT ?, ?) AS i(someval_str, machine)
   LEFT   JOIN new_data_someval_ref r USING (someval_str)
   )
, ins AS ( 
   INSERT INTO new_data_someval_ref (someval_str)
   SELECT DISTINCT someval_str FROM sel WHERE someval IS NULL
   RETURNING someval, someval_str
   )
INSERT INTO new_data (someval, machine)
SELECT COALESCE(sel.someval, ins.someval), sel.machine
FROM   sel
LEFT   JOIN ins USING (someval_str);

Everything standard SQL, not sure whether it's all implemented in SQL Server identically.

For a query with multiple input rows and ample explanation:

There is a tiny chance for a race condition under heavy concurrent load. (Doing everything in a single statement minimizes the time window.) If that's the case:

1

I think you will have to break it into 2 blocks (stored procedure would be easier)

DECLARE @SomeVal INT 

SET @SomeVal = Isnull((SELECT someval 
                       FROM   new_data_someval_ref 
                       WHERE  someval_str = 'WhatEverTheValueIs'), 0) 

IF @SomeVal = 0 
  BEGIN 
      INSERT INTO new_data_someval_ref 
                  (someval_str) 
      VALUES      ('WhatEverTheValueIs') 

      SET @SomeVal = Scope_identity() 
  END 

INSERT INTO new_data
                 (someval, machine) 
VALUES           (@SomeVal, ?) 
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.