Skip site navigation (1) Skip section navigation (2)

Re: Single Row Table?

From: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
To: Scott Marlowe <smarlowe(at)qwest(dot)net>,Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: Yateen Joshi <yjoshi(at)starentnetworks(dot)com>,pgsql-general(at)postgresql(dot)org
Subject: Re: Single Row Table?
Date: 2004-08-31 15:32:30
Message-ID: 20040831153230.50523.qmail@web20824.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-general
--- Scott Marlowe <smarlowe(at)qwest(dot)net> 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 <yjoshi(at)starentnetworks(dot)com> 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

In response to

Responses

pgsql-general by date

Next:From: Doug McNaughtDate: 2004-08-31 15:38:35
Subject: Re: Change config of running PGSQL
Previous:From: Chester KustarzDate: 2004-08-31 15:27:03
Subject: Re: DB failure?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group