PostgreSQL新增/插入資料的方式如下。
例如有employee資料表如下。
employee
CREATE TABLE IF NOT EXISTS employee (
id bigserial PRIMARY KEY,
name varchar(60) UNIQUE NOT NULL,
age integer,
created_at timestamp NOT NULL
);
下面則為新增一筆資料的語法。
INSERT INTO employee (name, age, created_at) VALUES ('john', 33, CURRENT_TIMESTAMP);
執行INSERT
後會返回INSERT oid count
,count
為新增的筆數。例如上面執行結果返回新增1筆(INSERT 0 1
)。
postgres=> INSERT INTO employee (name, age, created_at) VALUES ('john', 33, CURRENT_TIMESTAMP);
INSERT 0 1
查詢剛新增的資料。
postgres=> SELECT * FROM employee WHERE name = 'john';
id | name | age | created_at
----+------+-----+----------------------------
1 | john | 33 | 2021-11-24 22:07:21.697726
(1 row)
新增多筆資料語法如下。
INSERT INTO employee (name, age, created_at) VALUES
('mary', 28, CURRENT_TIMESTAMP),
('tony', 45, CURRENT_TIMESTAMP);
psql執行結果。
postgres=> INSERT INTO employee (name, age, created_at) VALUES
('mary', 28, CURRENT_TIMESTAMP),
('tony', 45, CURRENT_TIMESTAMP);
INSERT 0 2
查詢剛新增的兩筆資料。
postgres=> SELECT * FROM employee ORDER BY id DESC LIMIT 2;
id | name | age | created_at
----+------+-----+----------------------------
3 | tony | 45 | 2022-03-19 11:27:07.237089
2 | mary | 28 | 2022-03-19 11:27:07.237089
(2 rows)
沒有留言:
張貼留言