Data Types

 Table  lists the most common column data types.

 

Table: Common data types

Category	Type	Description
character string	CHAR(length)	blank-padded string, fixed storage length
	VARCHAR(length)	variable storage length
number	INTEGER	integer, +/-2 billion range
	FLOAT	floating point number, 15-digit precision
	NUMERIC (precision, decimal)	number with user-defined precision and decimal
		location
date/time	DATE	date
	TIME	time
	TIMESTAMP	date and time

Figure  shows queries using these types.  

        
        test=> CREATE TABLE alltypes (
        test(>                  state CHAR(2), 
        test(>                  name CHAR(30), 
        test(>                  children INTEGER, 
        test(>                  distance FLOAT, 
        test(>                  budget NUMERIC(16,2), 
        test(>                  born DATE, 
        test(>                  checkin TIME, 
        test(>                  started TIMESTAMP  
        test(> );
        CREATE
        test=> INSERT INTO alltypes
        test-> VALUES ( 
        test(>         'PA',
        test(>         'Hilda Blairwood',
        test(>          3,
        test(>          10.7,
        test(>          4308.20,
        test(>          '9/8/1974',
        test(>          '9:00',
        test(>          '07/03/1996 10:30:00');
        INSERT 19073 1
        test=> SELECT state, name, children, distance, budget FROM alltypes;
         state |              name              | children | distance | budget  
        -------+--------------------------------+----------+----------+---------
         PA    | Hilda Blairwood                |        3 |     10.7 | 4308.20
        (1 row) 
         
        test=> SELECT born, checkin, started FROM alltypes;
            born    | checkin  |        started         
        ------------+----------+------------------------
         1974-09-08 | 09:00:00 | 1996-07-03 10:30:00-04
        (1 row) 
         
        test=> \x
        Expanded display is on.
        test=> SELECT * FROM alltypes;
        -[ RECORD 1 ]----------------------------
        state    | PA
        name     | Hilda Blairwood               
        children | 3
        distance | 10.7
        budget   | 4308.20
        born     | 1974-09-08
        checkin  | 09:00:00
        started  | 1996-07-03 10:30:00-04
         

Notice that numbers do not require quotes, but character strings, dates, and times do require them.

The final SELECT uses psql's \x display mode.^9.1 Without \x, the SELECT would have displayed too much information to fit on one line. The fields would have wrapped around the edge of the display, making it difficult to read. The columns would still line up, but there would be other data in the way. Of course, another solution to field wrapping is to select fewer columns. Remember, you can select any columns from the table in any order.

Section  covers column types in more detail.