Timestamp and substring with regular expression

Question

I am using postgresql and I have the following table

 Table "public.test"
 Column | Type | Modifiers 
--------+--------------------------+-----------
 name | text | 
 time | timestamp with time zone | 
 name | time 
-------+------------------------
 ticka | 2016年07月08日 21:22:58+00
(1 row)

When I do the command:

select substring(name from '.*') from test;

I get a result, but when I do it for the time column I get:

Error: LINE 1: select substring(time from '.*' ) from test;
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Why is this? How can I solve it?

Answer 1

Ok, because the function is called substring, I think it only works for the string data type but not for the timestamp data type

To solve this I use the regular expression on the timestamp by converting the timestamp into a text first using the to_char function and then I use the substring function as follows:

select substring(to_char(time,'YYYY-MM-DD HH:MM:SS') from '.*') from test;

• András Váczi: Please tick the tickmark on your answer, that is the indicator of solved issues, not the [solved] tag in the title.
• ibrasec: haha, sorry for this, i thought that i have to added manually