INSERT diagram isn't intuitive

[jseldess]

Jesse Seldess (jseldess) commented:

On Slack, @darinpp mentioned:

Hi Jesse. I was just looking at this https://www.cockroachlabs.com/docs/stable/insert.html and it doesn't seem to be showing the insert into ... values(...) syntax in the BNF diagram. Am I missing something? For Postgress for example - https://www.postgresql.org/docs/11/sql-insert.html - they show first the default values (which we have), then values (which I can't see) and then the query.

I think the diagram using select_stmt to represent various types of “selection clauses", including VALUES. The link to https://www.cockroachlabs.com/docs/stable/selection-queries.html in the select_stmt parameter description suggests as much.

However, I very much agree with @darinpp that the diagram isn't friendly or intuitive. It should help users understand the statement syntax, as they most often use it, without having to dig deep and bounce around. We should revisit this and find a way to make the diagram express the most common cases, including VALUES.

cc: @knz, who was involved in the way we documented "selection clauses". @rmloveland, @ericharmeling, not sure which of you wants to take this. Not sure if this is p1 or p2.

Jira Issue: DOC-322

[knz]

It seems reasonable to me to present both the general form (the one currently presented) and then underneath in sub-sections expand the select_stmt clause into 3+ example diagrams including the one that mentions VALUES.

[ericharmeling]

I agree with @knz ... We do this for a few other SQL statements (e.g. CREATE TABLE, with "Basic" and "Expanded" diagrams)

[darinpp]

The VALUES is not a general use select statement. It would make sense to have it expanded as part of defining the INSERT. The easiest to understand in my mind is the approach that MySQL has - https://dev.mysql.com/doc/refman/8.0/en/insert.html
It shows separately each of the variants.

[knz]

The VALUES is not a general use select statement.

In fact, it is. The full generality of selection queries is available to INSERT -- this is a pg feature, which MySQL does not have.

Conversely all 3 selection clauses (TABLE, VALUES and SELECT) are valid top-level statements.

[darinpp]

You are right. I didn't know that. Everything is good then.

[jseldess]

Not sure if we'll have the bandwidth to update this for 19.2, so putting under 20.1.

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB docs!