Thread: Not Seeing Syntax Error

   For several INSERT INTO rows I get a syntax error when the quant column is
NULL for one specific parameter. I don't see my error. Here is an example
row:

psql:insert.sql:8: ERROR:  syntax error at or near ","
LINE 1: ...ALUES ('9609-0759','BC-1.5','1996-09-19','Arsenic',,'mg/L');
                                                               ^
   The source line is:

INSERT INTO chemistry (lab_nbr, loc_name, sample_date, param, quant,
units)VALUES ('9609-0759','BC-1.5','1996-09-19','Arsenic',,'mg/L');

and the quant column is defined as type real. There are numerous other rows
where quant IS NULL.

   What have I missed?

Rich

On Wed, 17 Aug 2011, Thom Brown wrote:

> The error message points to the problem.  No value, not even NULL, has
> been specified for 5th column.  Either put DEFAULT or NULL in there.  You
> can't put nothing.

   I was under the impression (obviously wrong) that a blank field was
accepted as a NULL.

Thanks,

Rich

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rich Shepard
Sent: Wednesday, August 17, 2011 12:27 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Not Seeing Syntax Error

On Wed, 17 Aug 2011, Thom Brown wrote:

> The error message points to the problem.  No value, not even NULL, has
> been specified for 5th column.  Either put DEFAULT or NULL in there.
> You can't put nothing.

   I was under the impression (obviously wrong) that a blank field was
accepted as a NULL.

Thanks,

Rich

----------------------------------------------

To be honest I was too and maybe I somehow implied that to you.  Anyway, I
believe if you are dealing with CSV import then you are correct but
apparently SQL is not as forgiving.  I use a third-party application to
import my CSV usually so whether that observation applies to PSQL or other
mechanisms (like pg_loader) I cannot say.

David J.

On Wed, 17 Aug 2011, David Johnston wrote:

> To be honest I was too and maybe I somehow implied that to you.  Anyway, I
> believe if you are dealing with CSV import then you are correct but
> apparently SQL is not as forgiving.  I use a third-party application to
> import my CSV usually so whether that observation applies to PSQL or other
> mechanisms (like pg_loader) I cannot say.

David,

   It may well be different between COPY and INSERT INTO. Anywho, I altered
the column and set the default to NULL. That works.

Regards,

Rich