~ubuntu-branches/ubuntu/utopic/pgadmin3/utopic-proposed

<pre class="synopsis"><i class="replaceable"><tt>database</tt></i><tt class="literal">.</tt><i class="replaceable"><tt>schema</tt></i><tt class="literal">.</tt><i class="replaceable"><tt>table</tt></i></pre>

<p>

can be used too, but at present this is just for pro-forma compliance

with the SQL standard. If you write a database name, it must be the

same as the database you are connected to.

</p>

<p> So to create a table in the new schema, use

</p>

<pre class="programlisting">CREATE TABLE myschema.mytable (

...

);</pre>

<p>

100

This works anywhere a table name is expected, including the table

101

modification commands and the data access commands discussed in

102

the following chapters.

103

</p>

104

<a name="id2526156"></a><p> To drop a schema if it's empty (all objects in it have been

105

dropped), use

106

</p>

107

<pre class="programlisting">DROP SCHEMA myschema;</pre>

108

<p>

109

To drop a schema including all contained objects, use

110

</p>

111

<pre class="programlisting">DROP SCHEMA myschema CASCADE;</pre>

112

<p>

113

See <a href="ddl-depend.html" title="5.10.�Dependency Tracking">Section�5.10, “Dependency Tracking”</a> for a description of the general

114

mechanism behind this.

115

</p>

116

<p> Often you will want to create a schema owned by someone else

117

(since this is one of the ways to restrict the activities of your

118

users to well-defined namespaces). The syntax for that is:

119

</p>

120

<pre class="programlisting">CREATE SCHEMA <i class="replaceable"><tt>schemaname</tt></i> AUTHORIZATION <i class="replaceable"><tt>username</tt></i>;</pre>

121

<p>

122

You can even omit the schema name, in which case the schema name

123

will be the same as the user name. See <a href="ddl-schemas.html#ddl-schemas-patterns" title="5.8.6.�Usage Patterns">Section�5.8.6, “Usage Patterns”</a> for how this can be useful.

124

</p>

125

<p> Schema names beginning with <tt class="literal">pg_</tt> are reserved for

126

system purposes and may not be created by users.

127

</p>

128

</div>

129

130

131

132

<a name="ddl-schemas-public"></a>5.8.2.�The Public Schema</h3></div></div>

133

134

</div>

135

<a name="id2526235"></a><p> In the previous sections we created tables without specifying any

136

schema names. By default, such tables (and other objects) are

137

automatically put into a schema named “<span class="quote">public</span>”. Every new

138

database contains such a schema. Thus, the following are equivalent:

139

</p>

140

<pre class="programlisting">CREATE TABLE products ( ... );</pre>

141

<p>

142

and

143

</p>

144

<pre class="programlisting">CREATE TABLE public.products ( ... );</pre>

145

<p>

146

</p>

147

</div>

148

149

150

151

<a name="ddl-schemas-path"></a>5.8.3.�The Schema Search Path</h3></div></div>

152

153

</div>

154

<a name="id2526279"></a><a name="id2526284"></a><a name="id2526290"></a><p> Qualified names are tedious to write, and it's often best not to

155

wire a particular schema name into applications anyway. Therefore

156

tables are often referred to by <i class="firstterm">unqualified names</i>,

157

which consist of just the table name. The system determines which table

158

is meant by following a <i class="firstterm">search path</i>, which is a list

159

of schemas to look in. The first matching table in the search path

160

is taken to be the one wanted. If there is no match in the search

161

path, an error is reported, even if matching table names exist

162

in other schemas in the database.

163

</p>

164

<a name="id2526317"></a><p> The first schema named in the search path is called the current schema.

165

Aside from being the first schema searched, it is also the schema in

166

which new tables will be created if the <tt class="command">CREATE TABLE</tt>

167

command does not specify a schema name.

168

</p>

169

<a name="id2526337"></a><p> To show the current search path, use the following command:

170

</p>

171

<pre class="programlisting">SHOW search_path;</pre>

172

<p>

173

In the default setup this returns:

174

</p>

175

<pre class="screen"> search_path

176

--------------

177

$user,public</pre>

178

<p>

179

The first element specifies that a schema with the same name as

180

the current user is to be searched. If no such schema exists,

181

the entry is ignored. The second element refers to the

182

public schema that we have seen already.

183

</p>

184

<p> The first schema in the search path that exists is the default

185

location for creating new objects. That is the reason that by

186

default objects are created in the public schema. When objects

187

are referenced in any other context without schema qualification

188

(table modification, data modification, or query commands) the

189

search path is traversed until a matching object is found.

190

Therefore, in the default configuration, any unqualified access

191

again can only refer to the public schema.

192

</p>

193

<p> To put our new schema in the path, we use

194

</p>

195

<pre class="programlisting">SET search_path TO myschema,public;</pre>

196

<p>

197

(We omit the <tt class="literal">$user</tt> here because we have no

198

immediate need for it.) And then we can access the table without

199

schema qualification:

200

</p>

201

<pre class="programlisting">DROP TABLE mytable;</pre>

202

<p>

203

Also, since <tt class="literal">myschema</tt> is the first element in

204

the path, new objects would by default be created in it.

205

</p>

206

<p> We could also have written

207

</p>

208

<pre class="programlisting">SET search_path TO myschema;</pre>

209

<p>

210

Then we no longer have access to the public schema without

211

explicit qualification. There is nothing special about the public

212

schema except that it exists by default. It can be dropped, too.

213

</p>

214

<p> See also <a href="functions-info.html" title="9.19.�System Information Functions">Section�9.19, “System Information Functions”</a> for other ways to access

215

the schema search path.

216

</p>

217

<p> The search path works in the same way for data type names, function names,

218

and operator names as it does for table names. Data type and function

219

names can be qualified in exactly the same way as table names. If you

220

need to write a qualified operator name in an expression, there is a

221

special provision: you must write

222

</p>

223

<pre class="synopsis"><tt class="literal">OPERATOR(</tt><i class="replaceable"><tt>schema</tt></i><tt class="literal">.</tt><i class="replaceable"><tt>operator</tt></i><tt class="literal">)</tt></pre>

224

<p>

225

This is needed to avoid syntactic ambiguity. An example is

226

</p>

227

<pre class="programlisting">SELECT 3 OPERATOR(pg_catalog.+) 4;</pre>

228

<p>

229

In practice one usually relies on the search path for operators,

230

so as not to have to write anything so ugly as that.

231

</p>

232

</div>

233

234

235

236

<a name="ddl-schemas-priv"></a>5.8.4.�Schemas and Privileges</h3></div></div>

237

238

</div>

239

<a name="id2526477"></a><p> By default, users cannot access any objects in schemas they do not

240

own. To allow that, the owner of the schema needs to grant the

241

<tt class="literal">USAGE</tt> privilege on the schema. To allow users

242

to make use of the objects in the schema, additional privileges

243

may need to be granted, as appropriate for the object.

244

</p>

245

<p> A user can also be allowed to create objects in someone else's

246

schema. To allow that, the <tt class="literal">CREATE</tt> privilege on

247

the schema needs to be granted. Note that by default, everyone

248

has <tt class="literal">CREATE</tt> and <tt class="literal">USAGE</tt> privileges on

249

the schema

250

<tt class="literal">public</tt>. This allows all users that are able to

251

connect to a given database to create objects in its

252

<tt class="literal">public</tt> schema. If you do

253

not want to allow that, you can revoke that privilege:

254

</p>

255

<pre class="programlisting">REVOKE CREATE ON SCHEMA public FROM PUBLIC;</pre>

256

<p>

257

(The first “<span class="quote">public</span>” is the schema, the second

258

“<span class="quote">public</span>” means “<span class="quote">every user</span>”. In the

259

first sense it is an identifier, in the second sense it is a

260

reserved word, hence the different capitalization; recall the

261

guidelines from <a href="sql-syntax.html#sql-syntax-identifiers" title="4.1.1.�Identifiers and Key Words">Section�4.1.1, “Identifiers and Key Words”</a>.)

262

</p>

263

</div>

264

265

266

267

<a name="ddl-schemas-catalog"></a>5.8.5.�The System Catalog Schema</h3></div></div>

268

269

</div>

270

<a name="id2526578"></a><p> In addition to <tt class="literal">public</tt> and user-created schemas, each

271

database contains a <tt class="literal">pg_catalog</tt> schema, which contains

272

the system tables and all the built-in data types, functions, and

273

operators. <tt class="literal">pg_catalog</tt> is always effectively part of

274

the search path. If it is not named explicitly in the path then

275

it is implicitly searched <span class="emphasis"><em>before</em></span> searching the path's

276

schemas. This ensures that built-in names will always be

277

findable. However, you may explicitly place

278

<tt class="literal">pg_catalog</tt> at the end of your search path if you

279

prefer to have user-defined names override built-in names.

280

</p>

281

<p> In <span class="productname">PostgreSQL</span> versions before 7.3,

282

table names beginning with <tt class="literal">pg_</tt> were reserved. This is

283

no longer true: you may create such a table name if you wish, in

284

any non-system schema. However, it's best to continue to avoid

285

such names, to ensure that you won't suffer a conflict if some

286

future version defines a system table named the same as your

287

table. (With the default search path, an unqualified reference to

288

your table name would be resolved as the system table instead.)

289

System tables will continue to follow the convention of having

290

names beginning with <tt class="literal">pg_</tt>, so that they will not

291

conflict with unqualified user-table names so long as users avoid

292

the <tt class="literal">pg_</tt> prefix.

293

</p>

294

</div>

295

296

297

298

<a name="ddl-schemas-patterns"></a>5.8.6.�Usage Patterns</h3></div></div>

299

300

</div>

301

<p> Schemas can be used to organize your data in many ways. There are

302

a few usage patterns that are recommended and are easily supported by

303

the default configuration:

304

</p>

305

306

<li><p> If you do not create any schemas then all users access the

307

public schema implicitly. This simulates the situation where

308

schemas are not available at all. This setup is mainly

309

recommended when there is only a single user or a few cooperating

310

users in a database. This setup also allows smooth transition

311

from the non-schema-aware world.

312

</p></li>

313

<li>

314

<p> You can create a schema for each user with the same name as

315

that user. Recall that the default search path starts with

316

<tt class="literal">$user</tt>, which resolves to the user name.

317

Therefore, if each user has a separate schema, they access their

318

own schemas by default.

319

</p>

320

<p> If you use this setup then you might also want to revoke access

321

to the public schema (or drop it altogether), so users are

322

truly constrained to their own schemas.

323

</p>

324

</li>

325

<li><p> To install shared applications (tables to be used by everyone,

326

additional functions provided by third parties, etc.), put them

327

into separate schemas. Remember to grant appropriate

328

privileges to allow the other users to access them. Users can

329

then refer to these additional objects by qualifying the names

330

with a schema name, or they can put the additional schemas into

331

their search path, as they choose.

332

</p></li>

333

</ul></div>

334

<p>

335

</p>

336

</div>

337

338

339

340

<a name="ddl-schemas-portability"></a>5.8.7.�Portability</h3></div></div>

341

342

</div>

343

<p> In the SQL standard, the notion of objects in the same schema

344

being owned by different users does not exist. Moreover, some

345

implementations do not allow you to create schemas that have a

346

different name than their owner. In fact, the concepts of schema

347

and user are nearly equivalent in a database system that

348

implements only the basic schema support specified in the

349

standard. Therefore, many users consider qualified names to

350

really consist of

351

<tt class="literal"><i class="replaceable"><tt>username</tt></i>.<i class="replaceable"><tt>tablename</tt></i></tt>.

352

This is how <span class="productname">PostgreSQL</span> will effectively

353

behave if you create a per-user schema for every user.

354

</p>

355

<p> Also, there is no concept of a <tt class="literal">public</tt> schema in the

356

SQL standard. For maximum conformance to the standard, you should

357

not use (perhaps even remove) the <tt class="literal">public</tt> schema.

358

</p>

359

<p> Of course, some SQL database systems might not implement schemas

360

at all, or provide namespace support by allowing (possibly

361

limited) cross-database access. If you need to work with those

362

systems, then maximum portability would be achieved by not using

363

schemas at all.

364

</p>

365

</div>

366

</div></body>

367

</html>

Older »