1. 7
  1.  

  2. 2

    Andrew has also noticed that you can’t declare a unique constraint on an expression deferrable but you can use an exclusion constraint to achieve the same effect, for example:

    ALTER TABLE test
    ADD CONSTRAINT uniq_test
    EXCLUDE USING btree (cardinality(data) WITH =) DEFERRABLE;
    
    1. 2

      Re-orderable position shouldn’t be stored as integers but as fractions (rational numbers). That way there is always room to find a number between any other two.

      This seems logical enough at face but it has me very nervous. Are my instincts correct, or is this totally fine?

      1. 1

        From what I’ve seen, it’s usually sufficient as long as you occasionally renumber items. It takes a user intentionally resorting items extensively in short succession to get to the pathological case.

        1. 1

          See the linked article on Postgres wiki, especially:

          There are a number of possible approaches. Using integers is simple but tends to require frequent renumberings. Using floats and picking the midpoints between adjacent values also runs out of space rapidly (you only need 50-odd inserts at the wrong spot to start hitting problems). So this approach uses integer fractions, choosing the values (from the Stern–Brocot tree) such that they can be sorted using (p::float8/q) but renumbering values is only rarely required.

          and:

          -- want to renormalize both to avoid possibility of integer overflow
          -- and to ensure that distinct fraction values map to distinct float8
          -- values. Bounding to 10 million gives us reasonable headroom while
          -- not requiring frequent normalization.
          
          IF (np > 10000000) OR (nq > 10000000) THEN
            perform cat_renormalize(cat_id);
          END IF;
          
          1. 1

            Ah - reading more into that Wiki entry was illuminating. I think when I first read OP I came away thinking they were advocating storing numbers as reals, not rationals.

            What a clever solution - thanks for helping clarify!