Details
-
New Feature
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
Description
Please add a RETURNING option to INSERT.
Example from PostgreSQL
postgres=# CREATE TABLE t1 (id SERIAL, name VARCHAR(100));
|
CREATE TABLE
|
postgres=# INSERT INTO t1(name) VALUES('test') RETURNING id;
|
id
|
----
|
1
|
(1 row)
|
|
|
INSERT 0 1
|
Inspired by: https://evertpot.com/writing-sql-for-postgres-mysql-sqlite/
This could make it easier to write statements which work with both MariaDB and PostgreSQL. And this might improve compatibility with Oracle RDBMS.
How it'll work:
- All variants of INSERT will work — INSERT ... VALUES, INSERT ... SET, INSERT ... SELECT
- INSERT ... ON DUPLICATE KEY UPDATE will work, will return the final row values (inserted or updated)
- REPLACE will return what's inserted
- INSERT IGNORE will return what it'll try to insert, (ignoring insert errors, as expected)
- INSERT DELAYED will, obviously, not work
- rows will be returned as they're being inserted. If the insert fails in the middle and is rolled back — rows might've been sent that didn't end up in the table
Attachments
Issue Links
- is duplicated by
-
MDEV-10422 INSERT RETURNING
-
- Closed
-
-
MDEV-20033
sql_mode="oracle" does not support INSERT INTO ... RETURNING
-
- Closed
-
- is part of
-
-
- In Progress
-
- links to
Activity
| Field | Original Value | New Value |
|---|---|---|
| Link |
This issue is duplicated by |
| Link |
This issue duplicates |
| Link |
This issue is duplicated by |
| Priority | Minor [ 4 ] | Major [ 3 ] |
| Labels | gsoc19 |
| Labels | gsoc19 |
| Labels | gsoc19 |
| Assignee | Oleksandr Byelkin [ sanja ] |
| Link |
This issue relates to |
| Assignee | Oleksandr Byelkin [ sanja ] | Sergei Golubchik [ serg ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Status | In Progress [ 3 ] | Stalled [ 10000 ] |
| Status | Stalled [ 10000 ] | In Review [ 10002 ] |
| Remote Link | This issue links to "PR (Web Link)" [ 29203 ] |
| Description |
(set as 'Data Manipulation - Insert Delayed', because I couldn't find a component for 'regular' insert)
Please add a RETURNING option to INSERT. Example from PostgreSQL {noformat} postgres=# CREATE TABLE t1 (id SERIAL, name VARCHAR(100)); CREATE TABLE postgres=# INSERT INTO t1(name) VALUES('test') RETURNING id; id ---- 1 (1 row) INSERT 0 1 {noformat} Inspired by: https://evertpot.com/writing-sql-for-postgres-mysql-sqlite/ This could make it easier to write statements which work with both MariaDB *and* PostgreSQL. And this might improve compatibility with Oracle RDBMS. |
Please add a RETURNING option to INSERT.
Example from PostgreSQL {noformat} postgres=# CREATE TABLE t1 (id SERIAL, name VARCHAR(100)); CREATE TABLE postgres=# INSERT INTO t1(name) VALUES('test') RETURNING id; id ---- 1 (1 row) INSERT 0 1 {noformat} Inspired by: https://evertpot.com/writing-sql-for-postgres-mysql-sqlite/ This could make it easier to write statements which work with both MariaDB *and* PostgreSQL. And this might improve compatibility with Oracle RDBMS. ---- How it'll work: * All variants of {{INSERT}} will work — {{INSERT ... VALUES}}, {{INSERT ... SET}}, {{INSERT ... SELECT}} * {{INSERT ... ON DUPLICATE KEY UPDATE}} will work, will return the final row values (inserted or updated) * {{REPLACE}} will return what's inserted * {{INSERT IGNORE}} will return what it'll try to insert, (ignoring insert errors, as expected) * {{INSERT DELAYED}} will, obviously, not work |
| Description |
Please add a RETURNING option to INSERT.
Example from PostgreSQL {noformat} postgres=# CREATE TABLE t1 (id SERIAL, name VARCHAR(100)); CREATE TABLE postgres=# INSERT INTO t1(name) VALUES('test') RETURNING id; id ---- 1 (1 row) INSERT 0 1 {noformat} Inspired by: https://evertpot.com/writing-sql-for-postgres-mysql-sqlite/ This could make it easier to write statements which work with both MariaDB *and* PostgreSQL. And this might improve compatibility with Oracle RDBMS. ---- How it'll work: * All variants of {{INSERT}} will work — {{INSERT ... VALUES}}, {{INSERT ... SET}}, {{INSERT ... SELECT}} * {{INSERT ... ON DUPLICATE KEY UPDATE}} will work, will return the final row values (inserted or updated) * {{REPLACE}} will return what's inserted * {{INSERT IGNORE}} will return what it'll try to insert, (ignoring insert errors, as expected) * {{INSERT DELAYED}} will, obviously, not work |
Please add a RETURNING option to INSERT.
Example from PostgreSQL {noformat} postgres=# CREATE TABLE t1 (id SERIAL, name VARCHAR(100)); CREATE TABLE postgres=# INSERT INTO t1(name) VALUES('test') RETURNING id; id ---- 1 (1 row) INSERT 0 1 {noformat} Inspired by: https://evertpot.com/writing-sql-for-postgres-mysql-sqlite/ This could make it easier to write statements which work with both MariaDB *and* PostgreSQL. And this might improve compatibility with Oracle RDBMS. ---- How it'll work: * All variants of {{INSERT}} will work — {{INSERT ... VALUES}}, {{INSERT ... SET}}, {{INSERT ... SELECT}} * {{INSERT ... ON DUPLICATE KEY UPDATE}} will work, will return the final row values (inserted or updated) * {{REPLACE}} will return what's inserted * {{INSERT IGNORE}} will return what it'll try to insert, (ignoring insert errors, as expected) * {{INSERT DELAYED}} will, obviously, not work * rows will be returned as they're being inserted. If the insert fails in the middle and is rolled back — rows might've been sent that didn't end up in the table |
| Link |
This issue duplicates |
| Link |
This issue relates to |
| Assignee | Sergei Golubchik [ serg ] | Vicentiu Ciorbaru [ cvicentiu ] |
| Assignee | Vicențiu Ciorbaru [ cvicentiu ] | Oleksandr Byelkin [ sanja ] |
| Fix Version/s | 10.5 [ 23123 ] |
| Assignee | Oleksandr Byelkin [ sanja ] | Sergei Golubchik [ serg ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.5.0 [ 23709 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 75500 ] | MariaDB v4 [ 132861 ] |
| Link | This issue causes MDEV-31560 [ MDEV-31560 ] |
| Link | This issue causes MDEV-31560 [ MDEV-31560 ] |
| Link |
This issue is duplicated by |
| Link |
This issue duplicates |
| Link |
This issue is duplicated by |
| Link |
This issue duplicates |
| Link | This issue is part of MENT-2216 [ MENT-2216 ] |
| Link | This issue is part of MDEV-35973 [ MDEV-35973 ] |
| Link | This issue is part of MENT-2216 [ MENT-2216 ] |
| Issue Type | Task [ 3 ] | New Feature [ 2 ] |