[GENERAL] unique amount more than one table
I have five tables each with a name field. Due to limitations in my user interface, I want a name to be unique amoung these five tables. I thought I could first create a view with something like: SELECT name, 'table1' as type from table1 UNION ALL SELECT name, 'table2' as type from table2 UNION ALL SELECT name, 'table3' as type from table3 ... I called this view xxx (I'm just experimenting right now). I then created a function: CREATE OR REPLACE FUNCTION unique_xxx ( ) RETURNS boolean AS $$ SELECT ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM xxx GROUP BY name ) AS foo ) = 1; $$ LANGUAGE SQL; Next I added a check constraint with: ALTER TABLE table1 ADD CHECK ( unique_xxx() ); A test shows: select unique_xxx(); unique_xxx t (1 row) After I insert a row that I want to be rejected, I can do: select unique_xxx(); unique_xxx f (1 row) but the insert was not rejected. I'm guessing because the check constraint runs before the insert? So, I could change my approach and have my unique_xxx function see if the name to be added is already in the xxx view but it is at that point that I stopped and thought I would ask for advice. Am I close or am I going down the wrong road? Thank you for your time, pedz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unique amount more than one table
On Tue, 2011-04-05 at 17:02 -0500, Perry Smith wrote: CREATE OR REPLACE FUNCTION unique_xxx ( ) RETURNS boolean AS $$ SELECT ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM xxx GROUP BY name ) AS foo ) = 1; $$ LANGUAGE SQL; Next I added a check constraint with: ALTER TABLE table1 ADD CHECK ( unique_xxx() ); ... After I insert a row that I want to be rejected, I can do: select unique_xxx(); unique_xxx f (1 row) but the insert was not rejected. I'm guessing because the check constraint runs before the insert? Yes. But even if it ran afterward, there is still a potential race condition, because the query in the CHECK constraint doesn't see the results of concurrent transactions. To make this work, you should be using LOCK TABLE inside of a trigger (probably a BEFORE trigger that locks the table, then looks to see if the value exists in the view already, and if so, throws an exception). CHECK is not the right place for this kind of thing. Keep in mind that the performance will not be very good, however. There is not a good way to make this kind of constraint perform well, unfortunately. But that may not be a problem in your case -- try it and see if the performance is acceptable. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unique amount more than one table
You can try restricting all name insertions (on any of the tables) to go through one or more functions that serialize amongst themselves. Basically lock a common table and check the view for the new name before inserting. On Apr 5, 2011, at 18:02, Perry Smith pedz...@gmail.com wrote: I have five tables each with a name field. Due to limitations in my user interface, I want a name to be unique amoung these five tables. I thought I could first create a view with something like: SELECT name, 'table1' as type from table1 UNION ALL SELECT name, 'table2' as type from table2 UNION ALL SELECT name, 'table3' as type from table3 ... I called this view xxx (I'm just experimenting right now). I then created a function: CREATE OR REPLACE FUNCTION unique_xxx ( ) RETURNS boolean AS $$ SELECT ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM xxx GROUP BY name ) AS foo ) = 1; $$ LANGUAGE SQL; Next I added a check constraint with: ALTER TABLE table1 ADD CHECK ( unique_xxx() ); A test shows: select unique_xxx(); unique_xxx t (1 row) After I insert a row that I want to be rejected, I can do: select unique_xxx(); unique_xxx f (1 row) but the insert was not rejected. I'm guessing because the check constraint runs before the insert? So, I could change my approach and have my unique_xxx function see if the name to be added is already in the xxx view but it is at that point that I stopped and thought I would ask for advice. Am I close or am I going down the wrong road? Thank you for your time, pedz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unique amount more than one table
On 04/05/2011 04:02 PM, Perry Smith wrote: I have five tables each with a name field. Due to limitations in my user interface, I want a name to be unique amoung these five tables. I thought I could first create a view with something like: SELECT name, 'table1' as type from table1 UNION ALL SELECT name, 'table2' as type from table2 UNION ALL SELECT name, 'table3' as type from table3 ... I called this view xxx (I'm just experimenting right now). I then created a function: CREATE OR REPLACE FUNCTION unique_xxx ( ) RETURNS boolean AS $$ SELECT ( SELECT max(cnt) FROM ( SELECT count(*) AS cnt FROM xxx GROUP BY name ) AS foo ) = 1; $$ LANGUAGE SQL; Next I added a check constraint with: ALTER TABLE table1 ADD CHECK ( unique_xxx() ); A test shows: select unique_xxx(); unique_xxx t (1 row) After I insert a row that I want to be rejected, I can do: select unique_xxx(); unique_xxx f (1 row) but the insert was not rejected. I'm guessing because the check constraint runs before the insert? So, I could change my approach and have my unique_xxx function see if the name to be added is already in the xxx view but it is at that point that I stopped and thought I would ask for advice. Am I close or am I going down the wrong road? Thank you for your time, pedz You might try making a separate name table and having a unique index there and make the other users of name refer to the new table's name field. (I would stick on id on the new name table...) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unique amount more than one table
On Apr 5, 2011, at 5:50 PM, Rob Sargent wrote: On 04/05/2011 04:02 PM, Perry Smith wrote: I have five tables each with a name field. Due to limitations in my user interface, I want a name to be unique amoung these five tables. I thought I could first create a view with something like: SELECT name, 'table1' as type from table1 UNION ALL snip f (1 row) but the insert was not rejected. I'm guessing because the check constraint runs before the insert? So, I could change my approach and have my unique_xxx function see if the name to be added is already in the xxx view but it is at that point that I stopped and thought I would ask for advice. Am I close or am I going down the wrong road? Thank you for your time, pedz You might try making a separate name table and having a unique index there and make the other users of name refer to the new table's name field. (I would stick on id on the new name table...) Thanks to all. I think this is the way I'm going to go. I'll have an id, name, and type to tell me which of the other tables owns it. Most of the other tables don't need to exist even. Thank you again, pedz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
