Sorry, So basically what I'm trying to achieve is the following. There is an input file from the user and a configuration describing what is being inserted. For example, he might have Last activity which is 'YYYY-MM-DD HH:mi:ss' and Join date which is only 'YYYY-MM-DD' because there is no associated timing. For dates this works perfectly and it is possible to configure what the input from the user will be. Think it is as a dropdown where the user says, this is the kind of data that I have.
Maybe that is not possible with numbers? To say in a format something like "my numbers have comma as decimal separator and no thousands separators" or "my numbers are point separated and have comma as thousands separator" Nice thing of having a string for the format is that I can use it as a parameter for a prepared statement. Thanks On Wed, 19 Sep 2018 at 15:22, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 9/19/18 6:11 AM, Gabriel Furstenheim Milerud wrote: > > I'm not completely sure that that actually works > > > > SELECT to_number('9,134','9V3') =9 > > SELECT (to_number('9,134', '99999')/1000)::numeric(4,3); > numeric > --------- > 9.134 > > > > > It's true when it should be false (it should be 9.134). Also it is > > completely dependent on the number of digits. So for example: > > > > SELECT to_number('19,134','9V3') > > > > Is 1, not 19.134 or even 19 > > We probably ought to back this question up and ask what is you want to > achieve in general? > > > > > > On Wed, 19 Sep 2018 at 14:57, Adrian Klaver <adrian.kla...@aklaver.com > > <mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 9/19/18 5:38 AM, Gabriel Furstenheim Milerud wrote: > > > Hello, > > > I'd like to convert a string number to a number being able to > > provide > > > the custom format. > > > With dates it works perfectly fine, so that I can do: > > > > > > SELECT to_date('18 09 10', 'YY MM DD') > > > > > > Is there something similar with numbers? > > > > > > SELECT to_number('9,000',some_format) =9; > > > > SELECT to_number('9,000', '9V3')::int; > > to_number > > ----------- > > 9 > > > > > SELECT to_number('9,000',another_format) =9000; > > > > SELECT to_number('9,000', '99999'); > > to_number > > ----------- > > 9000 > > > > > > > > It is not clear to me what some_format should be and what > > another_format > > > should be so that those selects are equal. > > > > > > I've read the documentation but I can't find a similar example. In > > > stackoverflow they don't provide a solution either: > > > > > > https://stackoverflow.com/questions/18882942/postgresql-convert-a-string-with-commas-into-an-integer > > > > > > Thanks > > > Gabriel Fürstenheim > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >