StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<h3>Postgres 10 or later</h3> <p>Consider an <strong><code>IDENTITY</code></strong> column. Postgres 10 implements this standard-SQL feature.</p> <pre><code>CREATE TABLE staff ( staff_id <b>int GENERATED BY DEFAULT AS IDENTITY</b> PRIMARY KEY , staff text NOT NULL );</code></pre> <p>Basic syntax and info in the <a href="https://www.postgresql.org/docs/current/static/sql-createtable.html" rel="nofollow noreferrer">manual for <code>CREATE TABLE</code></a>.<br> Detailed explanation in this <a href="https://blog.2ndquadrant.com/postgresql-10-identity-columns/" rel="nofollow noreferrer">blog entry of its primary author Peter Eisentraut.</a></p> <hr> <p>To <strong><em>add</em></strong> an <code>IDENTITY</code> column to a pre-existing table (populated with rows or not):</p> <pre><code>ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY; </code></pre> <p>To also make it the PK at the same time (table can't have a PK yet):</p> <pre><code>ALTER TABLE staff ADD COLUMN staff_id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY; </code></pre> <p>There was a bug in early versions, that could lead to an error message like:</p> <blockquote> <pre><code>ERROR: column "staff_id" contains null values </code></pre> </blockquote> <p>This was fixed with Postgres 10.2. Details:</p> <ul> <li><a href="https://dba.stackexchange.com/questions/200143/how-to-add-a-postgresql-10-identity-column-to-an-existing-table-with-rows/200156#200156">How to add a PostgreSQL 10 identity column to an existing table with rows?</a></li> </ul> <h3>Postgres 9.6 or older</h3> <p>Use the <a href="https://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL" rel="nofollow noreferrer"><strong><code>serial</code></strong> pseudo data type</a> instead:</p> <pre><code>CREATE TABLE staff ( staff_id <b>serial</b> PRIMARY KEY, , staff text NOT NULL );</code></pre> <p>It creates and attaches the sequence object automatically and sets the <code>DEFAULT</code> to <code>nextval()</code> from the sequence. It does all you need.</p> <p>I also use just <a href="http://www.postgresql.org/docs/current/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS" rel="nofollow noreferrer">lower case identifiers</a> in my example. Makes your life with Postgres easier.</p> <p>And better use descriptive column names. "id" as name is an anti-pattern, used by some middle-ware, but hardly descriptive. Similar with "name".</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload