Question #170

Author: admin
tags: PostgreSQL   backend  
A new table was created, some values were added to this table:
CREATE TABLE some_table (
  col1 integer,
  col2 integer,
  UNIQUE (col1, col2)
);
INSERT INTO some_table (col1, col2) VALUES
  (1, 1),
  (2, 1),
  (1, 2),
  (1, NULL),
  (1, NULL),
  (NULL, 1),
  (NULL, 1),
  (NULL, NULL),
  (NULL, NULL)
ON CONFLICT DO NOTHING;
How many rows does the some_table table contain?
0
2
3
6
7
9
By default, two NULL values are not considered equal in a comparison.
That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a NULL value in at least one of the constrained columns.
This behavior can be changed by adding the clause NULLS NOT DISTINCT (PostgreSQL 15+).
Rate the difficulty of the question:
easyhard