PostgreSQL 筆記

feellikelearning October 28, 2023 0 Comments

把一個表的數據加到另一表

INSERT INTO my_table (col1, col2)
SELECT col1, col2
FROM my_table2;

從timestamp裏面提取Date

-- method 1
SELECT DATE(your_timestamp_column) AS extracted_date
FROM your_table;

-- method 2
SELECT EXTRACT(DATE FROM your_timestamp_column) AS extracted_date
FROM your_table;

給一個表添加列

-- pattern
ALTER TABLE table_name
ADD COLUMN new_column_name data_type;

-- example 1
ALTER TABLE employees
ADD COLUMN email VARCHAR(255);

-- example 2
ALTER TABLE employees
ADD COLUMN email VARCHAR(255) NOT NULL DEFAULT 'N/A';

-- example 3
ALTER TABLE table_name
ADD COLUMN new_column_name TEXT;

用with keyword執行一系列查詢

-- Create multiple CTEs and then select from them
WITH cte1 AS (
    SELECT
        id,
        name
    FROM
        table1
),
cte2 AS (
    SELECT
        id,
        description
    FROM
        table2
)
-- Select from the CTEs
SELECT
    cte1.id AS cte1_id,
    cte1.name,
    cte2.id AS cte2_id,
    cte2.description
FROM
    cte1
JOIN
    cte2 ON cte1.id = cte2.id;

用臨時表

WITH…AS… 的表在query完成後就消失了，如果在當前session需要的臨時表，可以用temp table。語法和普通表幾乎一樣，就是多了個TEMP關鍵詞。

-- Create a temporary table named "temp_sales" with some sample data
CREATE TEMP TABLE temp_sales (
    order_id serial PRIMARY KEY,
    product_name VARCHAR(255),
    quantity INT,
    order_date DATE
);

-- Insert some sample data into the temporary table
INSERT INTO temp_sales (product_name, quantity, order_date)
VALUES
    ('Product A', 10, '2023-10-16'),
    ('Product B', 5, '2023-10-17'),
    ('Product C', 8, '2023-10-18');

-- Query the temporary table
SELECT * FROM temp_sales;

-- Drop the temporary table (Optional, it will be dropped automatically at the end of the session)
-- DROP TABLE temp_sales;

temp table和普通表的區別是，temp table只在當前session存在，session結束後就會自動清空。