插入表白色结果

Question

i'm trying to do an insert into a Postgres table. But i don't understand why it fails. ( I tried in mysql and it works ). This is my database:

CREATE TABLE public.prenotazione
(
    id integer NOT NULL DEFAULT nextval('prenotazione_id_seq'::regclass),
    "nominativo " character(45) COLLATE pg_catalog."default",
    "email " character(45) COLLATE pg_catalog."default",
    "oggetto " character(200) COLLATE pg_catalog."default",
    orario_inizio time(6) without time zone,
    orario_fine time(6) without time zone,
    "nominativoi " character(200) COLLATE pg_catalog."default",
    emaili character(200) COLLATE pg_catalog."default",
    "nominativoe " character(200) COLLATE pg_catalog."default",
    emaile character(200) COLLATE pg_catalog."default",
    stanza integer,
    data date
)

config.php

<?php 
$dbname = "postgres";
$host = "localhost";
$username = "postgres";

$dbh = new PDO("pgsql:dbname=$dbname;host=$host", $username, 123456789 ); 
?> 

This is my input file.

<?php
//Connecting to db here

require ('config.php');

// Richiedente
$nominativo = $_POST['nominativo'];

$email = $_POST['email'];
$oggetto = $_POST['oggetto'];
$data = $_POST['data'];
$orario_inizio = $_POST['orario_inizio'];
$orario_fine = $_POST['orario_fine'];
$stanza = 0;

// Personale Interno
$nominativoi = $_POST['nominativoi'];
$emaili = $_POST['emaili'];

// Persona Esterno
$nominativoe = $_POST['nominativoe'];
$emaile = $_POST['emaile'];

//aggiunta di un'ora ( per tornare indietro cambiare $newTimeEnd con $orario_fine
$dateTime = DateTime::createFromFormat('H:i', $orario_fine);
$dateInterval = DateInterval::createFromDateString('1 hour');

$dateTime->add($dateInterval);

$newTimeEnd = $dateTime->format('H:i');


//checkdata = query per il controllo delle prenotazioni.
$checkdata = "SELECT count(*) as prenotato
  FROM Prenotazione
 WHERE data='$data'
   AND NOT ('$newTimeEnd' < orario_inizio OR orario_fine < '$orario_inizio')";

$querycheck = $dbh->prepare($checkdata);
$querycheck->execute();
$prenotato = $querycheck->fetch()[0];
var_dump($prenotato);
if ($prenotato == 0 AND $stanza == 0 ) { 
$query1 = "INSERT INTO prenotazione (nominativo,email,data,orario_inizio,orario_fine,oggetto,nominativoi,emaili,nominativoe,emaile,stanza) VALUES ('$nominativo','$email','$data','$orario_inizio','$newTimeEnd','$oggetto','$nominativoi','$emaili','$nominativoe','$emaile',1)";
    var_dump($query1);
    $result1 = $dbh->prepare($query1);
    $result1->execute();
    $rex = 1;        
    $rex = 1;
}
else if ($prenotato == 1){
    $query1 = "INSERT INTO Prenotazione (nominativo,email,data,orario_inizio,orario_fine,oggetto,nominativoi,emaili,nominativoe,emaile,stanza) VALUES ('$nominativo','$email','$data','$orario_inizio','$orario_fine','$oggetto','$nominativoi','$emaili','$nominativoe','$emaile',2)";
    $result1 = $dbh->prepare($query1);
    $result1->execute();
    $rex = 1;
}
else
{
      $rex = 0;
}

?>

And i receive this with var_dump

NULL string(259) "INSERT INTO prenotazione (nominativo,email,data,orario_inizio,orario_fine,oggetto,nominativoi,emaili,nominativoe,emaile,stanza) VALUES ('aaa aaaa','[e-mail]','2018-10-04','09:30','12:30','aaaaa','bbbb bbbb','[e-mail]','cccc ccc','[e-mail]',1)"

In your opinion how can i do to solve this problem? Thank

Answer 1

MySQL is very relaxed with syntax, this might be a plus when you are not a database expert but can lead to "surprising" errors when it makes wrong assumptions. On the other hand PostgreSQL tends to be very strict when it comes to data definition. You have told Postgres your fields are named:

"nominativo " with quotes and blank space, not nominativo

"email " with quotes and blank space, not email

"oggetto " with quotes and blank space, not oggetto

"nominativoi " with quotes and blank space, not nominativoi

"nominativoe " with quotes and blank space, not nominativoe

So it is saying you nominativo, email, oggetto, nominativoi and nominativoe columns do not exist.

This sentence:

    INSERT INTO prenotazione (
    "nominativo ","email ",data,orario_inizio,
    orario_fine,"oggetto ","nominativoi ",emaili,
    "nominativoe ",emaile,stanza)
    VALUES ('aaa aaaa','[e-mail]','2018-10-04',
    '09:30','12:30','aaaaa','bbbb bbbb','[e-mail]','cccc 
    ccc','[e-mail]',1);

Works perfectly.

For postgres "nominativo " and nominativo are not the same, "surprisingly" they are for MySQL