how to insert null in postgres with php

Question

I'm trying to insert null into a postgres database with php. Say I have this:

<?php
$sql = <<<EOT
insert into
    t1
(c1, c2)
values
($param, 'test')
EOT;
?>

I tried setting $param to:

$param = null;
$param = '\N';

and in each case I got this error:

Native message: ERROR:  syntax error at or near ","

What do I need to set it to?

1. Prepared statements. 2. NULL.

Sammitch
– Sammitch

2021-05-21 23:58:49 +00:00
Commented May 21, 2021 at 23:58 — Sammitch
– Sammitch, Commented May 21, 2021 at 23:58

Your Common Sense · Accepted Answer · 2024-01-17 08:31:51Z

2

You can do it using parameters.

Either with PDO:

$query = "INSERT INTO t1 VALUES(?,?)";
$insertStatement = $pdo->prepare($query);
$insertStatement->execute([null, 'test']);

Execute PHP online

of with pgsql functions

pg_query_params($dbconn, 'INSERT INTO t1 VALUES($1,$2)', [null, 'test']);

edited Jan 17, 2024 at 8:31

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

answered May 24, 2021 at 10:20

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fran Cerezo · Accepted Answer · 2021-05-21 23:52:09Z

1

$param = 'null';

it should work.

answered May 21, 2021 at 23:52

Fran Cerezo

9483 gold badges8 silver badges20 bronze badges

Comments

alboforlizo · Accepted Answer · 2024-01-17 08:15:11Z

-1

The way you deal with null depends on type of the fields and the syntax of the query. Suppose you write the following query:

UPDATE table SET fld_char='$fld_char', fld_int=$fld_int WHERE id=0

Then you can set the variables as these:

fld_char = null;
fld_int = 'null';

You will have fld_int as null and fld_char as empty.

answered Jan 17, 2024 at 8:15

alboforlizo

3795 silver badges8 bronze badges

5 Comments

Your Common Sense Over a year ago

so you failed to answer how to insert fld_char as null. not to mention that you should NEVER have $fld_char or $fld_int in your query. Learn about prepared statements

alboforlizo Over a year ago

if I leave $fld_char or $fld_int in the query which thing is allowed though not recommended, how can I have fld_char NULL?

Your Common Sense Over a year ago

You just DON'T have them in the query. Problem solved

alboforlizo Over a year ago

I agree on prepared statements but look at the accepted answer which shows the OP requirement.

Your Common Sense Over a year ago

I did look at that bad answer. So what?

Collectives™ on Stack Overflow

how to insert null in postgres with php

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related