Converting to number with given format
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', another_format) = 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
Re: Converting to number with given format
I'm not completely sure that that actually works
SELECT to_number('9,134', '9V3') = 9
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
On Wed, 19 Sep 2018 at 14:57, Adrian Klaver
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', '9');
> 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
>
Re: Converting to number with given format
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 '-MM-DD HH:mi:ss' and
Join date which is only '-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
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', '9')/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 > <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', '9');
> >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
>
Re: Converting to number with given format
Hi Ken,
Thanks a lot, that's a cool idea and I think that it will cover my needs.
On Thu, 20 Sep 2018 at 02:04, Ken Tanzer wrote:
> On Wed, Sep 19, 2018 at 6:34 AM Gabriel Furstenheim Milerud <
> furstenh...@gmail.com> wrote:
>
> 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"
>>
>>
> Would stripping out the thousand separator, and leaving in the decimal
> separator work?
>
> SELECT replace('9,000.34',',','')::numeric;
> replace
> -
> 9000.34
>
> If so, then (conceptually) does this work?
>
> SELECT replace(
>
> replace(my_numeric_string, user_thousand_sep, ''),
>
> user_decimal_sep, system_decimal_sep
>
> )::numeric
>
>
> Or maybe I'm missing something about this!
>
> Cheers,
>
> Ken
>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ <http://agency-software.org/>*
> *https://demo.agency-software.org/client
> <https://demo.agency-software.org/client>*
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
> to
> learn more about AGENCY or
> follow the discussion.
>
Check that numeric is zero
Hi, I'm writing a Postgres native extension and I would like to check that a numeric is zero. My problem is that all exported methods like numeric_eq or numeric_sign require me to have a numeric to start with, and const_zero is not exported in numeric.c. Any idea how to check it? Thanks Gabriel Fürstenheim
Enforcing users to write schemas when creating tables
Hi, I'm trying to enforce db users to write a schema when creating a table. That is: create table some_schema.my_table (a int); -- should succeed create my_table (a int); -- should fail I don't know if that is possible. What I've tried so far is to create a schema which is first in the search path and where the user has no permission to create tables. I've done the following (and failed): create schema no_table_schema; alter schema no_table_schema owner to another_user; -- just in case revoke all on schema no_table_schema from my_user cascade; set search_path = no_table_schema; create table test_table (a int); -- this should fail because user should not have permission in no_table_schema, but it does not drop table no_table_schema.test_table; -- This succeeds, the table was created One thing that might affect is that my_user is a superuser. So I have two questions, first is how do I revoke create on a schema for a certain user. I guess there is something that I'm not doing properly. Then, is that enough my purpose? Or maybe there are easier ways to force users to provide schema when creating. Thanks Gabriel Fürstenheim
pg10 logical replication set schema
Hi, I've been following https://hackernoon.com/postgresql-logical-replication-86df5b51cc5a to set up logical replication. I've created the table in master and publication create table test_table_replication (a int); create publication test_table_replication_pub for table test_table_replication; In the standby I attempt to create a table and a subscription create table some_schema.test_table_replication (a int); create subscription test_table_replication connection 'host=localhost dbname=postgres user=standby_user pass=pass port=$master_port' And I get the error relation "public.test_table_replication" does not exist. Is it possible to tell the subscription that it should use the table some_schema.test_table_replication instead of public.test_table_replication? I can't find any reference in the docs about tables or schemas in subscriptions. Thanks Gabriel Fürstenheim
