Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 53874c5

Browse filesBrowse files
deanrasheeddeanrasheed
committed
Add pg_size_bytes() to parse human-readable size strings.
This will parse strings in the format produced by pg_size_pretty() and return sizes in bytes. This allows queries to be written with clauses like "pg_total_relation_size(oid) > pg_size_bytes('10 GB')". Author: Pavel Stehule with various improvements by Vitaly Burovoy Discussion: http://www.postgresql.org/message-id/CAFj8pRD-tGoDKnxdYgECzA4On01_uRqPrwF-8LdkSE-6bDHp0w@mail.gmail.com Reviewed-by: Vitaly Burovoy, Oleksandr Shulgin, Kyotaro Horiguchi, Michael Paquier and Robert Haas
1 parent 091b605 commit 53874c5
Copy full SHA for 53874c5

File tree

Expand file treeCollapse file tree

7 files changed

+331
-3
lines changed
Filter options
Expand file treeCollapse file tree

7 files changed

+331
-3
lines changed

‎doc/src/sgml/func.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/func.sgml
+30-2Lines changed: 30 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -17766,6 +17766,9 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1776617766
<indexterm>
1776717767
<primary>pg_relation_size</primary>
1776817768
</indexterm>
17769+
<indexterm>
17770+
<primary>pg_size_bytes</primary>
17771+
</indexterm>
1776917772
<indexterm>
1777017773
<primary>pg_size_pretty</primary>
1777117774
</indexterm>
@@ -17836,6 +17839,15 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1783617839
Shorthand for <literal>pg_relation_size(..., 'main')</literal>
1783717840
</entry>
1783817841
</row>
17842+
<row>
17843+
<entry>
17844+
<literal><function>pg_size_bytes(<type>text</type>)</function></literal>
17845+
</entry>
17846+
<entry><type>bigint</type></entry>
17847+
<entry>
17848+
Converts a size in human-readable format with size units into bytes
17849+
</entry>
17850+
</row>
1783917851
<row>
1784017852
<entry>
1784117853
<literal><function>pg_size_pretty(<type>bigint</type>)</function></literal>
@@ -17968,10 +17980,26 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
1796817980

1796917981
<para>
1797017982
<function>pg_size_pretty</> can be used to format the result of one of
17971-
the other functions in a human-readable way, using kB, MB, GB or TB as
17972-
appropriate.
17983+
the other functions in a human-readable way, using bytes, kB, MB, GB or TB
17984+
as appropriate.
1797317985
</para>
1797417986

17987+
<para>
17988+
<function>pg_size_bytes</> can be used to get the size in bytes from a
17989+
string in human-readable format. The input may have units of bytes, kB,
17990+
MB, GB or TB, and is parsed case-insensitively. If no units are specified,
17991+
bytes are assumed.
17992+
</para>
17993+
17994+
<note>
17995+
<para>
17996+
The units kB, MB, GB and TB used by the functions
17997+
<function>pg_size_pretty</> and <function>pg_size_bytes</> are defined
17998+
using powers of 2 rather than powers of 10, so 1kB is 1024 bytes, 1MB is
17999+
1024<superscript>2</> = 1048576 bytes, and so on.
18000+
</para>
18001+
</note>
18002+
1797518003
<para>
1797618004
The functions above that operate on tables or indexes accept a
1797718005
<type>regclass</> argument, which is simply the OID of the table or index

‎src/backend/utils/adt/dbsize.c

Copy file name to clipboardExpand all lines: src/backend/utils/adt/dbsize.c
+149Lines changed: 149 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -699,6 +699,155 @@ pg_size_pretty_numeric(PG_FUNCTION_ARGS)
699699
PG_RETURN_TEXT_P(cstring_to_text(result));
700700
}
701701

702+
/*
703+
* Convert a human-readable size to a size in bytes
704+
*/
705+
Datum
706+
pg_size_bytes(PG_FUNCTION_ARGS)
707+
{
708+
text *arg = PG_GETARG_TEXT_PP(0);
709+
char *str,
710+
*strptr,
711+
*endptr;
712+
char saved_char;
713+
Numeric num;
714+
int64 result;
715+
bool have_digits = false;
716+
717+
str = text_to_cstring(arg);
718+
719+
/* Skip leading whitespace */
720+
strptr = str;
721+
while (isspace((unsigned char) *strptr))
722+
strptr++;
723+
724+
/* Check that we have a valid number and determine where it ends */
725+
endptr = strptr;
726+
727+
/* Part (1): sign */
728+
if (*endptr == '-' || *endptr == '+')
729+
endptr++;
730+
731+
/* Part (2): main digit string */
732+
if (isdigit((unsigned char) *endptr))
733+
{
734+
have_digits = true;
735+
do
736+
endptr++;
737+
while (isdigit((unsigned char) *endptr));
738+
}
739+
740+
/* Part (3): optional decimal point and fractional digits */
741+
if (*endptr == '.')
742+
{
743+
endptr++;
744+
if (isdigit((unsigned char) *endptr))
745+
{
746+
have_digits = true;
747+
do
748+
endptr++;
749+
while (isdigit((unsigned char) *endptr));
750+
}
751+
}
752+
753+
/* Complain if we don't have a valid number at this point */
754+
if (!have_digits)
755+
ereport(ERROR,
756+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
757+
errmsg("invalid size: \"%s\"", str)));
758+
759+
/* Part (4): optional exponent */
760+
if (*endptr == 'e' || *endptr == 'E')
761+
{
762+
long exponent;
763+
char *cp;
764+
765+
/*
766+
* Note we might one day support EB units, so if what follows isn't a
767+
* number, just treat it all as a unit to be parsed.
768+
*/
769+
exponent = strtol(endptr + 1, &cp, 10);
770+
if (cp > endptr + 1)
771+
{
772+
if (exponent > NUMERIC_MAX_PRECISION ||
773+
exponent < -NUMERIC_MAX_PRECISION)
774+
ereport(ERROR,
775+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
776+
errmsg("invalid size: \"%s\"", str)));
777+
endptr = cp;
778+
}
779+
}
780+
781+
/*
782+
* Parse the number, saving the next character, which may be the first
783+
* character of the unit string.
784+
*/
785+
saved_char = *endptr;
786+
*endptr = '\0';
787+
788+
num = DatumGetNumeric(DirectFunctionCall3(numeric_in,
789+
CStringGetDatum(strptr),
790+
ObjectIdGetDatum(InvalidOid),
791+
Int32GetDatum(-1)));
792+
793+
*endptr = saved_char;
794+
795+
/* Skip whitespace between number and unit */
796+
strptr = endptr;
797+
while (isspace((unsigned char) *strptr))
798+
strptr++;
799+
800+
/* Handle possible unit */
801+
if (*strptr != '\0')
802+
{
803+
int64 multiplier = 0;
804+
805+
/* Trim any trailing whitespace */
806+
endptr = str + VARSIZE_ANY_EXHDR(arg) - 1;
807+
808+
while (isspace((unsigned char) *endptr))
809+
endptr--;
810+
811+
endptr++;
812+
*endptr = '\0';
813+
814+
/* Parse the unit case-insensitively */
815+
if (pg_strcasecmp(strptr, "bytes") == 0)
816+
multiplier = 1;
817+
else if (pg_strcasecmp(strptr, "kb") == 0)
818+
multiplier = 1024;
819+
else if (pg_strcasecmp(strptr, "mb") == 0)
820+
multiplier = 1024 * 1024;
821+
else if (pg_strcasecmp(strptr, "gb") == 0)
822+
multiplier = 1024 * 1024 * 1024;
823+
else if (pg_strcasecmp(strptr, "tb") == 0)
824+
multiplier = 1024 * 1024 * 1024 * 1024L;
825+
else
826+
ereport(ERROR,
827+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
828+
errmsg("invalid size: \"%s\"", text_to_cstring(arg)),
829+
errdetail("Invalid size unit: \"%s\".", strptr),
830+
errhint("Valid units are \"bytes\", \"kB\", \"MB\", \"GB\", and \"TB\".")));
831+
832+
if (multiplier > 1)
833+
{
834+
Numeric mul_num;
835+
836+
mul_num = DatumGetNumeric(DirectFunctionCall1(int8_numeric,
837+
Int64GetDatum(multiplier)));
838+
839+
num = DatumGetNumeric(DirectFunctionCall2(numeric_mul,
840+
NumericGetDatum(mul_num),
841+
NumericGetDatum(num)));
842+
}
843+
}
844+
845+
result = DatumGetInt64(DirectFunctionCall1(numeric_int8,
846+
NumericGetDatum(num)));
847+
848+
PG_RETURN_INT64(result);
849+
}
850+
702851
/*
703852
* Get the filenode of a relation
704853
*

‎src/include/catalog/catversion.h

Copy file name to clipboardExpand all lines: src/include/catalog/catversion.h
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201602171
56+
#define CATALOG_VERSION_NO 201602201
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Copy file name to clipboardExpand all lines: src/include/catalog/pg_proc.h
+2Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3601,6 +3601,8 @@ DATA(insert OID = 2288 ( pg_size_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f i s
36013601
DESCR("convert a long int to a human readable text using size units");
36023602
DATA(insert OID = 3166 ( pg_size_pretty PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 25 "1700" _null_ _null_ _null_ _null_ _null_ pg_size_pretty_numeric _null_ _null_ _null_ ));
36033603
DESCR("convert a numeric to a human readable text using size units");
3604+
DATA(insert OID = 3334 ( pg_size_bytes PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 20 "25" _null_ _null_ _null_ _null_ _null_ pg_size_bytes _null_ _null_ _null_ ));
3605+
DESCR("convert a size in human-readable format with size units into bytes");
36043606
DATA(insert OID = 2997 ( pg_table_size PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_ pg_table_size _null_ _null_ _null_ ));
36053607
DESCR("disk space usage for the specified table, including TOAST, free space and visibility map");
36063608
DATA(insert OID = 2998 ( pg_indexes_size PGNSP PGUID 12 1 0 0 0 f f f f t f v s 1 0 20 "2205" _null_ _null_ _null_ _null_ _null_ pg_indexes_size _null_ _null_ _null_ ));

‎src/include/utils/builtins.h

Copy file name to clipboardExpand all lines: src/include/utils/builtins.h
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -473,6 +473,7 @@ extern Datum pg_relation_size(PG_FUNCTION_ARGS);
473473
extern Datum pg_total_relation_size(PG_FUNCTION_ARGS);
474474
extern Datum pg_size_pretty(PG_FUNCTION_ARGS);
475475
extern Datum pg_size_pretty_numeric(PG_FUNCTION_ARGS);
476+
extern Datum pg_size_bytes(PG_FUNCTION_ARGS);
476477
extern Datum pg_table_size(PG_FUNCTION_ARGS);
477478
extern Datum pg_indexes_size(PG_FUNCTION_ARGS);
478479
extern Datum pg_relation_filenode(PG_FUNCTION_ARGS);

‎src/test/regress/expected/dbsize.out

Copy file name to clipboardExpand all lines: src/test/regress/expected/dbsize.out
+109Lines changed: 109 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -35,3 +35,112 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
3535
1000000000000000.5 | 909 TB | -909 TB
3636
(12 rows)
3737

38+
SELECT size, pg_size_bytes(size) FROM
39+
(VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
40+
('1TB'), ('3000 TB'), ('1e6 MB')) x(size);
41+
size | pg_size_bytes
42+
----------+------------------
43+
1 | 1
44+
123bytes | 123
45+
1kB | 1024
46+
1MB | 1048576
47+
1 GB | 1073741824
48+
1.5 GB | 1610612736
49+
1TB | 1099511627776
50+
3000 TB | 3298534883328000
51+
1e6 MB | 1048576000000
52+
(9 rows)
53+
54+
-- case-insensitive units are supported
55+
SELECT size, pg_size_bytes(size) FROM
56+
(VALUES ('1'), ('123bYteS'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB '),
57+
('1tb'), ('3000 tb'), ('1e6 mb')) x(size);
58+
size | pg_size_bytes
59+
----------+------------------
60+
1 | 1
61+
123bYteS | 123
62+
1kb | 1024
63+
1mb | 1048576
64+
1 Gb | 1073741824
65+
1.5 gB | 1610612736
66+
1tb | 1099511627776
67+
3000 tb | 3298534883328000
68+
1e6 mb | 1048576000000
69+
(9 rows)
70+
71+
-- negative numbers are supported
72+
SELECT size, pg_size_bytes(size) FROM
73+
(VALUES ('-1'), ('-123bytes'), ('-1kb'), ('-1mb'), (' -1 Gb'), ('-1.5 gB '),
74+
('-1tb'), ('-3000 TB'), ('-10e-1 MB')) x(size);
75+
size | pg_size_bytes
76+
-----------+-------------------
77+
-1 | -1
78+
-123bytes | -123
79+
-1kb | -1024
80+
-1mb | -1048576
81+
-1 Gb | -1073741824
82+
-1.5 gB | -1610612736
83+
-1tb | -1099511627776
84+
-3000 TB | -3298534883328000
85+
-10e-1 MB | -1048576
86+
(9 rows)
87+
88+
-- different cases with allowed points
89+
SELECT size, pg_size_bytes(size) FROM
90+
(VALUES ('-1.'), ('-1.kb'), ('-1. kb'), ('-0. gb'),
91+
('-.1'), ('-.1kb'), ('-.1 kb'), ('-.0 gb')) x(size);
92+
size | pg_size_bytes
93+
--------+---------------
94+
-1. | -1
95+
-1.kb | -1024
96+
-1. kb | -1024
97+
-0. gb | 0
98+
-.1 | 0
99+
-.1kb | -102
100+
-.1 kb | -102
101+
-.0 gb | 0
102+
(8 rows)
103+
104+
-- invalid inputs
105+
SELECT pg_size_bytes('1 AB');
106+
ERROR: invalid size: "1 AB"
107+
DETAIL: Invalid size unit: "AB".
108+
HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
109+
SELECT pg_size_bytes('1 AB A');
110+
ERROR: invalid size: "1 AB A"
111+
DETAIL: Invalid size unit: "AB A".
112+
HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
113+
SELECT pg_size_bytes('1 AB A ');
114+
ERROR: invalid size: "1 AB A "
115+
DETAIL: Invalid size unit: "AB A".
116+
HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
117+
SELECT pg_size_bytes('9223372036854775807.9');
118+
ERROR: bigint out of range
119+
SELECT pg_size_bytes('1e100');
120+
ERROR: bigint out of range
121+
SELECT pg_size_bytes('1e1000000000000000000');
122+
ERROR: invalid size: "1e1000000000000000000"
123+
SELECT pg_size_bytes('1 byte'); -- the singular "byte" is not supported
124+
ERROR: invalid size: "1 byte"
125+
DETAIL: Invalid size unit: "byte".
126+
HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
127+
SELECT pg_size_bytes('');
128+
ERROR: invalid size: ""
129+
SELECT pg_size_bytes('kb');
130+
ERROR: invalid size: "kb"
131+
SELECT pg_size_bytes('..');
132+
ERROR: invalid size: ".."
133+
SELECT pg_size_bytes('-.');
134+
ERROR: invalid size: "-."
135+
SELECT pg_size_bytes('-.kb');
136+
ERROR: invalid size: "-.kb"
137+
SELECT pg_size_bytes('-. kb');
138+
ERROR: invalid size: "-. kb"
139+
SELECT pg_size_bytes('.+912');
140+
ERROR: invalid size: ".+912"
141+
SELECT pg_size_bytes('+912+ kB');
142+
ERROR: invalid size: "+912+ kB"
143+
DETAIL: Invalid size unit: "+ kB".
144+
HINT: Valid units are "bytes", "kB", "MB", "GB", and "TB".
145+
SELECT pg_size_bytes('++123 kB');
146+
ERROR: invalid size: "++123 kB"

‎src/test/regress/sql/dbsize.sql

Copy file name to clipboardExpand all lines: src/test/regress/sql/dbsize.sql
+39Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10,3 +10,42 @@ SELECT size, pg_size_pretty(size), pg_size_pretty(-1 * size) FROM
1010
(10.5::numeric), (1000.5::numeric), (1000000.5::numeric),
1111
(1000000000.5::numeric), (1000000000000.5::numeric),
1212
(1000000000000000.5::numeric)) x(size);
13+
14+
SELECT size, pg_size_bytes(size) FROM
15+
(VALUES ('1'), ('123bytes'), ('1kB'), ('1MB'), (' 1 GB'), ('1.5 GB '),
16+
('1TB'), ('3000 TB'), ('1e6 MB')) x(size);
17+
18+
-- case-insensitive units are supported
19+
SELECT size, pg_size_bytes(size) FROM
20+
(VALUES ('1'), ('123bYteS'), ('1kb'), ('1mb'), (' 1 Gb'), ('1.5 gB '),
21+
('1tb'), ('3000 tb'), ('1e6 mb')) x(size);
22+
23+
-- negative numbers are supported
24+
SELECT size, pg_size_bytes(size) FROM
25+
(VALUES ('-1'), ('-123bytes'), ('-1kb'), ('-1mb'), (' -1 Gb'), ('-1.5 gB '),
26+
('-1tb'), ('-3000 TB'), ('-10e-1 MB')) x(size);
27+
28+
-- different cases with allowed points
29+
SELECT size, pg_size_bytes(size) FROM
30+
(VALUES ('-1.'), ('-1.kb'), ('-1. kb'), ('-0. gb'),
31+
('-.1'), ('-.1kb'), ('-.1 kb'), ('-.0 gb')) x(size);
32+
33+
-- invalid inputs
34+
SELECT pg_size_bytes('1 AB');
35+
SELECT pg_size_bytes('1 AB A');
36+
SELECT pg_size_bytes('1 AB A ');
37+
SELECT pg_size_bytes('9223372036854775807.9');
38+
SELECT pg_size_bytes('1e100');
39+
SELECT pg_size_bytes('1e1000000000000000000');
40+
SELECT pg_size_bytes('1 byte'); -- the singular "byte" is not supported
41+
SELECT pg_size_bytes('');
42+
43+
SELECT pg_size_bytes('kb');
44+
SELECT pg_size_bytes('..');
45+
SELECT pg_size_bytes('-.');
46+
SELECT pg_size_bytes('-.kb');
47+
SELECT pg_size_bytes('-. kb');
48+
49+
SELECT pg_size_bytes('.+912');
50+
SELECT pg_size_bytes('+912+ kB');
51+
SELECT pg_size_bytes('++123 kB');

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.