pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcan insert 'null' into timestamp type field from command line but not from input file?
3+ messages / 2 participants
[nested] [flat]
* can insert 'null' into timestamp type field from command line but not from input file?
@ 2004-01-13 17:28 Bing Du <bdu@iastate.edu>
2004-01-13 17:31 ` Re: can insert 'null' into timestamp type field from command scott.marlowe <scott.marlowe@ihs.com>
2004-01-13 17:45 ` Re: can insert 'null' into timestamp type field from command Bing Du <bdu@iastate.edu>
0 siblings, 2 replies; 3+ messages in thread
From: Bing Du @ 2004-01-13 17:28 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: can insert 'null' into timestamp type field from command
2004-01-13 17:28 can insert 'null' into timestamp type field from command line but not from input file? Bing Du <bdu@iastate.edu>
@ 2004-01-13 17:31 ` scott.marlowe <scott.marlowe@ihs.com>
1 sibling, 0 replies; 3+ messages in thread
From: scott.marlowe @ 2004-01-13 17:31 UTC (permalink / raw)
To: Bing Du <bdu@iastate.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: can insert 'null' into timestamp type field from command
2004-01-13 17:28 can insert 'null' into timestamp type field from command line but not from input file? Bing Du <bdu@iastate.edu>
@ 2004-01-13 17:45 ` Bing Du <bdu@iastate.edu>
1 sibling, 0 replies; 3+ messages in thread
From: Bing Du @ 2004-01-13 17:45 UTC (permalink / raw)
To: pgsql-sql
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)
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-01-13 17:45 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-13 17:28 can insert 'null' into timestamp type field from command line but not from input file? Bing Du <bdu@iastate.edu>
2004-01-13 17:31 ` Re: can insert 'null' into timestamp type field from command scott.marlowe <scott.marlowe@ihs.com>
2004-01-13 17:45 ` Re: can insert 'null' into timestamp type field from command Bing Du <bdu@iastate.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox