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结束后就会自动清空。