公告版位
需要找什麼嗎? 左邊分類或許可以幫助你...

目前分類:[資料庫] PostgreSQL (28)

瀏覽方式: 標題列表 簡短摘要

-- 先建立外部模組

CREATE EXTENSION pgcrypto;

-- 再建立 test_user 表格

CREATE TABLE test_user(id SERIAL, username VARCHAR(32), password TEXT);

CREATE unique index idx_test_user_username on test_user using btree (username);

黃彥霖 發表在 痞客邦 留言(3) 人氣()

雖然我標題寫 Json、Jsonb 操作,但實際上我這裡只會用 Jsonb 來示範

會用 Jsonb 實際上就會用 Json ,因為 Jsonb 效能比較好 (資料無排序),但如果你要有排序功能,可以用傳統 Json 來操作

前置作業重點:

插入 Jsonb 無法用 INSERT INTO ,因為他找不到 資料 插....,所以要先用 INSERT INTO 先插入一個 空 資料,這樣未來 UPDATE 才找的到洞插入...

更多資源可以參考 PostgreSQL 官方的 Json 文件:http://www.postgresql.org/docs/9.5/static/function...

黃彥霖 發表在 痞客邦 留言(1) 人氣()

範例:

CREATE OR REPLACE FUNCTION x() RETURNS TABLE(a INT, b TEXT, c TEXT) AS
$$
    DECLARE data TEXT := 'Loli';
    BEGIN
        RETURN QUERY SELECT 5, 'Hello'::TEXT, data;
    END;
$$
LANGUAGE plpgsql;

黃彥霖 發表在 痞客邦 留言(0) 人氣()

-- 判斷表是否已經有資料了? 沒有的話就做初始化動作,也就是插入 {} 大括號,以初始化 location-Jsonb

IF NOT EXISTS (SELECT * FROM location) THEN
    INSERT INTO location VALUES ('{}');
END IF;





黃彥霖 發表在 痞客邦 留言(0) 人氣()

查看擴充套件

select * from pg_extension;

列出可安裝清單,uuid-ossp 會出現在裡面

select * from pg_available_extensions;

安裝

黃彥霖 發表在 痞客邦 留言(0) 人氣()

官方文件:陣列 http://www.postgresql.org/docs/9.5/static/functions-array.html


建立 run () 涵式:

整數陣列:

CREATE OR REPLACE FUNCTION run(data int[]) RETURNS SETOF int AS
    $BODY$
        DECLARE i int;

黃彥霖 發表在 痞客邦 留言(0) 人氣()

1. 用現在時間寫入 Json 

SELECT jsonb_set('{"A":"B"}', '{"A"}', to_jsonb(now()));

2. Json 時間比大小:

假設我們有以下資料

SELECT * FROM json;
                                 data
------------------------------------------------------------------------------------

黃彥霖 發表在 痞客邦 留言(0) 人氣()

這是 PostgreSQL 9.5 版以上才有的語法
詳細可參考這裡:http://www.postgresql.org/docs/9.5/static/functions-json.html

1. 首先我們先建立一個 Json 表格
CREATE TABLE json (data JSONB);

2. 插入 Json 資料
INSERT INTO json VALUES ('{"A":"B"}');

3. 查看我們剛剛的資料

黃彥霖 發表在 痞客邦 留言(0) 人氣()

1. 先建立一個 users 表格:

CREATE TABLE users (
    id    serial    PRIMARY    KEY,
    username    TEXT    NOT    NULL,
    passwd    TEXT
);

2. 然後連續插入三次

黃彥霖 發表在 痞客邦 留言(0) 人氣()

crypt 就算插入相同的文字,也會顯示不同密碼


CREATE EXTENSION pgcrypto;

AES 加密:
create table demo(pw bytea);
insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') );

AES 解密:

黃彥霖 發表在 痞客邦 留言(0) 人氣()

CREATE OR REPLACE FUNCTION run() RETURNS TEXT AS
    $BODY$
        DECLARE k INT;
        BEGIN
            k := 0; -- 第一次宣告需要使用冒號等於
            FOR i IN 1..10 LOOP
                k = k + 1;
            END LOOP;
            RETURN '總共有 ' || k || ' 筆資料。'; -- 兩條直線代表字串串接,類似 Java、C# 的 +
        END

黃彥霖 發表在 痞客邦 留言(0) 人氣()

CREATE FUNCTION max (a INT, b INT) RETURNS INT AS
    $BODY$
        BEGIN
            IF (a > b) THEN
                RETURN a;
            ELSE
                RETURN b;
            END IF;
        END
    $BODY$

黃彥霖 發表在 痞客邦 留言(0) 人氣()

以下教大家如何用 PostgreSQL 操作 JSON

算是 SQL 與 NoSQL 結合的使用
(以下示範:新增、查詢

傳統的欄位屬性有text, int ....等等,現在多一個 json 與 jsonb

比較建議大家使用jsonb , 因為性能較好, 是二進制存取

先來看SQL語法如何做到吧!

詳細可參考官方文檔 (9.4 版):http://www.postgresql.org/docs/9.4/static/functions-json.html

黃彥霖 發表在 痞客邦 留言(0) 人氣()

修改 pg_hba.conf 檔案

加入可訪問的 IP 地址,如下:

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

# TYPE DATABASE USER ADDRESS METHOD

# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 192.168.1.10/32 md5

黃彥霖 發表在 痞客邦 留言(0) 人氣()

SELECT count(*) FROM data;

 

 

 


黃彥霖 發表在 痞客邦 留言(0) 人氣()

select extract(epoch FROM (time0 - time1)) // 取得時間差 (秒數)

select age(now(), timestamp '1989-02-05'); // 取得時間差 (年齡)





黃彥霖 發表在 痞客邦 留言(0) 人氣()

ALTER DATABASE <資料庫名稱> SET BYTEA_OUTPUT TO 'escape';

後面的單引號可以輸入 hex 或 escape ,由於預設是 hex 十六進制
所以由程式讀取 ( 如:C#、Java ) 會與放入前數值不一樣,且長度也不一樣
這時我們就需要事前使用這個語法,來改變資料庫輸出格式





黃彥霖 發表在 痞客邦 留言(0) 人氣()

我目前有兩個資料庫,一個是 postgres 另一個是 test 資料庫。今天我想要備份 test 資料庫,且 test 資料庫裡面有 my 資料表。

 

1

 

今天我只要在 test 資料庫上 按下滑鼠右鍵,選擇裡面的備份。

 

2

 

接著會進入這裡,選擇好你要備份的路徑,編碼方式、備份格式...等等

 

黃彥霖 發表在 痞客邦 留言(0) 人氣()

假設我新建一個表:CREATE TABLE my ( use   text,  pw   text );

插入一個資料 aaa111aaa 到 use 欄,bbb222bbb 到 pw 欄 且是經過 MD5 加密的:

INSERT INTO my (use,pw) VALUES (md5('aaa111aaa'),md5('bbb222bbb'));

 

此時我們搜尋 my 表:SELECT * FROM my ; 會得到以下數據:

use                                                         |                    pw
--------------------------------------------------+-------------------------------------------------
6248568d882f68e086bc1b557eba13a6 | 199d0afd164d634563fbfec5dfa29f7d

由上我們可以發現原先資料 aaa111aaa 與 bbb222bbb 資料都被加密了。

黃彥霖 發表在 痞客邦 留言(0) 人氣()

SQL99是數據庫的一個ANSI/ISO標準。這個標準的前身是SQL92 ANSI/ISO標準,而SQL92之前還有一個SQL89 ANSI/ISO標準。它定義了一種語言(SQL)以及數據庫的行為(事務、隔離級別等)。你知道許多商業數據庫至少在某種程度上是符合SQL99的嗎?不過,這對於查詢和應用的可移植性沒有多大的意義,這一點你也清楚嗎?
SQL92標準有4個層次:
q 入門級(Entry level)。這是大多數開發商符合的級別。這一級只是對前一個標準SQL89稍做修改。所有數據庫開發商都不會有更高的級別,實際上,美國國家標準和技術協會NIST(National Institute of Standards and Technology,這是一家專門檢驗SQL合規性的機構)除了驗證入門級外,甚至不做其他的驗證。 Oracle 7.0於1993年通過了NIST的SQL92入門級合規性驗證,那時我也是小組中的一個成員。如果一個數據庫符合入門級,它的特性集則是Oracle 7.0的一個功能子集。
q 過渡級。這一級在特性集方面大致介於入門級和中間級之間。
q 中間級。這一級增加了許多特性,包括(以下所列並不完整):
n 動態SQL
n 級聯DELETE以保證引用完整性
n DATE和TIME數據類型
n 域
n 變長字符串

黃彥霖 發表在 痞客邦 留言(1) 人氣()

1 2