Using PostgreSQL 11.5. I need to allow a table to only allow one row and one column in it (not 0 or 2, exactly 1). I feel like ADD CONSTRAINT would be useful but I'm not sure what to do.
I'd like to be able to update this row when needed.
Something to make the following valid:
foo
-----
bar
But not the following:
foo | baz
-----+-----
bar | bin
-----+-----
| gar
You can force a table to have only one row setting a CHECK constraint on your primary key (or any other coulmn that is unique and not nullable) like this:
ALTER TABLE <tablename> ADD CONSTRAINT [<name>] CHECK ([pk/unique field] = [constant value]);
Anyways, since you want to have an updatable field you would need to use a table with 2 columns. If you really need to have only one row (and selecting only foo or creating a view for this purpose is not an option), you could also use triggers on insert and delete (and reject any operations inside them by raising an exception). To avoid having an empty table (not sure if you need to), you will also need this trigger on delete, the constraint will not be strong enough. For triggers see here: https://www.postgresql.org/docs/current/triggers.html
To avoid any further ALTER TABLE commands (such as adding a column) and DROP TABLE, use an event trigger. For more information, have a look into the Documentation: https://www.postgresql.org/docs/current/event-triggers.html
For using PL/pgSQL with triggers, also have a look at https://www.postgresql.org/docs/current/plpgsql-trigger.html
Here's an example that might help you:
CREATE TABLE singleton (x SMALLINT NOT NULL UNIQUE CHECK (x=1), foo VARCHAR(10) NOT NULL);
INSERT INTO singleton (x, foo) VALUES (1,'one row');
Create a VIEW as superuser or some trusted role:
CREATE VIEW onerow AS SELECT 'bar' AS foo; -- defaults to type text
GRANT SELECT ON onerow TO public;
The creator is the owner. Or:
ALTER VIEW onerow OWNER TO postgres; -- or to a trusted role
Everybody can SELECT from it like from any other table.
Nobody can add or remove rows from a VIEW - or even change anything at all with DDL commands.
Only DML commands can change it, and only the owner or superusers are allowed to do so:
CREATE OR REPLACE VIEW onerow AS
SELECT 'bar1' AS foo;
Remember that, ultimately, a motivated superuser can do anything.
On an extremely busy system, the exclusive lock taken by CREATE OR REPLACE VIEW might be a problem. That's the only possible problem with this solution I can think of.
Alternatively, consider this closely related answer to allow at most one row:
You might do that and (as superuser):
ALTER TABLE onerow OWNER TO postgres;
REVOKE ALL ON onerow FROM public;
GRANT SELECT ON onerow TO public;
Superusers can still DELETE or TRUNCATE. You can prevent that, too, with a TRIGGER or RULE ...
Or you could add a general TRIGGER with RETURN NULL or a RULE with DO INSTEAD NOTHING for INSERT and DELETE ...
One single value might alternatively be provided by a function or by a "global variable". See: