Postgres problem with insert if not exist-CodePudding

I am trying to insert if not exist from variables but I am getting the error message:

Syntaxerror at "where"

I can´t use on conflict because I don´t have a primary key which I can use as it always depends on Sensorid and channelid

foreach($p in $pingchannel){
$sensorid=$p.sensorid
$channelid=$p.Id
$channelname=$p.Name
$Unit=$p.Unit 
$lastvalue=$p.lastvalue
$PercentMode=$p.PercentMode
$PercentValue=$p.PercentValue
$Factor=$p.Factor


$QueryTypes='
Insert Into "channels" (
sensorid,
"channelid",
"channelname",
"unit",
"lastvalue",
"percentmode",
"percentvalue",
"factor"
)


Values (
' $sensorid ',
' $channelid ',
''' $channelname ''',
''' $Unit ''',
''' $lastvalue ''',
''' $PercentMode ''',
''' $PercentValue ''',
''' $Factor '''
)

where not exists (select sensorid,channelid from channels where sensorid=$sensorid and channelid=$channelid); '

 $DBCmd = $connection.CreateCommand();
 $DBCmd.CommandText   =$QueryTypes
 $DBCmd.ExecuteNonQuery() | Out-Null
 $DBCmd.ExecuteReader();
 }

any Idea?

CodePudding user response：

You cannot use WHERE in this way on PostgreSQL. The only way WHERE fits on an INSERT is when you insert values got from a select like this:

insert into LeadCustomer (Firstname, Surname, BillingAddress, email)
select 
    'John', 'Smith', 
    '6 Brewery close, Buxton, Norfolk', '[email protected]'
where not exists (
    select 1 from leadcustomer where firstname = 'John' and surname = 'Smith'
);

So you can adapt your query to the example above, using the input values on SELECT statement.

But, as mentioned in the comments above, if you have access to change de table, you should create a unique id between sensorid and channelid. This would solve the problem in the proper way and avoid this whole thing on insert.

CodePudding user response：

I changed my query now to put a constraint key in and it looks now like this

$QueryTypes='
Insert Into "channels" (
"sensorid",
"channelid",
"channelname",
"unit",
"lastvalue",
"percentmode",
"percentvalue",
"factor"
)


Values (
' $sensorid ',
' $channelid ',
''' $channelname ''',
''' $unit ''',
''' $lastvalue ''',
''' $percentmode ''',
''' $percentvalue ''',
''' $factor '''
)
on conflict (uniqueid) do UPDATE; '

but now a get the error "syntaxerror near ;

any idea?