当前位置: 动力学知识库 > 问答 > 编程问答 >

ruby - Sequel adding a "returning null" to my inserts. How do I disable it?

问题描述:

I'm using Ruby Sequel (ORM gem) to connect to a Postgres database. I'm not using any models. My insert statements seem to have a "returning null" appended to them automatically (and thusly won't return the newly inserted row id/pk). What's the use of this? And why is this the default? And more importantly, how do I disable it (connection wide)?

Also, I noticed there's a dataset.returning method but it doesn't seem to work!

require 'sequel'

db = Sequel.connect 'postgres://user:[email protected]/foo'

tbl = "public__bar".to_sym #dynamically generated by the app

dat = {x: 1, y: 2}

id = db[tbl].insert(dat) #generated sql -- INSERT INTO "public"."bar" ("x", "y") VALUES (1, 2) RETURNING NULL

Don't know if it matters but the table in question is inherited (using postgres table inheritance)

ruby 1.9.3p392 (2013-02-22) [i386-mingw32]

sequel (3.44.0)

--Edit 1 -- After a bit of troubleshooting--

Looks like the table inheritance COULD BE the problem here. Sequel seems to run a query automatically to determine the pk of a table (in my case the pk's defined on a table up the chain), not finding which, perhaps the "returning null" is being appended?

SELECT pg_attribute.attname AS pk FROM pg_class, pg_attribute, pg_index, pg_namespace WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.relnamespace = pg_namespace.oid AND

pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = 't' AND pg_class.relname = 'bar'

AND pg_namespace.nspname = 'public'

--Edit 2--

Yup, looks like that's the problem!

网友答案:

If you are using PostgreSQL inheritance please note that the following are not inherited:

  • Primary Keys

  • Unique Constraints

  • Foreign Keys

In general you must declare these on each child table. Do for example:

CREATE TABLE my_parent (
        id bigserial primary key,
        my_value text not null unique
);
CREATE TABLE my_child() INHERITS (my_parent);
INSERT INTO my_child(id, my_value) values (1, 'test');
INSERT INTO my_child(id, my_value) values (1, 'test'); -- works, no error thrown

What you want instead is to do this:

CREATE TABLE my_parent (
        id bigserial primary key,
        my_value text not null unique
);
CREATE TABLE my_child(
        primary key(id),
        unique(my_value)
) INHERITS (my_parent);
INSERT INTO my_child(id, my_value) values (1, 'test');
INSERT INTO my_child(id, my_value) values (1, 'test'); -- unique constraint violation thrown

This sounds to me like you have some urgent DDL issues to fix.

You could retrofit the second's constraints onto the first with:

ALTER TABLE my_child ADD PRIMARY KEY(id);
ALTER TABLE my_child ADD UNIQUE (my_value);
分享给朋友:
您可能感兴趣的文章:
随机阅读: