How to insert salary "NULL" in sql

Question

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

I'd consider salary 0 instead, since the salary is known to be 0. — jarlh, Apr 23 '20 at 13:14
Why bother having a `salary` column in that table at all, if it is always `0` or `NULL`? Also, you should tag with the database you are using. — Gordon Linoff, Apr 23 '20 at 13:26
Re the relational algebra post you just deleted: Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For RA give what you can. Which is a lot--you are using a system executing RA. Isolate the 1st subexpression with unexpected result & say what & why (Debugging fundamental.) — philipxy, Apr 30 '20 at 10:46

score 1 · Answer 1 · answered Apr 23 '20 at 13:27

Most databases support generated columns. If you really want a salary column in voluntaryworker, then you can use such a column:

create table voluntaryworker (
    pid integer references Person,
    salary generated always as (cast(null as float))
);

The exact syntax may vary, depending on the database.

Note that having a separate table seems utterly superfluous. Why not just have a flag in the worker table.

Also, representing the salary as a float is quite troublesome. In general, you really should never use floating point representations for monetary amounts. decimal/numeric is much more appropriate for money.

score 0 · Answer 2 · answered Apr 23 '20 at 12:52

0

At least insert into voluntaryworker (pid) values (1354); leaves it NULL.

answered Apr 23 '20 at 12:52

ex4

2,289
1
14
21

Erwin Brandstetter · Accepted Answer · 2020-04-23T14:49:05.590

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

PostgreSQL: Which Datatype should be used for Currency?

How to insert salary "NULL" in sql

3 Answers3