Standard service - a fixed amount of work that is performed at a fixed price.  Post Service

  

Sunday, 15 September 2019 06:39

Postgres & Sequences

Written by  http://blog.mclaughlinsoftware.com/2019/09/16/postgres-sequences/
Rate this item
(0 votes)

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

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:

CREATETABLE new_hire( new_hire_id SERIAL CONSTRAINT new_hire_pk PRIMARYKEY, first_name VARCHAR(20)NOTNULL, middle_name VARCHAR(20), last_name VARCHAR(20)NOTNULL, hire_date DATENOTNULL,UNIQUE(first_name, middle_name, hire_date));

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');

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)

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:

ALTERSEQUENCE new_hire_new_hire_id_seq RESTART WITH1001;

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:

CREATESEQUENCE new_hire_s STARTWITH1001;

CREATE SEQUENCE new_hire_s START WITH 1001;

Then, you create the new_hire table like this:

CREATETABLE new_hire( new_hire_id INTNOTNULLDEFAULTNEXTVAL('new_hire_s'), first_name VARCHAR(20)NOTNULL, middle_name VARCHAR(20), last_name VARCHAR(20)NOTNULL, hire_date DATENOTNULL,PRIMARYKEY(new_hire_id),UNIQUE(first_name, middle_name, hire_date));

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

\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

-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

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.

Read 38 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.