Insert all default values

[Former Member]

Hi, I'm trying to add SAP Hana database support to my ORM. There are cases when entity / table is defined with all columns set to DEFAULT and user tries to insert rows into such table with any values specified. Also, primary key is generated thus isn't being specified as well.

Other databases either support following syntax: "INSERT INTO "table" ("id", "name") VALUES (DEFAULT, DEFAULT)", either support following syntax: "INSERT INTO "table" DEFAULT VALUES". And it seems SAP doesn't support such syntax and I can't find a workaround in the internet. Thank you.

[lbreddemann · Accepted solution]

TL;DR answer:

SAP HANA (up to HANA 2 SP04) does not support the DEFAULT/DEFAULT VALUES syntax.

The way to insert default values in HANA is to leave out the columns that should get default values from the insert columns list.
Obviously, with an identity column in the table, this leaves no columns in the insert columns list, which in turn is incorrect insert syntax.

This is a bit problematic, since the HANA documentation states that HANA complies to ANSI SQL Feature F221 "Explicit Defaults" which requires that default values can be obtained by either leaving the target column out of the insert list (which is what HANA supports) or via the DEFAULTS keyword that you have seen in other DBMS.

My guess is that there is no technology blocker here, so maybe opening a support message pointing out this shortcoming can get the feature onto the backlog or increase its priority.

Other than that, I have to agree with Mike, this is a fairly non-sensical operation and the explanation that it is the result of an ORM-framework test-suite kind of underlines this notion.

[lucian2 · Accepted solution]

Any update here?

None of these seem to be working:

INSERT INTO "product" DEFAULT VALUES;
INSERT INTO "product" VALUES();
INSERT INTO "product"() VALUES();

Is there any workaround that I could use?

[agentry_src]

Hi Umed,

Your question does not make any sense. If the field values are already set to default, you do not need to include the field in your insert field list or your values list. And you do not insert into an Identity field anyway. It self populates sequentially according to the table definition for the identity field. It cannot have default value since it increments for each new record.

Regards, Mike

[Former Member]

Thanks for the response, Mike. Let me try to clarify: I have a table (id: INT GENERATED ALWAYS AS IDENTITY, name: VARCHAR DEFAULT "anonymous"). What INSERT query should I write to insert a new "anonymous" user. Thanks!

[Former Member]

I don't really care about identity field. I do care about other fields with default, in this case "name". Case when I don't have any field without default and I want to insert a row with all default values for every column I have.

I expect something like this: `INSERT INTO table ("name") VALUES (DEFAULT) to work`. Or `INSERT INTO table DEFAULT VALUES to work.

[agentry_src]

This sounds like some sort of hypothetical situation that one would never actually encounter in the real world. Can you explain where such a situation would arise or how such a table would be used? One could probably do an extract on the field structure to get the default value and insert that, but again I see little value in doing so.

[Former Member]

Right, this is a rare use case, like for example if you want to create something for the first time with default data, but what can be changed later by user, and at the same time it should be separate rows in a db since its owned by a specific user. Personally I don't have such use case, however I do have such test cases in ORM I'm working on for all other supported databases (postgres, mysql, oracle, sqlite, mssql, etc.). They all either support VALUES (DEFAULT, DEFAULT) syntax either INSERT INTO table DEFAULT VALUES syntax.

I'm working on adding a new SAP Hana database support to TypeORM (https://github.com/typeorm/typeorm) - one of the most popular node.js ORM.