Alias

甚至你可以幫你的tabel命名

danny=# 
select id , make ,model,Round( price *.10,2) as ten_percent,Round( price-(price *.10),2) as discount_after_10_percent,price as origin_price from car;

coalesce

當我們下指令列出user所有email時你會發現有些人沒有email

danny=# slect email from person;

這時候就可以下coalesce

danny=# select coalesce(email,'envaild email') from person;

hint :
coalesce(arg1,arg2,...) 返回第一個非null的值

nullif

NULLIF ( expression , expression )

NULLIF如果兩個express相等就回傳null，如果不相等就回傳第一個值。

danny=# select nullif(100,100);
 nullif
--------

(1 row)

danny=# select nullif(100,19);
 nullif
--------
    100
(1 row)

//demo1
danny=# select coalesce(10/nullif(0,0),0);
 coalesce
----------
        0
(1 row)

//demo2
danny=# select coalesce(null,0);
 coalesce
----------
        0
(1 row)

//結合coalesce demo1跟demo2的做法是一樣的。

time format

備註

danny=# select now();
              now
-------------------------------
 2023-02-25 20:04:00.925601+08
(1 row)

danny=# select now()::date;
    now
------------
 2023-02-25
(1 row)

danny=# select now()::time;
       now
-----------------
 20:04:19.478294
(1 row)

//在psql中日期可以累加透過interval(間隔)運算符推做日期加總
danny=# select date '2001-09-28' + interval '1 hours';
   ?column?
---------------------
 2001-09-28 01:00:00
(1 row)

時間運算

// etc 找出去年的時間
danny=# select now() - interval '1 yaers';
   ?column?
---------------------
 2022-02-25 20:15:12.742052+08
(1 row)

但你會發現output格式多了小時，這時可以透過formate decorator只計算日期

danny=# select now()::date - interval '1 years';
      ?column?
---------------------
 2022-02-25 00:00:00
(1 row)

或是可以在簡化成以下的output

danny=# select (now() - interval '1 years')::date;
    date
------------
 2022-02-25
(1 row)

//extract 
danny=# select extract(year from now());
 extract
---------
    2023
(1 row)

//甚至可以選取世紀
danny=# select extract(century from now());
 extract
---------
      21
(1 row)

計算歲數

danny=# 
select first_name ,last_name,date_of_birth,age(now()::date,date_of_birth) as age from person;

修改table 欄位（ALTER TABLE）

移除pkey,constraint則是用來約束欄位的宣告例如宣告pkey就要加constraint

danny=# 
alter table person drop constraint person_pkey;

新增主鍵，注意如果要宣告的欄位如果資料重複會無法宣告主鍵

danny=# select * from person where id=1;

 id | first_name | last_name |          email           | gender | date_of_birth | country_of_birth
----+------------+-----------+--------------------------+--------+---------------+------------------
  1 | Rafa       | Hallagan  | rhallagan0@amazonaws.com | Female | 2022-12-20    | Japan
  1 | Rafa       | Hallagan  | rhallagan0@amazonaws.com | Female | 2022-12-20    | Japan
(2 rows)

danny=# alter table person add primary key(id);
//如果你要射pk的欄位data有重複就不能設置主鍵
ERROR:  could not create unique index "person_pkey"
DETAIL:  Key (id)=(1) is duplicated.


//解決方法重新新增唯一性data
danny=# delete from person where id = 1
danny=# insert into person (id,first_name, last_name, email, gender, date_of_birth, country_of_birth) values (1,'Rafa', 'Hallagan', 'rhallagan0@amazonaws.com', 'Female', '12/20/2022', 'Japan');

查詢email重否重複

danny=# select email ,count(*) from person
group by email;

圖中表示有304位person是沒有email的

將group by 結果做having

裡會發現304個null email，而duplicate email則是rhallagan0@amazonaws.com跟 rchaster1@wiley.com ，分別是3位跟2位

danny=# select email,count(*) from person group by email having count(*)>1;
                                                                      
          email           | count
--------------------------+-------
                          |   304
 rhallagan0@amazonaws.com |     3
 rchaster1@wiley.com      |     2
(3 rows)

這時候將duplicate的email list出來，你會發現當我要發送rchaster1@wiley.com 的mail，你會不知道到底發給誰，這時候我們就要做unique fileds

danny=# select * from person where email ='rchaster1@wiley.com';
  id  | first_name | last_name |        email        | gender | date_of_birth | country_of_birth
------+------------+-----------+---------------------+--------+---------------+------------------
    2 | Rich       | Chaster   | rchaster1@wiley.com | Male   | 2022-08-25    | Germany
 1003 | Danny       | Wu   | rchaster1@wiley.com | Male   | 2022-11-06    | Germany
(2 rows)

先移除duplicate的data

danny=# select * from person where email ='rchaster1@wiley.com';
  id  | first_name | last_name |        email        | gender | date_of_birth | country_of_birth
------+------------+-----------+---------------------+--------+---------------+------------------
    2 | Rich       | Chaster   | rchaster1@wiley.com | Male   | 2022-08-25    | Germany
 1003 | Rich       | Chaster   | rchaster1@wiley.com | Male   | 2022-08-25    | Germany
(2 rows)

danny=# delete from person where id = 1003;


danny=# select * from person where email ='rhallagan0@amazonaws.com';
  id  | first_name | last_name |          email           | gender | date_of_birth | country_of_birth
------+------------+-----------+--------------------------+--------+---------------+------------------
 1001 | Rafa       | Hallagan  | rhallagan0@amazonaws.com | Female | 2022-12-20    | Japan
 1002 | Rafa       | Hallagan  | rhallagan0@amazonaws.com | Female | 2022-12-20    | Japan
    1 | Rafa       | Hallagan  | rhallagan0@amazonaws.com | Female | 2022-12-20    | Japan
(3 rows)

danny=# delete from person where id in ('1001','1002');

當使用constraint後的field，如果在insert duplicate的data就會出現警告，
constraint 用於約束table field用法會是[method] constraint [constraint_key] [限制條件]

danny=#  alter table person add constraint unique_email_address UNIQUE(email);

danny=# insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Rich', 'Chaster', 'rchaster1@wiley.com', 'Male', '8/25/2022', 'Germany');
ERROR:  duplicate key value violates unique constraint "unique_email_address"
DETAIL:  Key (email)=(rchaster1@wiley.com) already exists

移除constraint [key]

danny=# alter table person drop constraint "unique_email_address";
ALTER TABLE

distinct常用於select field中資料的type

danny=# select distinct gender from person;
   gender
-------------
 Genderqueer
 Bigender
 hello
 Genderfluid
 Male
 Non-binary
 Polygender
 Female
 Agender
(9 rows)

刪除不要的option資料

danny=# select distinct gender from person;
   gender
-------------
 Male
 Non-binary
 Polygender
 Female
 hello
 Agender
 Genderfluid
(7 rows)

danny=# delete from person
danny-# where gender in ('Non-binary','Polygender','hello','Agender','Genderfluid');

之後新增constraint到table field這樣之後insert data時，gender必須是'Male','Female'否則會報錯

danny=# alter table person add constraint gender_constraint check (gender in ('Male','Female'));

delete table

danny=# delete from person where id = '501';
DELETE 1

update table

danny=# UPDATE  person   SET email='hiunji64@gmail.com' WHERE id =13;
UPDATE 1

danny=# UPDATE person SET first_name='Danny' ,last_name='Wu' WHERE id =14;

檢查constraint欄位，如果value duplicate就不操作db

danny=# insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Rich', 'Chaster', 'hello@wiley.com', 'Male', '8/25/2022', 'Germany') ON CONFLICT(email) DO NOTHING;
INSERT 0 0

ON CONFLICT的參數只能是有constraint過的欄位如果沒有會報錯，例如ON CONFLICT(first_name) ，DO NOTHING代表一個action,如果inset的data沒有符合constraint的要求會就不做db操作

conflict update

danny=# select * from person where id=1006;
  id  | first_name | last_name |       email        | gender | date_of_birth | country_of_birth
------+------------+-----------+--------------------+--------+---------------+------------------
 1006 | Rich       | Chaster   | hello@wiley.com.uk | Male   | 2022-08-25    | Germany
(1 row)

danny=# insert into person (first_name, last_name, email, gender, date_of_birth, country_of_birth) values ('Danny', 'Wu', 'Danny@wiley.com.uk', 'Male', '8/25/2022', 'Germany') ON CONFLICT(email) DO UPDATE SET email=EXCLUDED.email,first_name=EXCLUDED.first_name,last_name=EXCLUDED.last_name;
INSERT 0 1
danny=# select * from person where id=1006;
  id  | first_name | last_name |       email        | gender | date_of_birth | country_of_birth
------+------------+-----------+--------------------+--------+---------------+------------------
 1006 | Rich       | Chaster   | hello@wiley.com.uk | Male   | 2022-08-25    | Germany
(1 row)

我們可以透過ON CONFLICT後重寫入db資料