1
\$\begingroup\$

I'm getting into backend with postgresql and I would like to know how much of my example would fit for a real website database, just for storing and then displaying it on website.

create table clients (
    id BIGSERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18) NOT NULL,
    email VARCHAR(70) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    card VARCHAR(70) DEFAULT ('undefined') UNIQUE NOT NULL,
    joined TIMESTAMP NOT NULL,
    country VARCHAR(50) DEFAULT ('undefined') NOT NULL,
    language VARCHAR(50) DEFAULT ('undefined') NOT NULL
);

insert into clients (first_name, last_name, age, email, password, joined, language) values ('Rustie', 'Matchell', 18, 'rmatchell0@mayoclinic.com', 'OSauq0z2suY', '2021-04-18 05:26:40', 'Kurdish');
insert into clients (first_name, last_name, age, email, password, card, joined, country, language) values ('Ulric', 'Hoggins', 20, 'uhoggins1@goo.gl', 'M4hnFLJ5XeP', '30243414381012', '2021-02-20 08:07:13', 'China', 'Mongolian');
insert into clients (first_name, last_name, age, email, password, card, joined, country, language) values ('Sephira', 'Bayly', 26, 'sbayly2@rambler.ru', 'INL57w6gXe', '5100138794351466', '2021-04-25 06:17:26', 'North Korea', 'Gujarati');
insert into clients (first_name, last_name, age, email, password, card, joined, country, language) values ('Hermine', 'Fassman', 29, 'hfassman3@smh.com.au', '1UX4TApQMEuV', '3552094428434244', '2021-06-18 06:48:54', 'Indonesia', 'Albanian');

RESULT:

 id | first_name | last_name | age |            email             |   password   |        card        |       joined        |        country        |  language
----+------------+-----------+-----+------------------------------+--------------+--------------------+---------------------+-----------------------+------------
  1 | Rustie     | Matchell  |  18 | rmatchell0@mayoclinic.com    | OSauq0z2suY  | undefined          | 2021-04-18 05:26:40 | undefined             | Kurdish
  2 | Ulric      | Hoggins   |  20 | uhoggins1@goo.gl             | M4hnFLJ5XeP  | 30243414381012     | 2021-02-20 08:07:13 | China                 | Mongolian
  3 | Sephira    | Bayly     |  26 | sbayly2@rambler.ru           | INL57w6gXe   | 5100138794351466   | 2021-04-25 06:17:26 | North Korea           | Gujarati
  4 | Hermine    | Fassman   |  29 | hfassman3@smh.com.au         | 1UX4TApQMEuV | 3552094428434244   | 2021-06-18 06:48:54 | Indonesia             | Albanian
\$\endgroup\$
1
\$\begingroup\$

serial / bigserial are deprecated and you should instead use a generated always as identity clause on an integer-type column.

It looks like you're storing a plaintext password. No, please, no. You need to read about password hashing and salting in general, and then the PostgreSQL crypto support routines.

The string undefined is not a good way to represent a field being missing; this is an in-band value when you need an out-of-band value. The more reasonable thing to do for your card, country and language columns is to allow them to be nullable and to give them a default of null.

Consider combining your inserts into one insert statement with multiple rows in your values expression.

\$\endgroup\$
2
  • \$\begingroup\$ Reinderien can you explain why should I use null values instead of "undefined"? I've made some research, and I've found that using null values isn't advisable. What would happen if I don't use null values in this particular case? \$\endgroup\$
    – irtexas19
    Sep 8 at 14:50
  • 1
    \$\begingroup\$ The use of nullable columns is not advisable if you know that your data for that column has an always-defined guarantee. If it's necessary to represent an "undefined" value, then using nullable columns is the way to go. \$\endgroup\$
    – Reinderien
    Sep 8 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.