Thread: can insert 'null' into timestamp type field from command line but not from input file?

The table is like this:

========
maxware=# \d test;              Table "public.test"Column |            Type             | Modifiers
--------+-----------------------------+-----------a      | timestamp without time zone |b      | integer
    |
 
=========

The following insert command works fine:

maxware=# insert into test (a,b) values (null,'1');

But importing from an input data file does not seem to work as shown below:

=========
maxware=# copy tbl_spcase from '/home/bdu/test/input.data' delimiter as '|';
ERROR:  invalid input syntax for integer: "null"
CONTEXT:  COPY tbl_spcase, line 1, column col_id_spcase: "null"
==========

The input.data file just has one line.  But no matter what I did, neither
of the following input format worked.

1. null|1
2. |1
3. ''|1

How should I represent blank value for the field that's of timestamp type?

Thanks in advance for any help,

Bing

I've figured it out, that is using 'null as <null string>' with the COPY
command.

Bing

> The table is like this:
>
> ========
> maxware=# \d test;
>                Table "public.test"
>  Column |            Type             | Modifiers
> --------+-----------------------------+-----------
>  a      | timestamp without time zone |
>  b      | integer                     |
> =========
>
> The following insert command works fine:
>
> maxware=# insert into test (a,b) values (null,'1');
>
> But importing from an input data file does not seem to work as shown
> below:
>
> =========
> maxware=# copy tbl_spcase from '/home/bdu/test/input.data' delimiter as
> '|';
> ERROR:  invalid input syntax for integer: "null"
> CONTEXT:  COPY tbl_spcase, line 1, column col_id_spcase: "null"
> ==========
>
> The input.data file just has one line.  But no matter what I did, neither
> of the following input format worked.
>
> 1. null|1
> 2. |1
> 3. ''|1
>
> How should I represent blank value for the field that's of timestamp type?
>
> Thanks in advance for any help,
>
> Bing
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

On Tue, 13 Jan 2004, Bing Du wrote:

> The table is like this:
> 
> ========
> maxware=# \d test;
>                Table "public.test"
>  Column |            Type             | Modifiers
> --------+-----------------------------+-----------
>  a      | timestamp without time zone |
>  b      | integer                     |
> =========
> 
> The following insert command works fine:
> 
> maxware=# insert into test (a,b) values (null,'1');
> 
> But importing from an input data file does not seem to work as shown below:
> 
> =========
> maxware=# copy tbl_spcase from '/home/bdu/test/input.data' delimiter as '|';
> ERROR:  invalid input syntax for integer: "null"
> CONTEXT:  COPY tbl_spcase, line 1, column col_id_spcase: "null"
> ==========
> 
> The input.data file just has one line.  But no matter what I did, neither
> of the following input format worked.
> 
> 1. null|1
> 2. |1
> 3. ''|1

Here's a simple way to find out.  use pg_dump to dump the table:

psql
db=>create table test (dt timestamptz, id int);
db=>insert into test (dt, id) values (NULL,22);
db=>\q
pg_dump db -t test

<Extra data deleted>

-- Data for TOC entry 3 (OID 705319)
-- Name: test; Type: TABLE DATA; Schema: public; Owner: marl8412
--

COPY test (dt, id) FROM stdin;
\N      22
\.

Note that a null is imported / exported as \N