Postgres Incompatibility

[kmalakoff]

We're using knex for our queries and are in the process of moving from mysql to postgres. Great library!

We ran into a compatibility problem when creating an empty record. In knex.js's runQuery method, this.toSql() generates the following for creating a new empty record:

insert into "owners" () values () returning "id"

but based on a quick (Google search)[http://www.postgresql.org/message-id/Pine.BSF.4.21.0105191344070.50686-100000@megazone23.bigpanda.com], it should be:

insert into "owners" default values returning "id"

BTW, mysql generates acceptable code:

insert into `owners` () values ()

and I haven't tested sqlite3.

Is this something you could create a patch for? (I'm in crunch period and unfortunately do not have the time today to write a test, test on all sql variants, etc). If not, I can try to find time over the weekend.

Cheers!

[tgriesser]

Thanks for the issue... should be fixed on a new release (0.2.3). Let me know if you see any issues.

[kmalakoff]

That was quick! Thank you...

I don't seem to be getting back the ids anymore in postgres, mysql seems to work....

connection.insert(json).exec (err, res) =>

generates correctly:

insert into "flats" ("created_at", "name", "updated_at") values (?, ?, ?)

but res is [] on postgres and contains an id on mysql. Maybe there was a side effect from some of the changes? I'll comb through your changes and will see if I can spot anything.

[tgriesser]

In postgres, you need to make sure you're passing the second parameter in the insert with the id you wish to return:

connection.insert(json, 'id').exec - this is new as of 0.2.0, because it was causing issues for folks when they didn't have a primary key or the primary key was something other than id. Let me know if that still doesn't fix it.

Also, the id will just be ignored in mysql and sqlite, so you don't need to worry about it breaking anything there.

[kmalakoff]

Awesome. That worked! Thank you again for being so quick! (and writing such a great library)