Обсуждение: How do I COPY empty Datetimes?
To whoever is reading this mail for the second time, excuse me for the cross-postings... I've already asked in 'general', since I've not gotten any response I'm begging you guys in here to give me some advice... ------ Hi all. I have a problem here. I have to automatize the periodical COPY of a large set of ASCII data in a postgres database. The parser digests everything I put in there, with an exception: if there is a DateTime field that is empty, the copy of the field fails for a "Invalid datetime format". The field isn't a NOT NULL one, so I have to accept also empty values... I've tried with the sequence '', "", or simply /t/t (tab is the field separator in those ASCII data), but the output has always been the same. I would be immensely grateful to any 1 that could shed some light... private responses please, I've had to unsubscribe... ya know, the phone bill!! TIA ----------- Fabrizio Ermini
f.ermini@telemaco.it writes: > Hi all. I have a problem here. I have to automatize the periodical COPY > of a large set of ASCII data in a postgres database. The parser digests > everything I put in there, with an exception: if there is a DateTime > field that is empty, the copy of the field fails for a "Invalid datetime > format". The field isn't a NOT NULL one, so I have to accept also empty > values... I've tried with the sequence '', "", or simply /t/t (tab is > the field separator in those ASCII data), but the output has always been > the same. There's no such thing as an "empty" value of datetime. What you can put in is a NULL, which is not a datetime at all, but an indicator that the field has no data in this particular table row. NULLs work for any data type. The syntax for a NULL field in COPY is "\N". (Simply leaving the field blank, as you were trying to do, isn't good enough since there would be no way to distinguish an empty text field from a NULL text field.) For example, here are a couple of rows of COPY data from a database of my own. There are four datetime columns in the table, and these rows have different subsets of the four non-null (as well as nulls in several other columns): 37 16 16 Fri Nov 13 17:00:07 1998 EST EDF 29 S 7343 \N s 7100 100 f I 188 MonNov 16 07:46:38 1998 EST \N \N \N \N 63 8 8 Thu Nov 12 16:00:10 1998 EST EDF 49 s 11353 \N S 12408 100 f C 189 \N \N Mon Nov 16 07:46:45 1998 EST \N \N Apologies if your mailer mangles the data --- there are supposed to be two long lines with tabs in them. regards, tom lane
On Sat, 21 Nov 1998, Tom Lane wrote: > f.ermini@telemaco.it writes: > > Hi all. I have a problem here. I have to automatize the periodical COPY > > of a large set of ASCII data in a postgres database. The parser digests > > everything I put in there, with an exception: if there is a DateTime > > field that is empty, the copy of the field fails for a "Invalid datetime > > format". The field isn't a NOT NULL one, so I have to accept also empty > > values... I've tried with the sequence '', "", or simply /t/t (tab is > > the field separator in those ASCII data), but the output has always been > > the same. > > There's no such thing as an "empty" value of datetime. What you can > put in is a NULL, which is not a datetime at all, but an indicator that > the field has no data in this particular table row. NULLs work for any > data type. The syntax for a NULL field in COPY is "\N". (Simply > leaving the field blank, as you were trying to do, isn't good enough > since there would be no way to distinguish an empty text field from a > NULL text field.) > > For example, here are a couple of rows of COPY data from a database of > my own. There are four datetime columns in the table, and these rows > have different subsets of the four non-null (as well as nulls in several > other columns): > > 37 16 16 Fri Nov 13 17:00:07 1998 EST EDF 29 S 7343 \N s 7100 100 f I 188 Mon Nov 16 07:46:38 1998 EST \N \N \N \N > 63 8 8 Thu Nov 12 16:00:10 1998 EST EDF 49 s 11353 \N S 12408 100 f C 189 \N \N Mon Nov 16 07:46:45 1998 EST \N \N > > Apologies if your mailer mangles the data --- there are supposed to be > two long lines with tabs in them. > I think, you could also consider using 'infinity' or '-infinity' values for empty DATETIME fields ( choice is depending on what kind of dates you plan to store ) Aleskey