Обсуждение: CSV files & empty strings

I am new to Postgre, having formerly used Filemaker Pro.  I need to import CSV files into Postgre which contain some empty string "" values in several date-type fields.  I am able to import the CSV files into Postgre with the COPY FROM command if the datefields are converted to text fields in Postgre.  However, I need some method of "converting" the empty string "" values into NULL values after import, so that I can change the date fields back to date-type.  Any suggestions on how this could be accomplished?


Thanks,

-Nate

On 20/10/2009 05:55, Nate Randall wrote:
> I am new to Postgre, having formerly used Filemaker Pro.  I need to
> import CSV files into Postgre which contain some empty string "" values
> in several date-type fields.  I am able to import the CSV files into
> Postgre with the COPY FROM command if the datefields are converted to
> text fields in Postgre.  However, I need some method of "converting" the
> empty string "" values into NULL values after import, so that I can
> change the date fields back to date-type.  Any suggestions on how this
> could be accomplished?

How about:

  update <table> set <column> = null where <column> = '';

?


Ray.

On 20 okt 2009, at 16.15, Raymond O'Donnell wrote:

> On 20/10/2009 05:55, Nate Randall wrote:
>> However, I need some method of "converting" the
>> empty string "" values into NULL values after import, so that I can
>> change the date fields back to date-type.  Any suggestions on how
>> this
>> could be accomplished?
>
> How about:
>
>   update <table> set <column> = null where <column> = '';


You can also do the update and the column data type change in one
fell swoop like so:

ALTER TABLE table_name ALTER COLUMN column_name
SET DATA TYPE DATE USING NULLIF(column_name, '')::DATE;




Sincerely,

Niklas Johansson