Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Postgres Incompatibility #44

Closed
kmalakoff opened this issue Aug 22, 2013 · 4 comments
Closed

Postgres Incompatibility #44

kmalakoff opened this issue Aug 22, 2013 · 4 comments
Labels

Comments

@kmalakoff
Copy link

@kmalakoff kmalakoff commented Aug 22, 2013

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
Copy link
Member

@tgriesser tgriesser commented Aug 22, 2013

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

@tgriesser tgriesser closed this Aug 22, 2013
@kmalakoff
Copy link
Author

@kmalakoff kmalakoff commented Aug 22, 2013

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
Copy link
Member

@tgriesser tgriesser commented Aug 22, 2013

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
Copy link
Author

@kmalakoff kmalakoff commented Aug 22, 2013

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants