MacLochlainns Weblog

Many developers already know about the change from int to serial in Postgres. Serial is simpler because it automatically makes the column not null and links it to a sequence. The sequence is created with the following naming convention:

table_name_column_name_seq

The Postgres serial type always has a start with value of 1. Though, you can alter it immediately after creating the table.

You can create a serial surrogate key column like this:

CREATE TABLE new_hire
( new_hire_id  SERIAL        CONSTRAINT new_hire_pk PRIMARY KEY
, first_name   VARCHAR(20)   NOT NULL
, middle_name  VARCHAR(20)
, last_name    VARCHAR(20)   NOT NULL
, hire_date    DATE          NOT NULL
, UNIQUE(first_name, middle_name, hire_date));

If you want the sequence to start with 1001, you can ALTER the sequence after creating the table. Before you change the sequence staring value, you may want to confirm the automatic name assigned to the sequence. You can do that with the following query:

SELECT pg_get_serial_sequence('new_hire','new_hire_id');

It should return:

     pg_get_serial_sequence      
---------------------------------
 public.new_hire_new_hire_id_seq
(1 row)

Here’s the syntax for changing the current starting value of a Postgres sequence:

ALTER SEQUENCE new_hire_new_hire_id_seq RESTART WITH 1001;

If you want more control, you still have it with the older syntax. The older way requires that you create the sequence before you create the table, like this:

CREATE SEQUENCE new_hire_s START WITH 1001;

Then, you create the new_hire table like this:

CREATE TABLE new_hire
( new_hire_id  INT           NOT NULL DEFAULT NEXTVAL('new_hire_s')
, first_name   VARCHAR(20)   NOT NULL
, middle_name  VARCHAR(20)
, last_name    VARCHAR(20)   NOT NULL
, hire_date    DATE          NOT NULL
, PRIMARY KEY(new_hire_id)
, UNIQUE(first_name, middle_name, hire_date));

You probably will test this from a file because tables are tedious type and usually much larger than the sample new_hire table. If you wonder how to do that, typing help from the psql> prompt will not reveal it. The key is the \i command, like this:

\i new_hire.sql

Unfortunately, the psql> command-line shell does not allow for embedding a means to log the activity of your script file. You must do that from the Linux command-line through redirecting the console to a log file.

The rules are pretty simple for logging file results:

 -a, --echo-all       echo all input from script
 -b, --echo-errors    echo failed commands
 -e, --echo-queries   echo commands sent to the server

Here’s an example connecting to a videodb database with a student user:

psql -d videodb -U student -W -f postgres_tables.sql -a &> postgres_tables.txt

As always, I hope this helps those looking for how to solve the puzzle of a new environment.