---
# System prepended metadata

title: 'PostgreSQL '
tags: [postgresql]

---

安裝
```=
# 更新套件庫
sudo apt update -y
```
```=
# 安裝 PostgreSQL
sudo apt install postgresql postgresql-contrib
```
```=
# 查看 PostgreSQL 服務狀態
sudo systemctl status postgresql
```



# 基本語法
使用\l用於查看已經存在的數據庫
```=
postgres = # \ l   
postgres = # CREATE DATABASE dbname;(創建資料庫)
postgres=# DROP DATABASE runoobdb;(刪除)

```

接下來我們可以使用\c + 數據庫名來進入數據庫
```=
postgres =# \c runoobdb
您現在以用戶“postgres ”連接到數據庫“runoobdb” 。
runoobdb =#  
```
建DB
```=
CREATE DATABASE database_name;
```
修改欄位設定
```=
ALTER TABLE (news_title) ALTER COLUMN (title) TYPE VARCHAR(1000);
```
刪除
```=
DELETE FROM news_title WHERE  news_source  = ' value';
```
### PostgreSQL 創建表格以我們的例子為例

```=
CREATE TABLE Keyword_List (
   ID INT PRIMARY KEY NOT NULL,
   Publish_Date DATE NOT NULL,
   Timestamp TIMESTAMP NOT NULL,
   News_Source CHAR(50),
   Keyword_List TEXT[]
);
```
```=
CREATE TABLE news_articles (
    UID SERIAL PRIMARY KEY,
    Publish_Date DATE,
    Timestamp TIMESTAMP,
    Source VARCHAR(100),
    Author VARCHAR(100),
    R INT,
    Title VARCHAR(200),
    Content TEXT,
    ip VARCHAR(50),
    Upvotes INT,
    Downvotes INT,
    Score INT
);
```
```=
CREATE TABLE postive (
    id SERIAL PRIMARY KEY,
    date DATE,
    keyword TEXT,
    ptt_all_content TEXT,
    postive INTEGER,
    negative INTEGER,
    postive_percent DOUBLE PRECISION GENERATED ALWAYS AS (postive::DOUBLE PRECISION / (postive + negative)) STORED,
    negative_percent DOUBLE PRECISION GENERATED ALWAYS AS (negative::DOUBLE PRECISION / (postive + negative)) STORED
);

ALTER TABLE gossiping
ADD COLUMN source TEXT DEFAULT 'gossiping';

```
```=
CREATE TABLE hatepolitics (
    id SERIAL PRIMARY KEY,
    date DATE,
    keyword TEXT,
    ptt_all_content TEXT,
    positive INTEGER,
    negative INTEGER,
    postive_percent DOUBLE PRECISION GENERATED ALWAYS AS (
        CASE
            WHEN (positive + negative) = 0 THEN 0
            ELSE positive::DOUBLE PRECISION / (positive + negative)
        END
    ) STORED,
    negative_percent DOUBLE PRECISION GENERATED ALWAYS AS (
        CASE
            WHEN (positive + negative) = 0 THEN 0
            ELSE negative::DOUBLE PRECISION / (positive + negative)
        END
    ) STORED
);





ALTER TABLE hatepolitics
ADD COLUMN source TEXT DEFAULT 'hatepolitics';
```


使用\d命令來查看表格是否創建成功

election=# \d

![](https://hackmd.io/_uploads/HyxwP8gOth.png)


使用 \d tablename 查看表格信息
```=
election=# \d keyword_list
```
![](https://hackmd.io/_uploads/BJ-RIeuth.png)
```=
CREATE SEQUENCE keyword_list_id_seq;
ALTER TABLE keyword_list ALTER COLUMN id SET DEFAULT nextval('keyword_list_id_seq');
SELECT setval('news_title_id_seq', 8820, true);
重設ID起始位置
```
### 用戶管理
創建用戶：
```=
CREATE USER username WITH PASSWORD 'password';
```
刪除用戶：
```=
DROP USER username;
```
修改用戶密碼：
```=
ALTER USER username WITH PASSWORD 'new_password';
```
授予用戶特定數據庫的訪問權限：
```=
GRANT ALL PRIVILEGES ON database_name TO username;
GRANT ALL PRIVILEGES ON DATABASE ptt TO kenny;
GRANT ALL PRIVILEGES ON TABLE table_name TO kenny;
```
撤銷用戶對特定數據庫的訪問權限：
```=
REVOKE ALL PRIVILEGES ON database_name FROM username;
```
修改用戶的名稱：
```=
ALTER USER current_username RENAME TO new_username;
```
修改用戶的角色：
```=
ALTER USER username WITH SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB;
```
列出所有用戶：

```=
SELECT usename FROM pg_user;
```
顯示用戶的詳細信息：
```=
\du username
```


### 篩選
```=
SELECT keyword
FROM articles
WHERE source = 'HatePolitics'
  AND title NOT LIKE '[新聞]%'
  AND title NOT LIKE '[轉錄]%';
```
 
 
 
##### 條件篩選 
```=
SELECT keywords, COUNT(*) AS count
FROM  fp_45c
WHERE source = 'HatePolitics'  
  AND NOT (title LIKE '[新聞]%' OR title LIKE '[轉錄]%')
GROUP BY keywords;

#Gossiping
```

```=
 DELETE FROM article
WHERE DATE(publish_date) = '2023-08-15';

DELETE FROM article
WHERE title LIKE '[新聞]%' OR title LIKE '[轉錄]%';

```
對於table `keyword_list`，你可以建立一個觸發器 (trigger) 在每次插入資料時觸發計數。以下是相關的程式碼示例：

首先，建立名為 `keyword_list` 的資料表：

```sql
CREATE TABLE keyword_list (
  id serial PRIMARY KEY,
  publish_date DATE NOT NULL,
  timestamp TIMESTAMPTZ NOT NULL,
  news_source VARCHAR(50),
  keyword_list TEXT[]
);
```
## trigger
然後，創建一個名為 `keyword_list_trigger` 的觸發器，在每次插入資料時觸發計數：

```sql
CREATE FUNCTION news_title_trigger_function()
  RETURNS TRIGGER AS $$
DECLARE
  news_count INTEGER;
BEGIN
  -- 檢查該新聞來源 (news_source) 是否已觸發過計數
  SELECT COUNT(*) INTO news_count
  FROM trigger_log
  WHERE date = NEW.publish_date
    AND news_source = NEW.news_source;

  -- 只有在該新聞來源今日尚未觸發計數時才進行計數
  IF news_count = 0 THEN
    -- 在此處放置觸發計數的程式碼
    /* 執行計數相關的操作 */
    RAISE NOTICE '觸發計數';

    -- 新增 trigger_log 記錄
    INSERT INTO trigger_log (date, news_source)
    VALUES (NEW.publish_date, NEW.news_source);
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_news
AFTER INSERT ON news_title
FOR EACH ROW
EXECUTE FUNCTION news_title_trigger_function();
```

以上程式碼中，`keyword_list_trigger_function` 函數為觸發器的處理函數。觸發器在每次插入 `keyword_list` 表中的資料時被觸發。觸發器首先檢查該新聞來源 (news_source) 是否已在當天觸發過計數，並只有在該新聞來源今日尚未觸發計數時才進行計數操作。然後，觸發器新增一筆 `/trigger_log` 表中的記錄以標記已觸發計數。最後，它將插入的資料返回給 `keyword_list` 表。

請注意，上述示例中使用的 `trigger_log` 資料表並不是我之前提到的 `trigger_log`，這是一個新的表格，用於記錄已觸發過計數的新聞來源。你需要根據需求自行創建該表。






