Praktikum 6 bab6

Praktikum 6 Basis Data

TIPE DATA

Praktikum Sistem Basis Data I PostgreSQL 8.X Pertemuan 6 Oleh: Saepudin Nirwan, S.Kom., M.Kom. Purpose of Data Types It is tempting to think that databases would be easier to use if only one data type existed -- a type that could hold any type of information, such as numbers, character strings, or dates. Although a single data type would certainly make table creation simpler, having different data types offers definite advantages: Consistent Results Columns of a uniform type produce consistent results. Displaying, sorting, aggregates, and joins deliver consistent results. No conict arises over how different types are compared or displayed. For example, selecting from an INTEGER column always yields INTEGER values. Data Validation Columns of a uniform type accept only properly formated data; invalid data are rejected. For example, a column of type INTEGER will reject a DATE value. Compact Storage Columns of a uniform type are stored more compactly. Performance Columns of a uniform type are processed more quickly.

POSTGRESQL data types Installed Types

Character String

Character string types are the most commonly used data types. They can hold any sequence of letters, digits, punctuation, and other valid characters. Typical character strings are names, descriptions, and mailing addresses. You can store any value in a character string. Nevertheless, this type should be used only when other data types are inappropriate, as other types provide better data validation, more compact storage, and better performance. Three character string data types exist: TEXT, ARCHAR(length), and CHAR(length). TEXT does not limit the number of characters stored. VARCHAR(length) limits the length of the eld to length characters. Both TEXT and VARCHAR() store only the number of characters in the string. CHAR(length) is similar to VARCHAR(), except it always stores exactly length characters. This type pads the value with trailing spaces to achieve the specied length, and provides slightly faster access than TEXT or VARCHAR().

Number

Number types allow the storage of numbers. The number types are INTEGER, INT2, INT8, OID, NUMERIC(), FLOAT, and FLOAT4. INTEGER, INT2, and INT8 store whole numbers of various ranges. Larger ranges require more storage. For example, INT8 requires twice the storage of INTEGER and is slower that INTEGER. OID is used to store POSTGRESQL object identiers. Although you could use INTEGER for this purpose, OID better documents the meaning of the value stored in the column. NUMERIC(precision, decimal) allows user-dened digits of precision, rounded to decimal places. This type is slower than the other number types.

 Geometric Type: Temporal

Temporal types allow storage of date, time, and time interval information. Although these data can be stored in character strings, it is better to use temporal types. The four temporal types are DATE, TIME, TIMESTAMP, and INTERVAL. DATE allows storage of a single date consisting of a year, month, and day. The format used to input and display dates is controlled by the DATESTYLE setting TIME allows storage of an hour, minute, and second, separated by colons. TIMESTAMP stores both the date and the time.for example, 2000-7-12 17:34:29. INTERVAL represents an interval of time, like 5 hours or 7 days. INTERVAL values are often generated by subtracting two TIMESTAMP values to nd the elapsed time. For example, 1996.12.15 19:00:40 minus 1996.12.8 14:00:10 results in an INTERVAL value of 7 05:00:30, which is 7 days, 5 hours, and 30 seconds. Temporal types can also handle time zone designations. Logical The only logical type is BOOLEAN. A BOOLEAN eld can store only true or false, and of course NULL. You can input true as true, t, yes, y, or 1. False can be input as false, f, no, n, or 0. Although true and false can be input in a variety of ways, true is always output as t and false as f.

Geometric

The geometric types support storage of geometric primitives. They include POINT, LSEG, PATH, BOX, CIRCLE, and POLYGON. Network The network types are INET, CIDR, and MACADDR. INET allows storage of an IP address, with or without a netmask. A typical INET value with a netmask is 172.20.90.150 255.255.255.0. CIDR stores IP network addresses. It allows a subnet mask to specify the size of the network segment. A typical CIDR value is 172.20.90.150/24. MACADDR stores MAC (Media Access Control) addresses, which are assigned to Ethernet network cards at the time of their manufacture. A typical MACADDR value is 0:50:4:1d:f6:db. Internal A variety of types are used internally. Psql's \dT command shows all data types.

Type Conversion Using CAST

In most cases, values of one type are converted to another type automatically. In those rare circumstances where you need to explicitly convert one type to another, you can use CAST to perform the conversion. To convert val to an INTEGER, use CAST(val AS INTEGER). To convert a column date_col of type DATE to type TEXT, use CAST(date_col AS TEXT). You can also perform type casting using double colons.that is, date_col::text or num_val::numeric(10,2).

Support Functions

 Functions enable you to access specialized routines from SQL. They take one or more arguments and return a result. Suppose you want to uppercase a value or column. No command will perform this operation, but a function can handle it. POSTGRESQL has a function called upper that takes a single string argument and returns the argument in uppercase. The function call upper(col) calls the function upper with col as its argument and returns it in uppercase.

Support Operators Operators

are similar to functions Psql's \do command shows all dened operators and their arguments. Example of a function call

Membuat  tabel functest

create  table functest (name varchar (20), date_part date);

Memasukan data  ke dalam tabel functest

Insert into functest values (‘judy’,’02/05/2011’);

Menampilkan semua data yang ada di tabel functest

select * from functest;

Posted in: Tugas Basdat