Thread: Blank Numeric Column For INSERT

   I am trying to load 143K rows into a postgres-9.0.5 table from an ASCII
text file. The file consists of INSERT INTO ... statements and the VALUES
are comma delimited. One column is numeric (REAL), but ~10K rows have that
value missing, and postgres rejects the lines.

   The column does not have a NOT NULL constraint.

   The command line I use is 'psql -d <database_name> -f wq.sql'.

   Originally I had two commas in sequence since there were no values between
them. Next I tried a space between the two commas. I tried searching in the
9.0.5 manual for 'missing values', 'missing', and another term I don't
recall but found nothing.

   An example:

psql:wq.sql:8121: ERROR:  syntax error at or near ","
LINE 1: ...its) VALUES (214,'SW-6','1992-11-25','oil_grease', ,'mg/L');

   What is the approprate way to represent the missing column?

TIA,

Rich

On Wed, Nov 23, 2011 at 10:33 AM, Rich Shepard <rshepard@appl-ecosys.com> wrote:

>  Originally I had two commas in sequence since there were no values between
> them. Next I tried a space between the two commas. I tried searching in the
> 9.0.5 manual for 'missing values', 'missing', and another term I don't
> recall but found nothing.

My pg.dump files show nulls as:

\N

--
Regards,
Richard Broersma Jr.

On Wed, 23 Nov 2011, Richard Broersma wrote:

> My pg.dump files show nulls as:
> \N

Richard,

   Mine do, too. But, that's not what postgres wants to see in the .sql file.
It takes it as a newline (\n) whether quoted or not.

Thanks,

Rich

Rich Shepard <rshepard@appl-ecosys.com> writes:
>    Mine do, too. But, that's not what postgres wants to see in the .sql file.

In an insert command, you need to either write NULL or omit the column
from the column list; empty expressions aren't syntactically correct.
(Note that the latter option actually results in inserting the column's
default, not necessarily null...)

            regards, tom lane

On Wed, 23 Nov 2011, Tom Lane wrote:

> In an insert command, you need to either write NULL or omit the column
> from the column list; empty expressions aren't syntactically correct.
> (Note that the latter option actually results in inserting the column's
> default, not necessarily null...)

Tom,

   I must have written Null rather than NULL yesterday evening. That's why
it didn't work for me.

   Must be some other glitches but they scroll up too quickly to read. I'll
fix those next.

   Many thanks. Happy Thanksgiving.

Rich