PostgresSQL用returning实现mysql的last_insert_id
2024-09-08 18:32:47
今天开发问到,postgressql里面有没有像mysql那样插入一个值后返回插入的值,这个是有的,而且有更强的扩展性。
示例:
[postgres@localhost ~]$ psql
psql (9.2.4)
Type "help" for help.
postgres=# create table t_kenyon(id int,vname varchar(30),remark text);
CREATE TABLE
postgres=# insert into t_kenyon(id,vname) values(1,'test_kenyon') returning id;
id
----
1
(1 row)
INSERT 0 1
postgres=# insert into t_kenyon(id,vname) select generate_series(1,5),'Kenyon here' returning id;
id
----
1
2
3
4
5
(5 rows)
INSERT 0 5
扩展:
a.返回更多的insert内容
postgres=# insert into t_kenyon(id,vname) select generate_series(6,8),'Kenyon here' returning id,vname;
id | vname
----+-------------
6 | Kenyon here
7 | Kenyon here
8 | Kenyon here
(3 rows)
INSERT 0 3
postgres=# insert into t_kenyon(id,vname,remark) select generate_series(9,11),'Kenyon here','KENYON GOOD BOY!' returning *;
id | vname | remark
----+-------------+------------------
9 | Kenyon here | KENYON GOOD BOY!
10 | Kenyon here | KENYON GOOD BOY!
11 | Kenyon here | KENYON GOOD BOY!
(3 rows)
INSERT 0 3
b.返回delete掉的数据
postgres=# select * from t_kenyon;
id | vname | remark
----+-------------+------------------
1 | test_kenyon |
1 | Kenyon here |
2 | Kenyon here |
3 | Kenyon here |
4 | Kenyon here |
5 | Kenyon here |
6 | Kenyon here |
7 | Kenyon here |
8 | Kenyon here |
9 | Kenyon here | KENYON GOOD BOY!
10 | Kenyon here | KENYON GOOD BOY!
11 | Kenyon here | KENYON GOOD BOY!
(12 rows)
postgres=# delete from t_kenyon where id >9 returning id,vname;
id | vname
----+-------------
10 | Kenyon here
11 | Kenyon here
(2 rows)
DELETE 2
postgres=# delete from t_kenyon where id <5 returning *;
id | vname | remark
----+-------------+--------
1 | test_kenyon |
1 | Kenyon here |
2 | Kenyon here |
3 | Kenyon here |
4 | Kenyon here |
(5 rows)
DELETE 5
postgres=# select * from t_kenyon;
id | vname | remark
----+-------------+------------------
5 | Kenyon here |
6 | Kenyon here |
7 | Kenyon here |
8 | Kenyon here |
9 | Kenyon here | KENYON GOOD BOY!
(5 rows)
c.返回update掉的数据
postgres=# update t_kenyon set remark = 'kenyon bad boy!' where id <7 returning id,remark;
id | remark
----+-----------------
5 | kenyon bad boy!
6 | kenyon bad boy!
(2 rows)
UPDATE 2
mysql的last_insert_id使用有诸多限制和注意的地方,如字段需要auto_increment,一个SQL插入多个值的时候只会返回第一个id值,此不再叙述。
最新文章
- button自适应宽度 并根据屏幕宽自动换行排列
- IIS7.0上传文件限制的解决方法
- mongodb ubuntu下自启动
- WPF 窗口在右下角出现,识别分辨率
- 《转载》使用CSS3 Flexbox布局
- Hibernate常见异常
- 高性能日志类KLog(已开源代码)
- Java开发笔记(十一)常见的数学函数
- C\C++控制台程序隐藏方法总结
- hashMap源码解析(四)
- 转:前端页面a标签嵌套a标签效果的两种解决方案
- ie9 form submit 请求参数问题替代办法
- Jmeter常用脚本开发之Junit Request
- WINRAR 自解压脚本命令及变量
- Nginx/LVS/HAProxy负载均衡软件的优缺点详解【转】
- 20165333 学习基础和C语言学习基础
- 三极管工作区在Spectre中的表示
- java并行之parallelStream与CompletableFuture比较
- Linux命令(基础3)
- matplotlib对LaTeX数学公式的支持
热门文章
- 【每日一题】【栈和队列、双端队列】20. 有效的括号/NC52 有效括号序列-211127/220126
- 教你铁威马NAS中如何进行阵列升级
- java中的静态属性和静态方法
- 一款极简的流媒体Web服务器(Streaming Media Web Server),提供视频音乐的在线播放功能
- [编程基础] Python随机数生成模块总结
- [OpenCV实战]26 基于OpenCV实现选择性搜索算法
- C语言函数值传递问题
- python之路34 MySQL 2 配置文件 字段类型
- 学习记录C
- AtCoder Beginner Contest 285 解题报告