title: PostgreSQL's SERIAL vs MySQL's AUTO_INCREMENT?
body: <p>I have this snippet of MySQL:</p>
  
  <pre><code>CREATE TABLE seq_test (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name TEXT
  );
  
  INSERT INTO seq_test(id, name) VALUES (1, 'one');
  INSERT INTO seq_test(name) VALUES ('two');
  </code></pre>
  
  <p>When I try to write this in PostgreSQL:</p>
  
  <pre><code>CREATE TABLE seq_test (
    id SERIAL PRIMARY KEY,
    name TEXT
  );
  
  INSERT INTO seq_test(id, name) VALUES (1, 'one');
  INSERT INTO seq_test(name) VALUES ('two');
  </code></pre>
  
  <p>I get the following error:</p>
  
  <pre><code>[23505] ERROR: duplicate key value violates unique constraint "seq_test_pkey"
    Detail: Key (id)=(1) already exists.
  </code></pre>
  
  <p>This is because in PostgreSQL, inserting one does not increment the id for the next insert. How can I create my table so that it conforms to the MySQL behaviour?</p>
  
  <p>This is ofcourse an artificial example but I am porting a large code base from MySQL to PostgreSQL and parts of the code (that I don't have control over) uses both styles (i.e with and without ids) and they work in MySQL but does not work in PostgreSQL.</p>
  
  <p>An ugly hack would be to always do <code>SELECT setval('my_table_id_seq', (SELECT count(*) FROM my_table), TRUE)</code> ...</p>