PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Create an SQLAlchemy table that only has maximum one entry

I want to store a secret key encrypted and I want to ensure there is always either none or exactly one entry in this SQLAlchemy model/table. How can this be enforced?

Tags:

2

Answers


  1. 0

    Use some generated content that must be unique. Something like this:

    CREATE TABLE t1(id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY
               , lock BOOLEAN UNIQUE GENERATED ALWAYS AS ( TRUE ) STORED
               , content TEXT);

INSERT INTO t1(content) VALUES ('my content') RETURNING id;
INSERT INTO t1(content) VALUES ('some other content') RETURNING id; -- fail
DELETE FROM t1 WHERE content = 'my content';

INSERT INTO t1(content) VALUES ('some other content') RETURNING id;
    Login or Signup to reply.
  2. 0

    The simplest approach is to create a table with a primary key that has a fixed value with one or more columns to hold singleton values:

    CREATE TABLE singleton_demo (
  id INTEGER PRIMARY KEY CHECK (id = 1) DEFAULT 1,
  singleton_value TEXT
);

    The primary key column’s name and type were chosen to be consistent with conventions used by many ORM platforms; e.g., ActiveRecord in Ruby on Rails.

    Because id has a single, fixed value, it needn’t be referenced in queries.

    INSERT INTO singleton_demo (singleton_value) VALUES ('initial value');

-- attempts to insert a second row will fail
INSERT INTO singleton_demo (singleton_value) VALUES ('rejected insert');

UPDATE singleton_demo SET singleton_value = 'new value';

DELETE FROM singleton_demo;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search