title: What is the difference between Postgres DISTINCT vs DISTINCT ON?
body: <p>I have a Postgres table created with the following statement. This table is filled by as dump of data from another service.</p>
  
  <pre><code>CREATE TABLE data_table (
      date date DEFAULT NULL,
      dimension1 varchar(64) DEFAULT NULL,
      dimension2 varchar(128) DEFAULT NULL
  ) TABLESPACE pg_default;
  </code></pre>
  
  <p>One of the steps in a ETL I'm building is extracting the unique values of <code>dimension1</code> and inserting them in another intermediary table.
  However, during some tests I found out that the 2 commands below do not return the same results. I would expect for both to return the same sum.
  The first command returns more results compared with the second (1466 rows vs. 1504.</p>
  
  <pre><code>-- command 1
  SELECT DISTINCT count(dimension1)
  FROM data_table;
  
  -- command 2    
  SELECT count(*)
  FROM (SELECT DISTINCT ON (dimension1) dimension1
  FROM data_table
  GROUP BY dimension1) AS tmp_table;
  </code></pre>
  
  <p>Any obvious explanations for this? Alternatively to an explanation, is there any suggestion of any check on the data I should do?</p>
  
  <p>EDIT: The following queries both return 1504 (same as the "simple" <code>DISTINCT</code>)</p>
  
  <pre><code>SELECT count(*)
  FROM data_table WHERE dimension1 IS NOT NULL;
  
  SELECT count(dimension1)
  FROM data_table;
  </code></pre>
  
  <p>Thank you!</p>