<!--

$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.197 2004/04/10 18:02:59 momjian Exp $

$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.198 2004/04/23 20:32:19 neilc Exp $

PostgreSQL documentation

-->

<entry><literal>-42</literal></entry>

</row>

<row>

<entry><literal><function>ceiling</function>(<type>dp</type> or <type>numeric</type>)</literal></entry>

<entry>(same as input)</entry>

<entry>smallest integer not less than argument (alias for <function>ceil</function>)</entry>

<entry><literal>ceiling(-95.3)</literal></entry>

<entry><literal>-95</literal></entry>

</row>

<row>

<entry><literal><function>degrees</function>(<type>dp</type>)</literal></entry>

<entry><type>dp</type></entry>

</row>

<row>

<entry><literal><function>pow</function>(<parameter>a</parameter> <type>dp</type>,

<entry><literal><function>power</function>(<parameter>a</parameter> <type>dp</type>,

<parameter>b</parameter> <type>dp</type>)</literal></entry>

<entry><type>dp</type></entry>

<entry><parameter>a</> raised to the power of <parameter>b</parameter></entry>

<entry><literal>pow(9.0, 3.0)</literal></entry>

<entry><literal>power(9.0, 3.0)</literal></entry>

<entry><literal>729</literal></entry>

</row>

<row>

<entry><literal><function>pow</function>(<parameter>a</parameter> <type>numeric</type>,

<entry><literal><function>power</function>(<parameter>a</parameter> <type>numeric</type>,

<parameter>b</parameter> <type>numeric</type>)</literal></entry>

<entry><type>numeric</type></entry>

<entry><parameter>a</> raised to the power of <parameter>b</parameter></entry>

<entry><literal>pow(9.0, 3.0)</literal></entry>

<entry><literal>power(9.0, 3.0)</literal></entry>

<entry><literal>729</literal></entry>

</row>

DESCR("truncate to integer");

DATA(insertOID=2308 (ceilPGNSPPGUID12fftfi1701"701"_null_dceil-_null_ ));

DESCR("smallest integer >= value");

DATA(insertOID=2320 (ceilingPGNSPPGUID12fftfi1701"701"_null_dceil-_null_ ));

DESCR("smallest integer >= value");

DATA(insertOID=2309 (floorPGNSPPGUID12fftfi1701"701"_null_dfloor-_null_ ));

DESCR("largest integer <= value");

DATA(insertOID=2310 (signPGNSPPGUID12fftfi1701"701"_null_dsign-_null_ ));

DESCR("cube root");

DATA(insertOID=1346 (powPGNSPPGUID12fftfi2701"701 701"_null_dpow-_null_ ));

DESCR("exponentiation");

DATA(insertOID=1368 (powerPGNSPPGUID12fftfi2701"701 701"_null_dpow-_null_ ));

DESCR("exponentiation");

DATA(insertOID=1347 (expPGNSPPGUID12fftfi1701"701"_null_dexp-_null_ ));

DESCR("exponential");

DESCR("value truncated to 'scale' of zero");

DATA(insertOID=1711 (ceilPGNSPPGUID12fftfi11700"1700"_null_numeric_ceil-_null_ ));

DESCR("smallest integer >= value");

DATA(insertOID=2167 (ceilingPGNSPPGUID12fftfi11700"1700"_null_numeric_ceil-_null_ ));

DESCR("smallest integer >= value");

DATA(insertOID=1712 (floorPGNSPPGUID12fftfi11700"1700"_null_numeric_floor-_null_ ));

DESCR("largest integer <= value");

DATA(insertOID=1718 (numeric_eqPGNSPPGUID12fftfi216"1700 1700"_null_numeric_eq-_null_ ));

DESCR("logarithm base m of n");

DATA(insertOID=1738 (powPGNSPPGUID12fftfi21700"1700 1700"_null_numeric_power-_null_ ));

DESCR("m raised to the power of n");

DATA(insertOID=2169 (powerPGNSPPGUID12fftfi21700"1700 1700"_null_numeric_power-_null_ ));

DESCR("m raised to the power of n");

DATA(insertOID=1739 (numeric_powerPGNSPPGUID12fftfi21700"1700 1700"_null_numeric_power-_null_ ));

DESCR("m raised to the power of n");

DATA(insertOID=1740 (numericPGNSPPGUID12fftfi11700"23"_null_int4_numeric-_null_ ));

| 1.2345678901234e-200 | 0

(5 rows)

-- ceil

-- ceil / ceiling

select ceil(f1) as ceil_f1 from float8_tbl f;

ceil_f1

----------------------

1

(5 rows)

select ceiling(f1) as ceiling_f1 from float8_tbl f;

ceiling_f1

----------------------

0

1005

-34

1.2345678901234e+200

1

(5 rows)

-- floor

select floor(f1) as floor_f1 from float8_tbl f;

floor_f1

| 1.2345678901234e-200 | 1.11111110611109e-100

(3 rows)

-- power

SELECT power(float8 '144', float8 '0.5');

power

-------

12

(1 row)

-- take exp of ln(f.f1)

SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1

FROM FLOAT8_TBL f

| 1.2345678901234e-200 | 0

(5 rows)

-- ceil

-- ceil / ceiling

select ceil(f1) as ceil_f1 from float8_tbl f;

ceil_f1

----------------------

1

(5 rows)

select ceiling(f1) as ceiling_f1 from float8_tbl f;

ceiling_f1

----------------------

0

1005

-34

1.2345678901234e+200

1

(5 rows)

-- floor

select floor(f1) as floor_f1 from float8_tbl f;

floor_f1

| 1.2345678901234e-200 | 1.11111110611109e-100

(3 rows)

-- power

SELECT power(float8 '144', float8 '0.5');

power

-------

12

(1 row)

-- take exp of ln(f.f1)

SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1

FROM FLOAT8_TBL f

| 1.2345678901234e-200 | 0

(5 rows)

-- ceil

-- ceil / ceiling

select ceil(f1) as ceil_f1 from float8_tbl f;

ceil_f1

----------------------

1

(5 rows)

select ceiling(f1) as ceiling_f1 from float8_tbl f;

ceiling_f1

----------------------

0

1005

-34

1.2345678901234e+200

1

(5 rows)

-- floor

select floor(f1) as floor_f1 from float8_tbl f;

floor_f1

| 1.2345678901234e-200 | 1.11111110611109e-100

(3 rows)

-- power

SELECT power(float8 '144', float8 '0.5');

power

-------

12

(1 row)

-- take exp of ln(f.f1)

SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1

FROM FLOAT8_TBL f

| 1.2345678901234e-200 | 0

(5 rows)

-- ceil

-- ceil / ceiling

select ceil(f1) as ceil_f1 from float8_tbl f;

ceil_f1

----------------------

1

(5 rows)

select ceiling(f1) as ceiling_f1 from float8_tbl f;

ceiling_f1

----------------------

0

1005

-34

1.2345678901234e+200

1

(5 rows)

-- floor

select floor(f1) as floor_f1 from float8_tbl f;

floor_f1

| 1.2345678901234e-200 | 1.11111110611109e-100

(3 rows)

-- power

SELECT power(float8 '144', float8 '0.5');

power

-------

12

(1 row)

-- take exp of ln(f.f1)

SELECT '' AS three, f.f1, exp(ln(f.f1)) AS exp_ln_f1

FROM FLOAT8_TBL f

(0 rows)

-- ******************************

-- *POW(10, LN(value)) check

-- *POWER(10, LN(value)) check

-- ******************************

DELETE FROM num_result;

INSERT INTO num_result SELECT id, 0,POW(numeric '10', LN(ABS(round(val,200))))

INSERT INTO num_result SELECT id, 0,POWER(numeric '10', LN(ABS(round(val,200))))

FROM num_data

WHERE val != '0.0';

SELECT t1.id1, t1.result, t2.expected

(6 rows)

DROP TABLE fract_only;

-- Simple check that ceil(), floor(), and round() work correctly

CREATE TABLE ceil_floor_round (a numeric);

INSERT INTO ceil_floor_round VALUES ('-5.5');

INSERT INTO ceil_floor_round VALUES ('-5.499999');

INSERT INTO ceil_floor_round VALUES ('9.5');

INSERT INTO ceil_floor_round VALUES ('9.4999999');

INSERT INTO ceil_floor_round VALUES ('0.0');

INSERT INTO ceil_floor_round VALUES ('0.0000001');

INSERT INTO ceil_floor_round VALUES ('-0.000001');

SELECT a, ceil(a), ceiling(a), floor(a), round(a) FROM ceil_floor_round;

a | ceil | ceiling | floor | round

-----------+------+---------+-------+-------

-5.5 | -5 | -5 | -6 | -6

-5.499999 | -5 | -5 | -6 | -5

9.5 | 10 | 10 | 9 | 10

9.4999999 | 10 | 10 | 9 | 9

0.0 | 0 | 0 | 0 | 0

0.0000001 | 1 | 1 | 0 | 0

-0.000001 | 0 | 0 | -1 | 0

(7 rows)

DROP TABLE ceil_floor_round;

-- TO_CHAR()

--

SELECT '' AS to_char_1, to_char(val, '9G999G999G999G999G999')

SELECT''AS five,f.f1,f.f1 %AS round_f1

FROM FLOAT8_TBL f;

select ceil(f1)as ceil_f1from float8_tbl f;

select ceiling(f1)as ceiling_f1from float8_tbl f;

select floor(f1)as floor_f1from float8_tbl f;

FROM FLOAT8_TBL f

WHEREf.f1>'0.0';

SELECT power(float8'144', float8'0.5');

SELECT''AS three,f.f1, exp(ln(f.f1))AS exp_ln_f1

FROM FLOAT8_TBL f

ANDt1.result!=t2.expected;

DELETEFROM num_result;

INSERT INTO num_resultSELECT id,0,POW(numeric'10', LN(ABS(round(val,200))))

INSERT INTO num_resultSELECT id,0,POWER(numeric'10', LN(ABS(round(val,200))))

FROM num_data

WHERE val!='0.0';

SELECTt1.id1,t1.result,t2.expected

SELECT*FROM fract_only;

DROPTABLE fract_only;

CREATETABLEceil_floor_round (anumeric);

INSERT INTO ceil_floor_roundVALUES ('-5.5');

INSERT INTO ceil_floor_roundVALUES ('-5.499999');

INSERT INTO ceil_floor_roundVALUES ('9.5');

INSERT INTO ceil_floor_roundVALUES ('9.4999999');

INSERT INTO ceil_floor_roundVALUES ('0.0');

INSERT INTO ceil_floor_roundVALUES ('0.0000001');

INSERT INTO ceil_floor_roundVALUES ('-0.000001');

SELECT a, ceil(a), ceiling(a), floor(a), round(a)FROM ceil_floor_round;

DROPTABLE ceil_floor_round;

SELECT''AS to_char_1, to_char(val,'9G999G999G999G999G999')