Обсуждение: Extended ability to alter column type when empty
Hi,
I am a real newbee and I hope this is the right place to post a feature request.
I am receiving data from a csv file where one column has a strange data format. It would be nice if I could use Copy From with to_timestamp to transform the date. As far as I know this is not possible to do in one step (unlike MySQL I believe). I, therefore, have to first read large amounts of CSV data into one table where the data is a char(15) column. Then create another table using:
CREATE TABLE T (like tempT);
ALTER TABLE T ALTER COLUMN thedate TYPE TIMESTAMP;
However, then I run into:
ERROR: column "thedate" cannot be cast to type "pg_catalog.timestamp"
This error comes even though the table is empty. Could it be an idea to allow this for empty tables? Am I missing something obvious in my unreasonably complicated approach?
Regards,
David
I am a real newbee and I hope this is the right place to post a feature request.
I am receiving data from a csv file where one column has a strange data format. It would be nice if I could use Copy From with to_timestamp to transform the date. As far as I know this is not possible to do in one step (unlike MySQL I believe). I, therefore, have to first read large amounts of CSV data into one table where the data is a char(15) column. Then create another table using:
CREATE TABLE T (like tempT);
ALTER TABLE T ALTER COLUMN thedate TYPE TIMESTAMP;
However, then I run into:
ERROR: column "thedate" cannot be cast to type "pg_catalog.timestamp"
This error comes even though the table is empty. Could it be an idea to allow this for empty tables? Am I missing something obvious in my unreasonably complicated approach?
Regards,
David
2009/2/17 David Andersen <mrdavidandersen@gmail.com>: > Hi, > > I am a real newbee and I hope this is the right place to post a feature > request. > > I am receiving data from a csv file where one column has a strange data > format. It would be nice if I could use Copy From with to_timestamp to > transform the date. As far as I know this is not possible to do in one step > (unlike MySQL I believe). I, therefore, have to first read large amounts of > CSV data into one table where the data is a char(15) column. Then create > another table using: > CREATE TABLE T (like tempT); > > ALTER TABLE T ALTER COLUMN thedate TYPE TIMESTAMP; > > However, then I run into: > ERROR: column "thedate" cannot be cast to type "pg_catalog.timestamp" > > This error comes even though the table is empty. Could it be an idea to > allow this for empty tables? Am I missing something obvious in my > unreasonably complicated approach? > Try: ALTER TABLE T ALTER COLUMN thedate TYPE TIMESTAMP USING CAST (thedate AS timestamp); Osvaldo
Hi Osvaldo,
Neat! Thanks a lot for your help!
Regards,
David
Neat! Thanks a lot for your help!
Regards,
David
On Thu, Feb 19, 2009 at 2:56 PM, Osvaldo Kussama <osvaldo.kussama@gmail.com> wrote:
2009/2/17 David Andersen <mrdavidandersen@gmail.com>:Try:> Hi,
>
> I am a real newbee and I hope this is the right place to post a feature
> request.
>
> I am receiving data from a csv file where one column has a strange data
> format. It would be nice if I could use Copy From with to_timestamp to
> transform the date. As far as I know this is not possible to do in one step
> (unlike MySQL I believe). I, therefore, have to first read large amounts of
> CSV data into one table where the data is a char(15) column. Then create
> another table using:
> CREATE TABLE T (like tempT);
>
> ALTER TABLE T ALTER COLUMN thedate TYPE TIMESTAMP;
>
> However, then I run into:
> ERROR: column "thedate" cannot be cast to type "pg_catalog.timestamp"
>
> This error comes even though the table is empty. Could it be an idea to
> allow this for empty tables? Am I missing something obvious in my
> unreasonably complicated approach?
>
ALTER TABLE T ALTER COLUMN thedate TYPE TIMESTAMP USING CAST (thedate
AS timestamp);
Osvaldo