sql postgresql timestamp rounding greenplum

Two questions for formatting timestamp and number using postgresql

I am selecting a date column which is in the format "YYYY-MM-DD".

I want to cast it to a timestamp such that it will be "YYYY-MM-DD HH:MM:SS:MS"

I attempted:

select CAST(mycolumn as timestamp) from mytable;

but this resulted in the format YYYY-MM-DD HH:MM:SS

I also tried

select TO_TIMESTAMP(mycolumn,YYYY-MM-DD HH:MM:SS:MS) from mytable;

but this did not work either. I cannot seem to figure out the correct way to format this. Note that I only want the first digit of the milliseconds.

//////////////second question

I am also trying to select numeric data such that there will not be any trailing zeros.

For example, if I have values in a table such as 1, 2.00, 3.34, 4.50.

I want to be able to select those values as 1, 2, 3.34, 4.5.

I tried using ::float, but I occasionally strange output. I also tried the rounding function, but how could I use it properly without knowing how many decimal points I need before hand?

thanks for your help!

Solution

It seems that the functions to_timestamp() and to_char() are unfortunately not perfect. If you cannot find anything better, use these workarounds:

with example_data(d) as (
    values ('2016-02-02')
    )
select d, d::timestamp || '.0' tstamp
from example_data;

     d      |        tstamp         
------------+-----------------------
 2016-02-02 | 2016-02-02 00:00:00.0
(1 row)

create function my_to_char(numeric)
returns text language sql as $$
    select case 
        when strpos($1::text, '.') = 0 then $1::text
        else rtrim($1::text, '.0')
    end
$$;

with example_data(n) as (
    values (100), (2.00), (3.34), (4.50))
select n::text, my_to_char(n)
from example_data;

  n   | my_to_char 
------+------------
 100  | 100
 2.00 | 2
 3.34 | 3.34
 4.50 | 4.5
(4 rows)