Postgres DB Column Type Conversion

Question

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

Vao Tsun · Accepted Answer · 2017-11-30 09:27:46Z

1

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

edited Nov 30, 2017 at 9:27

answered Nov 30, 2017 at 9:18

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gitastic Over a year ago

this helped me a lot! Thanks! it turns out there was a bad record (bah, can't believe I didn't notice it before). I removed the bad record, and boom, there it goes! Thanks!

Collectives™ on Stack Overflow

Postgres DB Column Type Conversion

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related