Inserting Data

--- Single Row
INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', DEFAULT);

--- Multiple Row
INSERT INTO products (product_no, name, price) VALUES
    (1, 'Cheese', 9.99),
    (2, 'Bread', 1.99),
    (3, 'Milk', 2.99);

如果需要大批量插入数据，PG 的 COPY 命令在性能上是更好的选择（但是灵活性不如 INSERT）。

Updating Data

UPDATE products SET price = 10 WHERE price = 5;
UPDATE products SET price = price * 1.10;
UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

Deleting Data

DELETE FROM products WHERE price = 10;
DELETE FROM products;

Returning Data

--- Insert
CREATE TABLE users (firstname text, lastname text, id serial primary key);
INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

--- Update
UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

--- Delete
DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;