sql - Two questions for formatting timestamp and number using postgressql -

i selecting date column in format "yyyy-mm-dd".

i want cast timestamp such "yyyy-mm-dd hh:mm:ss:ms"

i attempted:

select cast(mycolumn timestamp) mytable; 

but resulted in format yyyy-mm-dd hh:mm:ss

i tried

select to_timestamp(mycolumn,yyyy-mm-dd hh:mm:ss:ms) mytable; 

but did not work either. cannot seem figure out correct way format this. note want first digit of milliseconds.

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

i trying select numeric data such there not trailing zeros.

for example, if have values in table such 1, 2.00, 3.34, 4.50.

i want able select values 1, 2, 3.34, 4.5.

i tried using ::float, strange output. tried rounding function, how use without knowing how many decimal points need before hand?

thanks help!

it seems functions to_timestamp() , to_char() unfortunately not perfect. if cannot find better, use these workarounds:

with example_data(d) (     values ('2016-02-02')     ) select d, d::timestamp || '.0' tstamp 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 $$     select case          when strpos($1::text, '.') = 0 $1::text         else rtrim($1::text, '.0')     end $$;  example_data(n) (     values (100), (2.00), (3.34), (4.50)) select n::text, my_to_char(n) example_data;    n   | my_to_char  ------+------------  100  | 100  2.00 | 2  3.34 | 3.34  4.50 | 4.5 (4 rows) 

see also: how remove dot in to_char if number integer