[gentoo-doc-cvs] cvs commit: postgres-howto.xml metadoc.xml - gentoo-doc-cvs

From:	Xavier Neys <neysx@×××××××××××.org>
To:	gentoo-doc-cvs@l.g.o
Subject:	[gentoo-doc-cvs] cvs commit: postgres-howto.xml metadoc.xml
Date:	Tue, 14 Mar 2006 12:30:48
Message-Id:	`200603141230.k2ECUZpg026277@robin.gentoo.org`

1

neysx       06/03/14 12:30:33

2

3

  Modified:             metadoc.xml

4

  Added:                postgres-howto.xml

5

  Log:

6

  #125892 new postgres howto

7

8

Revision  Changes    Path

9

1.146                xml/htdocs/doc/en/metadoc.xml

10

11

file : http://www.gentoo.org/cgi-bin/viewcvs.cgi/xml/htdocs/doc/en/metadoc.xml?rev=1.146&content-type=text/x-cvsweb-markup&cvsroot=gentoo

12

plain: http://www.gentoo.org/cgi-bin/viewcvs.cgi/xml/htdocs/doc/en/metadoc.xml?rev=1.146&content-type=text/plain&cvsroot=gentoo

13

diff : http://www.gentoo.org/cgi-bin/viewcvs.cgi/xml/htdocs/doc/en/metadoc.xml.diff?r1=1.145&r2=1.146&cvsroot=gentoo

14

15

Index: metadoc.xml

16

===================================================================

17

RCS file: /var/cvsroot/gentoo/xml/htdocs/doc/en/metadoc.xml,v

18

retrieving revision 1.145

19

retrieving revision 1.146

20

diff -u -r1.145 -r1.146

21

--- metadoc.xml	11 Mar 2006 21:25:49 -0000	1.145

22

+++ metadoc.xml	14 Mar 2006 12:30:33 -0000	1.146

23

@@ -1,9 +1,9 @@

24

 <?xml version='1.0' encoding="UTF-8"?>

25

-<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/metadoc.xml,v 1.145 2006/03/11 21:25:49 neysx Exp $ -->

26

+<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/metadoc.xml,v 1.146 2006/03/14 12:30:33 neysx Exp $ -->

27

 <!DOCTYPE metadoc SYSTEM "/dtd/metadoc.dtd">

28

29

 <metadoc lang="en">

30

-<version>1.73</version>

31

+<version>1.74</version>

32

   <members>

33

     <lead>neysx</lead>

34

     <member>fox2mike</member>

35

@@ -103,6 +103,7 @@

36

     <file id="autotools-practices">/doc/en/articles/autotools-practices.xml</file>

37

     <file id="bugzie-howto">/doc/en/bugzilla-howto.xml</file>

38

     <file id="mysql-howto">/doc/en/mysql-howto.xml</file>

39

+    <file id="postgres-howto">/doc/en/postgres-howto.xml</file>

40

     <file id="bt-guide">/doc/en/bluetooth-guide.xml</file>

41

     <file id="utf-8">/doc/en/utf-8.xml</file>

42

     <file id="cron-guide">/doc/en/cron-guide.xml</file>

43

@@ -586,6 +587,10 @@

44

       <memberof>sysadmin_specific</memberof>

45

       <fileid>mysql-howto</fileid>

46

     </doc>

47

+    <doc id="postgres-howto">

48

+      <memberof>sysadmin_specific</memberof>

49

+      <fileid>postgres-howto</fileid>

50

+    </doc>

51

     <doc id="bluetooth-guide">

52

       <memberof>install_other</memberof>

53

       <fileid>bt-guide</fileid>

1.1                  xml/htdocs/doc/en/postgres-howto.xml

58

59

file : http://www.gentoo.org/cgi-bin/viewcvs.cgi/xml/htdocs/doc/en/postgres-howto.xml?rev=1.1&content-type=text/x-cvsweb-markup&cvsroot=gentoo

60

plain: http://www.gentoo.org/cgi-bin/viewcvs.cgi/xml/htdocs/doc/en/postgres-howto.xml?rev=1.1&content-type=text/plain&cvsroot=gentoo

61

62

Index: postgres-howto.xml

63

===================================================================

64

<?xml version="1.0" encoding="UTF-8"?>

65

<!DOCTYPE guide SYSTEM "/dtd/guide.dtd">

66

<!-- $Header: /var/cvsroot/gentoo/xml/htdocs/doc/en/postgres-howto.xml,v 1.1 2006/03/14 12:30:33 neysx Exp $ -->

67

68

<guide link="/doc/en/postgres-howto.xml" lang="en">

69

<title>PostgreSQL Guide</title>

70

71

<author title="Author">

72

  <mail link="chriswhite@g.o">Chris White</mail>

73

</author>

74

<author title="Editor">

75

  <mail link="neysx@g.o">Xavier Neys</mail>

76

</author>

77

78

<abstract>

79

This guide is meant to show the basic setup of PostgreSQL. The setup described

80

here should be sufficient enough to use for basic web appplications, and any

81

other program that provides PostgreSQL support.

82

</abstract>

83

84

<!-- The content of this document is licensed under the CC-BY-SA license -->

85

<!-- See http://creativecommons.org/licenses/by-sa/2.5 -->

86

<license/>

87

88

<version>1.0</version>

89

<date>2006-03-14</date>

90

91

<chapter>

92

<title>Introduction</title>

93

<section>

94

<title>PostgreSQL introduction</title>

95

<body>

96

97

<p>

98

When talking to most developers about the different database solutions to use,

99

two major databases will usually form the answer. One would be <c>MySQL</c>,

100

and the other is what this document will refer to, <c>PostgreSQL</c>.  The

101

advantages of one over the other is a somewhat long winded debate, however it

102

is just to say that PostgreSQL has had a more firm grasp on true relational

103

database structure than MySQL.  Most of the standard features such as

104

<b>FOREIGN KEY</b> was only just added in MySQL 5. However, whatever the case

105

may be, this document assumes that you have selected PostgreSQL as the

106

database to use. The first place to start is the <c>emerge</c> process. In the

107

next section, the installation process through emerge will be described, as

108

well as the basic configuration.

109

</p>

110

111

</body>

112

</section>

113

<section>

114

<title>PostgreSQL installation</title>

115

<body>

116

117

<p>

118

To begin, we must first <c>emerge</c> the PostgreSQL package. To do so, run the

119

following code to first ensure that the options for it are properly set:

120

</p>

121

122

<pre caption="Checking the PostgreSQL build options">

123

# <i>emerge -pv postgresql</i>

124

125

These are the packages that I would merge, in order:

126

127

Calculating dependencies ...done!

128

[ebuild  N    ] dev-db/postgresql-8.0.4  -doc -kerberos +libg++ +nls +pam +perl

129

-pg-hier -pg-intdatetime +python +readline (-selinux) +ssl -tcltk +xml2 +zlib 0 kB

130

</pre>

131

132

<p>

133

Here's a list of what the different build options indicate:

134

</p>

135

136

<table>

137

<tr>

138

  <th>USE Flag</th>

139

  <th>Meaning</th>

140

</tr>

141

<tr>

142

  <ti>doc</ti>

143

  <ti>

144

    This USE flag enables or disables the installation of documentation

145

    outside of the standard man pages. The one good time to disable this

146

    option is if you are low on space, or you have alternate methods of

147

    getting a hold of the documentation (online, etc.)

148

  </ti>

149

</tr>

150

<tr>

151

  <ti>kerberos</ti>

152

  <ti>

153

    When connecting to the database, with this option enabled, the admin

154

    has the option of using <c>kerberos</c> to authenticate their

155

    users/services to the database.

156

  </ti>

157

</tr>

158

<tr>

159

  <ti>libg++</ti>

160

  <ti>

161

    If this option is enabled, C++ bindings for PostgreSQL will be built.

162

    This will build libpq++ for use by C++ programs to link against.

163

  </ti>

164

</tr>

165

<tr>

166

  <ti>nls</ti>

167

  <ti>

168

    If this option is enabled, PostgreSQL can utilized translated strings

169

    for non-English speaking users.

170

  </ti>

171

</tr>

172

<tr>

173

  <ti>pam</ti>

174

  <ti>

175

    If this option is enabled, and the admin configures the PostgreSQL

176

    configuration file properly, users/services will be able to login to a

177

    PostgreSQL database using <c>PAM</c> (Pluggable Authentication Module).

178

  </ti>

179

</tr>

180

<tr>

181

  <ti>perl</ti>

182

  <ti>

183

    If this option is enabled, <c>perl</c> bindings for PostgreSQL will be

184

    built.

185

  </ti>

186

</tr>

187

<tr>

188

  <ti>pg-hier</ti>

189

  <ti>

190

    If this option is enabled, a patch is applied to enable hierarchical

191

    queries, such as those seen by the <c>CONNECT</c> statement in

192

    <c>Oracle</c> database servers. More information on hierarchical queries

193

    can be found in the <uri

194

    link="http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/

195

    queries4a. htm">Oracle Reference Guide</uri>.

196

  </ti>

197

</tr>

198

<tr>

199

  <ti>pg-intdatetime</ti>

200

  <ti>

201

    If this option is enabled, PostgreSQL will support 64 bit integer date

202

    types.

203

  </ti>

204

</tr>

205

<tr>

206

  <ti>python</ti>

207

  <ti>

208

    If this option is enabled, PostgreSQL will be built with

209

    <c>python</c> bindings.

210

  </ti>

211

</tr>

212

<tr>

213

  <ti>readline</ti>

214

  <ti>

215

    If this option is enabled, PostgreSQL will support <c>readline</c> style

216

    command line editing. This includes command history and isearch.

217

  </ti>

218

</tr>

219

<tr>

220

  <ti>selinux</ti>

221

  <ti>

222

    If this option is enabled, an <c>selinux</c> policy for PostgreSQL will be

223

    installed.

224

  </ti>

225

</tr>

226

<tr>

227

  <ti>ssl</ti>

228

  <ti>

229

    If this option is enabled, PostgreSQL will utilize the <c>OpenSSL</c>

230

    library to encrypt traffic between PostgreSQL clients and servers.

231

  </ti>

232

</tr>

233

<tr>

234

  <ti>tcltk</ti>

235

  <ti>

236

    If this option is enabled, PostgreSQL will build <c>tcl/tk</c> bindings.

237

  </ti>

238

</tr>

239

<tr>

240

  <ti>xml2</ti>

241

  <ti>

242

    If this option is enabled, <c>XPATH</c> style xml support will be built.

243

    More information on using xml support with PostgreSQL can be found on:

244

     <uri link="http://www.throwingbeans.org/tech/postgresql_and_xml.html">

245

    PostgreSQL and XML</uri>.

246

  </ti>

247

</tr>

248

<tr>

249

  <ti>zlib</ti>

250

  <ti>

251

    This isn't really used by PostgreSQL itself, but by <c>pg_dump</c> to

252

    compress the dumps it produces.

253

  </ti>

254

</tr>

255

</table>

256

257

<note>

258

The <c>pg-hier</c> patch author has stopped working on the patch, and it will

259

most likely be removed in later versions.

260

</note>

261

262

<p>

263

Once you've customized PostgreSQL to meet your specific needs, go ahead and

264

start the <c>emerge</c>:

265

</p>

266

267

<pre caption="Emerge-ing PostgreSQL">

268

# <i>emerge postgresql</i>

269

<comment>(Output shortened)</comment>

270

>>> /usr/lib/libecpg.so.5 -> libecpg.so.5.0

271

>>> /usr/bin/postmaster -> postgres

272

 * Make sure the postgres user in /etc/passwd has an account setup with /bin/bash as the shell

273

*

274

 * Execute the following command

275

 * emerge --config =postgresql-8.0.4

276

 * to setup the initial database environment.

277

*

278

>>> Regenerating /etc/ld.so.cache...

279

>>> dev-db/postgresql-8.0.4 merged.

280

</pre>

281

282

<p>

283

As shown by the einfo output, there is some post setup that must be done. The

284

next chapter will look at the actual configuration of PostgreSQL.

285

</p>

286

287

</body>

288

</section>

289

</chapter>

290

<chapter>

291

<title>PostgreSQL configuration</title>

292

<section>

293

<title>Setting up the initial database environment</title>

294

<body>

295

296

<p>

297

As noted in the earlier <c>emerge</c> output, the initial database environment

298

must be setup. However, before this is done, one thing needs to be considered.

299

Unlike, say MySQL, PostgreSQL's "root" password is the password of the actual

300

user. However, only the user is created by the ebuild <e>not</e> the password.

301

So before we can begin, the password must be set for the postgres user:

302

</p>

303

304

<pre caption="Setting the password">

305

# <i>passwd postgres</i>

306

New UNIX password:

307

Retype new UNIX password:

308

passwd: password updated successfully

309

</pre>

310

311

<p>

312

Now that this is setup, the creation of the initial database environment can occur:

313

</p>

314

315

<pre caption="Configuring the database environment with emerge --config">

316

# <i>emerge --config =postgresql-8.0.4</i>

317

318

319

Configuring pkg...

320

321

 * Creating the data directory ...

322

 * Initializing the database ...

323

The files belonging to this database system will be owned by user "postgres".

324

This user must also own the server process.

325

326

The database cluster will be initialized with locale C.

327

328

fixing permissions on existing directory /var/lib/postgresql/data ... ok

329

creating directory /var/lib/postgresql/data/global ... ok

330

creating directory /var/lib/postgresql/data/pg_xlog ... ok

331

creating directory /var/lib/postgresql/data/pg_xlog/archive_status ... ok

332

creating directory /var/lib/postgresql/data/pg_clog ... ok

333

creating directory /var/lib/postgresql/data/pg_subtrans ... ok

334

creating directory /var/lib/postgresql/data/base ... ok

335

creating directory /var/lib/postgresql/data/base/1 ... ok

336

creating directory /var/lib/postgresql/data/pg_tblspc ... ok

337

selecting default max_connections ... 100

338

selecting default shared_buffers ... 1000

339

creating configuration files ... ok

340

creating template1 database in /var/lib/postgresql/data/base/1 ... ok

341

initializing pg_shadow ... ok

342

enabling unlimited row size for system tables ... ok

343

initializing pg_depend ... ok

344

creating system views ... ok

345

loading pg_description ... ok

346

creating conversions ... ok

347

setting privileges on built-in objects ... ok

348

creating information schema ... ok

349

vacuuming database template1 ... ok

350

copying template1 to template0 ... ok

351

352

WARNING: enabling "trust" authentication for local connections

353

You can change this by editing pg_hba.conf or using the -A option the

354

next time you run initdb.

355

356

Success. You can now start the database server using:

357

358

    /usr/bin/postmaster -D /var/lib/postgresql/data

359

or

360

    /usr/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start

361

362

*

363

 * You can use /etc/init.d/postgresql script to run PostgreSQL instead of pg_ctl.

364

*

365

</pre>

366

367

<p>

368

Now the initial database environment is setup. The next section will look at

369

verifying the install and setting up users to access the database.

370

</p>

371

372

</body>

373

</section>

374

<section>

375

<title>PostgreSQL database setup</title>

376

<body>

377

378

<p>

379

Now that PostgreSQL is setup, it's a good idea at this point to verify the

380

installation. First, make sure the service starts up ok:

381

</p>

382

383

<pre caption="Starting up the PostgreSQL service">

384

# <i>/etc/init.d/postgresql start</i>

385

* Starting PostgreSQL ...                                          [ ok ]

386

</pre>

387

388

<p>

389

Once this is verified working, it's also a good idea to add it to the default

390

runlevel so it starts at boot:

391

</p>

392

393

<pre caption="Adding to the default runlevel">

394

# <i>rc-update add postgresql default</i>

395

* postgresql added to runlevel default

396

</pre>

397

398

<p>

399

Now that the service has started, it's time to try setting up a test database.

400

To start out, let's create a test database by using the <c>createdb</c>

401

command. We'll also pass along the <c>-U</c> option to set the user (it

402

defaults to the current user name if you don't), and the <c>-W</c> option to

403

request the password we created earlier. Finally we give it the name of the

404

database we want to create:

405

</p>

406

407

<pre caption="Creating a database with createdb">

408

$ <i>createdb -U postgres -W test</i>

409

Password:

410

CREATE DATABASE

411

</pre>

412

413

<p>

414

The database was successfully created, and we can confirm that the database can

415

run basic tasks. We'll go ahead and drop this database (remove it) with the

416

<c>dropdb</c> command, creating a new one for usage later on:

417

</p>

418

419

<pre caption="Droping a database with dropdb">

420

$ <i>dropdb -U postgres -W test</i>

421

Password:

422

DROP DATABASE

423

</pre>

424

425

<p>

426

Right now, only the postgres user can run commands. Obviously this is not the

427

sort of setup one would like in a multi-user environment. The next section will

428

look at working with user accounts.

429

</p>

430

431

</body>

432

</section>

433

<section>

434

<title>Setting up database user accounts</title>

435

<body>

436

437

<p>

438

As mentioned earlier, having to login as the postgres user is somewhat

439

undesirable in a mult-user environment. In most cases there will be various

440

users and services accessing the server, and each have different permission

441

requirements. So, to handle this, the <c>createuser</c> command can be used.

442

This command is an alternative to running a few SQL queries, and is a lot more

443

flexible from an admin standpoint. We'll go ahead and create two users, a

444

'superuser' that can add other users and administer the db, and a standard user:

445

</p>

446

447

<pre caption="Setting up the superuser">

448

<comment>(replace chris with the username you'd like to use)</comment>

449

$ <i>createuser -a -d -P -E -U postgres -W chris</i>

450

Enter password for new user:

451

Enter it again:

452

Password:

453

CREATE USER

454

</pre>

455

456

<p>

457

There, we've created the superuser. The command line option <c>-a</c> specifies

458

that this user can add other users. <c>-d</c> means that this user can create

459

databases. <c>-P</c> let's you enter a password for the user and <c>-E</c> will

460

encrypt it for security purposes. Now then, we'll test this new user's

461

permissions out by setting up our standard user:

462

</p>

463

464

<pre caption="Setting up the standard user">

465

<comment>(replace chris with the username you've just created)</comment>

466

$ <i>createuser -A -D -P -E -U chris -W testuser</i>

467

Enter password for new user:

468

Enter it again:

469

Password:

470

CREATE USER

471

</pre>

472

473

<p>

474

Success! Our new user was created using the previously created superuser. The

475

<c>-A</c> and <c>-D</c> options do the opposite of <c>-a</c> and <c>-d</c>, and

476

instead deny the user the ability to create other users and databases. Now that

477

there are users to work with, and a new database created, the next chapter will

478

look at using the new database.

479

</p>

480

481

</body>

482

</section>

483

</chapter>

484

<chapter>

485

<title>Using PostgreSQL</title>

486

<section>

487

<title>Setting up permissions</title>

488

<body>

489

490

<p>

491

With the new database created, there is a user that can create databases and

492

add other users, and the main postgres user that can do anything. The user

493

created earlier can currently login to the server, and that's about it. In

494

general, users need to be able to insert data and retrieve data, and sometimes

495

any other number of tasks. So, for this new user to be able to do anything,

496

they must be setup with the proper permissions. This can easily be done by

497

passing the <c>-O</c> parameter to <c>createdb</c>. We'll start by making a

498

new database, <b>MyDB</b> with our superuser that will be owned by the previous

499

testuser:

500

</p>

501

502

<pre caption="Creating the MyDB database">

503

$ <i>createdb -O testuser -U chris -W MyDB</i>

504

Password:

505

CREATE DATABASE

506

</pre>

507

508

<p>

509

Alright, now we have a new MyDB database, and a testuser that can access it.

510

To test this out, we'll login as the testuser to the new MyDB database. We'll

511

do this with the <c>psql</c> program. This program is what's used to connect to

512

the PostgreSQL database from command line. So connect to the new database like

513

so:

514

</p>

515

516

<pre caption="Logging into the MyDB database as the testuser">

517

$ <i>psql -U testuser -W MyDB</i>

518

Password:

519

Welcome to psql 8.0.4, the PostgreSQL interactive terminal.

520

521

Type:  \copyright for distribution terms

522

       \h for help with SQL commands

523

       \? for help with psql commands

524

       \g or terminate with semicolon to execute query

525

       \q to quit

526

527

MyDB=&gt;

528

</pre>

529

530

<p>

531

So, the testuser is now logged into the database, and can begin to initiate

532

some commands. To get a feel for using PostgreSQL, the next section will take a

533

look at some of the basic commands in navigating the <c>psql</c> client.

534

</p>

535

536

</body>

537

</section>

538

<section>

539

<title>Basic PostgreSQL commands and creating a table</title>

540

<body>

541

542

<p>

543

For those who are used to MySQL, this is somewhat of a definite read. This is

544

where PostgreSQL may get somewhat unique with regards to running commands. To

545

start, here is a list of some commands that will be discussed:

546

</p>

547

548

<table>

549

<tr>

550

  <th>Command</th>

551

  <th>Usage</th>

552

  <th>MySQL Equivalent</th>

553

</tr>

554

<tr>

555

  <ti>\c[onnect] [DBNAME|- [USER]]</ti>

556

  <ti>Connects to another database</ti>

557

  <ti>USE DATABASE</ti>

558

</tr>

559

<tr>

560

  <ti>\q</ti>

561

  <ti>Quit the <c>psql</c> client</ti>

562

  <ti>quit</ti>

563

</tr>

564

<tr>

565

  <ti>\i FILE</ti>

566

  <ti>Run commands from <c>FILE</c></ti>

567

  <ti>source FILE</ti>

568

</tr>

569

<tr>

570

  <ti>\o [FILE]</ti>

571

  <ti>Send query results to <c>FILE</c></ti>

572

  <ti>INTO OUTFILE, but outputs everything (not just SELECTS)</ti>

573

</tr>

574

<tr>

575

  <ti>\d [NAME]</ti>

576

  <ti>Describe a database or table (as well as other items)</ti>

577

  <ti>DESC(RIBE)</ti>

578

</tr>

579

<tr>

580

  <ti>\db [PATTERN]</ti>

581

  <ti>

582

    List available tables that match <c>PATTERN</c> (all if no pattern

583

    is given)

584

  </ti>

585

  <ti>SHOW TABLES</ti>

586

</tr>

587

</table>

588

589

<p>

590

With the exception of <c>\c[onnect]</c>, all the commands shown will be used

591

later on in the section. So right now the database is empty. That said, we need

592

to insert some data. The first step to inserting data, however, is to put it in

593

a table. Right now there are no tables in the database, so we need to create

594

one. This is done with the <c>CREATE TABLE</c> command.  We'll make a table of

595

items. They will contain a Product ID, Description, and price:

596

</p>

597

598

<pre caption="Creating the products table">

599

MyDB=> CREATE TABLE products (

600

MyDB(&gt;   product_id SERIAL,

601

MyDB(&gt;   description TEXT,

602

MyDB(&gt;   price DECIMAL

603

MyDB(&gt; );

604

NOTICE:  CREATE TABLE will create implicit sequence "products_product_id_seq"

605

for serial column "products.product_id"

606

CREATE TABLE

607

</pre>

608

609

<p>

610

You can ignore the NOTICE, it's perfectly harmless. Looking at the last line of

611

the function, <c>CREATE TABLE</c> seems to indicate that the command has

612

succeeded. However, let's go ahead and verify that the table was indeed

613

successfully created with the <c>\d</c> command:

614

</p>

615

616

<pre caption="Looking at the newly created table">

617

MyDB=&gt; <i>\d products</i>

618

                                 Table "public.products"

619

   Column    |  Type   |                            Modifiers

620

-------------+---------+------------------------------------------------------------------

621

 product_id  | integer | not null default nextval('public.products_product_id_seq'::text)

622

 description | text    |

623

 price       | numeric |

624

</pre>

625

626

<p>

627

Indeed the table was successfully created. Now that the table is created, it

628

needs to be populated with data. The next section will look at populating the

629

database with data.

630

</p>

631

632

</body>

633

</section>

634

<section>

635

<title>Inserting data into the database</title>

636

<body>

637

638

<p>

639

This section will look at the two ways of populating the newly created table

640

with data. First let's look at the most basic command, <c>INSERT</c>:

641

</p>

642

643

<pre caption="INSERT syntax">

644

INSERT INTO [tablename] (column1,column2,column3) VALUES(value1,value2,value3)

645

</pre>

646

647

<p>

648

<c>tablename</c> contains the name of the table to insert the data into.

649

(column1,column2,column3) lets you specify the specific columns to insert the

650

values into. VALUES(value1,value2,value3) is the listing of values. The values

651

are inserted into the same order as the columns (column1 gets value1, column2

652

gets value2, column3 gets value3). These counts <e>must</e> be the same. So

653

let's go ahead and insert an item into the table:

654

</p>

655

656

<impo>

657

>From working with databases for a long time, I personally recommend specifying

658

<c>INSERT</c> statements exactly as above. Developers often make the mistake of

659

using <c>INSERT INTO</c> without specifying columns. This is unproductive, as

660

if a new column gets added to the database, it will cause in error if the value

661

to column count is not the same. You should <e>always</e> specify the columns

662

unless you're 300% sure you'll never add a column.

663

</impo>

664

665

<pre caption="Inserting data into the table">

666

MyDB=&gt; <i>INSERT INTO products (description,price) VALUES('A test product', 12.00);</i>

667

INSERT 17273 1

668

</pre>

669

670

<p>

671

The last line needs a bit of explaining. The return of an insert command is an

672

OID (Object Identifier) and the number of rows inserted. OID's are a bit beyond

673

the scope of this guide, and the <uri

674

link="http://www.postgresql.org/docs/8.1/static/datatype-oid.html">PostgreSQL

675

manual</uri> has some good information on it. Now, for a situation where you

676

have 20,000 products, these insert statements can be a little tedious. However,

677

not all is lost. The <c>COPY</c> command can be used to insert data into a

678

table from a file or stdin. In this example, let's assume that you have a csv

679

(comma separated values) file, which contains the product id, description, and

680

price. The file looks like this:

681

</p>

682

683

<pre caption="products.csv">

684

2,meat,6.79

685

3,soup,0.69

686

4,soda,1.79

687

</pre>

688

689

<p>

690

Now we'll use the <c>COPY</c> command to populate our data:

691

</p>

692

693

<impo>

694

The <c>COPY FROM STDIN</c> command is used because only the postgres user can

695

insert data from a file (for obvious security reasons).

696

</impo>

697

698

<pre caption="Using COPY to populate the products table">

699

MyDB=&gt; <i>COPY products FROM STDIN WITH DELIMITER AS ',';</i>

700

Enter data to be copied followed by a newline.

701

End with a backslash and a period on a line by itself.

702

>> <i>2,meat,6.79</i>

703

>> <i>3,soup,0.69</i>

704

>> <i>4,soda,1.79</i>

705

>> <i>\.</i>

706

</pre>

707

708

<p>

709

Unfortunately, this line doesn't return the same status information as the

710

<c>INSERT INTO</c> statement. How do we know the data was inserted? The next

711

section will look at running queries to check our data.

712

</p>

713

714

</body>

715

</section>

716

<section>

717

<title>Using PostgreSQL queries</title>

718

<body>

719

720

<p>

721

This section will look at using the <c>SELECT</c> statement to view data in our

722

tables. The basic <c>SELECT</c> format looks like this:

723

</p>

724

725

<pre caption="SELECT syntax">

726

SELECT (column1,column2|*) FROM (table) [WHERE (conditionals)]

727

</pre>

728

729

<p>

730

There are two ways to select columns. The first is using <c>*</c> to select all

731

columns, and the second is to specify a list of specific columns you wish to

732

see. The second is quite handy when you want to find a specific column in a

733

rather large list of them. Let's start out with using <c>SELECT</c> with

734

<c>*</c> to specify all columns:

735

</p>

736

737

<pre caption="Viewing the products table">

738

MyDB=&gt; <i>SELECT * FROM products;</i>

739

 product_id |  description   | price

740

------------+----------------+-------

741

          1 | A test product | 12.00

742

          2 | meat           |  6.79

743

          3 | soup           |  0.69

744

          4 | soda           |  1.79

745

(4 rows)

746

</pre>

747

748

<p>

749

As shown here, all the data we inserted earlier is indeed in the table. Now

750

let's say we only want to see the description and the price, and don't care

751

about the product id. In this case we'll use the column specific SELECT form:

752

</p>

753

754

<pre caption="Viewing specific columns from the products table">

755

MyDB=&gt; <i>SELECT description,price FROM products;</i>

756

  description   | price

757

----------------+-------

758

 A test product | 12.00

759

 meat           |  6.79

760

 soup           |  0.69

761

 soda           |  1.79

762

(4 rows)

763

</pre>

764

765

<p>

766

Now only the product and price is shown, letting us focus on only the important

767

data. Now let's say that we want to see only the items that are greater than

768

$2.00. Here's where the <c>WHERE</c> clause comes in handy:

769

</p>

770

771

<pre caption="Viewing specific rows from the products table">

772

MyDB=&gt; <i>SELECT description,price FROM products WHERE price > 2.00;</i>

773

  description   | price

774

----------------+-------

775

 A test product | 12.00

776

 meat           |  6.79

777

(2 rows)

778

</pre>

779

780

<p>

781

Now a listing of products over $2.00 is displayed, focusing the data even more.

782

These forms of querying for information are very powerful, and can help create

783

extremely useful reports.

784

</p>

785

786

</body>

787

</section>

788

<section>

789

<title>Conclusion</title>

790

<body>

791

792

<p>

793

This concludes the PostgreSQL Guide. A big thanks goes to Masatomo Nakano, the

794

Gentoo PostgreSQL maintainer for his help in answering my questions. Any

795

suggestions on this guide should be sent to <mail>chriswhite@g.o</mail>.

796

For more extensive documentation, see the <uri

797

link="http://www.postgresql.org">PostgreSQL website</uri>.

798

</p>

799

800

</body>

801

</section>

802

</chapter>

803

</guide>

--

808

gentoo-doc-cvs@g.o mailing list

Gentoo Archives: gentoo-doc-cvs