Single Row Table?

[Yateen Joshi]

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

[Bruno Wolff III]

On Fri, Aug 27, 2004 at 13:32:07 +0530,
Yateen Joshi <yj****@starent networks.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.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Scott Marlowe]

On Sun, 2004-08-29 at 15:30, Bruno Wolff III wrote:

On Fri, Aug 27, 2004 at 13:32:07 +0530,
Yateen Joshi <yj****@starent networks.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?
---------------------------(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

[Bruno Wolff III]

On Sun, Aug 29, 2004 at 15:38:45 -0600,
Scott Marlowe <sm******@qwest .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 <yj****@starent networks.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?

It should be possible to do that with an after trigger, but it is a bit
more work to set up. As long as the table gets vacuumed, either way
is probably reasonably fast.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Tim Penhey]

Yateen Joshi 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?

Thanks and regards,

Yateen V. Joshi

You could try:

id INT PRIMARY KEY NOT NULL DEFAULT(1) CHECK (id = 1),

Tim

[Jeff Eckermann]

--- Scott Marlowe <sm******@qwest .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 <yj****@starent networks.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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Mike Nolan]

> 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 ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Thomas Kellerer]

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

[Mike Nolan]

> 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 ma*******@postg resql.org

[Thomas Kellerer]

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