Sequence vs Serial - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size:
10.0pt;font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">I was wondering when it is better to choose sequence, and when it is better to use serial.
 Theserial type is a sequence with default parameters (<a
href="http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SERIAL">http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SERIAL</a>).
 Actually,I never use serial – I prefer sequence for some reasons that I explain later in this electronic mail – but I
maybe wrong.  It’s never late to learn!</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">There is not that much difference between using sequence or serial when the current value
needsto be returned (which is most of my cases).</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">Using a sequence [1]:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal" style="text-indent:24.0pt;text-autospace:none"><b><font
color="#4b3b90"face="Courier New" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">SELECT</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> v_mytable_id </span></font><b><font color="#4b3b90" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">nextval</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">('seq_mytable_id');</span></font><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font
color="black"face="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">    </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:#4B3B90;font-weight:bold">INSERT</span></font></b><font color="black" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> mytable(id, mycolumn1, mycolumn2)</span></font><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:
 
"Courier New""></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">      </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;
font-family:"Courier New";color:#4B3B90;font-weight:bold">VALUES</span></font></b><font color="black" face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> (v_mytable_id, v_mycolumn1, v_mycolumn2);</span></font><p class="MsoNormal"
style="text-autospace:none"><fontcolor="black" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:black"> </span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">    </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:#4B3B90;font-weight:bold">RETURN </span></font></b><font color="black" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:black">v_mytable_id;</span></font><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""></span></font><p class="MsoNormal" style="text-autospace:none"><font
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New""> </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">Using a serial:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal" style="text-autospace:none"><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">    </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:#4B3B90;font-weight:bold">INSERT</span></font></b><font color="black" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt; 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> mytable(mycolumn1, mycolumn2)</span></font><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:
 
"Courier New""></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">      </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;
font-family:"Courier New";color:#4B3B90;font-weight:bold">VALUES</span></font></b><font color="black" face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> (v_mycolumn1, v_mycolumn2);</span></font><p class="MsoNormal"
style="text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><b><fontcolor="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">SELECT</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> v_mytable_id </span></font><b><font color="#4b3b90" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">currval</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">('mytable_id_seq');</span></font><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><fontcolor="black" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;
font-family:"Courier New";color:black"> </span></font><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><b><fontcolor="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">RETURN </span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">v_mytable_id;</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">I would say that these two code snippets have equivalent performance, if sequence
</span></font><fontcolor="black" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:black">seq_mytable_id</span></font><font face="Arial" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:Arial">has been created with default parameters.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">The reason why I generally prefer sequence over serial is that it is possible to “tune”
sequence,which it seems to not be possible while using serial.  For instance, the sequence allows defining a cache:
<i><spanstyle="font-style:italic">the optional clause CACHE specifies how many sequence numbers are to be preallocated
andstored in memory for faster access.</span></i>  When inserting a lot of entities in a few times, that should make a
bigdifference!</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">Now, when is it better to use serial?  Serial is easier to define and to use.  I would say
thatserial could be used to insert entities in <i><span style="font-style:italic">dimension</span></i> tables (such as
atable that references countries, and where a unique identifier must be defined), a table which data are inserted when
theoperational system is setup.  However, using serial for <i><span style="font-style:italic">fact</span></i> tables
(suchas a table that stores player actions in an MMO game) seems to not be that much relevant. </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">What are your experiences?</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">Regards,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">[1] It is possible to use another form, similar to serial:</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal" style="text-indent:24.0pt;text-autospace:none"><b><font
color="#4b3b90"face="Courier New" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">CREATE SEQUENCE </span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier
New";color:black">seq_mytable_id;</span></font><pclass="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><b><fontcolor="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold"> </span></font></b><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><b><fontcolor="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">CREATE TABLE </span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">v_mytable(id int </span></font><b><font color="#4b3b90" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">nextval</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">('seq_mytable_id'), mycolumn1 int , mycolumn2 int);</span></font><p
class="MsoNormal"style="text-indent:24.0pt;text-autospace:none"><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial">Then it is possible to use the sequence as follows:</span></font><p class="MsoNormal"
style="text-indent:24.0pt;text-autospace:none"><fontface="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""> </span></font><p class="MsoNormal" style="text-autospace:none"><font
color="black"face="Courier New" size="2"><span lang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">    </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:#4B3B90;font-weight:bold">INSERT</span></font></b><font color="black" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> mytable(mycolumn1, mycolumn2)</span></font><font face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:
 
"Courier New""></span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">      </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;
font-family:"Courier New";color:#4B3B90;font-weight:bold">VALUES</span></font></b><font color="black" face="Courier
New"size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> (v_mycolumn1, v_mycolumn2);</span></font><p class="MsoNormal"
style="text-autospace:none"><fontcolor="black" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:black"> </span></font><p class="MsoNormal" style="text-indent:24.0pt;text-autospace:none"><b><font
color="#4b3b90"face="Courier New" size="2"><span lang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">SELECT</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:#4B3B90;font-weight:bold">INTO</span></font></b><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black"> v_mytable_id </span></font><b><font color="#4b3b90" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:
 
10.0pt;font-family:"Courier New";color:#4B3B90;font-weight:bold">currval</span></font></b><font color="black"
face="CourierNew" size="2"><span lang="EN-US" style="font-size:10.0pt;
 
font-family:"Courier New";color:black">('seq_mytable_id');</span></font><p class="MsoNormal"
style="text-autospace:none"><fontcolor="black" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:black"> </span></font><p class="MsoNormal" style="text-autospace:none"><font color="black" face="Courier New"
size="2"><spanlang="EN-US" style="font-size:10.0pt;font-family:"Courier New";
 
color:black">    </span></font><b><font color="#4b3b90" face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew";
 
color:#4B3B90;font-weight:bold">RETURN </span></font></b><font color="black" face="Courier New" size="2"><span
lang="EN-US"style="font-size:10.0pt;
 
font-family:"Courier New";color:black">v_mytable_id;</span></font><font face="Courier New" size="2"><span lang="EN-US"
style="font-size:10.0pt;font-family:"CourierNew""></span></font><p class="MsoNormal"><font face="Arial" size="2"><span
lang="EN-US"style="font-size:
 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">--</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-US"
style="font-size:
10.0pt;font-family:Arial">Daniel</span></font></div>