0

I want to create a table

for example i want the column "test3 " to be test1/test2) so therefore the table would look like

test3 int = test1/test2

create table test (test1 int, test2 int, test3 int ??);

FanoFN
  • 6,815
  • 2
  • 13
  • 33
alvin villafranca
  • 11
  • 3
  • Are you using PostgreSql or MySql? Please only select the DBMS you're using. This sounds like it should be a view and not a table. – Jesse Jan 25 '22 at 00:20
  • Looks like you want a generated column. They exist in both [PostgreSQL](https://www.postgresql.org/docs/12/ddl-generated-columns.html) and [MySQL](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html) but the syntax differs – Tangentially Perpendicular Jan 25 '22 at 00:31

2 Answers2

1

A typical way to handle values calculated from table values is to include them in a view. See also: What are views good for?.

So, for example:

CREATE TABLE test(test1 INTEGER, test2 INTEGER);

followed by

CREATE OR REPLACE VIEW test_v AS
SELECT
  test1,
  test2,
  test1/test2 AS test3
FROM test

You would then query the view exactly as you would your proposed table. It behaves similarly to a table in terms of syntax, but the rows of the view are just pulled from the table; they aren't stored elsewhere in the database. And test3 is not stored anywhere, it's simply calculated when needed.

One last thing: when dividing you should always ensure no division by zero. How to avoid that depends on whether test2 is the kind of thing that should never be zero, or might sometimes be zero. If it shouldn't, then that check can be built right into the table:

CREATE TABLE test(test1 INTEGER, test2 INTEGER CHECK (test2 <> 0));

If instead you might possibly want to allow test2 to be zero, then a better way might be to account for it in the view with a case statement:

CREATE OR REPLACE VIEW test_v AS
SELECT
  test1,
  test2,
  CASE test2 WHEN 0 THEN NULL
    ELSE test1/test2 END AS test3
FROM test
Eli Johnson
  • 349
  • 2
  • 10
1

You can create it as a generated column:

create table test 
(
  test1 int, 
  test2 int, 
  test3 int generated always as ( test1 / nullif(test2, 0) ) stored
);

Note that this uses integer division, so e.g. 1/3 will yield 0. If you don't want that, you need to define the generated column as numeric and cast the values to numeric in the division.

But why store a value that can be calculated very cheaply while retrieving the data, e.g. using a view as Eli Johnson suggested.