CODEWITHSUNDEEP
ruby-on-railsrubydatabasepostgresqlmigration
gitastic
gitastic

Reputation: 526

Postgres DB Column Type Conversion

Trying to convert a string column to date. Column currently holds values, such as '20170130', and '20161130'.

When I try:

change_column :tickets, :string_date, :date

This gives me an error:

PG::DatatypeMismatch: ERROR:  column "string_date" cannot be cast automatically to type date. 
HINT:  Specify a USING expression to perform the conversion.

So, I try: 

change_column :tickets, :string_date, 'date USING CAST(string_date AS date)'

Still no luck, with the following error:

PG::InvalidDatetimeFormat: ERROR: invalid input syntax for type date

Anyone know what's going on?? Thought converting from string to date would be easier...

thanks in advance

Upvotes: 0

Views: 112

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51446

strange - I would expect to see the value, where you fail to cast date, eg:

t=# create table a2(string_date text);
CREATE TABLE
t=# insert into a2 values ('20170130'),('20161130');
INSERT 0 2
t=# insert into a2 select 'bad value';
INSERT 0 1
t=# alter table a2 alter COLUMN string_date type date using string_date::date;
ERROR:  invalid input syntax for type date: "bad value"

maybe its an empty string?..

anyway - find bad value and fix it, then try to convert again:

t=# select string_date from a2 where string_date !~ '\d{8}';
 string_date
-------------
 bad value
(1 row)
t=# begin;
BEGIN
t=# delete from a2 where string_date !~ '\d{8}';
DELETE 1
t=# alter table a2 alter COLUMN string_date type date using string_date::date;
ALTER TABLE
t=# end;
COMMIT

Upvotes: 1

Related Questions