ืคึผืจืึธื”ืึธืกื˜ืขืจ > ื‘ืœืึธื’ > ืึทื“ืžื™ื ื™ืกื˜ืจืึทืฆื™ืข > ืกืึทืกืคึผื™ืฉืึทืก ื˜ื™ื™ืคึผืก

ืกืึทืกืคึผื™ืฉืึทืก ื˜ื™ื™ืคึผืก

ืขืก ืื™ื– ื’ืึธืจื ื™ืฉื˜ ืกืึทืกืคึผื™ืฉืึทืก ื•ื•ืขื’ืŸ ื–ื™ื™ืขืจ ืื•ื™ืกื–ืขืŸ. ื“ืขืจืฆื•, ื–ื™ื™ ืืคื™ืœื• ื•ื™ืกืงื•ืžืขืŸ ื‘ืึทืงืึทื ื˜ ืฆื• ืื™ืจ ื’ืขื–ื•ื ื˜ ืื•ืŸ ืคึฟืึทืจ ืึท ืœืึทื ื’ ืฆื™ื™ึทื˜. ืื‘ืขืจ ืึทื– ืก ื ืึธืจ ื‘ื™ื– ืื™ืจ ื˜ืฉืขืง ื–ื™ื™. ื“ืึธืก ืื™ื– ื•ื•ื• ื–ื™ื™ ื•ื•ื™ื™ึทื–ืŸ ื–ื™ื™ืขืจ ื™ื ืกื™ื“ื™ืึทืก ื ืึทื˜ื•ืจ, ืืจื‘ืขื˜ืŸ ื’ืึธืจ ืึทื ื“ืขืจืฉ ื•ื•ื™ ืื™ืจ ื“ืขืจื•ื•ืึทืจื˜. ืื•ืŸ ืžืืœ ื–ื™ื™ ื˜ืึธืŸ ืขืคึผืขืก ื•ื•ืึธืก ืžืื›ื˜ ื“ื™ื™ืŸ ื”ืึธืจ ืฉื˜ื™ื™ืŸ ืื•ื™ืฃ ืกื•ืฃ - ืคึฟืึทืจ ื‘ื™ื™ึทืฉืคึผื™ืœ, ื–ื™ื™ ืคืึทืจืœื™ืจืŸ ืกื•ื“ ื“ืึทื˜ืŸ ืขื ื˜ืจืึทืกื˜ื™ื“ ืฆื• ื–ื™ื™. ื•ื•ืขืŸ ืื™ืจ ืงืึธื ืคืจืึธื ื˜ื™ืจืŸ ื–ื™ื™, ื–ื™ื™ ืคืึธื“ืขืจืŸ ืึทื– ื–ื™ื™ ืงืขื ืขืŸ ื ื™ืฉื˜ ื™ืขื“ืขืจ ืื ื“ืขืจืขืจ, โ€‹โ€‹ื›ืึธื˜ืฉ ืื™ืŸ ื“ื™ ืฉืึทื“ืึธื•ื– ื–ื™ื™ ืึทืจื‘ืขื˜ืŸ ืฉื•ื•ืขืจ ืื•ื ื˜ืขืจ ื“ืขืจ ื–ืขืœื‘ื™ืงืขืจ ืงืึทืคึผื˜ืขืจ. ืขืก ืก ืฆื™ื™ึทื˜ ืฆื• ืœืขืกืึธืฃ ื‘ืจืขื ื’ืขืŸ ื–ื™ื™ ืฆื• ืจื™ื™ืŸ ื•ื•ืึทืกืขืจ. ืœืึธืžื™ืจ ื–ื™ืš ืื•ื™ืš ื‘ืึทื”ืึทื ื“ืœืขืŸ ืžื™ื˜ ื“ื™ ืกืึทืกืคึผื™ืฉืึทืก ื˜ื™ื™ืคึผืก.

ื“ืึทื˜ืŸ ื˜ื™ื™ืคึผื™ื ื’ ืื™ืŸ PostgreSQL, ืคึฟืึทืจ ืึทืœืข ื–ื™ื™ึทืŸ ืœืึธื’ื™ืง, ืื™ื– ืžืืœ ื–ื™ื™ืขืจ ืžืึธื“ื ืข ืกืึทืคึผืจื™ื™ื–ื™ื–. ืื™ืŸ ื“ืขื ืึทืจื˜ื™ืงืœ, ืžื™ืจ ื•ื•ืขืœืŸ ืคึผืจื•ื‘ื™ืจืŸ ืฆื• ื“ืขืจืงืœืขืจืŸ ืขื˜ืœืขื›ืข ืคื•ืŸ โ€‹โ€‹โ€‹โ€‹ื–ื™ื™ืขืจ ืงื•ื•ืขืจืงืก, ืคึฟืึทืจืฉื˜ื™ื™ืŸ ื“ื™ ืกื™ื‘ื” ืคึฟืึทืจ ื–ื™ื™ืขืจ ืžืึธื“ื ืข ื ืึทื˜ื•ืจ ืื•ืŸ ืคึฟืึทืจืฉื˜ื™ื™ืŸ ื•ื•ื™ ื ื™ืฉื˜ ืฆื• ืœื•ื™ืคืŸ ืื™ืŸ ืคึผืจืึธื‘ืœืขืžืก ืื™ืŸ ื•ื•ืึธื›ืขื“ื™ืง ืคื™ืจ. ื“ืขื ืืžืช ื–ืึธื’ืŸ, ื”ืึธื‘ ืื™ืš ืฆื•ื ื•ื™ืคึฟื’ืขืฉื˜ืขืœื˜ ื“ืขื ืึทืจื˜ื™ืงืœ ืื•ื™ืš ื•ื•ื™ ืึท ืžื™ืŸ ืจืขืคึฟืขืจืขื ืฅ ื‘ื•ืš ืคึฟืึทืจ ื–ื™ืš, ืึท ืจืขืคึฟืขืจืขื ืฅ ื‘ื•ืš, ื•ื•ืึธืก ืžืขืŸ ืงืขืŸ ื’ืจื™ื ื’ ื‘ืึทื•ื•ืฒึทื–ืŸ ืื™ืŸ ืงืึธื ื˜ืจืึธื•ื•ืขืจืกื™ืึทืœืข ืคึฟืึทืœืŸ. ื“ืขืจื™ื‘ืขืจ, ืขืก ื•ื•ืขื˜ ื–ื™ื™ืŸ ืจื™ืคึผืœืขื ื™ืฉื˜ ื•ื•ื™ ื ื™ื™ึท ืกืึทืคึผืจื™ื™ื–ื™ื– ืคื•ืŸ ืกืึทืกืคึผื™ืฉืึทืก ื˜ื™ื™ืคึผืก ื–ืขื ืขืŸ ื“ื™ืกืงืึทื•ื•ืขืจื“. ืึทื–ื•ื™, ืœืึธื–ืŸ ืื•ื ื“ื– ื’ื™ื™ืŸ, ื˜ืึทืงืข ื˜ื™ื™ืขืจืœืึทืก ื“ืึทื˜ืึทื‘ื™ื™ืก ื˜ืจืึทืงืขืจื–!

ื“ืึธื–ืข ื ื•ืžืขืจ ืื™ื™ืŸ. ืคืึทืงื˜ื™ืฉ / ื˜ืึธืคึผืœ ืคึผื™ื ื˜ืœืขื›ืงื™ื™ึทื˜ / โ€‹โ€‹ื ื•ืžืขืจื™ืง / ื’ืขืœื˜

ืขืก ื•ื•ืึธืœื˜ ื•ื™ืกืงื•ืžืขืŸ ืึทื– ื ื•ืžืขืจื™ืง ื˜ื™ื™ืคึผืก ื–ืขื ืขืŸ ื“ื™ ืงืœืขื ืกื˜ืขืจ ืคึผืจืึธื‘ืœืขืžืึทื˜ื™ืง ืื™ืŸ ื˜ืขืจืžื™ื ืขืŸ ืคื•ืŸ ืกืึทืคึผืจื™ื™ื–ื™ื– ืื™ืŸ ื ืึทื˜ื•ืจ. ืื‘ืขืจ ืงื™ื™ืŸ ืขื ื™ืŸ ื•ื•ื™ ืขืก ืื™ื–. ืึทื–ื•ื™ ืœืึธื–ืŸ ืก ืึธื ื”ื™ื™ื‘ืŸ ืžื™ื˜ ื–ื™ื™. ืึทื–ื•ื™โ€ฆ

ืคืืจื’ืขืกืŸ ื•ื•ื™ ืฆื• ืฆื™ื™ืœืŸ

SELECT 0.1::real = 0.1

?column?
boolean
---------
f

ื•ื•ืืก ืื™ื– ื“ื™ ืžืขืฉื”? ื“ืขืจ ืคึผืจืึธื‘ืœืขื ืื™ื– ืึทื– PostgreSQL ืงืึทื ื•ื•ืขืจืฅ ื“ื™ ื•ื ื˜ื™ืคึผืขื“ ืงืขืกื™ื™ื“ืขืจื“ื™ืง 0.1 ืฆื• ื˜ืึธืคึผืœ ืคึผื™ื ื˜ืœืขื›ืงื™ื™ึทื˜ ืื•ืŸ ืคืจื•ื•ื•ื˜ ืฆื• ืคืึทืจื’ืœื™ื™ึทื›ืŸ ืขืก ืžื™ื˜ 0.1 ืคื•ืŸ ืคืึทืงื˜ื™ืฉ ื˜ื™ืคึผ. ืื•ืŸ ื“ืึธืก ื–ืขื ืขืŸ ื’ืึธืจ ืคืึทืจืฉื™ื“ืขื ืข ืžื™ื ื™ื ื’ื–! ื“ืขืจ ื’ืขื“ืึทื ืง ืื™ื– ืฆื• ืคืึธืจืฉื˜ืขืœืŸ ืคืึทืงื˜ื™ืฉ ื ื•ืžืขืจืŸ ืื™ืŸ ืžืึทืฉื™ืŸ ื–ื›ึผืจื•ืŸ. ื–ื™ื ื˜ 0.1 ืงืขื ืขืŸ ื ื™ื˜ ื–ื™ื™ืŸ ืจืขืคึผืจื™ื–ืขื ื˜ื™ื“ ื•ื•ื™ ืึท ืขื ื“ืœืขืš ื‘ื™ื™ื ืขืจื™ ื‘ืจืึธื›ืฆืึธืœ (ืขืก ื•ื•ืึธืœื˜ ื–ื™ื™ืŸ 0.0 (0011) ืื™ืŸ ื‘ื™ื™ื ืขืจื™), ื ื•ืžืขืจืŸ ืžื™ื˜ ืคืึทืจืฉื™ื“ืขื ืข ื‘ื™ืกืœ ื˜ื™ืคืขื ื™ืฉืŸ ื•ื•ืขื˜ ื–ื™ื™ืŸ ืึทื ื“ืขืจืฉ, ื“ืขืจ ืจืขื–ื•ืœื˜ืึทื˜ ืึทื– ื–ื™ื™ ื–ืขื ืขืŸ ื ื™ืฉื˜ ื’ืœื™ื™ึทืš. ืื™ืŸ ืึทืœื’ืขืžื™ื™ืŸ, ื“ืึธืก ืื™ื– ืึท ื˜ืขืžืข ืคึฟืึทืจ ืึท ื‘ืึทื–ื•ื ื“ืขืจ ืึทืจื˜ื™ืงืœ, ืื™ืš ื•ื•ืขืœ ื ื™ืฉื˜ ืฉืจื™ื™ึทื‘ืŸ ืžืขืจ ื“ืขื˜ืึทืœ ื“ืึธ.

ืคึฟื•ืŸ ื•ื•ืึทื ืขืŸ ืงื•ืžื˜ ื“ืขืจ ื˜ืขื•ืช?

SELECT double precision(1)

ERROR:  syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** ะžัˆะธะฑะบะฐ **********
ERROR: syntax error at or near "("
SQL-ัะพัั‚ะพัะฝะธะต: 42601
ะกะธะผะฒะพะป: 24

ืคื™ืœืข ืžืขื ื˜ืฉืŸ ื•ื•ื™ืกืŸ ืึทื– PostgreSQL ืึทืœืึทื•ื– ืคืึทื ื’ืงืฉืึทื ืึทืœ ื ืึธื•ื˜ื™ื™ืฉืึทืŸ ืคึฟืึทืจ ื˜ื™ืคึผ ืงืึทืกื˜ื™ื ื’. ืึทื– ืื™ื–, ืื™ืจ ืงืขื ืขืŸ ืฉืจื™ื™ึทื‘ืŸ ื ื™ื˜ ื‘ืœื•ื™ื– 1::ื™ื ื˜, ืึธื‘ืขืจ ืื•ื™ืš ื™ื ื˜ (1), ื•ื•ืึธืก ื•ื•ืขื˜ ื–ื™ื™ืŸ ืขืงื•ื•ื™ื•ื•ืึทืœืขื ื˜. ืื‘ืขืจ ื ื™ืฉื˜ ืคืืจ ื˜ื™ืคืŸ ื•ื•ืขืžืขื ืก ื ืขืžืขืŸ ื‘ืืฉื˜ื™ื™ื˜ ืคื•ืŸ ืขื˜ืœื™ื›ืข ื•ื•ืขืจื˜ืขืจ! ื“ืขืจื™ื‘ืขืจ, ืื•ื™ื‘ ืื™ืจ ื•ื•ื™ืœืŸ ืฆื• ื•ื•ืึทืจืคืŸ ืึท ื ื•ืžืขืจื™ืง ื•ื•ืขืจื˜ ืฆื• ื˜ืึธืคึผืœ ืคึผื™ื ื˜ืœืขื›ืงื™ื™ึทื˜ ื˜ื™ืคึผ ืื™ืŸ ืคืึทื ื’ืงืฉืึทื ืึทืœ ืคืึธืจืขื, ื ื•ืฆืŸ ื“ื™ ืึทืœื™ืึทืก ืคื•ืŸ ื“ืขื ื˜ื™ืคึผ float8, ื“ืึธืก ืื™ื– SELECT float8 (1).

ื•ื•ืึธืก ืื™ื– ื’ืจืขืกืขืจ ื•ื•ื™ ื“ื™ ื•ืžืขื ื“ื™ืงื™ื™ึทื˜?

SELECT 'Infinity'::double precision < 'NaN'::double precision

?column?
boolean
---------
t

ืงื•ืง ื•ื•ื™ ืขืก ืื™ื–! ืขืก ื˜ื•ืจื ืก ืื•ื™ืก ืึทื– ืขืก ืื™ื– ืขืคึผืขืก ื‘ื™ื’ืขืจ ื•ื•ื™ ื•ืžืขื ื“ื™ืงื™ื™ึทื˜, ืื•ืŸ ืขืก ืื™ื– NaN! ืื™ืŸ ื“ืขืจ ื–ืขืœื‘ื™ืงืขืจ ืฆื™ื™ื˜, ื“ื™ PostgreSQL ื“ืึทืงื™ื•ืžืขื ื˜ื™ื™ืฉืึทืŸ ืงื•ืงื˜ ืื•ื™ืฃ ืื•ื ื“ื– ืžื™ื˜ ืขืจืœืขืš ืื•ื™ื’ืŸ ืื•ืŸ ืงืœื™ื™ืžื– ืึทื– NaN ืื™ื– ื“ืึธืš ื’ืจืขืกืขืจ ื•ื•ื™ ืงื™ื™ืŸ ืื ื“ืขืจืข ื ื•ืžืขืจ, ืื•ืŸ, ื“ืขืจื™ื‘ืขืจ, ื•ืžืขื ื“ื™ืงื™ื™ึทื˜. ื“ืขืจ ืคืึทืจืงืขืจื˜ ืื™ื– ืื•ื™ืš ืืžืช ืคึฟืึทืจ -ื ืึทืŸ. ื”ืขืœื, ืžืื˜ืขืžืื˜ื™ืง ืœื™ื‘ื”ืื‘ืขืจืก! ืื‘ืขืจ ืžื™ืจ ืžื•ื–ืŸ ื’ืขื“ืขื ืงืขืŸ ืึทื– ืึทืœืข ื“ืขื ืึทืคึผืขืจื™ื™ืฅ ืื™ืŸ ื“ืขื ืงืึธื ื˜ืขืงืกื˜ ืคื•ืŸ ืคืึทืงื˜ื™ืฉ ื ื•ืžืขืจืŸ.

ืื•ื™ื’ ืจืึทื•ื ื“ื™ื ื’

SELECT round('2.5'::double precision)
     , round('2.5'::numeric)

      round      |  round
double precision | numeric
-----------------+---------
2                | 3

ืืŸ ืื ื“ืขืจ ืื•ืžื’ืขืจื™ื›ื˜ ื’ืจื•ืก ืคื•ืŸ ื“ืขืจ ื‘ืึทื–ืข. ื•ื•ื™ื“ืขืจ, ื’ืขื“ืขื ืงืขืŸ ืึทื– ื˜ืึธืคึผืœ ืคึผื™ื ื˜ืœืขื›ืงื™ื™ึทื˜ ืื•ืŸ ื ื•ืžืขืจื™ืง ื˜ื™ื™ืคึผืก ื”ืึธื‘ืŸ ืคืึทืจืฉื™ื“ืขื ืข ืจืึทื•ื ื“ื™ื ื’ ื™ืคืขืงืฅ. ืคึฟืึทืจ ื ื•ืžืขืจื™ืง - ื“ื™ ื’ืขื•ื•ื™ื™ื ื˜ืœืขืš ืื™ื™ื ืขืจ, ื•ื•ืขืŸ 0,5 ืื™ื– ืจืึทื•ื ื“ื™ื“ ืึทืจื•ื™ืฃ, ืื•ืŸ ืคึฟืึทืจ ื˜ืึธืคึผืœ ืคึผื™ื ื˜ืœืขื›ืงื™ื™ึทื˜ - 0,5 ืื™ื– ืจืึทื•ื ื“ื™ื“ ืฆื• ื“ื™ ื ื™ืจืึทืกื˜ ืืคื™ืœื• ื™ื ื˜ืึทื“ื–ืฉืขืจ.

ื’ืขืœื˜ ืื™ื– ืขืคึผืขืก ืกืคึผืขืฆื™ืขืœ

SELECT '10'::money::float8

ERROR:  cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** ะžัˆะธะฑะบะฐ **********
ERROR: cannot cast type money to double precision
SQL-ัะพัั‚ะพัะฝะธะต: 42846
ะกะธะผะฒะพะป: 19

ืœื•ื™ื˜ PostgreSQL, ื’ืขืœื˜ ืื™ื– ื ื™ืฉื˜ ืึท ืคืึทืงื˜ื™ืฉ ื ื•ืžืขืจ. ืœื•ื™ื˜ ืขื˜ืœืขื›ืข ื™ื—ื™ื“ื™ื ืื•ื™ืš. ืžื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ื’ืขื“ืขื ืงืขืŸ ืึทื– ืงืึทืกื˜ื™ื ื’ ื“ื™ ื’ืขืœื˜ ื˜ื™ืคึผ ืื™ื– ื‘ืœื•ื™ื– ืžืขื’ืœืขืš ืฆื• ื“ื™ ื ื•ืžืขืจื™ืง ื˜ื™ืคึผ, ืคึผื•ื ืงื˜ ื•ื•ื™ ื‘ืœื•ื™ื– ื“ื™ ื ื•ืžืขืจื™ืง ื˜ื™ืคึผ ืงืขื ืขืŸ ื–ื™ื™ืŸ ื•ื•ืึทืจืคืŸ ืฆื• ื“ื™ ื’ืขืœื˜ ื˜ื™ืคึผ. ืึธื‘ืขืจ ืื™ืฆื˜ ืื™ืจ ืงืขื ืขืŸ ืฉืคึผื™ืœืŸ ืžื™ื˜ ืื™ื ื•ื•ื™ ื“ื™ื™ืŸ ื”ืึทืจืฅ ื•ื•ื™ืœ. ืื‘ืขืจ ืขืก ื•ื•ืขื˜ ื ื™ืฉื˜ ื–ื™ื™ืŸ ื“ื™ ื–ืขืœื‘ืข ื’ืขืœื˜.

ืกืžืึธืœื™ื ื˜ ืื•ืŸ ืกื™ืงื•ื•ืึทื ืก ื“ื•ืจ

SELECT *
  FROM generate_series(1::smallint, 5::smallint, 1::smallint)

ERROR:  function generate_series(smallint, smallint, smallint) is not unique
LINE 2:   FROM generate_series(1::smallint, 5::smallint, 1::smallint...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
********** ะžัˆะธะฑะบะฐ **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-ัะพัั‚ะพัะฝะธะต: 42725
ะŸะพะดัะบะฐะทะบะฐ: Could not choose a best candidate function. You might need to add explicit type casts.
ะกะธะผะฒะพะป: 18

PostgreSQL ื˜ื•ื˜ ื ื™ืฉื˜ ื•ื•ื™ ืฆื• ื•ื•ื™ืกื˜ ืฆื™ื™ื˜ ืื•ื™ืฃ ืงืœื™ื™ื ื™ืงื™ื™ื˜ืŸ. ื•ื•ืึธืก ื–ืขื ืขืŸ ื“ื™ ืกื™ืงื•ื•ืึทื ืกื™ื– ื‘ืื–ื™ืจื˜ ืื•ื™ืฃ ืงืœื™ื™ืŸ? ื™ื ื˜, ื ื™ื˜ ื•ื•ื™ื™ื ื™ืงืขืจ! ื“ืขืจื™ื‘ืขืจ, ื•ื•ืขืŸ ืื™ืจ ืคึผืจื•ึผื•ื•ื˜ ืฆื• ื•ื™ืกืคื™ืจืŸ ื“ื™ ืื•ื™ื‘ืŸ ืึธื ืคึฟืจืขื’, ื“ื™ ื“ืึทื˜ืึทื‘ืึทืกืข ืคืจื•ื•ื•ื˜ ืฆื• ื•ื•ืึทืจืคืŸ ืกืžืึทืจื˜ื™ื ื˜ ืฆื• ืขื˜ืœืขื›ืข ืื ื“ืขืจืข ื™ื ื˜ืึทื“ื–ืฉืขืจ ื˜ื™ืคึผ, ืื•ืŸ ื–ืขื˜ ืึทื– ืขืก ืงืขืŸ ื–ื™ื™ืŸ ืขื˜ืœืขื›ืข ืึทื–ืึท ืงืึทืกืฅ. ื•ื•ืึธืก ื•ื•ืึทืจืคืŸ ืฆื• ืงืœื™ื™ึทื‘ืŸ? ื–ื™ ืงืขืŸ ื“ืึธืก ื ื™ืฉื˜ ื‘ืึทืฉืœื™ืกืŸ, ืื•ืŸ ื“ืขืจื™ื‘ืขืจ ืงืจืึทืฉื™ื– ืžื™ื˜ ืึท ื˜ืขื•ืช.

ื˜ืขืงืข ื ื•ืžืขืจ ืฆื•ื•ื™ื™. "char"/char/varchar/text

ื ื ื•ืžืขืจ ืคื•ืŸ ืึทื“ืึทื˜ื™ื– ื–ืขื ืขืŸ ืื•ื™ืš ืคืึธืจืฉื˜ืขืœืŸ ืื™ืŸ ื›ืึทืจืึทืงื˜ืขืจ ื˜ื™ื™ืคึผืก. ืœืืžื™ืจ ื–ื™ื™ ืื•ื™ืš ื‘ืืงืขื ืขืŸ.

ื•ื•ืึธืก ืžื™ืŸ ืคื•ืŸ ื˜ืจื™ืงืก ื–ืขื ืขืŸ ื“ืึธืก?

SELECT 'ะŸะ•ะขะฏ'::"char"
     , 'ะŸะ•ะขะฏ'::"char"::bytea
     , 'ะŸะ•ะขะฏ'::char
     , 'ะŸะ•ะขะฏ'::char::bytea

 char  | bytea |    bpchar    | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
 โ•จ     | xd0  | ะŸ            | xd09f

ื•ื•ืึธืก ืคึฟืึทืจ ืึท "ื˜ืฉืึทืจ" ืื™ื– ื“ืึธืก, ื•ื•ืึธืก ืคึฟืึทืจ ืึท ื‘ืœืึทื–ืŸ ืื™ื– ื“ืึธืก? ืžื™ืจ ื˜ืึธืŸ ื ื™ื˜ ื“ืึทืจืคึฟืŸ ื“ื™ ... ื•ื•ื™ื™ึทืœ ืขืก ืคึผืจื™ื˜ืขื ื“ื– ืฆื• ื–ื™ื™ืŸ ืึท ืคึผืจืึธืกื˜ ื˜ืฉืึทืจ, ื›ืึธื˜ืฉ ืขืก ืื™ื– ืื™ืŸ ืฆื™ื˜ื™ืจื˜. ืื•ืŸ ืขืก ืื™ื– ืื•ื ื˜ืขืจืฉื™ื™ื“ืŸ ืคื•ืŸ ืึท ืจืขื’ื•ืœืขืจ ื˜ืฉืึทืจ, ื•ื•ืึธืก ืื™ื– ืึธืŸ ืฆื™ื˜ื™ืจื˜, ืื™ืŸ ืึทื– ืขืก ืึทื•ื˜ืคึผื•ืฅ ื‘ืœื•ื™ื– ื“ืขืจ ืขืจืฉื˜ืขืจ ื‘ื™ื™ื˜ ืคื•ืŸ ื“ื™ ืฉื˜ืจื™ืงืœ ืคืึทืจื˜ืจืขื˜ื•ื ื’, ื‘ืฉืขืช ืึท ื ืึธืจืžืึทืœ ื˜ืฉืึทืจ ืึทื•ื˜ืคึผื•ืฅ ื“ืขืจ ืขืจืฉื˜ืขืจ ื›ืึทืจืึทืงื˜ืขืจ. ืื™ืŸ ืื•ื ื“ื–ืขืจ ืคืึทืœ, ื“ืขืจ ืขืจืฉื˜ืขืจ ื›ืึทืจืึทืงื˜ืขืจ ืื™ื– ื“ืขืจ ื‘ืจื™ื•ื• ืคึผ, ื•ื•ืึธืก ืื™ืŸ ื“ื™ ืื•ื ื™ืงืึธื“ ืคืึทืจื˜ืจืขื˜ื•ื ื’ ื ืขืžื˜ 2 ื‘ื™ื˜ืขืก, ื•ื•ื™ ืขื•ื•ื™ื“ืึทื ืกื˜ ื“ื•ืจืš ืงืึทื ื•ื•ืขืจื˜ื™ื ื’ ื“ืขืจ ืจืขื–ื•ืœื˜ืึทื˜ ืฆื• ื“ื™ ื‘ื™ื™ื˜ื™ ื˜ื™ืคึผ. ืื•ืŸ ื“ื™ "ื˜ืฉืึทืจ" ื˜ื™ืคึผ ื ืขืžื˜ ื‘ืœื•ื™ื– ื“ืขืจ ืขืจืฉื˜ืขืจ ื‘ื™ื™ื˜ ืคื•ืŸ ื“ืขื ืื•ื ื™ืงืึธื“ ืคืึทืจื˜ืจืขื˜ื•ื ื’. ื“ืขืžืึธืœื˜ ื•ื•ืึธืก ืื™ื– ื“ืขื ื˜ื™ืคึผ ื“ืืจืฃ? ื“ื™ PostgreSQL ื“ืึทืงื™ื•ืžืขื ื˜ื™ื™ืฉืึทืŸ ื–ืื’ื˜ ืึทื– ื“ืึธืก ืื™ื– ืึท ืกืคึผืขืฆื™ืขืœ ื˜ื™ืคึผ ื’ืขื ื™ืฆื˜ ืคึฟืึทืจ ืกืคึผืขืฆื™ืขืœ ื‘ืื“ืขืจืคืขื ื™ืฉืŸ. ืึทื–ื•ื™ ืžื™ืจ ื–ืขื ืขืŸ ืึทื ืœื™ื™ืงืœื™ ืฆื• ื“ืึทืจืคึฟืŸ ืขืก. ืึธื‘ืขืจ ืงื•ืง ืื™ื ืึทืจืฒึทืŸ ืื™ืŸ ื“ื™ ืื•ื™ื’ืŸ, ืื•ืŸ ืื™ืจ ื•ื•ืขื˜ ื ื™ืฉื˜ ืžืึทื›ืŸ ืงื™ื™ืŸ ื˜ืขื•ืช ื•ื•ืขืŸ ืื™ืจ ื˜ืจืขืคืŸ ืื™ื ืžื™ื˜ ื–ื™ื™ืŸ ืกืคึผืขืฆื™ืขืœ ื ืึทื˜ื•ืจ.

ืขืงืกื˜ืจืข ืกืคึผื™ื™ืกืึทื–. ืื•ื™ืก ืคื•ืŸ ื“ืขืจื–ืขืŸ, ืื•ื™ืก ืคื•ืŸ ืžื™ื™ื ื•ื ื’

SELECT 'abc   '::char(6)::bytea
     , 'abc   '::char(6)::varchar(6)::bytea
     , 'abc   '::varchar(6)::bytea

     bytea     |   bytea  |     bytea
     bytea     |   bytea  |     bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020

ื ืขืžืขืŸ ืึท ืงื•ืง ืื™ืŸ ื“ืขื ื‘ื™ื™ึทืฉืคึผื™ืœ ื’ืขื’ืขื‘ืŸ. ืื™ืš ืกืคึผืขืฉืœื™ ืงืึธื ื•ื•ืขืจื˜ืขื“ ืึทืœืข ื“ื™ ืจืขื–ื•ืœื˜ืึทื˜ืŸ ืฆื• ื“ื™ ื‘ื™ื™ื˜ื™ ื˜ื™ืคึผ, ืึทื–ื•ื™ ืึทื– ืขืก ืื™ื– ืงืœืืจ ืงืขื ื˜ื™ืง ื•ื•ืึธืก ืื™ื– ื“ืึธืจื˜. ื•ื•ื• ื–ืขื ืขืŸ ื“ื™ ื˜ืจื™ื™ืœื™ื ื’ ืกืคึผื™ื™ืกืึทื– ื ืึธืš ืงืึทืกื˜ื™ื ื’ ืฆื• ื•ื•ืึทืจื˜ืฉืึทืจ (6)? ื“ื™ ื“ืึทืงื™ื•ืžืขื ื˜ื™ื™ืฉืึทืŸ ืกืึทืงืกื™ื ืงื˜ืœื™ ื–ืื’ื˜: "ื•ื•ืขืŸ ืงืึทืกื˜ื™ื ื’ ื“ื™ ื•ื•ืขืจื˜ ืคื•ืŸ ื›ืึทืจืึทืงื˜ืขืจ ืฆื• ืืŸ ืื ื“ืขืจ ื›ืึทืจืึทืงื˜ืขืจ ื˜ื™ืคึผ, ื˜ืจื™ื™ืœื™ื ื’ ื•ื•ื™ื™ื˜ืกืคึผื™ื™ืก ืื™ื– ืึทื•ื•ืขืงื’ืขื ื•ืžืขืŸ." ืžืขืŸ ื“ืึทืจืฃ ื’ืขื“ืขื ืงืขืŸ ื“ืขื ื ื™ื˜ ืœื™ื‘ืฉืึทืคื˜. ืื•ืŸ ื˜ืึธืŸ ืึทื– ืื•ื™ื‘ ืึท ืฆื™ื˜ื™ืจื˜ืŸ ืฉื˜ืจื™ืงืœ ืงืขืกื™ื™ื“ืขืจื“ื™ืง ืื™ื– ื•ื•ืึทืจืคืŸ ื’ืœื™ื™ึทืš ืฆื• ื˜ื™ืคึผ ื•ื•ืึทืจื˜ืฉืึทืจ (6), ื“ื™ ื˜ืจื™ื™ืœื™ื ื’ ืกืคึผื™ื™ืกืึทื– ื–ืขื ืขืŸ ืืคื’ืขื”ื™ื˜. ืึทื–ืขืœื›ืข ื–ืขื ืขืŸ ื“ื™ ื ืกื™ื.

ื˜ืขืงืข ื ื•ืžืขืจ ื“ืจื™ื™. json/jsonb

JSON ืื™ื– ืึท ื‘ืึทื–ื•ื ื“ืขืจ ืกื˜ืจื•ืงื˜ื•ืจ ื•ื•ืึธืก ืœืขื‘ื˜ ื–ื™ื™ืŸ ืื™ื™ื’ืขื ืข ืœืขื‘ืŸ. ื“ืขืจื™ื‘ืขืจ, ื“ื™ ืขื ื˜ื™ื˜ื™ื– ืื•ืŸ ื“ื™ ืคื•ืŸ PostgreSQL ื–ืขื ืขืŸ ืึท ื‘ื™ืกืœ ืึทื ื“ืขืจืฉ. ื“ืึธ ื–ืขื ืขืŸ ื‘ื™ื™ืฉืคื™ืœืŸ.

ื–ืฉืื ืกืืŸ ืื•ืŸ ื–ืฉืื ืกืืŸ. ืคื™ืœืŸ ื“ื™ ื—ื™ืœื•ืง

SELECT 'null'::jsonb IS NULL

?column?
boolean
---------
f

ื“ื™ ื–ืึทืš ืื™ื– ืึทื– JSON ื”ืื˜ ื–ื™ื™ืŸ ืื™ื™ื’ืขื ืข ื ืึทืœ ืขื ื˜ื™ื˜ื™, ื•ื•ืึธืก ืื™ื– ื ื™ืฉื˜ ื“ืขืจ ืึทื ืึทืœืึธื’ ืคื•ืŸ NULL ืื™ืŸ PostgreSQL. ืื™ืŸ ื“ืขืจ ื–ืขืœื‘ื™ืงืขืจ ืฆื™ื™ื˜, ื“ื™ JSON ื›ื™ื™ืคืขืฅ ื–ื™ืš ืงืขืŸ ื”ืึธื‘ืŸ ื“ื™ ื•ื•ืขืจื˜ NULL, ืึทื–ื•ื™ ื“ืขืจ ืื•ื™ืกื“ืจื•ืง SELECT null :: jsonb IS NULL (ื˜ืึธืŸ ื“ื™ ืคืขืœืŸ ืคื•ืŸ ืื™ื™ืŸ ืฆื™ื˜ื™ืจื˜) ื•ื•ืขื˜ ืฆื•ืจื™ืงืงื•ืžืขืŸ ืืžืช ื“ืึธืก ืžืึธืœ.

ืื™ื™ืŸ ื‘ืจื™ื•ื• ืขื ื“ืขืจื˜ ืึทืœืฅ

SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::json

                     json
                     json
------------------------------------------------
{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}

---

SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::jsonb

             jsonb
             jsonb
--------------------------------
{"1": [7, 8, 9], "2": [4, 5, 6]}

ื“ื™ ื–ืึทืš ืื™ื– ืึทื– json ืื•ืŸ jsonb ื–ืขื ืขืŸ ื’ืึธืจ ืคืึทืจืฉื™ื“ืขื ืข ืกื˜ืจืึทืงื˜ืฉืขืจื–. ืื™ืŸ json, ื“ื™ ื›ื™ื™ืคืขืฅ ืื™ื– ืกื˜ืึธืจื“ ื•ื•ื™ ืื™ื–, ืื•ืŸ ืื™ืŸ jsonb ืขืก ืื™ื– ืฉื•ื™ืŸ ืกื˜ืึธืจื“ ืื™ืŸ ื“ื™ ืคืึธืจืขื ืคื•ืŸ ืึท ืคึผืึทืจืกืขื“, ื™ื ื“ืขืงืกื˜ ืกื˜ืจื•ืงื˜ื•ืจ. ื“ืขืจืคึฟืึทืจ ืื™ื– ืื™ืŸ ื“ื™ ืฆื•ื•ื™ื™ื˜ืข ืคืึทืœ ื“ื™ ื•ื•ืขืจื˜ ืคื•ืŸ ื“ื™ ื›ื™ื™ืคืขืฅ ื“ื•ืจืš ืฉืœื™ืกืœ 1 ืจื™ืคึผืœื™ื™ืกื˜ ืคื•ืŸ [1, 2, 3] ืฆื• [7, 8, 9], ื•ื•ืึธืก ืื™ื– ื’ืขืงื•ืžืขืŸ ืื™ืŸ ื“ื™ ืกื˜ืจื•ืงื˜ื•ืจ ืื™ืŸ ื“ื™ ืกื•ืฃ ืžื™ื˜ ื“ื™ ื–ืขืœื‘ืข ืฉืœื™ืกืœ.

ื ื™ื˜ ื˜ืจื™ื ืงืขืŸ ื•ื•ืึทืกืขืจ ืคื•ืŸ ื“ื™ื™ืŸ ืคึผื ื™ื

SELECT '{"reading": 1.230e-5}'::jsonb
     , '{"reading": 1.230e-5}'::json

          jsonb         |         json
          jsonb         |         json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}

PostgreSQL ืื™ืŸ ื–ื™ื™ืŸ JSONB ื™ืžืคึผืœืึทืžืขื ื˜ื™ื™ืฉืึทืŸ ืขื ื“ืขืจื•ื ื’ืขืŸ ื“ื™ ืคืึธืจืžืึทื˜ื˜ื™ื ื’ ืคื•ืŸ ืคืึทืงื˜ื™ืฉ ื ื•ืžืขืจืŸ, ื‘ืจืขื ื’ืขืŸ ื–ื™ื™ ืฆื• ื“ื™ ืงืœืืกื™ืฉืข ืคืึธืจืขื. ื“ืึธืก ืงืขืŸ ื ื™ืฉื˜ ืคึผืึทืกื™ืจืŸ ืคึฟืึทืจ ื“ื™ JSON ื˜ื™ืคึผ. ื ื‘ื™ืกืœ ืžืึธื“ื ืข, ืึธื‘ืขืจ ืขืจ ืื™ื– ืจืขื›ื˜.

ื˜ืขืงืข ื ื•ืžืขืจ ืคื™ืจ. ื“ืึทื˜ืข / ืฆื™ื™ื˜ / ืฆื™ื™ื˜ ืฉื˜ืขืžืคึผืœ

ืขืก ื–ืขื ืขืŸ ืื•ื™ืš ืขื˜ืœืขื›ืข ืึทื“ืึทื˜ื™ื– ืžื™ื˜ ื“ืึทื˜ืข / ืฆื™ื™ื˜ ื˜ื™ื™ืคึผืก. ื–ืืœ ืก ืงื•ืง ื‘ื™ื™ึท ื–ื™ื™. ืœืึธื–ืŸ ืžื™ืจ ืžืึทื›ืŸ ืึท ืจืขื–ืขืจื•ื•ืึทืฆื™ืข ื’ืœื™ื™ืš ืึทื– ืขื˜ืœืขื›ืข ืคื•ืŸ โ€‹โ€‹โ€‹โ€‹ื“ื™ ื ืึทื˜ื•ืจืึทืœ ืคึฟืขื™ึดืงื™ื™ื˜ืŸ ื•ื•ืขืจืŸ ืงืœืึธืจ ืื•ื™ื‘ ืื™ืจ ืคึฟืึทืจืฉื˜ื™ื™ืŸ ื“ื™ ืขืกืึทื ืก ืคื•ืŸ ืืจื‘ืขื˜ืŸ ืžื™ื˜ ืฆื™ื™ื˜ ื–ืึธื ืขืก. ืื‘ืขืจ ื“ืึธืก ืื™ื– ืื•ื™ืš ืึท ื˜ืขืžืข ืคึฟืึทืจ ืึท ื‘ืึทื–ื•ื ื“ืขืจ ืึทืจื˜ื™ืงืœ.

ืžื™ื™ึทืŸ ื“ื™ื™ื ืก ื˜ืึธืŸ ื ื™ื˜ ืคึฟืึทืจืฉื˜ื™ื™ืŸ

SELECT '08-Jan-99'::date

ERROR:  date/time field value out of range: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
               ^
HINT:  Perhaps you need a different "datestyle" setting.
********** ะžัˆะธะฑะบะฐ **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL-ัะพัั‚ะพัะฝะธะต: 22008
ะŸะพะดัะบะฐะทะบะฐ: Perhaps you need a different "datestyle" setting.
ะกะธะผะฒะพะป: 8

ืขืก ื•ื•ืึธืœื˜ ื•ื™ืกืงื•ืžืขืŸ ืึทื– ื•ื•ืึธืก ืื™ื– ืื•ืžื‘ืึทืงืึทื ื˜ ื“ืึธ? ืื‘ืขืจ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ื ืึธืš ื˜ื•ื˜ ื ื™ืฉื˜ ืคึฟืึทืจืฉื˜ื™ื™ืŸ ื•ื•ืึธืก ืžื™ืจ ืฉื˜ืขืœืŸ ืื™ืŸ ืขืจืฉื˜ืขืจ ืึธืจื˜ ื“ืึธ - ื“ื™ ื™ืึธืจ ืึธื“ืขืจ ื“ื™ ื˜ืึธื’? ืื•ืŸ ื–ื™ ื‘ืึทืฉืœืึธืกืŸ ืึทื– ืขืก ืื™ื– 99 ื™ืื ื•ืืจ 2008, ื•ื•ืึธืก ื‘ืœืึธื•ื– ืื™ืจ ืžื™ื™ื ื•ื ื’. ืื™ืŸ ืึทืœื’ืขืžื™ื™ืŸ, ื•ื•ืขืŸ ื˜ืจืึทื ืกืžื™ื˜ื™ื ื’ ื“ืึทื˜ืขืก ืื™ืŸ ื˜ืขืงืกื˜ ืคึฟืึธืจืžืึทื˜, ืื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ื–ื™ื™ืขืจ ืงืขืจืคืึทืœื™ ืงืึธื ื˜ืจืึธืœื™ืจืŸ ื•ื•ื™ ืจื™ื›ื˜ื™ืง ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ื“ืขืจืงืขื ื˜ ื–ื™ื™ (ืกืคึผืขืฆื™ืขืœ ืคื•ื ืึทื ื“ืขืจืงืœื™ื™ึทื‘ืŸ ื“ื™ ื“ืึทื˜ืขืกื˜ื™ื™ืœ ืคึผืึทืจืึทืžืขื˜ืขืจ ืžื™ื˜ ื“ื™ SHOW datesstyle ื‘ืึทืคึฟืขืœ), ื•ื•ื™ื™ึทืœ ืึทืžื‘ื™ื’ื™ื•ืึทื˜ื™ื– ืื™ืŸ ื“ืขื ืขื ื™ืŸ ืงืขื ืขืŸ ื–ื™ื™ืŸ ื–ื™ื™ืขืจ ื˜ื™ื™ึทืขืจ.

ืคื•ืŸ ื•ื•ืื• ื”ืืกื˜ื• ื“ืืก ื‘ืืงื•ืžืขืŸ?

SELECT '04:05 Europe/Moscow'::time

ERROR:  invalid input syntax for type time: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
               ^
********** ะžัˆะธะฑะบะฐ **********
ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"
SQL-ัะพัั‚ะพัะฝะธะต: 22007
ะกะธะผะฒะพะป: 8

ืคืืจื•ื•ืืก ืงืขืŸ ื“ื™ ื“ืึทื˜ืึทื‘ื™ื™ืก ื ื™ืฉื˜ ืคึฟืึทืจืฉื˜ื™ื™ืŸ ื“ื™ ื‘ืคื™ืจื•ืฉ ืกืคึผืขืกื™ืคื™ืขื“ ืฆื™ื™ื˜? ื•ื•ื™ื™ืœ ื“ื™ ืฆื™ื™ื˜ ื–ืื ืข ื”ืื˜ ื ื™ืฉื˜ ืงื™ื™ืŸ ืื‘ืจื™ื•ื•ื™ืืฆื™ืข, ื ืืจ ื ืคื•ืœืขืจ ื ืืžืขืŸ, ื•ื•ืืก ืžืื›ื˜ ื–ื™ื ืขืŸ ื ืืจ ืื™ืŸ ื“ืขื ืงืื ื˜ืขืงืกื˜ ืคื•ืŸ ื ื“ืื˜ื•ื, ื•ื•ื™ื‘ืืœื“ ืขืก ื ืขืžื˜ ืื™ืŸ ื—ืฉื‘ื•ืŸ ื“ื™ ื”ื™ืกื˜ืืจื™ืข ืคื•ืŸ โ€‹โ€‹ืฆื™ื™ื˜ ื–ืื ืข ืขื ื“ืขืจื•ื ื’ืขืŸ, ืื•ืŸ ืขืก ืืจื‘ืขื˜ ื ื™ืฉื˜ ืืŸ ื ื“ืื˜ื•ื. ืื•ืŸ ื“ืขืจ ืขืฆื ื ื•ืกื— ืคื•ืŸ ื“ื™ ืฆื™ื™ื˜-ืœื™ื ื™ืข ืจื™ื™ื–ืึทื– ืคืจืื’ืขืก - ื•ื•ืึธืก ื”ืื˜ ื“ืขืจ ืคึผืจืึธื’ืจืึทืžื™ืกื˜ ื˜ืึทืงืข ืžื™ื™ื ืขืŸ? ื“ืขืจื™ื‘ืขืจ, ืึทืœืฅ ืื™ื– ืœืึทื“ื–ืฉื™ืงืึทืœ ื“ืึธ, ืื•ื™ื‘ ืื™ืจ ืงื•ืง ืื™ืŸ ืขืก.

ื•ื•ืืก ืื™ื– ืžื™ื˜ ืื™ื?

ื™ืžืึทื“ื–ืฉืึทืŸ ื“ื™ ืกื™ื˜ื•ืึทืฆื™ืข. ืื™ืจ ื”ืึธื‘ืŸ ืึท ืคืขืœื“ ืื™ืŸ ื“ื™ื™ืŸ ื˜ื™ืฉ ืžื™ื˜ ื˜ื™ืคึผ Timestamptz. ืื™ืจ ื•ื•ื™ืœืŸ ืฆื• ืื™ื ื“ืขืงืก ืขืก. ืึธื‘ืขืจ ืื™ืจ ืคึฟืึทืจืฉื˜ื™ื™ืŸ ืึทื– ื“ื™ ืงืึทื ืกื˜ืจืึทืงืฉืึทืŸ ืคื•ืŸ ืึทืŸ ืื™ื ื“ืขืงืก ืื•ื™ืฃ ื“ืขื ืคืขืœื“ ืื™ื– ื ื™ื˜ ืฉื˜ืขื ื“ื™ืง ื’ืขืจืขื›ื˜ืคืืจื˜ื™ืงื˜ ื•ื•ื™ื™ึทืœ ืคื•ืŸ ื–ื™ื™ึทืŸ ื”ื•ื™ืš ืกืขืœืขืงื˜ื™ื•ื•ื™ื˜ื™ (ื›ึผืžืขื˜ ืึทืœืข ื•ื•ืึทืœื•ืขืก ืคื•ืŸ ื“ืขื ื˜ื™ืคึผ ื•ื•ืขื˜ ื–ื™ื™ืŸ ื™ื™ื ืฆื™ืง). ืึทื–ื•ื™ ืื™ืจ ื‘ืึทืฉืœื™ืกืŸ ืฆื• ืจืขื“ื•ืฆื™ืจืŸ ื“ื™ ืกืขืœืขืงื˜ื™ื•ื•ื™ื˜ื™ ืคื•ืŸ ื“ื™ ืื™ื ื“ืขืงืก ื“ื•ืจืš ืงืึทืกื˜ื™ื ื’ ื“ืขื ื˜ื™ืคึผ ืฆื• ืึท ื“ืึทื˜ืข. ืื•ืŸ ืื™ืจ ื‘ืึทืงื•ืžืขืŸ ืึท ื™ื‘ืขืจืจืึทืฉืŸ:

CREATE INDEX "iIdent-DateLastUpdate"
  ON public."Ident" USING btree
  (("DTLastUpdate"::date));

ERROR:  functions in index expression must be marked IMMUTABLE
********** ะžัˆะธะฑะบะฐ **********
ERROR: functions in index expression must be marked IMMUTABLE
SQL-ัะพัั‚ะพัะฝะธะต: 42P17

ื•ื•ืืก ืื™ื– ื“ื™ ืžืขืฉื”? ื“ืขืจ ืคืึทืงื˜ ืื™ื– ืึทื– ืฆื• ื•ื•ืึทืจืคืŸ ืึท ื˜ื™ื™ืžืกื˜ืึทืžืคึผื˜ื– ื˜ื™ืคึผ ืฆื• ืึท ื“ืึทื˜ืข ื˜ื™ืคึผ, ื“ื™ ื•ื•ืขืจื˜ ืคื•ืŸ ื“ื™ TimeZone ืกื™ืกื˜ืขื ืคึผืึทืจืึทืžืขื˜ืขืจ ืื™ื– ื’ืขื ื™ืฆื˜, ื•ื•ืึธืก ืžืื›ื˜ ื“ื™ ื˜ื™ืคึผ ืงืึทื ื•ื•ืขืจื–ืฉืึทืŸ ืคึฟื•ื ืงืฆื™ืข ืึธืคืขื ื’ื™ืง ืื•ื™ืฃ ืึท ืžื ื”ื’ ืคึผืึทืจืึทืžืขื˜ืขืจ, ื“"ื”. ื•ื•ืึทืœืึทื˜ืึทืœ. ืึทื–ืึท ืคืึทื ื’ืงืฉืึทื ื– ื–ืขื ืขืŸ ื ื™ืฉื˜ ืขืจืœื•ื™ื‘ื˜ ืื™ืŸ ื“ื™ ืื™ื ื“ืขืงืก. ืื™ืŸ ื“ืขื ืคืึทืœ, ืื™ืจ ืžื•ื–ืŸ ื‘ืคื™ืจื•ืฉ ืึธื ื•ื•ื™ื™ึทื–ืŸ ืื™ืŸ ื•ื•ืึธืก ืฆื™ื™ื˜ ื–ืึธื ืข ื“ื™ ื˜ื™ืคึผ ื•ื•ืึทืจืคืŸ ืื™ื– ื“ื•ืจื›ื’ืขืงืึธื›ื˜.

ื•ื•ืขืŸ ื™ืขืฆื˜ ืื™ื– ืืคื™ืœื• ื ื™ืฉื˜ ื™ืขืฆื˜ ื‘ื›ืœืœ

ืžื™ืจ ื–ืขื ืขืŸ ื’ืขื ื™ืฆื˜ ืฆื• ืื™ืฆื˜ () ืฆื•ืจื™ืงืงื•ืžืขืŸ ื“ื™ ืงืจืึทื ื˜ ื“ืึทื˜ืข / ืฆื™ื™ื˜, ื’ืขื ื•ืžืขืŸ ืื™ืŸ ื—ืฉื‘ื•ืŸ ื“ื™ ืฆื™ื™ื˜ ื–ืึธื ืข. ืึธื‘ืขืจ ืงื•ืง ืื™ืŸ ื“ื™ ืคืืœื’ืขื ื“ืข ืคึฟืจืื’ืŸ:

START TRANSACTION;
SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

COMMIT;

ื“ื™ ื“ืึทื˜ืข / ืฆื™ื™ื˜ ืื™ื– ืื•ืžื’ืขืงืขืจื˜ ื“ื™ ื–ืขืœื‘ืข ืงื™ื™ืŸ ืขื ื™ืŸ ื•ื•ื™ ืคื™ืœ ืฆื™ื™ึทื˜ ืื™ื– ื“ื•ืจื›ื’ืขื’ืื ื’ืขืŸ ื–ื™ื ื˜ ื“ื™ ืคืจื™ืขืจื“ื™ืงืข ื‘ืขื˜ืŸ! ื•ื•ืืก ืื™ื– ื“ื™ ืžืขืฉื”? ื“ืขืจ ืคืึทืงื˜ ืื™ื– ืึทื– ืื™ืฆื˜ () ืื™ื– ื ื™ืฉื˜ ื“ื™ ืงืจืึทื ื˜ ืฆื™ื™ื˜, ืึธื‘ืขืจ ื“ื™ ืึธื ื”ื™ื™ื‘ ืฆื™ื™ื˜ ืคื•ืŸ ื“ืขื ืงืจืึทื ื˜ ื˜ืจืึทื ืกืึทืงื˜ื™ืึธืŸ. ื“ืขืจื™ื‘ืขืจ, ืขืก ื˜ื•ื˜ ื ื™ืฉื˜ ื˜ื•ื™ืฉืŸ ืื™ืŸ ื“ื™ ืžืึทืกืข - ืžืึทื˜ืŸ. ืงื™ื™ืŸ ืึธื ืคึฟืจืขื’ ืœืึธื ื˜ืฉื˜ ืึทืจื•ื™ืก ื“ื™ ืคืึทืจื ืขื ืคื•ืŸ ืึท ื˜ืจืึทื ืกืึทืงื˜ื™ืึธืŸ ืื™ื– ืืœื ื’ืขื•ื•ื™ืงืœื˜ ืื™ืŸ ืึท ื˜ืจืึทื ืกืึทืงื˜ื™ืึธืŸ ื™ืžืคึผืœื™ืกืึทื˜ืœื™, ื•ื•ืึธืก ืื™ื– ื•ื•ืึธืก ืžื™ืจ ื˜ืึธืŸ ื ื™ื˜ ื‘ืึทืžืขืจืงืŸ ืึทื– ื“ื™ ืฆื™ื™ื˜ ืื•ืžื’ืขืงืขืจื˜ ื“ื•ืจืš ืึท ืคึผืฉื•ื˜ ืื•ื™ืกืงืœื™ื™ึทื‘ืŸ ืื™ืฆื˜ (); ืื™ืŸ ืคืึทืงื˜, ื ื™ืฉื˜ ื“ื™ ืงืจืึทื ื˜ ... ืื•ื™ื‘ ืื™ืจ ื•ื•ื™ืœืŸ ืฆื• ื‘ืึทืงื•ืžืขืŸ ืึทืŸ ืขืจืœืขืš ืงืจืึทื ื˜ ืฆื™ื™ื˜, ืื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ื ื•ืฆืŸ ื“ื™ clock_timestamp() ืคึฟื•ื ืงืฆื™ืข.

ื˜ืขืงืข ื ื•ืžืขืจ ืคื™ื ืฃ. ื‘ื™ืกืœ

ืžืึธื“ื ืข ืึท ื‘ื™ืกืœ

SELECT '111'::bit(4)

 bit
bit(4)
------
1110

ื•ื•ืึธืก ื–ื™ื™ึทื˜ ื–ืึธืœ ื“ื™ ื‘ื™ื˜ืŸ ื–ื™ื™ืŸ ืฆื•ื’ืขื’ืขื‘ืŸ ืื™ืŸ ืคืึทืœ ืคื•ืŸ ื˜ื™ืคึผ ืคืึทืจืœืขื ื’ืขืจื•ื ื’? ืขืก ืžื™ื™ื ื˜ ืฆื• ื–ื™ื™ืŸ ืื•ื™ืฃ ื“ื™ ืœื™ื ืงืก. ืื‘ืขืจ ื‘ืœื•ื™ื– ื“ื™ ื‘ืึทื–ืข ื”ืื˜ ืึท ืึทื ื“ืขืจืฉ ืžื™ื™ื ื•ื ื’ ืื•ื™ืฃ ื“ืขื ืขื ื™ืŸ. ื–ื™ื™ืŸ ืึธืคึผื’ืขื”ื™ื˜: ืื•ื™ื‘ ื“ื™ ื ื•ืžืขืจ ืคื•ืŸ ื“ื™ื“ื–ืฉืึทืฅ ืงืขืŸ ื ื™ืฉื˜ ืฉื˜ื™ืžืขืŸ ื•ื•ืขืŸ ืื™ืจ ืงืึทืกื˜ื™ื ื’ ืึท ื˜ื™ืคึผ, ืื™ืจ ื•ื•ืขื˜ ื ื™ืฉื˜ ื‘ืึทืงื•ืžืขืŸ ื•ื•ืึธืก ืื™ืจ ื•ื•ื™ืœื˜. ื“ืึธืก ืึทืคึผืœื™ื™ื– ืฆื• ื‘ื™ื™ื“ืข ืึทื“ื™ื ื’ ื‘ื™ื˜ืŸ ืฆื• ื“ื™ ืจืขื›ื˜ ืื•ืŸ ื˜ืจื™ืžื™ื ื’ ื‘ื™ื˜ืŸ. ืื•ื™ืš ืื•ื™ืฃ ื“ื™ ืจืขื›ื˜ืข...

ื˜ืขืงืข ื ื•ืžืขืจ ื–ืขืงืก. ืขืจื™ื™ื–

ืืคื™ืœื• NULL ื”ืื˜ ื ื™ืฉื˜ ืคื™ื™ืขืจื“

SELECT ARRAY[1, 2] || NULL

?column?
integer[]
---------
{1,2}

ื•ื•ื™ ื ืึธืจืžืึทืœ ืžืขื ื˜ืฉืŸ ืื•ื™ืคืฉื˜ื™ื™ืŸ ืื•ื™ืฃ SQL, ืžื™ืจ ื“ืขืจื•ื•ืึทืจื˜ืŸ ืึทื– ื“ืขืจ ืจืขื–ื•ืœื˜ืึทื˜ ืคื•ืŸ ื“ืขื ืื•ื™ืกื“ืจื•ืง ืื™ื– NULL. ืื‘ืขืจ ืขืก ืื™ื– ื ื™ืฉื˜ ื“ืึธืจื˜. ื ืžืขื ื’ืข ืื™ื– ืื•ืžื’ืขืงืขืจื˜. ืคืืจื•ื•ืืก? ื•ื•ื™ื™ึทืœ ืื™ืŸ ื“ืขื ืคืึทืœ ื“ื™ ื‘ืึทื–ืข ืงืึทืกืฅ NULL ืฆื• ืึท ื™ื ื˜ืึทื“ื–ืฉืขืจ ืžืขื ื’ืข ืื•ืŸ ื™ืžืคึผืœื™ืกืึทื˜ ืจื•ืคื˜ ื“ื™ array_cat ืคื•ื ืงืฆื™ืข. ืึธื‘ืขืจ ืขืก ืื™ื– ื ืึธืš ื•ืžืงืœืึธืจ ื•ื•ืึธืก ื“ื™ "ืžืขื ื’ืข ืงืึทืฅ" ื˜ื•ื˜ ื ื™ืฉื˜ ื‘ืึทืฉื˜ืขื˜ื™ืง ื“ื™ ืžืขื ื’ืข. ื“ืขื ืึธืคึผืคื™ืจื•ื ื’ ืื•ื™ืš ื ืึธืจ ื“ืึทืจืฃ ืฆื• ื–ื™ื™ืŸ ื“ืขืจืžืื ื˜.

ืกืึทืžืขืจื™ื™ื–. ืขืก ื–ืขื ืขืŸ ืึท ืคึผืœืึทืฅ ืคื•ืŸ ืžืึธื“ื ืข ื–ืื›ืŸ. ืจื•ื‘ึฟ ืคื•ืŸ ื–ื™ื™, ืคื•ืŸ ืงื•ืจืก, ื–ืขื ืขืŸ ื ื™ืฉื˜ ืึทื–ื•ื™ ืงืจื™ื˜ื™ืฉ ื•ื•ื™ ืฆื• ืจืขื“ืŸ ื•ื•ืขื’ืŸ ื‘ืœื™ื™ื˜ืึทื ื˜ืœื™ ื™ื ืึทืคึผืจืึธื•ืคึผืจื™ื™ื˜ ื ืึทื˜ื•ืจ. ืื•ืŸ ืื ื“ืขืจืข ื–ืขื ืขืŸ ื“ืขืจืงืœืขืจื˜ ื“ื•ืจืš ื™ื– ืคื•ืŸ ื ื•ืฆืŸ ืึธื“ืขืจ ื“ื™ ืึธืคื˜ืงื™ื™ึทื˜ ืคื•ืŸ ื–ื™ื™ืขืจ ืึธื ื•ื•ืขื ื“ืœืขืš ืื™ืŸ ื–ื™ื›ืขืจ ืกื™ื˜ื•ืึทื˜ื™ืึธื ืก. ืื‘ืขืจ ืื™ืŸ ื“ืขืจ ื–ืขืœื‘ื™ืงืขืจ ืฆื™ื™ึทื˜, ืขืก ื–ืขื ืขืŸ ืคื™ืœืข ืกืึทืคึผืจื™ื™ื–ื™ื–. ื“ืขืจื™ื‘ืขืจ, ืื™ืจ ื“ืึทืจืคึฟืŸ ืฆื• ื•ื•ื™ืกืŸ ื•ื•ืขื’ืŸ ื–ื™ื™. ืื•ื™ื‘ ืื™ืจ ื’ืขืคึฟื™ื ืขืŸ ืขืคึผืขืก ืึทื ื“ืขืจืฉ ืžืึธื“ื ืข ืึธื“ืขืจ ื•ืžื’ืขื•ื•ื™ื™ื ื˜ืœืขืš ืื™ืŸ ื“ื™ ื ืึทื˜ื•ืจ ืคื•ืŸ ืงื™ื™ืŸ ื˜ื™ื™ืคึผืก, ืฉืจื™ื™ึทื‘ืŸ ืื™ืŸ ื“ื™ ื‘ืึทืžืขืจืงื•ื ื’ืขืŸ, ืื™ืš ื•ื•ืขืœ ื–ื™ื™ืŸ ืฆื•ืคืจื™ื“ืŸ ืฆื• ืœื™ื™ื’ืŸ ืฆื• ื“ื™ ื“ืึธื•ืกืึทื“ื–ืฉ ื‘ื ื™ืžืฆื ืื•ื™ืฃ ื–ื™ื™.

ืžืงื•ืจ: www.habr.com

ืœื™ื™ื’ืŸ ืึท ื‘ืึทืžืขืจืงื•ื ื’