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?

1 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;
  • Please tick the tickmark on your answer, that is the indicator of solved issues, not the [solved] tag in the title. Commented Jul 18, 2018 at 8:17
  • haha, sorry for this, i thought that i have to added manually
    – ibrasec
    Commented Jul 18, 2018 at 9:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.