Example of Postgres Code Showing Non-Existence of SQL Table

Ensure that the RATE datatype tables you previously created have primary keys, as your foreign keys will reference them. Prior to these two tables, have you already created the following: Users, Cinemas, Actor, Director, and UserComments? If not, both of these tables will not be generated.


Solution 1:

It appears that you desire the following MSDT code:

insert ... select

.

insert into t1 (a1, a2, a3, a4)
select 123, '20e16411-b8f7', 4, u1
from t2 
where 
    u2 = '[email protected]'
    and exists (
        select a3 
        from t1 
        where 
            t1.a1 = 123 
            and t1.a2 = '20e16411-b8f7' 
            and t1.a3 = 4 
            and t1.a4 = t2.u1
    )


Solution 2:


Allow the database to verify distinctiveness by setting up a unique constraint on the relevant columns.

create unique index unq_t1_a1_a2_a3_a4 on t1(a1, a2, a3, a4);

Then use

on conflict

:

insert into T1 (a1, a2, a3, a4)
    select 123, '20e16411-b8f7', 4, t2.u1
    from T2
     where t2.u2 = '[email protected]'
    on conflict (a1, a2, a3, a4) do nothing;


Solution 3:


In this query, using WHERE after VALUES will result in an error. Instead, consider using SELECT in place of VALUES.

INSERT INTO T1 (a1, a2, a3, a4)
SELECT 123, '20e16411-b8f7', 4, (SELECT u1 from T2 where u2 = '[email protected]')
WHERE NOT EXISTS (SELECT a3 
              FROM TI 
              WHERE a1 =123 
                AND a2 = '20e16411-b8f7' 
                AND a3 = 4 
                AND a4 = (SELECT u1 from T2 where u2 = '[email protected]'))

Frequently Asked Questions

Posted in Sql