Best way to designate unit of weight in PostgreSQL I am working with a simple database with tables of users, products and orders, but previously the products table was not very well designed in my opinion in that for the weights column, it just had a number, but its unclear whether its in pounds or ounces.
I have tried to find documentation on how to designate pounds and ounces as a unit of weight for a table, but I have not been successful. Can anyone suggest a way to do this? Again the column is simply called weight, how do I make it clear that it starts at 2 ounces up to 1 pound as a unit of weight inside of Postgres?
My first instinct is to use the metric system, but if you have to use imperial measurements, I suggest that you store the weights as ounces but have [Computed | Calculated | Virtual | Derived] columns - actually called Generated in PostgreSQL - see this wiki article about Virtual columns - which says that the &quot;The industry standard&quot; is:
column_name [type] GENERATED ALWAYS AS (expression)

PostgreSQL is normally very good about sticking to the SQL standard! The PostgreSQL documentation on generated columns is available here.
Imperial units - i.e. weight in ounces (see below for metric):
I would have something like this (see fiddle here):
CREATE TABLE foo
(
  field_1 SERIAL PRIMARY KEY,
  field_2 VARCHAR (25) NOT NULL,
  --
  --  Other fields
  -- 
  weight_oz INTEGER NOT NULL,
  pound_oz INTEGER GENERATED ALWAYS AS (weight_oz/16) STORED,   -- INTEGER division
  ounce_oz INTEGER GENERATED ALWAYS AS (MOD(weight_oz, 16)) STORED  -- Remainder
);

And then populate it with some sample data:
INSERT INTO foo (field_2, weight_oz) VALUES ('asdfa', 25), ('adfads', 78);

And then:
SELECT * FROM foo;

Result:
field_1 field_2  weight_oz  pound_oz    ounce_oz
      1   asdfa         25         1           9
      2  adfads         78         4          14

Now, unfortunately, PostgreSQL doesn't yet have the VIRTUAL keyword for the storage type of the generated field (unlike, say, MySQL) which would be better here, but AIUI, it's on the way.
Also, you didn't mention your version of PostgreSQL (you should always do this), but methods to do this in versions prior to 12 are explored here.
S.I. (Système International) units - i.e. weight in kg.
I've added a method for dealing where the weight is stored in kg (S.I. units are the ideal storage medium for any quantity IMHO). From here, &quot;1 kg = 35.27396195 oz&quot;. Conversely, the mass m in ounces (oz) is equal to the mass m in kilograms (kg) divided by 0.02834952 (the reciprocal of 35.xxx).
From the PostgreSQL documentation, a REAL variable:

On most platforms, the real type has a range of at least 1E-37 to
1E+37 with a precision of at least 6 decimal digits.

That should be sufficient for most needs - if not, you could use NUMERIC or DECIMAL types, but (from the same page):

However, calculations on numeric values are very slow compared to the
integer types, or to the floating-point types

This method works to the nearest ounce - I've tested here and it appears to work well. Of course, if you want fractional ounces to eliminate potential rounding errors, just keep it REAL (pardon the pun!)! A fiddle is available here:
CREATE TABLE foo
(
  field_1 SERIAL PRIMARY KEY,
  field_2 VARCHAR (25) NOT NULL,
  --
  --  Other fields
  -- 
  weight_kg REAL NOT NULL,
  pound_kg INTEGER GENERATED ALWAYS AS (((weight_kg * 35.273961)::INT)/16) STORED, 
  ounce_kg INTEGER GENERATED ALWAYS AS (MOD((weight_kg * 35.273961)::INT, 16)) STORED
);

Populate:
INSERT INTO foo (field_2, weight_kg) 
VALUES 
('xxxxx', 0.85), ('asdfa', 25), ('adfads', 78);

Then:
SELECT * FROM foo;

Result:
field_1 field_2 weight_kg   pound_kg    ounce_kg
      1   xxxxx      0.85          1          14
      2   asdfa        25         55           2
      3   adfads       78        171          15