Postgres uses returning to implement last_insert_id of mysql

Postgres uses returning to implement last_insert_id of mysql. Today, I am asked if postgres has inserted a value like mysql and returned the inserted value. This is yes and has higher scalability. Example: [S @ localhost ~] $ Psqlpsql (9.2.4) Typehelpforhelp. S # createtable

Postgres uses returning to implement last_insert_id of mysql. Today, I am asked if postgres has inserted a value like mysql and returned the inserted value. This is yes and has higher scalability. Example: [S @ localhost ~] $ Psql (9.2.4) Type help for help. S = # create table

Postgres uses returning to implement last_insert_id of mysql

Today, I asked if postgres has inserted a value like mysql and then returned the inserted value. This is yes and has higher scalability.

Example:

[S @ 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

Extension:

A. return more insert content

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

S = # 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. Return the deleted data.

S = # 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

S = # 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. Return the updated data.

S = # 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

Last_insert_id of mysql has many restrictions and notes. For example, if the field requires auto_increment, only the first id value is returned when multiple values are inserted in an SQL statement.