java怎么處理postgresql的存儲函數(shù)返回的數(shù)組？

postgreSQL的JDBC驅動是支持數(shù)組返回的，數(shù)組對應的類型為org.postgresql.jdbc4.Jdbc4Array(我用的是jdbc4).

成都創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務，包含不限于成都網(wǎng)站設計、網(wǎng)站制作、天全網(wǎng)絡推廣、重慶小程序開發(fā)公司、天全網(wǎng)絡營銷、天全企業(yè)策劃、天全品牌公關、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務，您的肯定，是我們最大的嘉獎；成都創(chuàng)新互聯(lián)為所有大學生創(chuàng)業(yè)者提供天全建站搭建服務，24小時服務熱線：18982081108，官方網(wǎng)址：www.cdcxhl.com

一個示例, 函數(shù)如下:

create?or?replace?function?fn_rtbAry()

returns?integer[]

as?$fn_rtbAry$

begin

return?'{10,?20,?30}'::integer[];

end;

$fn_rtbAry$?language?plpgsql;

Java中獲得返回的數(shù)組, 要引入java.lang.*,java.sql.*,java.util.*,org.postgresql.jdbc4.*:

Connection??????conn?=?null;

Statement???????cmd?=?null;

ResultSet???????rs?=?null;

Jdbc4Array??????obj?=?null;

Integer[]???????dataAry?=?null;

//?...?連接,查詢創(chuàng)建以及初始化代碼省略

rs?=?cmd.executeQuery("select?fn_rtbAry()?as?v");

//?循環(huán)獲得數(shù)據(jù)

while?(rs.next())?{

obj?=?(Jdbc4Array)rs.getObject(1);

dataAry?=?(Integer[])obj.getArray();?//?這里獲得函數(shù)返回的數(shù)組，并轉換成Integer數(shù)組

//?其他代碼省略

}

//?釋放對象代碼省略

如何從PostgreSQL json中提取數(shù)組

postgresql 從json數(shù)組中提取json值，并分組，匯總

json數(shù)據(jù)

[java] view plain copy

{"os": "Android", "chn": "-1", "dan": 0, "sex": 0, "file": "lv_statistics", "time": "2017-01-23 16:47:54", "honor": 0, "chn_id": "-1", "is_pay": 0, "account": "-1", "role_id": -1, "battle_id": 1050, "game_time": 301, "role_name": "-1", "battle_name": "-1", "battle_type": 4, "lv_num_json": [{"lv": 8,"num": 1},{"lv": 9,"num": 10}]"}

[java] view plain copy

SELECT cont-'lv_num_json' FROM log_info WHERE file = 'lv_statisticsx'

[java] view plain copy

查詢結果：[{"lv": 8, "num": 1}, {"lv": 9, "num": 10}]

[sql] view plain copy

--例子

select json_array_elements(lv_num_json)-'lv' lv,json_array_elements(lv_num_json)-'num' num from (

select '[{"lv": 8, "num": 1}, {"lv": 9, "num": 10}]'::json lv_num_json

)as t1

輸出結果:

lv num

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

8 1

9 10

-- json_array_elements :展開一個JSON數(shù)組的JSON值

SELECT json_array_elements((cont-'lv_num_json')::json)-'lv' lv,json_array_elements((cont-'lv_num_json')::json)-'num' num

FROM log_info WHERE file = 'lv_statisticsx'

-- 按等級分組，求總人數(shù)

select lv ,sum(num::int) from(

SELECT json_array_elements((cont-'lv_num_json')::json)-'lv' lv,json_array_elements((cont-'lv_num_json')::json)-'num' num

FROM log_info WHERE file = 'lv_statisticsx'

) t1 GROUP BY lv

輸出結果:

lv num

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

8 2

9 20

postgresql 建立索引

一、索引的類型：

PostgreSQL提供了多種索引類型：B-Tree、Hash、GiST和GIN，由于它們使用了不同的算法，因此每種索引類型都有其適合的查詢類型，缺省時，CREATE INDEX命令將創(chuàng)建B-Tree索引。

1. B-Tree:

CREATE TABLE test1 (

id integer,

content varchar

);

CREATE INDEX test1_id_index ON test1 (id);

B-Tree索引主要用于等于和范圍查詢，特別是當索引列包含操作符" 、=和"作為查詢條件時，PostgreSQL的查詢規(guī)劃器都會考慮使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查詢中，PostgreSQL也可以使用B-Tree索引。然而對于基于模式匹配操作符的查詢，如LIKE、ILIKE、~和 ~*，僅當模式存在一個常量，且該常量位于模式字符串的開頭時，如col LIKE 'foo%'或col ~ '^foo'，索引才會生效，否則將會執(zhí)行全表掃描，如：col LIKE '%bar'。

2. Hash：

CREATE INDEX name ON table USING hash (column);

散列(Hash)索引只能處理簡單的等于比較。當索引列使用等于操作符進行比較時，查詢規(guī)劃器會考慮使用散列索引。

這里需要額外說明的是，PostgreSQL散列索引的性能不比B-Tree索引強，但是散列索引的尺寸和構造時間則更差。另外，由于散列索引操作目前沒有記錄WAL日志，因此一旦發(fā)生了數(shù)據(jù)庫崩潰，我們將不得不用REINDEX重建散列索引。

3. GiST：

GiST索引不是一種單獨的索引類型，而是一種架構，可以在該架構上實現(xiàn)很多不同的索引策略。從而可以使GiST索引根據(jù)不同的索引策略，而使用特定的操作符類型。

4. GIN：

GIN索引是反轉索引，它可以處理包含多個鍵的值(比如數(shù)組)。與GiST類似，GIN同樣支持用戶定義的索引策略，從而可以使GIN索引根據(jù)不同的索引策略，而使用特定的操作符類型。作為示例，PostgreSQL的標準發(fā)布中包含了用于一維數(shù)組的GIN操作符類型，如：、=、等。

二、復合索引：

PostgreSQL中的索引可以定義在數(shù)據(jù)表的多個字段上，如：

CREATE TABLE test2 (

major int,

minor int,

name varchar

}

CREATE INDEX test2_mm_idx ON test2 (major, minor);

1. B-Tree類型的復合索引：

在B-Tree類型的復合索引中，該索引字段的任意子集均可用于查詢條件，不過，只有當復合索引中的第一個索引字段(最左邊)被包含其中時，才可以獲得最高效率。

2. GiST類型的復合索引：

在GiST類型的復合索引中，只有當?shù)谝粋€索引字段被包含在查詢條件中時，才能決定該查詢會掃描多少索引數(shù)據(jù)，而其他索引字段上的條件只是會限制索引返回的條目。假如第一個索引字段上的大多數(shù)數(shù)據(jù)都有相同的鍵值，那么此時應用GiST索引就會比較低效。

3. GIN類型的復合索引：

與B-Tree和GiST索引不同的是，GIN復合索引不會受到查詢條件中使用了哪些索引字段子集的影響，無論是哪種組合，都會得到相同的效率。

使用復合索引應該謹慎。在大多數(shù)情況下，單一字段上的索引就已經(jīng)足夠了，并且還節(jié)約時間和空間。除非表的使用模式非常固定，否則超過三個字段的索引幾乎沒什么用處。

三、組合多個索引：

PostgreSQL可以在查詢時組合多個索引(包括同一索引的多次使用)，來處理單個索引掃描不能實現(xiàn)的場合。與此同時，系統(tǒng)還可以在多個索引掃描之間組成AND和OR的條件。比如，一個類似WHERE x = 42 OR x = 47 OR x = 53 OR x = 99的查詢，可以被分解成四個獨立的基于x字段索引的掃描，每個掃描使用一個查詢子句，之后再將這些掃描結果OR在一起并生成最終的結果。另外一個例子是，如果我們在x和y上分別存在獨立的索引，那么一個類似WHERE x = 5 AND y = 6的查詢，就會分別基于這兩個字段的索引進行掃描，之后再將各自掃描的結果進行AND操作并生成最終的結果行。

為了組合多個索引，系統(tǒng)掃描每個需要的索引，然后在內存里組織一個BITMAP，它將給出索引掃描出的數(shù)據(jù)在數(shù)據(jù)表中的物理位置。然后，再根據(jù)查詢的需要，把這些位圖進行AND或者OR的操作并得出最終的BITMAP。最后，檢索數(shù)據(jù)表并返回數(shù)據(jù)行。表的數(shù)據(jù)行是按照物理順序進行訪問的，因為這是位圖的布局，這就意味著任何原來的索引的排序都將消失。如果查詢中有ORDER BY子句，那么還將會有一個額外的排序步驟。因為這個原因，以及每個額外的索引掃描都會增加額外的時間，這樣規(guī)劃器有時候就會選擇使用簡單的索引掃描，即使有多個索引可用也會如此。

四、唯一索引：

CREATE UNIQUE INDEX name ON table (column [, ...]);

五、表達式索引：

表達式索引主要用于在查詢條件中存在基于某個字段的函數(shù)或表達式的結果與其他值進行比較的情況，如：

SELECT * FROM test1 WHERE lower(col1) = 'value';

此時，如果我們僅僅是在col1字段上建立索引，那么該查詢在執(zhí)行時一定不會使用該索引，而是直接進行全表掃描。如果該表的數(shù)據(jù)量較大，那么執(zhí)行該查詢也將會需要很長時間。解決該問題的辦法非常簡單，在test1表上建立基于col1字段的表達式索引，如：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';

和上面的例子一樣，盡管我們可能會為first_name和last_name分別創(chuàng)建獨立索引，或者是基于這兩個字段的復合索引，在執(zhí)行該查詢語句時，這些索引均不會被使用，該查詢能夠使用的索引只有我們下面創(chuàng)建的表達式索引。

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));

CREATE INDEX命令的語法通常要求在索引表達式周圍書寫圓括弧，就像我們在第二個例子里顯示的那樣。如果表達式只是一個函數(shù)調用，那么可以省略，就像我們在第一個例子里顯示的那樣。

從索引維護的角度來看，索引表達式要相對低效一些，因為在插入數(shù)據(jù)或者更新數(shù)據(jù)的時候，都必須為該行計算表達式的結果，并將該結果直接存儲到索引里。然而在查詢時，PostgreSQL就會把它們看做WHERE idxcol = 'constant'，因此搜索的速度等效于基于簡單索引的查詢。通常而言，我們只是應該在檢索速度比插入和更新速度更重要的場景下使用表達式索引。

六、部分索引：

部分索引(partial index)是建立在一個表的子集上的索引，而該子集是由一個條件表達式定義的(叫做部分索引的謂詞)。該索引只包含表中那些滿足這個謂詞的行。

由于不是在所有的情況下都需要更新索引，因此部分索引會提高數(shù)據(jù)插入和數(shù)據(jù)更新的效率。然而又因為部分索引比普通索引要小，因此可以更好的提高確實需要索引部分的查詢效率。見以下三個示例：

1. 索引字段和謂詞條件字段一致：

CREATE INDEX access_log_client_ip_ix ON access_log(client_ip)

WHERE NOT (client_ip inet '192.168.100.0' AND client_ip inet '192.168.100.255');

下面的查詢將會用到該部分索引：

SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

下面的查詢將不會用該部分索引：

一個不能使用這個索引的查詢可以是

SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

2. 索引字段和謂詞條件字段不一致：

PostgreSQL支持帶任意謂詞的部分索引，唯一的約束是謂詞的字段也要來自于同樣的數(shù)據(jù)表。注意，如果你希望你的查詢語句能夠用到部分索引，那么就要求該查詢語句的條件部分必須和部分索引的謂詞完全匹配。 準確說，只有在PostgreSQL能夠識別出該查詢的WHERE條件在數(shù)學上涵蓋了該索引的謂詞時，這個部分索引才能被用于該查詢。

CREATE INDEX orders_unbilled_index ON orders(order_nr) WHERE billed is not true;

下面的查詢一定會用到該部分索引：

SELECT * FROM orders WHERE billed is not true AND order_nr 10000;

那么對于如下查詢呢？

SELECT * FROM orders WHERE billed is not true AND amount 5000.00;

這個查詢將不像上面那個查詢這么高效，畢竟查詢的條件語句中沒有用到索引字段，然而查詢條件"billed is not true"卻和部分索引的謂詞完全匹配，因此PostgreSQL將掃描整個索引。這樣只有在索引數(shù)據(jù)相對較少的情況下，該查詢才能更有效一些。

下面的查詢將不會用到部分索引。

SELECT * FROM orders WHERE order_nr = 3501;

3. 數(shù)據(jù)表子集的唯一性約束：

CREATE TABLE tests (

subject text,

target text,

success boolean,

...

);

CREATE UNIQUE INDEX tests_success_constraint ON tests(subject, target) WHERE success;

該部分索引將只會對success字段值為true的數(shù)據(jù)進行唯一性約束。在實際的應用中，如果成功的數(shù)據(jù)較少，而不成功的數(shù)據(jù)較多時，該實現(xiàn)方法將會非常高效。

七、檢查索引的使用：

見以下四條建議：

1. 總是先運行ANALYZE。

該命令將會收集表中數(shù)值分布狀況的統(tǒng)計。在估算一個查詢返回的行數(shù)時需要這個信息，而規(guī)劃器則需要這個行數(shù)以便給每個可能的查詢規(guī)劃賦予真實的開銷值。如果缺乏任何真實的統(tǒng)計信息，那么就會使用一些缺省數(shù)值，這樣肯定是不準確的。因此，如果還沒有運行ANALYZE就檢查一個索引的使用狀況，那將會是一次失敗的檢查。

2. 使用真實的數(shù)據(jù)做實驗。

用測試數(shù)據(jù)填充數(shù)據(jù)表，那么該表的索引將只會基于測試數(shù)據(jù)來評估該如何使用索引，而不是對所有的數(shù)據(jù)都如此使用。比如從100000行中選1000行，規(guī)劃器可能會考慮使用索引，那么如果從100行中選1行就很難說也會使用索引了。因為100行的數(shù)據(jù)很可能是存儲在一個磁盤頁面中，然而沒有任何查詢規(guī)劃能比通過順序訪問一個磁盤頁面更加高效了。與此同時，在模擬測試數(shù)據(jù)時也要注意，如果這些數(shù)據(jù)是非常相似的數(shù)據(jù)、完全隨機的數(shù)據(jù)，或按照排序順序插入的數(shù)據(jù)，都會令統(tǒng)計信息偏離實際數(shù)據(jù)應該具有的特征。

3. 如果索引沒有得到使用，那么在測試中強制它的使用也許會有些價值。有一些運行時參數(shù)可以關閉各種各樣的查詢規(guī)劃。

4. 強制使用索引用法將會導致兩種可能：一是系統(tǒng)選擇是正確的，使用索引實際上并不合適，二是查詢計劃的開銷計算并不能反映現(xiàn)實情況。這樣你就應該對使用和不使用索引的查詢進行計時，這個時候EXPLAIN ANALYZE命令就很有用了。

postgresql中創(chuàng)建一個類型

你是要定義一個符串數(shù)組類型(每個字符串的最大長度為8000)嗎? 這個類型不需特別定義，直接使用就可以了, 示例SQL:

do?language?plpgsql?$$

declare

v_a?varchar(8000)[]?:=?array['Andy',?'Jack'];

begin

raise?notice?'%',?case?when?v_a[1]?=?'Andy'?then?'Is?Andy'?else?'Other?:?'?||?v_a[1]?end;

end?$$;

postgreSQL的簡單介紹？

postgreSQL是一款先進的開源數(shù)據(jù)庫，擁有非常齊全的自由軟件的對象-關系型數(shù)據(jù)庫管理系統(tǒng)（ORDBMS），可面向企業(yè)復雜SQL的OLTP業(yè)務場景，支持多項企業(yè)級功能，能解決使用數(shù)據(jù)庫的各種難題。

PostgreSQL的優(yōu)勢有很多。它是一個免費的對象-關系數(shù)據(jù)庫服務器(ORDBMS)，在靈活的BSD許可證下發(fā)行。

postgreSQL的特征

函數(shù)：通過函數(shù)，可以在數(shù)據(jù)庫服務器端執(zhí)行指令程序。

索引：用戶可以自定義索引方法，或使用內置的 B 樹，哈希表與 GiST 索引。

觸發(fā)器：觸發(fā)器是由SQL語句查詢所觸發(fā)的事件。如：一個INSERT語句可能觸發(fā)一個檢查數(shù)據(jù)完整性的觸發(fā)器。觸發(fā)器通常由INSERT或UPDATE語句觸發(fā)。 多版本并發(fā)控制：PostgreSQL使用多版本并發(fā)控制（MVCC，Multiversion concurrency control）系統(tǒng)進行并發(fā)控制，該系統(tǒng)向每個用戶提供了一個數(shù)據(jù)庫的"快照"，用戶在事務內所作的每個修改，對于其他的用戶都不可見，直到該事務成功提交。

規(guī)則：規(guī)則（RULE）允許一個查詢能被重寫，通常用來實現(xiàn)對視圖（VIEW）的操作，如插入（INSERT）、更新（UPDATE）、刪除（DELETE）。

數(shù)據(jù)類型：包括文本、任意精度的數(shù)值數(shù)組、JSON 數(shù)據(jù)、枚舉類型、XML 數(shù)據(jù)等。全文檢索：通過 Tsearch2 或 OpenFTS，8.3版本中內嵌 Tsearch2。

NoSQL：JSON，JSONB，XML，HStore 原生支持，至 NoSQL 數(shù)據(jù)庫的外部數(shù)據(jù)包裝器。

數(shù)據(jù)倉庫：能平滑遷移至同屬postgreSQL生態(tài)的GreenPlum，DeepGreen，HAWK 等，使用 FDW 進行 ETL。

postgresql里邊的數(shù)組怎么操作

1. 數(shù)組可以作為字段類型

PostgreSQL中數(shù)據(jù)是一種基本的數(shù)據(jù)類型，可以作為字段的類型定義。例如,

CREATE?TABLE?ads.tb_mo_item

(

mo_key?integer?NOT?NULL,

input_flow?integer[]?NOT?NULL?DEFAULT?ARRAY[]::integer[]?

);

2. 可以用array[]來初始化一個數(shù)組

select?array[1,?3,?4]::int[];

3. 操作數(shù)組有一系列函數(shù), 可以實現(xiàn)數(shù)組比較，添加新元素，一般數(shù)組是否包含另一數(shù)組的判斷，等等。具體參考PostgreSQL說明文檔中函數(shù)和操作符中有關Array的部分。

操作符有: =, , , , =, =, @, @, , ||

函數(shù)有: array_append, array_cat, array_ndims, array_dims, array_fill, array_length, array_lower, array_remove, array_replace, array_to_string, array_upper, string_to_array, unnest等.

其中，常用的是: array_append, ?array_length, unnest

4. 使用數(shù)組下標獲得數(shù)組的元素，下標是從1開始的

select?(array[1,?3,?4]::int[])[2];

5. 可以用unnest將數(shù)組轉換成一個結果集，個人覺得這個很有用處

select?a.a?from?unnest(array[1,?3,?4]::int[])?a;