How to define the limit length for numeric type?

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

--
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

--
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: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

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 ms

Regards

sorry, I wrongly read a question

Pavel

On Sat, Mar 11, 2017 at 11:14 PM, vod vos <vodvos@zoho.com> wrote:

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.

https://www.postgresql.org/docs/9.6/static/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS

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 format like 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);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

Please don't top-post on these lists.

On Sun, Mar 12, 2017 at 12:00 AM, vod vos <vodvos@zoho.com> wrote:

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

--
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

--
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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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?

---- 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

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.

/messages/by-id/15358.1489336741@sss.pgh.pa.us
/messages/by-id/040301d29b01$43d71f50$cb855df0$@swisspug.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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Mar 13, 2017 at 7:51 AM, vod vos <vodvos@zoho.com> wrote:

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.​