Thread: [GENERAL] How to define the limit length for numeric type?
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? Thank you.
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 07:15 > To: pgsql-general <pgsql-general@postgresql.org> > Subject: [GENERAL] How to define the limit length for numeric type? > > > 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? Maybe with a CHECK constraint? CREATE TABLE test ( id serial, goose numeric(4,1), CHECK (goose > 30.2) ); INSERT INTO test (goose) VALUES (300.2); INSERT 0 1 INSERT INTO test (goose) VALUES (30.2); ERROR: new row for relation "test" violates check constraint "test_goose_check" DETAIL: Failing row contains (2, 30.2). Of course you should set the correct value that you want to use in the contraint definition. Regards Charles > > 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
## vod vos (vodvos@zoho.com):
> 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?
testing=# CREATE TABLE test (
id SERIAL,
goose NUMERIC(4,1),
CHECK (goose >= 100 OR goose <= -100)
);
CREATE TABLE
testing=# INSERT INTO test (goose) VALUES (300.2);
INSERT 0 1
testing=# INSERT INTO test (goose) VALUES (30.2);
ERROR: new row for relation "test" violates check constraint "test_goose_check"
DETAIL: Failing row contains (2, 30.2).
testing=# INSERT INTO test (goose) VALUES (-300.2);
INSERT 0 1
testing=# INSERT INTO test (goose) VALUES (-30.2);
ERROR: new row for relation "test" violates check constraint "test_goose_check"
DETAIL: Failing row contains (4, -30.2).
Regards,
Christoph
--
Spare Space
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 TABLE Time: 351,066 ms postgres=# insert into test values(1,3.2); INSERT 0 1 Time: 65,997 ms postgres=# 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
2017-03-12 7:25 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:
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 TABLE Time: 351,066 ms postgres=# insert into test values(1,3.2); INSERT 0 1 Time: 65,997 ms postgres=# select * from test; ┌────┬───────┐ │ id │ goose │ ╞════╪═══════╡ │ 1 │ 3.2 │ └────┴───────┘ (1 row) Time: 68,022 msRegards
sorry, I wrongly read a question
Pavel
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
Hi everyone,
How to define the exact limit length of numeric type? For example,
CREATE TABLE test (id serial, goose numeric(4,1));[...]30.2 can be inserted into COLUMN goose, but I want 30.2[...]can not be inserted, how to do this?
Not possible to both allow and disallow the same value (30.2) ...
A check constraint is "how" you define additional limitation on the data - but you'll need to figure out where the logic flaw (or typo) came from.
David J.
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 formatlike 59.22, only four digits length. 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);INSERT0 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 > >
Please don't top-post on these lists.
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.
length(trunc(goose, 0)::text) + scale(goose)
I suspect you might encounter some issues, namely around 123.456789::numeric(6,1) casting behavior and maybe 00059.12000::numeric(6,1) treatment of unimportant zeros. I haven't tested any of that. The above will get you a single length value for a given input.
David J.
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
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
>
On 03/12/2017 12:33 AM, vod vos wrote:
> So there is no other simpler method for checking that? like varchar(4), only 4 char can be input?
That is not how that works:
test=# create table varchar_test(fld_1 varchar(4));
CREATE TABLE
test=# \d varchar_test
Table "public.varchar_test"
Column | Type | Modifiers
--------+----------------------+-----------
fld_1 | character varying(4) |
INSERT INTO varchar_test VALUES ('test'), ('tes'), ('te'), ('t');
INSERT 0 4
test=# select * from varchar_test ;
fld_1
-------
test
tes
te
t
(4 rows)
test=# INSERT INTO varchar_test VALUES ('tests');
ERROR: value too long for type character varying(4)
It just sets the upper limit of what can be entered.
>
> would using regexp cost more CPU or memory resources?
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Sun, Mar 12, 2017 at 12:00 AM, vod vos <vodvos@zoho.com> wrote:
>> 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.
> length(trunc(goose, 0)::text) + scale(goose)
> I suspect you might encounter some issues, namely around
> 123.456789::numeric(6,1) casting behavior and maybe
> 00059.12000::numeric(6,1) treatment of unimportant zeros.
Yeah. I wonder if the OP wouldn't be better off thinking of his data as
strings rather than numbers. The format requirement could be expressed
as a CHECK constraint, along the lines of length(goose) = 5 AND
goose ~ '^\d+\.\d+$' (or possibly \d* if zero digits on one side of
the decimal point is OK). You could imagine storing as numeric and
having CHECK constraints that cast to string and make those tests,
but I fear trailing zeroes would break it.
regards, tom lane
Hello, On Sat, 2017-03-11 at 22:14 -0800, vod vos wrote: > 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? > > Thank you. > > > Assuming that column goose may only contain values ranging from 100.0 to 999.9, then a check constraint along the lines of:- goose > 99.9 and < 1000 should do the trick. HTH, Rob
Sorry for taking varchar(4) as an example, What I want urgently is how to make a constraint of fixed length of a numericdata, that you can only input data like 23.45, and if you input the data like 2.45, 23.4356, 233.45, you will get a warning message from postgresql. I think expr will do the job, but are there any simpler ways to do it in postgresql? ---- On 星期日, 12 三月 2017 14:28:53 -0700 rob stone <floriparob@gmail.com> wrote ---- > Hello, > > On Sat, 2017-03-11 at 22:14 -0800, vod vos wrote: > > 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? > > > > Thank you. > > > > > > > > > Assuming that column goose may only contain values ranging from 100.0 > to 999.9, then a check constraint along the lines of:- > > goose > 99.9 and < 1000 > > should do the trick. > > HTH, > Rob > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Hello > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of vod vos > Sent: Montag, 13. März 2017 15:52 > To: rob stone <floriparob@gmail.com> > Cc: pgsql-general <pgsql-general@postgresql.org> > Subject: Re: [GENERAL] How to define the limit length for numeric type? > > Sorry for taking varchar(4) as an example, What I want urgently is how to make a constraint of fixed length of a > numeric data, that you can only input data like 23.45, and if you input the data like 2.45, 23.4356, 233.45, you > will get a warning message from postgresql. > > I think expr will do the job, but are there any simpler ways to do it in postgresql? Well, I don't think that you will find anything simpler than using a regexp in a check constraint, as Tom and I did suggest. https://www.postgresql.org/message-id/15358.1489336741%40sss.pgh.pa.us https://www.postgresql.org/message-id/040301d29b01%2443d71f50%24cb855df0%24%40swisspug.org I have some trouble understanding what you find so complicated in that solution? Bye Charles > > > ---- On 星期日, 12 三月 2017 14:28:53 -0700 rob stone <floriparob@gmail.com> wrote ---- > Hello, > > On Sat, 2017- > 03-11 at 22:14 -0800, vod vos wrote: > > > 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? > > > > > > Thank you. > > > > > > > > > > > > > > > Assuming that column goose may only contain values ranging from 100.0 > to 999.9, then a check constraint along > the lines of:- > > goose > 99.9 and < 1000 > > should do the trick. > > > > HTH, > > Rob > > > > > > -- > > 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
Sorry for taking varchar(4) as an example, What I want urgently is how to make a constraint of fixed length of a numeric data,
that you can only input data like 23.45, and if you input the data like 2.45, 23.4356, 233.45, you will get a warning
message from postgresql.
I think expr will do the job, but are there any simpler ways to do it in postgresql?
Requiring a fixed length, and not an amount range is unusual. That the only way to do it is to consider the input as text and use a regular expression is understandable.
David J.