Re: [GENERAL] Single Row Table?
--- Scott Marlowe [EMAIL PROTECTED] wrote: On Sun, 2004-08-29 at 15:30, Bruno Wolff III wrote: On Fri, Aug 27, 2004 at 13:32:07 +0530, Yateen Joshi [EMAIL PROTECTED] wrote: Hi, I have got a table which is supposed to contain only one row. It does not have any primary keys defined. So, essentially, when a new insert happens in that table, I would like it (the insert) to fail if there is already a row existing in that table. How can I do that? Can I add any constraints? Or do I need to write a separate trigger for the same? A simple way to force this is to add a primary key and a constraint that forces the primary key to be a particular value. Is it reasonable / possible to add a check constraint something like select count(*) from table =1? ISTM most natural to do this with a rule, e.g.: CREATE RULE my_insert_rule AS ON INSERT TO my_table DO INSTEAD NOTHING; Which will cause all inserts to be silently dropped. If you want to return a message to the application, you could use a statement trigger, which I believe we don't have yet, or you could use a rule like: CREATE RULE my_insert_rule AS ON INSERT TO my_table DO INSTEAD SELECT 'Inserts to my_table not allowed!'; Although the application may not be expecting a return message, and might not handle it. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ___ Do you Yahoo!? Win 1 of 4,000 free domain names from Yahoo! Enter now. http://promotions.yahoo.com/goldrush ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Single Row Table?
ISTM most natural to do this with a rule, e.g.: CREATE RULE my_insert_rule AS ON INSERT TO my_table DO INSTEAD NOTHING; Which will cause all inserts to be silently dropped. This strikes me as bad programming practice. Errors should be reported, not silently ignored. If the application is doing an insert when it doesn't need to, then the application is flawed as well. -- Mike Nolan ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Single Row Table?
Tim Penhey wrote on 30.08.2004 23:12: I have got a table which is supposed to contain only one row. It does not have any primary keys defined. So, essentially, when a new insert happens in that table, I would like it (the insert) to fail if there is already a row existing in that table. You could try: id INT PRIMARY KEY NOT NULL DEFAULT(1) CHECK (id = 1), I like that approach :) But should you also prevent DELETE's from that table? Otherwise you could wind up with no rows at all. I guess that would have to be done using a rule... Thomas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Single Row Table?
But should you also prevent DELETE's from that table? Otherwise you could wind up with no rows at all. I guess that would have to be done using a rule... Why not just revoke the delete privilege? -- Mike Nolan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Single Row Table?
Mike Nolan wrote on 31.08.2004 21:46: But should you also prevent DELETE's from that table? Otherwise you could wind up with no rows at all. I guess that would have to be done using a rule... Why not just revoke the delete privilege? That was one of my first guesses as well, but then I'm not sure if you can revoke DELETE and INSERT privilege from the owner of the table... Thomas ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Single Row Table?
Hi, I have got a table which is supposed to contain only one row. It does not have any primary keys defined. So, essentially, when a new insert happens in that table, I would like it (the insert) to fail if there is already a row existing in that table. How can I do that? Can I add any constraints? Or do I need to write a separate trigger for the same? Thanks and regards, Yateen V. Joshi
Re: [GENERAL] Single Row Table?
On Fri, Aug 27, 2004 at 13:32:07 +0530, Yateen Joshi [EMAIL PROTECTED] wrote: Hi, I have got a table which is supposed to contain only one row. It does not have any primary keys defined. So, essentially, when a new insert happens in that table, I would like it (the insert) to fail if there is already a row existing in that table. How can I do that? Can I add any constraints? Or do I need to write a separate trigger for the same? A simple way to force this is to add a primary key and a constraint that forces the primary key to be a particular value. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Single Row Table?
On Sun, 2004-08-29 at 15:30, Bruno Wolff III wrote: On Fri, Aug 27, 2004 at 13:32:07 +0530, Yateen Joshi [EMAIL PROTECTED] wrote: Hi, I have got a table which is supposed to contain only one row. It does not have any primary keys defined. So, essentially, when a new insert happens in that table, I would like it (the insert) to fail if there is already a row existing in that table. How can I do that? Can I add any constraints? Or do I need to write a separate trigger for the same? A simple way to force this is to add a primary key and a constraint that forces the primary key to be a particular value. Is it reasonable / possible to add a check constraint something like select count(*) from table =1? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
