I'm just wondering if this syntax is standard or where it comes from?

For example

INSERT INTO gtest3 SET gDateTime='2017-07-16 01:00:00'

You can see it documented in MySQL but PostgreSQL does not support it.


Apparently this is a MySQL extension, the SQL 2011 spec defines the insert grammar as,

<insert statement> ::=
INSERT INTO <insertion target> <insert columns and source>

<insertion target> ::=
<table name>

<insert columns and source> ::=
<from subquery>
| <from constructor>
| <from default>

<from subquery> ::=
[ <left paren> <insert column list> <right paren> ]
[ <override clause> ]
<query expression>

<from constructor> ::=
[ <left paren> <insert column list> <right paren> ]
[ <override clause> ]
<contextually typed table value constructor>

<override clause> ::=

<from default> ::=

<insert column list> ::=
<column name list>

You can see this talked about on the Pg Lists

| improve this answer | |
  • 2
    Interesting - , and considering how UPDATE works, it makes sense and everything. – RDFozz Aug 2 '17 at 17:15
  • @RDFozz yea, truth be told, I don't find it to be particularly offensive. It's not a bad extension on SQL. Even a blind squirrel finds a nut once in a while, etc. – Evan Carroll Aug 2 '17 at 17:23
  • 1
    Recently I was writing long INSERT..SELECTs, trying to check the columns aligned, wishing I had insert T1 (col1 = t.cola, col2 = t.colx) from table2 as t – Michael Green Aug 2 '17 at 23:25
  • 1
    @MichaelGreen like I said, definitely not MySQL's worst idea. I admitted it! – Evan Carroll Aug 3 '17 at 2:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.