30

I have one table in which I would like only one entry. So if someone is trying to insert another row it shouldn't be allowed, only after someone deleted the previously existing row.

How do I set a rule for a table like this?

user4157124
  • 2,809
  • 13
  • 27
  • 42
X-jo
  • 491
  • 1
  • 5
  • 13

5 Answers5

50

A UNIQUE constraint allows multiple rows with null values, because two null values are not considered to be the same. (Except when using NULLS NOT DISTINCT in Postgres 15 or later.)

Similar considerations apply to CHECK constraints. They allow the expression to be true or null (just not false). Again, null values get past the check.

To rule that out, define the column NOT NULL. Or make it the PRIMARY KEY since PK columns are defined NOT NULL automatically. See:

Also, just use boolean:

CREATE TABLE public.onerow (
   onerow_id bool PRIMARY KEY DEFAULT TRUE
 , data text
 , CONSTRAINT onerow_uni CHECK (onerow_id)
);

The CHECK constraint can be that simple for a boolean column. Only true is allowed.

You may want to REVOKE (or not GRANT) the DELETE and TRUNCATE privileges from public (and all other roles) to prevent the single row from ever being deleted. Like:

REVOKE DELETE, TRUNCATE ON public.onerow FROM public;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
15

Add a new column to the table, then add a check constraint and a uniqueness constraint on this column. For example:

CREATE TABLE logging (
    LogId integer UNIQUE default(1),
    MyData text,
    OtherStuff numeric,
    Constraint CHK_Logging_singlerow CHECK (LogId = 1)
);

Now you can only ever have one row with a LogId = 1. If you try to add a new row it will either break the uniqueness or check constraint.

(I might have messed up the syntax, but it gives you an idea?)

Ben
  • 34,935
  • 6
  • 74
  • 113
Richard Hansell
  • 5,315
  • 1
  • 16
  • 35
  • 1
    `UNIQUE` constraints allow (multiple) `NULL` values, so this is *not safe*. Details in my answer. – Erwin Brandstetter Aug 21 '14 at 16:39
  • Okay, I checked this and PostgreSQL works differently to SQL Server; SQL Server would only allow a single NULL value (which in my opinion is a much more sensible implementation). Anyway, this is all irrelevant as the idea is to NOT specify LogId when creating a new row, which will set the value to the default of 1, which isn't NULL so it doesn't really matter? However, if we are being VERY pedantic, I guess the definition should be NOT NULL :P – Richard Hansell Aug 22 '14 at 08:31
  • 9
    There are two kind of DB admins: the "pedantic" ones and the ones posting desperate questions about mysteriously "broken" databases on SO. :p – Erwin Brandstetter Aug 22 '14 at 08:42
7

You should create a ON BEFORE INSERT trigger on this table. On the trigger, call a procedure that checks count(*) and when the count is 1, it returns an exception message to the user otherwise the insert is allowed to proceed.

Check this documentation for an example.

CodeNewbie
  • 2,003
  • 16
  • 29
  • 1
    Possible, but much more expensive and complicated than necessary. – Erwin Brandstetter Aug 19 '14 at 22:22
  • 3
    Your way works, no doubt, and yes, this is more expensive. I was simply offering a solution that works without altering the table. – CodeNewbie Aug 20 '14 at 05:19
  • 3
    Why would the expensiveness of this matter when this isn't a table you're going to be inserting to, ever (beyond the one time; presumably the purpose of such a table is to store a singleton like configuration)? Aesthetically it's certainly much more pleasant to me because you don't need columns and checks that aren't related to the data being stored. – Dmitry Minkovsky Feb 10 '22 at 22:13
4

I suppose no additional field is requried here. Just unique index on constant:

CREATE UNIQUE INDEX ux_onerow ON onerow ((0));
barsuk
  • 41
  • 2
2

You can force single row with:

id int GENERATED ALWAYS AS (1) STORED UNIQUE

This will force the id column to always be 1 and since it's a UNIQUE column the table will only be able to hold this row.

DB Fiddle

enriquejr99
  • 530
  • 6
  • 6