Postgres Y Postgis

PostgreSQL
Introduction and Concepts

PostgreSQL
Introduction and Concepts
Bruce Momjian

ADDISON–WESLEY Boston San Francisco New York Toronto Montreal London Munich Paris Madrid Cape Town Sidney Tokyo Singapore Mexico City
                       

Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where thosedesignations appear in this book, and we were aware of a trademark claim, the designations have been printed in initial capital letters or in all capitals. The author and publisher have taken care in the preparation of this book, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for incidental or consequential damages inconnection with or arising out of the use of the information or programs contained herein. The publisher offers discounts on this book when ordered in quantity for special sales. For more information, please contact: Pearson Education Corporate Sales Division One Lake Street Upper Saddle River, NJ 07458 (800) 382-3419 corpsales@pearsontechgroup.com Visit AW on the Web: www.awl.com/cseng/ Copyright ©2001 by Addison–Wesley. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior consent of the publisher. Printed in the United States of America. Published simultaneously in Canada. Library of Congress Cataloging-in-Publication DataMomjian, Bruce. PostgreSQL : introduction and concepts / Momjian, Bruce. p. cm. ISBN 0-201-70331-9 1. Database management. 2. PostgreSQL. I. Title. QA76.9.D3 M647 2000 005.75’85–dc21 00-045367 CIP
A This book was prepared with LYX and LTEX and reproduced by Addison–Wesley from files supplied by the author.

Text printed on recycled and acid-free paper 1 2 3 4 5 6 7 8 9-MA-0403020100 FirstPrinting, November 2000

To my wonderful wife, Christine, and my fine boys, Matthew, Luke, and Peter

Contents
List of Figures List of Tables Foreword Preface Acknowledgments 1 History of POSTGRESQL 1.1 Introduction . . . . . . . . . . . . . . . 1.2 University of California at Berkeley . . 1.3 Development Leaves Berkeley . . . . . 1.4 POSTGRESQL Global Development Team 1.5 Open Source Software .. . . . . . . . . 1.6 Summary . . . . . . . . . . . . . . . . 2 Issuing Database Commands 2.1 Starting a Database Session 2.2 Controlling a Session . . . 2.3 Getting Help . . . . . . . . 2.4 Exiting a Session . . . . . 2.5 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv xxi xxiii xxv xxvii . . . . . . . . . . . . . . . . 1 1 1 2 2 4 4 5 5 6 9 9 9 11 11 13 14 15 17

3 Basic SQL Commands 3.1 Relational Databases . . . . . . . . 3.2 Creating Tables . .. . . . . . . . . 3.3 Adding Data with INSERT . . . . . . 3.4 Viewing Data with SELECT . . . . . 3.5 Selecting Specific Rows with WHERE

vii

viii 3.6 3.7 3.8 3.9 3.10 Removing Data with DELETE Modifying Data with UPDATE Sorting Data with ORDER BY Destroying Tables . . . . . . Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ....