Conditional INSERT or SELECT like in a CASE statement

Question

I want to do an insert using a CASE statement in PostgreSQL.

I am trying to do an insert if the count of my result set is equal to zero. If it isn't equal to zero, then I just want to do a select of the max time on that table.

select
    case
        when (select count(*) from x where TableName = 'p' ) > 0
              then
             (select    MaxDataDate from    x where TableName = 'p' ) 
        when (select count(*) from s where TableName = 'p') = 0
              then
             (insert into x values('p','1900-01-01'))
end

what database engine are you using,please put in tag, that would be helpful — xbb, Apr 28 '14 at 15:13
You cannot write a query that selects or inserts whether the test validates or not. You'll have to test your conditions, and then execute the relevant query. — xlecoustillier, Apr 28 '14 at 15:15
@user3581913 . . . A single query cannot both return data and insert data at the same time. You need to decide what you want the query to do. — Gordon Linoff, Apr 28 '14 at 15:20
There you should find the answer you need : http://stackoverflow.com/questions/11299037/postgresql-if-statement — xlecoustillier, Apr 28 '14 at 15:43
We would need the table definitions to answer that properly. What you get with \d x and \d in psql. Or is `s` just a typo? And don't you want anything returned form the `INSERT` statement? Also, your version of Postgres? — Erwin Brandstetter, Apr 28 '14 at 19:47

score 0 · Answer 1 · edited May 23 '17 at 12:11

Could be translated to pure SQL with a data-modyfying CTE like this:

WITH sel AS (SELECT maxdatadate FROM x where tablename = 'p')
,    ins AS (
    INSERT INTO x (?column_name1?, ?column_name2?)
    SELECT 'p'::?type1?, '1900-01-01'::?type2?
    WHERE  NOT EXISTS (SELECT 1 FROM ins)
    AND    EXISTS (SELECT 1 FROM s WHERE tablename = 'p')
    )
SELECT * FROM ins;

Fill in missing information. ? marks the spots.

Assuming x.tablename is unique. Otherwise, your 2nd SELECT statement would be nonsense.

The alternative would be a procedural solution in a plpgsql function or a DO statement like @X.L.Ant already hinted in his comment.

Conditional INSERT or SELECT like in a CASE statement

1 Answers1