PostgreSQL different datatypes in one column

Question

I have a database which contains 2 tables - tests and questions(for those tests).

Questions table has a column called right_answer which might be an array of strings or a single string.

So, I'm wondering what is the best approach to store data in this case? Should I have several questions tables for an each answer type or there's some other way?

Maybe I can store my right_answer using only one table somehow?

Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. — user330315
– user330315, Commented Mar 14, 2020 at 10:58
@a_horse_with_no_name thanks, but it doesn`t answer my question — Vadim Sheremetov
– Vadim Sheremetov, Commented Mar 14, 2020 at 11:11

user330315 · Accepted Answer · 2020-03-14 11:20:34Z

2

A properly normalized model should always be your first approach:

create table questions
(
  id       integer generated always as identity primary key,
  type     text not null,
  test_id  bigint references tests
);

create table answers
(
  id               integer generated always as identity primary key,
  question_id      integer not null references questions,
  answer           text not null,
  is_right_answer  boolean not null
);

answered Mar 14, 2020 at 11:20

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL different datatypes in one column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related