~ubuntu-branches/ubuntu/precise/postgresql-9.1/precise-security

If a schema name is given then the sequence is created in the specified schema\&. Otherwise it is created in the current schema\&. Temporary sequences exist in a special schema, so a schema name cannot be given when creating a temporary sequence\&. The sequence name must be distinct from the name of any other sequence, table, index, view, or foreign table in the same schema\&.

.PP

After a sequence is created, you use the functions

\fBnextval\fR,

\fBcurrval\fR, and

\fBsetval\fR

to operate on the sequence\&. These functions are documented in

Section 9.15, \(lqSequence Manipulation Functions\(rq, in the documentation\&.

.PP

Although you cannot update a sequence directly, you can use a query like:

.sp

.if n \{\

.RS 4

.\}

.nf

SELECT * FROM \fIname\fR;

.fi

.if n \{\

.RE

.\}

.sp

to examine the parameters and current state of a sequence\&. In particular, the

last_value

field of the sequence shows the last value allocated by any session\&. (Of course, this value might be obsolete by the time it\(aqs printed, if other sessions are actively doing

\fBnextval\fR

calls\&.)

.SH "PARAMETERS"

.PP

TEMPORARY or TEMP

.RS 4

If specified, the sequence object is created only for this session, and is automatically dropped on session exit\&. Existing permanent sequences with the same name are not visible (in this session) while the temporary sequence exists, unless they are referenced with schema\-qualified names\&.

.RE

.PP

\fIname\fR

.RS 4

The name (optionally schema\-qualified) of the sequence to be created\&.

.RE

.PP

\fIincrement\fR

.RS 4

The optional clause

INCREMENT BY \fIincrement\fR

specifies which value is added to the current sequence value to create a new value\&. A positive value will make an ascending sequence, a negative one a descending sequence\&. The default value is 1\&.

.RE

.PP

\fIminvalue\fR, NO MINVALUE

.RS 4

The optional clause

MINVALUE \fIminvalue\fR

determines the minimum value a sequence can generate\&. If this clause is not supplied or

\fBNO MINVALUE\fR

is specified, then defaults will be used\&. The defaults are 1 and \-263\-1 for ascending and descending sequences, respectively\&.

.RE

.PP

\fImaxvalue\fR, NO MAXVALUE

.RS 4

The optional clause

MAXVALUE \fImaxvalue\fR

determines the maximum value for the sequence\&. If this clause is not supplied or

\fBNO MAXVALUE\fR

is specified, then default values will be used\&. The defaults are 263\-1 and \-1 for ascending and descending sequences, respectively\&.

.RE

100

.PP

101

\fIstart\fR

102

.RS 4

103

The optional clause

104

START WITH \fIstart\fR

105

allows the sequence to begin anywhere\&. The default starting value is

106

\fIminvalue\fR

107

for ascending sequences and

108

\fImaxvalue\fR

109

for descending ones\&.

110

.RE

111

.PP

112

\fIcache\fR

113

.RS 4

114

The optional clause

115

CACHE \fIcache\fR

116

specifies how many sequence numbers are to be preallocated and stored in memory for faster access\&. The minimum value is 1 (only one value can be generated at a time, i\&.e\&., no cache), and this is also the default\&.

117

.RE

118

.PP

119

CYCLE, NO CYCLE

120

.RS 4

121

The

122

CYCLE

123

option allows the sequence to wrap around when the

124

\fImaxvalue\fR

125

126

\fIminvalue\fR

127

has been reached by an ascending or descending sequence respectively\&. If the limit is reached, the next number generated will be the

128

\fIminvalue\fR

129

130

\fImaxvalue\fR, respectively\&.

131

.sp

132

133

NO CYCLE

134

is specified, any calls to

135

\fBnextval\fR

136

after the sequence has reached its maximum value will return an error\&. If neither

137

CYCLE

138

139

NO CYCLE

140

are specified,

141

NO CYCLE

142

is the default\&.

143

.RE

144

.PP

145

OWNED BY \fItable\fR\&.\fIcolumn\fR, OWNED BY NONE

146

.RS 4

147

The

148

OWNED BY

149

option causes the sequence to be associated with a specific table column, such that if that column (or its whole table) is dropped, the sequence will be automatically dropped as well\&. The specified table must have the same owner and be in the same schema as the sequence\&.

150

OWNED BY NONE, the default, specifies that there is no such association\&.

151

.RE

152

.SH "NOTES"

153

.PP

154

Use

155

DROP SEQUENCE

156

to remove a sequence\&.

157

.PP

158

Sequences are based on

159

bigint

160

arithmetic, so the range cannot exceed the range of an eight\-byte integer (\-9223372036854775808 to 9223372036854775807)\&. On some older platforms, there might be no compiler support for eight\-byte integers, in which case sequences use regular

161

integer

162

arithmetic (range \-2147483648 to +2147483647)\&.

163

.PP

164

Unexpected results might be obtained if a

165

\fIcache\fR

166

setting greater than one is used for a sequence object that will be used concurrently by multiple sessions\&. Each session will allocate and cache successive sequence values during one access to the sequence object and increase the sequence object\(aqs

167

last_value

168

accordingly\&. Then, the next

169

\fIcache\fR\-1 uses of

170

\fBnextval\fR

171

within that session simply return the preallocated values without touching the sequence object\&. So, any numbers allocated but not used within a session will be lost when that session ends, resulting in

172

\(lqholes\(rq

173

in the sequence\&.

174

.PP

175

Furthermore, although multiple sessions are guaranteed to allocate distinct sequence values, the values might be generated out of sequence when all the sessions are considered\&. For example, with a

176

\fIcache\fR

177

setting of 10, session A might reserve values 1\&.\&.10 and return

178

\fBnextval\fR=1, then session B might reserve values 11\&.\&.20 and return

179

\fBnextval\fR=11 before session A has generated

180

nextval=2\&. Thus, with a

181

\fIcache\fR

182

setting of one it is safe to assume that

183

\fBnextval\fR

184

values are generated sequentially; with a

185

\fIcache\fR

186

setting greater than one you should only assume that the

187

\fBnextval\fR

188

values are all distinct, not that they are generated purely sequentially\&. Also,

189

last_value

190

will reflect the latest value reserved by any session, whether or not it has yet been returned by

191

\fBnextval\fR\&.

192

.PP

193

Another consideration is that a

194

\fBsetval\fR

195

executed on such a sequence will not be noticed by other sessions until they have used up any preallocated values they have cached\&.

196

.SH "EXAMPLES"

197

.PP

198

Create an ascending sequence called

199

serial, starting at 101:

200

.sp

201

.if n \{\

202

.RS 4

203

.\}

204

.nf

205

CREATE SEQUENCE serial START 101;

206

.fi

207

.if n \{\

208

.RE

209

.\}

210

.PP

211

Select the next number from this sequence:

212

.sp

213

.if n \{\

214

.RS 4

215

.\}

216

.nf

217

SELECT nextval(\(aqserial\(aq);

218

219

nextval

220

\-\-\-\-\-\-\-\-\-

221

101

222

.fi

223

.if n \{\

224

.RE

225

.\}

226

.PP

227

Select the next number from this sequence:

228

.sp

229

.if n \{\

230

.RS 4

231

.\}

232

.nf

233

SELECT nextval(\(aqserial\(aq);

234

235

nextval

236

\-\-\-\-\-\-\-\-\-

237

102

238

.fi

239

.if n \{\

240

.RE

241

.\}

242

.PP

243

Use this sequence in an

244

INSERT

245

command:

246

.sp

247

.if n \{\

248

.RS 4

249

.\}

250

.nf

251

INSERT INTO distributors VALUES (nextval(\(aqserial\(aq), \(aqnothing\(aq);

252

.fi

253

.if n \{\

254

.RE

255

.\}

256

.PP

257

Update the sequence value after a

258

COPY FROM:

259

.sp

260

.if n \{\

261

.RS 4

262

.\}

263

.nf

264

BEGIN;

265

COPY distributors FROM \(aqinput_file\(aq;

266

SELECT setval(\(aqserial\(aq, max(id)) FROM distributors;

267

END;

268

.fi

269

.if n \{\

270

.RE

271

.\}

272

.SH "COMPATIBILITY"

273

.PP

274

CREATE SEQUENCE

275

conforms to the

276

SQL

277

standard, with the following exceptions:

278

.sp

279

.RS 4

280

.ie n \{\

281

\h'-04'\(bu\h'+03'\c

282

.\}

283

.el \{\

284

.sp -1

285

.IP \(bu 2.3

286

.\}

287

The standard\(aqs

288

AS <data type>

289

expression is not supported\&.

290

.RE

291

.sp

292

.RS 4

293

.ie n \{\

294

\h'-04'\(bu\h'+03'\c

295

.\}

296

.el \{\

297

.sp -1

298

.IP \(bu 2.3

299

.\}

300

Obtaining the next value is done using the

301

\fBnextval()\fR

302

function instead of the standard\(aqs

303

NEXT VALUE FOR

304

expression\&.

305

.RE

306

.sp

307

.RS 4

308

.ie n \{\

309

\h'-04'\(bu\h'+03'\c

310

.\}

311

.el \{\

312

.sp -1

313

.IP \(bu 2.3

314

.\}

315

The

316

OWNED BY

317

clause is a

318

PostgreSQL

319

extension\&.

320

.RE

321

.SH "SEE ALSO"

322

ALTER SEQUENCE (\fBALTER_SEQUENCE\fR(7)), DROP SEQUENCE (\fBDROP_SEQUENCE\fR(7))

Older »