【postgresql】數(shù)據(jù)庫(kù)函數(shù)返回字符串問(wèn)題

(1)"pg_catalog"."varchar" 字符串

韶關(guān)網(wǎng)站制作公司哪家好，找創(chuàng)新互聯(lián)公司！從網(wǎng)頁(yè)設(shè)計(jì)、網(wǎng)站建設(shè)、微信開(kāi)發(fā)、APP開(kāi)發(fā)、響應(yīng)式網(wǎng)站開(kāi)發(fā)等網(wǎng)站項(xiàng)目制作，到程序開(kāi)發(fā)，運(yùn)營(yíng)維護(hù)。創(chuàng)新互聯(lián)公司自2013年起到現(xiàn)在10年的時(shí)間，我們擁有了豐富的建站經(jīng)驗(yàn)和運(yùn)維經(jīng)驗(yàn)，來(lái)保證我們的工作的順利進(jìn)行。專注于網(wǎng)站建設(shè)就選創(chuàng)新互聯(lián)公司。

(2)"pg_catalog"."bool" 布爾值

(3)"pg_catalog"."int" 數(shù)值

(4)"public"."table" 表

現(xiàn)有一個(gè)pgsql遞歸查詢，遞歸查詢父級(jí)節(jié)點(diǎn)并拼接成字符串，查詢語(yǔ)句如下

查詢結(jié)果為

將該查詢語(yǔ)句放在函數(shù)中，運(yùn)行沒(méi)問(wèn)題，但是使用函數(shù)查詢時(shí)報(bào)錯(cuò)，報(bào)錯(cuò)如下：

類型轉(zhuǎn)換問(wèn)題，需要強(qiáng)制將返回結(jié)果轉(zhuǎn)為字符串類型 varchar

將查詢修改為

將上傳查詢放到函數(shù)中，可以正常查詢。

postgresql啟動(dòng)失敗

1、看看服務(wù)里的 Postgres 服務(wù)的用戶名、密碼，必須用 postres 用戶啟動(dòng)的。

2、鑒于重啟后就啟動(dòng)不了，是不是服務(wù)設(shè)置手動(dòng)，或者是安裝了影子系統(tǒng)

3、會(huì)不會(huì)是安裝的時(shí)候，上一次的安裝沒(méi)有卸載干凈？

4、手動(dòng)啟動(dòng)服務(wù)試試看，看看系統(tǒng)日志有沒(méi)有相應(yīng)內(nèi)容分。

5、到 c:\program files下的安裝目錄（假設(shè)是windows)看看log 文件夾里的日志

6、進(jìn)入cmd, netstat -na ，看看開(kāi)了多少端口，很多病毒會(huì)強(qiáng)占許多端口，包括5432端口，如果5432被占用，肯定不行。netstat -navbo 會(huì)查看是哪個(gè)進(jìn)程

7、安裝的時(shí)候，locale 是不是選擇了默認(rèn)一路Next, 卸載，用locale = 'C'試試看!

PostgreSQL sql問(wèn)題，操作符不存在

錯(cuò)誤提示的意思是沒(méi)有對(duì)character和integer類型的值進(jìn)行比較的操作符。也就是說(shuō)在where條件中有等于號(hào)兩邊的類型不同，一個(gè)為charcter，一個(gè)為interger。如果需要比較的話，需要轉(zhuǎn)換成同類型的值(比如轉(zhuǎn)換成character或轉(zhuǎn)換成integer)再做比較。

如何評(píng)價(jià)postgresql

PostgreSQL的主要優(yōu)點(diǎn)：

1、對(duì)事務(wù)的支持與MySQL相比，經(jīng)歷了更為徹底的測(cè)試。對(duì)于一個(gè)嚴(yán)肅的商業(yè)應(yīng)用來(lái)說(shuō)，事務(wù)的支持是不可或缺的。

2、MySQL對(duì)于無(wú)事務(wù)的MyISAM表。采用表鎖定，一個(gè)長(zhǎng)時(shí)間運(yùn)行的查詢很可能會(huì)長(zhǎng)時(shí)間地阻礙對(duì)表的更新。而PostgreSQL不存在這樣的問(wèn)題。

3、PostgreSQL支持存儲(chǔ)過(guò)程，而目前MySQL不支持，對(duì)于一個(gè)嚴(yán)肅的商業(yè)應(yīng)用來(lái)說(shuō)，作為數(shù)據(jù)庫(kù)本身，有眾多的商業(yè)邏輯的存在，此時(shí)使用存儲(chǔ)過(guò)程可以在較少地增加數(shù)據(jù)庫(kù)服務(wù)器的負(fù)擔(dān)的前提下，對(duì)這樣的商業(yè)邏輯進(jìn)行封裝，并可以利用數(shù)據(jù)庫(kù)服務(wù)器本身的內(nèi)在機(jī)制對(duì)存儲(chǔ)過(guò)程的執(zhí)行進(jìn)行優(yōu)化。此外存儲(chǔ)過(guò)程的存在也避免了在網(wǎng)絡(luò)上大量的原始的SQL語(yǔ)句的傳輸，這樣的優(yōu)勢(shì)是顯而易見(jiàn)的。

4、對(duì)視圖的支持，視圖的存在同樣可以最大限度地利用數(shù)據(jù)庫(kù)服務(wù)器內(nèi)在的優(yōu)化機(jī)制。而且對(duì)于視圖權(quán)限的合理使用，事實(shí)上可以提供行級(jí)別的權(quán)限，這是MySQL的權(quán)限系統(tǒng)所無(wú)法實(shí)現(xiàn)的。

5、對(duì)觸發(fā)器的支持，觸發(fā)器的存在不可避免的會(huì)影響數(shù)據(jù)庫(kù)運(yùn)行的效率，但是與此同時(shí)，觸發(fā)器的存在也有利于對(duì)商業(yè)邏輯的封裝，可以減少應(yīng)用程序中對(duì)同一商業(yè)邏輯的重復(fù)控制。合理地使用觸發(fā)器也有利于保證數(shù)據(jù)的完整性。

6、對(duì)約束的支持。約束的作用更多地表現(xiàn)在對(duì)數(shù)據(jù)完整性的保證上，合理地使用約束，也可以減少編程的工作量。

postgresql查詢表問(wèn)題

表里哪有名為?column?的字段？

再說(shuō)，column是postgresql?（及其它許多數(shù)據(jù)庫(kù)）的關(guān)鍵字，不能做為字段名。

Column是指這個(gè)表的字段這一列，并不是說(shuō)這個(gè)表有一列叫column，看仔細(xì)了。

postgresql 建立索引

一、索引的類型：

PostgreSQL提供了多種索引類型：B-Tree、Hash、GiST和GIN，由于它們使用了不同的算法，因此每種索引類型都有其適合的查詢類型，缺省時(shí)，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索引主要用于等于和范圍查詢，特別是當(dāng)索引列包含操作符" 、=和"作為查詢條件時(shí)，PostgreSQL的查詢規(guī)劃器都會(huì)考慮使用B-Tree索引。在使用BETWEEN、IN、IS NULL和IS NOT NULL的查詢中，PostgreSQL也可以使用B-Tree索引。然而對(duì)于基于模式匹配操作符的查詢，如LIKE、ILIKE、~和 ~*，僅當(dāng)模式存在一個(gè)常量，且該常量位于模式字符串的開(kāi)頭時(shí)，如col LIKE 'foo%'或col ~ '^foo'，索引才會(huì)生效，否則將會(huì)執(zhí)行全表掃描，如：col LIKE '%bar'。

2. Hash：

CREATE INDEX name ON table USING hash (column);

散列(Hash)索引只能處理簡(jiǎn)單的等于比較。當(dāng)索引列使用等于操作符進(jìn)行比較時(shí)，查詢規(guī)劃器會(huì)考慮使用散列索引。

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

3. GiST：

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

4. GIN：

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

二、復(fù)合索引：

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

CREATE TABLE test2 (

major int,

minor int,

name varchar

}

CREATE INDEX test2_mm_idx ON test2 (major, minor);

1. B-Tree類型的復(fù)合索引：

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

2. GiST類型的復(fù)合索引：

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

3. GIN類型的復(fù)合索引：

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

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

三、組合多個(gè)索引：

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

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

四、唯一索引：

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

五、表達(dá)式索引：

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

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

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

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

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

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

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

CREATE INDEX命令的語(yǔ)法通常要求在索引表達(dá)式周圍書(shū)寫(xiě)圓括弧，就像我們?cè)诘诙€(gè)例子里顯示的那樣。如果表達(dá)式只是一個(gè)函數(shù)調(diào)用，那么可以省略，就像我們?cè)诘谝粋€(gè)例子里顯示的那樣。

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

六、部分索引：

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

由于不是在所有的情況下都需要更新索引，因此部分索引會(huì)提高數(shù)據(jù)插入和數(shù)據(jù)更新的效率。然而又因?yàn)椴糠炙饕绕胀ㄋ饕?，因此可以更好的提高確實(shí)需要索引部分的查詢效率。見(jiàn)以下三個(gè)示例：

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');

下面的查詢將會(huì)用到該部分索引：

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

下面的查詢將不會(huì)用該部分索引：

一個(gè)不能使用這個(gè)索引的查詢可以是

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

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

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

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

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

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

那么對(duì)于如下查詢呢？

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

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

下面的查詢將不會(huì)用到部分索引。

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;

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

七、檢查索引的使用：

見(jiàn)以下四條建議：

1. 總是先運(yùn)行ANALYZE。

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

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

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

3. 如果索引沒(méi)有得到使用，那么在測(cè)試中強(qiáng)制它的使用也許會(huì)有些價(jià)值。有一些運(yùn)行時(shí)參數(shù)可以關(guān)閉各種各樣的查詢規(guī)劃。

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