Re: How to define the limit length for numeric type?

So there is no other simpler method for checking that? like varchar(4), only 4 char can be input?

would using regexp cost more CPU or memory resources?

---- On 星期六, 11 三月 2017 23:21:16 -0800 Charles Clavadetscher <clavadetscher(at)swisspug(dot)org> wrote ----
> Hello
>
> > -----Original Message-----
> > From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of vod vos
> > Sent: Sonntag, 12. März 2017 08:01
> > To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> > Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
> > Subject: Re: [GENERAL] How to define the limit length for numeric type?
> >
> > Maybe CHECK (goose >= 100 AND goose <= -100) works better, But if :
> >
> > INSERT INTO test VALUES (1, 59.2);
> > INSERT INTO test VALUES (1, 59.24);
> > INSERT INTO test VALUES (1, 59.26);
> > INSERT INTO test VALUES (1, 59.2678);
> >
> > The INSERT action still can be done. What I want is just how to limit the length of the insert value, you can just
> > type format like 59.22, only four digits length.
>
> You may change (or extend) the CHECK condition using regexp:
>
> SELECT 59.2::TEXT ~ '^[0-9]+\.[0-9]{2}
;
> ?column?
> ----------
> f
>
> SELECT 59.24::TEXT ~ '^[0-9]+\.[0-9]{2}
;
> ?column?
> ----------
> t
>
> SELECT 59.26::TEXT ~ '^[0-9]+\.[0-9]{2}
;
> ?column?
> ----------
> t
>
> SELECT 59.2678::TEXT ~ '^[0-9]+\.[0-9]{2}
;
> ?column?
> ----------
> f
>
> Of course you can change the part left of the dot to also be limited to 2 digits.
>
> Regards
> Charles
>
> >
> > Thank you.
> >
> >
> > ---- On 星期六, 11 三月 2017 22:25:19 -0800 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote ---- > > > 2017-03-12
> > 7:14 GMT+01:00 vod vos <vodvos(at)zoho(dot)com>:
> > >
> > > Hi everyone,
> > >
> > > How to define the exact limit length of numeric type? For example, > > CREATE TABLE test (id serial, goose
> > numeric(4,1)); > > 300.2 and 30.2 can be inserted into COLUMN goose, but I want 30.2 or 3.2 can not be inserted,
> > how to do this?
> > >
> > > ostgres=# CREATE TABLE test (id serial, goose numeric(4,1));CREATE TABLETime: 351,066 mspostgres=# insert into
> > test values(1,3.2);INSERT 0 1Time: 65,997 mspostgres=# select * from test;┌────┬───────┐│ id │ goose
> > │╞════╪═══════╡│ 1 │ 3.2 │└────┴───────┘(1 row)Time: 68,022 ms
> > > Regards
> > > Pavel
> > > Thank you.
> > >
> > >
> > >
> > > --
> > > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) > To make changes to your subscription:
> > > http://www.postgresql.org/mailpref/pgsql-general
> > >
> > >
> >
> >
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>