1
Download Postgres Book
Transcript
CHAPTER 7. NUMBERING ROWS 68 7.6 Serial Column Type There is an easier way to use sequences. If you define a column of type SERIAL, a sequence will be automatically created, and a proper DEFAULT assigned to the column. Figure 7.5 shows an example of this. The first NOTICE line indicates a sequence was created for the SERIAL column. Do not be concerned about test=> CREATE TABLE customer ( test(> customer_id SERIAL, test(> name CHAR(30) test(> ); NOTICE: CREATE TABLE will create implicit sequence ’customer_customer_id_seq’ for SERIAL column ’customer.customer_id’ NOTICE: CREATE TABLE/UNIQUE will create implicit index ’customer_customer_id_key’ for table ’customer’ CREATE test=> \d customer Table "customer" Attribute | Type | Extra -------------+----------+-----------------------------------------------------------customer_id | int4 | not null default nextval(’customer_customer_id_seq’::text) name | char(30) | Index: customer_customer_id_key test=> INSERT INTO customer (name) VALUES (’Car Wash’); INSERT 19152 1 test=> SELECT * FROM customer; customer_id | name -------------+-------------------------------1 | Car Wash (1 row) Figure 7.5: Customer table using SERIAL the second NOTICE line in the figure. Indexing is covered in section 11.1. 7.7 Manually Numbering Rows Some people wonder why OIDs and sequences are needed. Why can’t a database user just find the highest number in use, add one, and use that as the new unique row number? There are several reasons why OIDs and sequences are preferred: • Performance • Concurrency • Standardization First, it is usually slow to scan all numbers currently in use to find the next available number. Using a counter in a separate location is faster. Second, there is the problem of concurrency. If one user gets the highest number, and another user is looking for the highest number at the same time, the two users might 6007 6008 6009 6010 6011 6012 6013 6014 6015 6016 6017 6018 6019 6020 6021 6022 6023 6024 6025 6026 6027 6028 6029 6030 6031 6032 6033 6034 6035 6036 6037 6038 6039 6040 6041 6042 6043 6044 6045 6046 6047 6048 6049 6050 6051 6052 6053 6054 6055 6056 6057 6058 6059 6060 6061 6062 6063 6064 6065 6066 6067 6068 6069 6070 6071 6072
