Re: [GENERAL] How to define the limit length for numeric type? - Mailing list pgsql-general

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@swisspug.org> wrote ----
 > Hello
 >
 > > -----Original Message-----
 > > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of vod vos
 > > Sent: Sonntag, 12. März 2017 08:01
 > > To: Pavel Stehule <pavel.stehule@gmail.com>
 > > Cc: pgsql-general <pgsql-general@postgresql.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.stehule@gmail.com> wrote ----  >  >  > 2017-03-12
 > > 7:14 GMT+01:00 vod vos <vodvos@zoho.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@postgresql.org)  >  To make changes to your subscription:
 > >  >  http://www.postgresql.org/mailpref/pgsql-general
 > >  >
 > >  >
 > >
 > >
 > >
 > > --
 > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
 > > http://www.postgresql.org/mailpref/pgsql-general
 >
 >
 >
 > --
 > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 > To make changes to your subscription:
 > http://www.postgresql.org/mailpref/pgsql-general
 >