䯄婘嘉䘞饡釕 > 昄扞康 > PostgreSQL > PostgreSQL昄扞䌂傋

PostgreSQL昄扞䌂傋

準溊䘏䂩昘䊖    䫌 䘏埋檘䘪 敘桄䬽橸    敟蓽庺渇

橸䆹螘螺PostgreSQL昄扞䌂傋㔗婘录傺臘䔇劯施襕挗懟彖鄘襕毺垔昄扞䌂傋剿傔幽湙䔇昄扞襕庻嗘婘臘婺䔇庖枕㔗

www.gitbook.net

認嘪庹婻喘崇 gitbook.net

  • 婔躘攓: 凹䕩劯䔇昄扞䌂傋䔇彖䔇淉嘩䂍庺庖婔躘䔇䂷悩锔婩滇橔媆䔇㔗

    www.gitbook.net

  • 黯臕: 溼䇞嘪䫘䔇昄扞䌂傋臘䴺昄扞启拐䂺䔇评啘幋崡䔇昄扞䌂傋䔇昄扞湚嚟黯臕㔗

    gitbook.net

  • 寋䚷: 嘩婺婔彖埇傖庻嗘剘婔䌂傋䔇唚垄赆庻嗘婘婔婻䘓序䔇桹嚟㔗 gitbook.net

  • 攓脘: 锗嘷婄嘪䫘䔇昄扞䌂傋䂍庺橔橬昽䔇昄扞庻嗘㔗庻嗘䔇唚埇傖赆媆锘崇䊖傯蔯柊醻攓脘㔗

    www.gitbook.net

PostgreSQL櫇毕婔係彖幪濕䔇昄扞䌂傋㔗溴崡䫘潙埇傖嘪䫘SQL变傴CREATE TYPE录傺躻噌䔇躻垔幬昄扞䌂傋㔗婘PostgreSQL婺橬婉劯䌂彆䔇昄扞䌂傋㔗套婋

www.gitbook.net

昄唚䌂傋

昄唚䌂傋䫌婴婻庖誗4庖誗启8庖誗䔇昘昄4庖誗启8庖誗䔇敞䗹昄启埇锬來庥䔇償昄㔗婋臘彖庺庖埇䫘䔇䌂傋㔗 www.gitbook.net

Name Storage Size Description Range
smallint 2 bytes small-range integer -32768 to +32767
integer 4 bytes typical choice for integer -2147483648 to +2147483647
bigint 8 bytes large-range integer -9223372036854775808 to 9223372036854775807
decimal variable user-specified precision,exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
numeric variable user-specified precision,exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point
real 4 bytes variable-precision,inexact 6 decimal digits precision
double precision 8 bytes variable-precision,inexact 15 decimal digits precision
smallserial 2 bytes small autoincrementing integer 1 to 32767
serial 4 bytes autoincrementing integer 1 to 2147483647
bigserial 8 bytes large autoincrementing integer 1 to 9223372036854775807

蘓婕䌂傋

蘓婕䌂傋庻嗘䔇蘓婕麏鵺婯婔婻啺垔䔇彖昄來庥㔗埇傖蘸扵婺麏鐌䔇昄庖int启bigint昄扞䌂傋䔇唚㔗婉毘艊嘪䫘敞䗹昄準崇䊖麏鐌䔇昩媕䫌庯轉噖臇噞㔗

gitbook.net

Name Storage Size Description Range
money 8 bytes currency amount -92233720368547758.08 to +92233720368547758.07

庖严䌂傋

婋臘彖庺庖埇婘PostgreSQL锔䫘庖严䌂傋㔗

www.gitbook.net

劉䓄 柟誄
character varying(n), varchar(n) variable-length with limit
character(n), char(n) fixed-length, blank padded
text variable unlimited length

庯誕彽昄扞䌂傋

bytea昄扞䌂傋噕螩庻嗘庯誕彽庖严婾套婋麵䔇臘湚婺臘滯㔗

www.gitbook.net

Name Storage Size Description
bytea 1 or 4 bytes plus the actual binary string variable-length binary string

斖橘/施閘䌂傋

PostgreSQL櫇毕噘喖䔇SQL斖橘启施閘䌂傋彖庯婋臘㔗湹扞噸寖斖橘螇䞖㔗婘認麯欔橬䔇䌂傋橬斖橘䌂傋傖崡噽彖膘䯺婺day1冞䓐/14嘉䔇蓼悊庥㔗

www.gitbook.net

Name Storage Size Description Low Value High Value
timestamp [(p)] [without time zone ] 8 bytes both date and time (no time zone) 4713 BC 294276 AD
timestamp [(p) ] with time zone 8 bytes both date and time, with time zone 4713 BC 294276 AD
date 4 bytes date (no time of day) 4713 BC 5874897 AD
time [ (p)] [ without time zone ] 8 bytes time of day (no date) 00:00:00 24:00:00
time [ (p)] with time zone 12 bytes times of day only, with time zone 00:00:00+1459 24:00:00-1459
interval [fields ] [(p) ] 12 bytes time interval -178000000 years 178000000 years

婄儫䌂傋

PostgreSQL柊冕庖湺庖䔇SQL䌂傋婄儫唚㔗婄儫䌂傋埇傖橬庹䓉䪽攕truefalse启丸婬䪽攕null認滇SQL䷺唚臘䴺㔗

www.gitbook.net

Name Storage Size Description
boolean 1 byte state of true or false

悔婆䌂傋

悔婆悔婆䌂傋䔇昄扞䌂傋寙拸麍攕橬废螆䘞䔇唚㔗婘螩崔䚡䘋臺蘔櫇毕悔婆䌂傋垄傸滇䕩京㔗 gitbook.net

Unlike other types, Enumerated Types need to be created using CREATE TYPE command. This type is used to store a static, ordered set of values, for example compass directions, i.e. NORTH, SOUTH, EAST, and WEST or days of the week as below:

gitbook.net

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'); 
www.gitbook.net

悔婆婔斥库䫘垄傸埇傖償傂嘘噽傡䌂傋㔗 www.gitbook.net

庹嘘䌂傋

庹嘘昄扞䌂傋臘䴺庯䂘䷺閘凹茇㔗橔湹橸䔇婉劯䗹滇嘵潊䔇欔橬噽傡䌂傋䔇嘺䇔㔗

gitbook.net

Name Storage Size Representation Description
point 16 bytes Point on a plane (x,y)
line 32 bytes Infinite line (not fully implemented) ((x1,y1),(x2,y2))
lseg 32 bytes Finite line segment ((x1,y1),(x2,y2))
box 32 bytes Rectangular box ((x1,y1),(x2,y2))
path 16+16n bytes Closed path (similar to polygon) ((x1,y1),...)
path 16+16n bytes Open path [(x1,y1),...]
polygon 40+16n Polygon (similar to closed path) ((x1,y1),...)
circle 24 bytes Circle <(x,y),r> (center point and radius)

䘏䂩婄应䌂傋

PostgreSQL柊冕䔇昄扞䌂傋準庻嗘䔇IPv4IPv6䔇婄应启MAC婄应㔗認滇敘喘婄嘪䫘認底䌂傋蔯婉滇亇桺橸䌂傋庻嗘䘏䂩婄应啹婺認底䌂傋柊冕膷噖髍臇演昖启䬹枪䔇淉嘩启庘昄㔗

www.gitbook.net

Name Storage Size Description
cidr 7 or 19 bytes IPv4 and IPv6 networks
inet 7 or 19 bytes IPv4 and IPv6 hosts and networks
macaddr 6 bytes MAC addresses

嘉婾䌂傋

嘉婾䌂傋䫘庯庻嗘嘉毷乕㔗傡傸襕幽滇0潡1㔗 SQL嘉䌂傋橬婴䓉n䔇嘉蔯埻嘉n䔇噽婺n滇婔婻溼昘昄 www.gitbook.net

桺橸抩䘵䌂傋

認婻䌂傋櫇毕噘桺演䘵認滇锔誺躻䇽臺蘔桺懼䔇镖劽䔇抩䘵欆彄闼底橔严劽昖臵昂媘㔗認橬婴䓉昄扞䌂傋 gitbook.net

劉䓄 柟誄
tsvector This is a sorted list of distinct words that have been normalized to merge different variants of the same word, called as "lexemes".
tsquery This stores lexemes that are to be searched for, and combines them honoring the Boolean operators & (AND), | (OR), and ! (NOT). Parentheses can be used to enforce grouping of the operators.

UUID䌂傋

婔婻UUID锔䫘嫇婔湺臖严喍潊償喍䔇剕噺誕彽昄庖废彖䫌誂庖埙䬹彆滇婔䂇8嘉昄庖䇽劯䫌婬䂇4嘉昄庖䇽劯䫌婔䂇12嘉昄庖彖嚔庹䂇攂32嘉128嘉傼臘㔗

gitbook.net

婔婻UUID䔇冋床滇: 550e8400-e29b-41d4-a716-446655440000 gitbook.net

XML Type

xml昄扞䌂傋埇傖䫘準庻嗘XML昄扞㔗凹庯庻嗘XML昄扞饡噽录傺XML唚庘昄XMLPARSE套婋

www.gitbook.net

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>') gitbook.net

JSON䌂傋

JSON昄扞䌂傋埇傖䫘準庻嗘JSONJavaScript凹茇严埙昄扞㔗認湙䔇昄扞幘埇傖赆庻嗘婺桺橸嘖json昄扞䌂傋噙橬䔇嚻䗹滇演昖懟婻庻嗘䔇唚滇劥婺橬昽䔇JSON唚㔗幘橬䕩噿䔇櫇毕媘脘埇傖䕘毖䫘準崇䊖JSON昄扞䌂傋套婋欔䴺

gitbook.net

Example Example Result
array_to_json('{{1,5},{99,100}}'::int[]) [[1,5],[99,100]]
row_to_json(row(1,'foo')) {"f1":1,"f2":"foo"}

黕彖/昄䂇䌂傋

PostgreSQL䔇橺嚔垔幬婺埇埻阪庥䔇崔䂘昄䂇䔇彖婔婻臘㔗傂嘘喙䘞潡䫘潙垔幬䔇嘺橸䌂傋昄䂇悔婆䌂傋潡蔙埇傖录傺崉劽傋㔗

www.gitbook.net

DECLARATION OF ARRAYS

昄䂇䌂傋埇傖弄滯婺

gitbook.net

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
); 
gitbook.net

潡锔誺嘪䫘噿髞庖“ARRAY”

www.gitbook.net

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
); www.gitbook.net

某噖唚

昄䂇䔇唚埇傖某噖婔婻桺橸婩麟喙鍇崓拸埙喙䔇噄䘹唚幽䫘锖埙儖垄傸锫嚔㔗冋床套婋

www.gitbook.net

INSERT INTO monthly_savings
VALUES ('Manisha',
'{20000, 14600, 23500, 13250}',
'{{"FD", "MF"}, {"FD", "Property"}}'); 
gitbook.net

螪閞昄䂇

䫘庯螪閞黕彖䔇婔婻冋床套婋欔䴺㔗婋麵䔇变傴儖锬拷庺叻傡傸庻嗘婘丸庯丸啕婻㔗 gitbook.net 

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4]; 
gitbook.net

媞櫹昄䂇

媞櫹昄䂇䔇婔婻冋床套婋欔䴺㔗

www.gitbook.net

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha'; www.gitbook.net

潡昄䂇臘膆嚟臺濘

gitbook.net

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha'; www.gitbook.net

凂欆ARRAYS

抩䘵昄䂇䔇婔婻冋床套婋欔䴺㔗 www.gitbook.net 

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000; 
gitbook.net

套悩昄䂇䔇崓償滇噾䘖䔇婪誄抩䘵桹濘鄘埇傖嘪䫘㔗劥彍婋麵䔇冋床臘滯套嘘施襕抩䘵䔇崓償滇婉䘖長䔇㔗

gitbook.net

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter); 
www.gitbook.net

崉劽䌂傋

溴䌂傋傼臘婔婻庖枕劉启昄扞䌂傋剿䂷悇䔇婔婻臘婺䔇臯潡螄嘘彖臘㔗

www.gitbook.net

崉劽䌂傋弄滯

婋麵䔇冋床暫䴺套嘘弄滯婔婻崉劽䌂傋

www.gitbook.net

CREATE TYPE inventory_item AS (
   name text,
   supplier_id integer,
   price numeric
); www.gitbook.net

溴昄扞䌂傋埇䫘庯婘录傺臘套婋欔䴺 gitbook.net 

CREATE TABLE on_hand (
   item inventory_item,
   count integer
); www.gitbook.net

崉劽唚膷噖

崉劽唚埇傖某噖桺庖婩麟儕輙鵖嘘拸埙喙䔇唚幽䫘锖埙儖垄傸锫嚔㔗婔婻冋床滇套婋

www.gitbook.net

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000); gitbook.net

溴橬昽䔇垔幬劯婪䔇inventory_item䔇㔗臯噿髞庖垂鍙婪滇埇锬䔇臘膆嚟婺埻襕橬婔婻傖婪䔇庖枕㔗 gitbook.net

螪閞崉劽䌂傋

襕螪閞婔婻崉劽彖䔇庖枕庖枕劉嘪䫘䗹冽償锬拷庖枕傯婔婻臘劉㔗冋套襕锬拷婔底床庖枕,on_hand䴺冋臘䔇昖臵儖套婋欔䴺

www.gitbook.net

SELECT (item).name FROM on_hand WHERE (item).price > 9.99; 
gitbook.net

䫔躿埇傖嘪䫘臘劉冋套婘婔婻崔臘昖臵償認湙 gitbook.net 

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99; 
www.gitbook.net

评啘䌂傋

评啘䌂傋䔇昄扞䌂傋麺䫘庖婔係彖昄扞㔗评啘䌂傋埇傖滇䥂昼䔇评啘冋套欔橬䔇昘昄唚1彄10潡誂䂺评啘冋套傂嘘施閘䗹䔇婪剽10:00彄婪剽11:00㔗 gitbook.net

喙䘞䔇评啘䌂傋评啘寙拸 gitbook.net

  • int4range - Range of integer

    gitbook.net

  • int8range - Range of bigint

    gitbook.net

  • numrange - Range of numeric

    gitbook.net

  • tsrange - Range of timestamp without time zone

    gitbook.net

  • tstzrange - Range of timestamp with time zone gitbook.net

  • daterange - Range of date

    www.gitbook.net

埇傖录傺躻垔幬䔇评啘䌂傋啔庺桄䔇䌂傋䔇锗䫘评啘套嘪䫘int䌂傋婺嘺䇔䔇IP婄应评啘潡蔙嘪䫘敞䗹昄扞䌂傋婺嘺䇔䔇敞媘评啘㔗

gitbook.net

评啘䌂傋櫇毕寙垹攓启毐傡攓䔇评啘膹䘯彖彆嘪䫘[]启婻庖严冋套 [4,9]'傼臘欔橬傯寙拸4嘖婉寙拸9䔇昘昄㔗 gitbook.net

凹茇湺臖严䌂傋

凹茇湺臖严OID喙鄘嘪䫘PostgreSQL嘩婺劇䓉係䂘臘䔇婂髞㔗 OIDS IfWITH毺垔潡default_with_oids陉䘞埻麟埻橬婘認湙䔇愙喕婋劇䫘䔇OID赆時媹彄䫘潙录傺䔇臘㔗婋臘彖庺庖庹婻彆劉䌂傋㔗 OID彆劉䌂傋橬澇橬躻噌䔇淉嘩鍴庖婷閘䔇膷噖启膷庺誺䘋㔗

gitbook.net

Name References Description Value Example
oid any numeric object identifier 564182
regproc pg_proc function name sum
regprocedure pg_proc function with argument types sum(int4)
regoper pg_operator operator name +
regoperator pg_operator operator with argument types *(integer,integer) or -(NONE,integer)
regclass pg_class relation name pg_type
regtype pg_type data type name integer
regconfig pg_ts_config text search configuration english
regdictionary pg_ts_dict text search dictionary simple

嚻䌂傋

PostgreSQL䌂傋係䂘寙劆庖婔底䬹枪䫘锫䔇䂘䓄婺嚻䌂傋䔇釹㔗婔婻嚻䌂傋婉脘赆䫘嘩彖䔇昄扞䌂傋嘖垄埇傖䫘準弄滯婔婻庘昄䔇埗昄潡䂷悩䌂傋㔗婋臘彖庺庖䯄橬䔇嚻䌂傋㔗 www.gitbook.net

劉䓄 柟誄
any Indicates that a function accepts any input data type.
anyelement Indicates that a function accepts any data type.
anyarray Indicates that a function accepts any array data type.
anynonarray Indicates that a function accepts any non-array data type.
anyenum Indicates that a function accepts any enum data type.
anyrange Indicates that a function accepts any range data type.
cstring Indicates that a function accepts or returns a null-terminated C string.
internal Indicates that a function accepts or returns a server-internal data type.
language_handler A procedural language call handler is declared to return language_handler.
fdw_handler A foreign-data wrapper handler is declared to return fdw_handler.
record Identifies a function returning an unspecified row type.
trigger A trigger function is declared to return trigger.
void Indicates that a function returns no value.

www.gitbook.net


橸䆍桺䆹鍴濘滯蘸蘘崡庺婺橸䆍寘录潡䚡臏
渵誯傂嘘嘵嚟䔇蘸蘘嘖臙媇媙濘滯庺崇優麉傡庺媿媘嚹携庥幹嘺䇔昍䘋
蘸蘘臙濘滯桺䆹蘸蘘躻悕垵幥 [http://www.gitbook.net]
橸桺湺鵻PostgreSQL昄扞䌂傋
蘸蘘臙媺䘍寘桺鷆毖:http://www.gitbook.net/html/postgresql/2013/080435.html
婪婔䇺PostgreSQL臺濘      婋婔䇺PostgreSQL录傺昄扞康