How to convert varchar to timestamp in postgreSQL?

I have data as following, But the column type is Varchar:

2019-09-28T23:59:59.52Z

I assume 52 here is milli seconds, If so..

I would like to convert it as following and change the column type to timestamp:

2019-09-28 23:59:59.52

Can someone let me know how I can convert in postgreSQL?

EDIT: I can see data in table as (since the column type is varchar):

2019-09-28T23:59:59.52Z

Instead, I want data in the table to be shown as:

2019-09-28 23:59:59        ( and may be .52, if possible)

I need to change the column type to timestamp as well, I guess, Please help with that too.

Answer:

Tim has provided a solution, You can follow that.

But, In my case, It is prod env, So, I have just changed the type using:

ALTER TABLE my_table ALTER COLUMN my_column TYPE TIMESTAMP USING my_column::timestamp without time zone;

Thanks

Solution

Your timestamp string literal is already in a format which can be directly cast in Postgres:

SELECT '2019-09-28T23:59:59.52Z'::timestamp;  -- 2019-09-28 23:59:59.52

As a test, let's add one day to make sure it's working:

SELECT '2019-09-28T23:59:59.52Z'::timestamp + interval '1 day';
-- 2019-09-29 23:59:59.52

If you want to actually add a new timestamp column using string data from another column, then try:

ALTER TABLE yourTable ADD COLUMN new_ts TIMESTAMP;

UPDATE yourTable SET new_ts = old_ts::timestamp;

ALTER TABLE yourTable DROP COLUMN old_ts;
ALTER TABLE yourTable RENAME COLUMN new_ts TO old_ts;  -- optional

The last ALTER statement is optional if you want the new bona fide timestamp column to bear the same name as the old text timestamp column.