How to insert salary “NULL” in sql

How do I implement a sql command which outputs NULL for salary if it is a voluntary worker? Here are the tables I created first:

create Table worker (
pid integer references Person,
salary float);

create Table person(
pid integer primary key,
name varchar(30),
adress varchar(30));

Since I’m not sure how to distinguish a normal worker from a voluntary one, I decided to make another table. Unfortunately, I don’t know how to insert NULL values for salary for all voluntary workers. That is what I tried out:

create table voluntaryworker(
pid integer references Person,
salary = null);

insert into Person (pid, name, adress) values (1345, anna, 'festreet');
insert into voluntaryworker (pid, salary) values (1345, null);

pid = person ID

Answer

Like others commented, you certainly don’t need another table to implement this. All you need is some way to remember whether a worker is voluntary.

To make sure salary sticks to your rule, you can add a CHECK constraint:

CREATE TABLE worker (
  pid       integer PRIMARY KEY REFERENCES person
, voluntary boolean NOT NULL DEFAULT false
, salary    numeric
, CONSTRAINT voluntary_has_no_salary CHECK (NOT voluntary OR salary IS NULL)
);

Meaning: voluntary workers cannot have a nonnull salary.

Alternatively, you might drop the table worker, too, and just add the columns worker_salary and worker_voluntary to table person. (You may need an additional flag worker, or integrate this information in the other two columns …)

If you are still interested in generated columns (not needed here), see this example with correct syntax and instructions:

• Computed / calculated / virtual / derived columns in PostgreSQL

Related:

• PostgreSQL: Which Datatype should be used for Currency?