Make insert in table if there is no POSTGRESQL record

Question

Make insert in table if there is no POSTGRESQL record

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

I am trying to make an insert in a table but only insert IF the record I want to enter does not exist, in the case that there is already doing nothing.

I've tried it this way, I've just registered the names but the insert is as is:

INSERT INTO mitabla (campo1, campo2, campo3, campo4, campo5, campo6) 
VALUES (
(SELECT id FROM secciones WHERE nombre = 'MiSeccion'),
                'valor2',
                'valor3',
                'valor4',
                'valor5',
                'valor6')
WHERE NOT EXISTS (SELECT campo2 FROM mitabla WHERE UPPER(campo2) = 'CAMPO2');

And I get this error but I do not understand what I have to do:

ERROR: syntax error at or near "WHERE" LINE 8: WHERE NOT EXISTS (SELECT field2 FROM mytable WHER ... ^ SQL state: 42601 Character: 297

But it does not work ... Another option to do it?

I'm using doctrine migrations de Symfony

sql postgresql symfony doctrine

asked by Pavlo B. 14.11.2018 в 17:08

source

2 answers

1

Ask a question and ask if you have a id same as the one you are inserting, and count the amount of row , if it is greater than zero obvious that exists in the database, I put a example of how I did it

$este= $_POST['este'];
$query2 = "SELECT id FROM tipo_c WHERE nombre = '$este'";
$stmt = $db->prepare( $query2 );
$stmt->execute();
$can = $stmt->rowCount();

if($can == 0){
   $obj_tipo_c->tipo_c= $_POST['este'];

if($obj_tipo_c->create())} // si es cero lo inserto
else    
   echo "ya existe"

answered by 14.11.2018 в 17:14

Where to place the Data Annotations? what is the best way to upload an excel file [closed]

score 1 · Accepted Answer

insert the result of a select from your same table only if field2 is different

for example:

INSERT INTO mitabla (campo1, campo2, campo3, campo4, campo5, campo6) 
SELECT
(SELECT id FROM secciones WHERE nombre = 'MiSeccion'),
'valor2',
'valor3',
'valor4',
'valor5',
'valor6'
FROM mitabla as mt
WHERE UPPER(mt.campo2) != 'CAMPO2';

Another option would be the following

INSERT INTO mitabla (campo1, campo2, campo3, campo4, campo5, campo6)
SELECT
    (SELECT id FROM secciones WHERE nombre = 'MiSeccion'),
    'valor2',
    'valor3',
    'valor4',
    'valor5',
    'valor6'
WHERE NOT EXISTS (SELECT campo2 FROM mitabla WHERE UPPER(campo2) = 'CAMPO2');