POSTGRES SQL Data Types Unidad 5_6

8/7/2019 POSTGRES SQL Data Types Unidad 5_6

1/46

rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr

POSTGRES

Data Types

The three string data types are CHARACTER(n),CHARACTER VARYING(n), and TEXT.

Six numeric data types SMALLINT,INTEGER

,BIGINT

,NUMERIC(p,s)and two are approximate

REAL,DOUBLE PRECISION.

______________

TIPOS DE DATOS

The three string data types are CHARACTER(n),CHARACTER VARYING(n), and TEXT.

Six numeric data types SMALLINT,INTEGER,BIGINT,NUMERIC(p,s)and two are approximate

REAL,DOUBLE PRECISION.

A value of type CHARACTER(n) can hold a fixed-length string ofn characters. If you store a value that is shorte

than n, the value is padded with spaces to increase the length to exactly n characters. You can abbreviate

CHARACTER(n) to CHAR(n). If you omit the "(n) " when you create a CHARACTER column, the length isassumed to be 1.

The CHARACTER VARYING(n) type defines a variable-length string of at most n characters. VARCHAR(n) is

synonym forCHARACTER VARYING(n). If you omit the "(n) " when creating a CHARACTER VARYINGcolumn, you can store strings of any length in that column.

The last string type is TEXT. A TEXT column is equivalent to a VARCHAR column without a specified lengtha

TEXT column can store strings of any length.

Syntax for Literal ValuesA string value is a sequence of characters surrounded by a pair of delimiters. Prior to PostgreSQL version 8.0, youhad to use a pair of single quote characters to delimit a string value. Starting with version 8.0, you can also defineyour own delimiters for each string value using a form known as dollar quoting. Each of the following is a validstring value:'I am a string''3.14159265'''

You can also write these same string values using dollar quoting as follows:$$I am a string$$

$$3.14159265$$$$$$

An empty string is not the same as a NULL value. An empty string means that you have a known value that just

happens to be empty, whereas NULL implies that the value is unknown. An empty string means that you do have

the information, but it just happens to be empty.


2/46

An escape is a special character that tells PostgreSQL that the character (or characters) following the escape is to

interpreted as a directive instead of as a literal value. In PostgreSQL, the escape character is the backslash (\).

"Where's my car?" could be entered as:'Where''s my car?''Where\'s my car?'$$Where's my car?$$

'Where\047s my car?'

----------

To summarize, here are the four ways that you can embed a single quote within a string:'It''s right where you left it''It\'s right where you left it''It\047s right where you left it'$$It's right where you left it$$

When PostgreSQL sees a backslash in a string literal, it discards the backslash and interprets the following

characters according to the following rules:\b is the backspace character\f is the form feed character\r is the carriage-return character\n is the newline character\t is the tab character

Supported Operators

The concatenation operator (||) is used to combine two string values into a single TEXT value. For example, theexpression

'This is '||'one string'

will evaluate to the value: 'This is one string'. And the expression'The current time is '|| now()

will evaluate to a TEXT value such as,

'The current time is 2002-01-0119:45:17-04'.

Table. Sample String Comparisons

Operator (U)

Expression

'string' U'string' FALSE trUE trUE FALSE trUE FALSE


3/46

Table. Sample String Comparisons

Operator (U)

'string1'U'string' FALSE FALSE FALSE trUE TRUE trUE

'String1'U'string' trUE trUE FALSE trUE FALSE FALSE

____________________

Type Conversion Operators

There are two important operators that you should know about before we go much furtheractually it's one operatobut you can write it two different ways.

The CAST() operator is used to convert a value from one data type to another. There are two ways to write the

CAST() operator:

CAST(expression AS type)expression::type

The expression is converted into the specified type.

For example, the expression CAST( 'abc ' AS INTEGER ) results in an error (specifically, 'pg_atoi: erro

in "abc": can't parse "abc" ') because 'abc' obviously can't be converted into an integer.

When you convert between related data types, you may gain or lose precision. For example, when you convert froa fractional numeric type into an integer type, the value is rounded:

SELECT CAST( CAST( 12345.67 AS FLOAT8 ) AS INTEGER );----------

12346

Numeric Values

PostgreSQL provides a variety of numeric data types. Of the six numeric types, four are exact (SMALLINT,

INTEGER, BIGINT, NUMERIC(p,s)) and two are approximate (REAL, DOUBLE PRECISION).

Three of the four exact numeric types (SMALLINT, INTEGER, and BIGINT) can store only integer values. T

fourth (NUMERIC(p,s)) can accurately store any value that fits within the specified number (p) of digits.

NUMERIC column, obviously, holds numeric values. When you create a NUMERIC column, you have to PostgreSQL the total number of digits that you want to store and the number of fractional digits (that is, the numbof digits to the right of the decimal point).

The approximate numeric types, on the other hand, cannot store all values exactly. Instead, an approximate d

type stores an approximation of a real number. The DOUBLE PRECISION type, for example, can store a total

15 significant digits, but when you perform calculations using a DOUBLE PRECISION value, you can run inrounding errors.


4/46

Size, Precision, and Range-of-Values

The four exact data types can accurately store any value within a type-specific range.

Table. Exact Numeric Data Types

Type Name Size in Bytes Minimum Value Maximum Value

SMALLINT 2 -32768 +32767

INTEGER 4 -2147483648 +2147483647

BIGINT 8 -9223372036854775808 +9223372036854775807

NUMERIC(p,s) 11+(p/2) No limit No limit

The NUMERIC(p,s) data type can accurately store any number that fits within the specified number of dig

When you create a column of type NUMERIC(p,s), you can specify the total number of decimal digits (p) and

number of fractional digits (s). The total number of decimal digits is called the precision, and the number fractional digits is called the scale.

Table. Approximate Numeric Data Types

Type Name Size in Bytes Range

REAL 4 6 decimal digits

DOUBLE PRECISION 8 15 decimal digits

Table. Alternate Names for Numeric Data Types

Common Name Synonyms

SMALLINT INT2

INTEGER INT, INT4

BIGINT INT8

NUMERIC(p,s) DECIMAL(p,s)

REAL FLOAT, FLOAT4

DOUBLE PRECISION FLOAT8


5/46

Syntax for Literal Values

Here are some examples of valid fractional literals:3.141592.0e+150.2e-15

4e10

A numeric literal that contains only digits is considered to be an integer literal. Here are some examples of vainteger literals:

-100555903329223372036854775807-9223372036854775808

A fractional literal is always considered to be of type DOUBLE PRECISION. An integer literal is considered to b

of type INTEGER, unless the value is too large to fit into an integerin which case, it will be promoted first to typeBIGINT, then to NUMERIC orREAL if necessary.

Commenting

The -- characters introduce a commentary text that follows is ignored.

Date/Time Values

PostgreSQL supports four basic temporal data types plus a couple of extensions that deal with time zone issues.

The DATE type is used to store dates. A DATE value stores a century, year, month, and day.

The TIME data type is used to store a time-of-day value. A TIME value stores hours, minutes, seconds, andmicroseconds.

The TIMESTAMP data type combines a DATE and a TIME, storing a century, year, month, day, hour, minut

seconds, and microseconds (TIMESTAMP column gives you both date and time components, centuries throu

microseconds.). Unlike the TIME data type, a TIMESTAMP does include a time zone. If, for some reason, you w

a date/time value that does not include a time zone, you can use the type TIMESTAMP WITHOUT TIME ZONE

The last temporal data type is the INTERVAL. An INTERVAL represents a span of time. I find that the easiest w

to think about INTERVAL values is to remember that an INTERVAL stores some (possibly large) number

seconds, but you can group the seconds into larger units for convenience. For example, the CAST( '1 wee

AS INTERVAL ) is equal to CAST( '604800 seconds' AS INTERVAL ), which is equal to CAST(

days' AS INTERVAL ) you can use whichever format you find easiest to work with.

select CAST( '1 week' AS INTERVAL ) "7 days"

select CAST( '604800 seconds' AS INTERVAL ) "168:00:00" // 604800/3600 = 168 horas y 168/24 = 7

select CAST( '60 minutes' AS INTERVAL )


6/46

"01:00:00" // hrs:min:secThe data types that contain a time value (TIME, TIME WITH TIME ZONE, TIMESTAMP, TIMESTAMP WITH

TIME ZONE, and INTERVAL) have microsecond precision. The DATE data type has a precision of one day.

_____________________________________

Basic Database Terminology

PostgreSQL has a long history you can trace its history back to 1977 and a program known as Ingres. A lot haschanged in the relational database world since 1977.

y SchemaA schema is a named collection of tables. (see table). A schema can also contain views, indexes, sequencedata types, operators, and functions. Other relational database products use the term catalog.

y DatabaseA database is a named collection of schemas. When a client application connects to a PostgreSQL server, specifies the name of the database that it wants to access. A client cannot interact with more than onedatabase per connection but it can open any number of connections in order to access multiple databasessimultaneously.

y CommandA command is a string that you send to the server in hopes of having the server do something useful. Sompeople use the word statement to mean command. The two words are very similar in meaning and, in

practice, are interchangeable.

y QueryA query is a type of command that retrieves data from the server.

y Table (relation, file, class)


7/46

A table is a collection of rows. A table usually has a name, although some tables are temporary and existonly to carry out a command. All the rows in a table have the same shape (in other words, every row in atable contains the same set of columns). In other database systems, you may see the terms relation, file, oreven class these are all equivalent to a table.

y Column (field, attribute)A column is the smallest unit of storage in a relational database. A column represents one piece ofinformation about an object. Every column has a name and a data type. Columns are grouped into rows, anrows are grouped into tables.

The terms field and attribute have similar meanings.

y Row (record, tuple)A row is a collection of column values. Every row in a table has the same shape (in other words, every ris composed of the same set of columns). If you are trying to model a real-world application, a ro

represents a real-world object. For example, if you are running an auto dealership, you might havevehicles table. Each row in the vehicles table represents a car (or truck, or motorcycle, and so o

The kinds of information that you store are the same for all vehicles (that is, every car has a mo

number, color, a vehicle ID, an engine, and so on).

You may also see the terms record or tuple these are equivalent to a row.

y Composite typeStarting with PostgreSQL version 8, you can create new data types that are composed of multiple values. F

example, you could create a composite type named address that holds a street address, ci

state/province, and postal code. When you create a table that contains a column of type address, you cstore all four components in a single field.

y DomainA domain defines a named specialization of another data type. Domains are useful when you need to ensuthat a single data type is used in several tables. For example, you might define a domain nam

accountNumber that contains a single letter followed by four digits. Then you can create columns of ty

accountNumber in a general ledger accounts table, an accounts receivable customer table, and so on.

y ViewA view is an alternative way to present a table (or tables). You might think of a view as a "virtual" table.view is (usually) defined in terms of one or more tables. When you create a view, you are not storing modata, you are instead creating a different way of looking at existing data. A view is a useful way to givename to a complex query that you may have to use repeatedly.

y Client/server


8/46

PostgreSQL is built around a client/server architecture. In a client/server product, there are at least twoprograms involved. One is a client and the other is a server. These programs may exist on the same host oron different hosts that are connected by some sort of network. The server offers a service; in the case ofPostgreSQL, the server offers to store, retrieve, and change data. The client asks a server to perform work;PostgreSQL client asks a PostgreSQL server to serve up relational data.

y ClientA client is an application that makes requests of the PostgreSQL server. Before a client application can tal

to a server, it must connect to a postmaster (see postmaster) and establish its identity. Client applicatioprovide a user interface and can be written in many languages.

y ServerThe PostgreSQL server is a program that services commands coming from client applications. TPostgreSQL server has no user interfaceyou can't talk to the server directly, you must use a cliapplication.

y PostmasterBecause PostgreSQL is a client/server database, something has to listen for connection requests comi

from a client application. That's what the postmaster does. When a connection request arrives,

postmaster creates a new server process in the host operating system.

y TransactionA transaction is a collection of database operations that are treated as a unit. PostgreSQL guarantees that the operations within a transaction complete or that none of them complete. This is an important propert

ensures that if something goes wrong in the middle of a transaction, changes made before the point of failuwill not be reflected in the database. A transaction usually starts with a BEGIN command and ends with

COMMIT orROLLBACK (see the next entries).

y CommitA commit marks the successful end of a transaction. When you perform a commit, you are telliPostgreSQL that you have completed a unit of operation and that all the changes that you made to database should become permanent.

y RollbackA rollback marks the unsuccessful end of a transaction. When you roll back a transaction, you are tellPostgreSQL to discard any changes that you have made to the database (since the beginning of ttransaction).

y Index


9/46

An index is a data structure that a database uses to reduce the amount of time it takes to perform certaoperations. An index can also be used to ensure that duplicate values don't appear where they aren't wanted

y TablespaceA tablespace defines an alternative storage location where you can create tables and indexes. When y

create a table (or index), you can specify the name of a tablespaceif you don't specify a tablespaPostgreSQL creates all objects in the same directory tree. You can use tablespaces to distribute the workloacross multiple disk drives.

y Result setWhen you issue a query to a database, you get back a result set. The result set contains all the rows thsatisfy your query. A result set may be empty.

y Column constraintIs a condition that must be met by a column.

y Null ValuesSometimes when you add data to a table, you find that you don't know what value you should include for acolumn. This value is unknown.

_________________________________________________________________________________

The model case: movies database with tapes, customers, and rentals.

Because you are pretending to model a movie-rental business (that is, a video store), you will create tables th

model the data that you might need in a video store. Start by creating three tables: tapes, customers, a

rentals.

The tapes table is simple: For each videotape, you want to store the name of the movie, the duration, and a uniqidentifier (remember that you may have more than one copy of any given movie, so the movie name is not sufficito uniquely identify a specific tape).

Here is the command you should use to create the tapes table:

CREATE TABLE tapes (tape_id CHARACTER(8) UNIQUE, // column constraint UNIQUE, each row in the tapestitle CHARACTER VARYING(80), // table must have a unique tape_idduration INTERVAL // an INTERVAL stores a period of time such as 2 weeks, 1

hour 45 minutes, and so on.);


10/46

The customers table is used to record information about each customer for the video store.

CREATE TABLE customers (customer_id INTEGER UNIQUE,customer_name VARCHAR(50),

phone CHAR(8),birth_date DATE,balance NUMERIC(7,2)

);

Rents recorded in rentals

CREATE TABLE rentals (tape_id CHARACTER(8),customer_id INTEGER,rental_date DATE

);

What Makes a Relational Database Relational?

Notice that the each row in the rentals table refers to a row in the customer table (and a row in the tap

table). In other words, there is a relationship between rentals and customers and a relationship betwe

rentals and tapes. The relationship between two rows is established by including an identifier from one ro

within the other row. Each row in the rentals table refers to a customer by including the customer_iThat's the heart of the relational database modelthe relationship between two entities is established by including tunique identifier of one entity within the other.

Viewing Table Descriptions

Use the interface pgAdminIII to do maintenance to database schema.

Using the INSERTCommand

AddingNew Records to a Table

The two previous sections showed you how to create some simple tables and how to view the table definitions. Nolet's see how to insert data into these tables.

Using the INSERTCommandThe most common method to get data into a table is by using the INSERT command. Like most SQL comman

there are a number of different formats for the INSERT command. Let's look at the simplest form first:


11/46

INSERT INTO table VALUES ( expression [,...] );

INSERT INTO customers VALUES(1,'William Rubin','555-1212','1970-12-31',0.00)

Here is an example that shows how to INSERT a customer who wasn't willing to give you his date of birth:

INSERT INTO customers(customer_name, phone, customer_id, balance) VALUES('William Rubin','555-1212', 1, 0.00);

This is equivalent to either of the following statements:

INSERT INTO customers(customer_name, birth_date, phone, customer_id, balanceVALUES('William Rubin', NULL, '555-1212', 1, 0.00);

or

// use the order that the table has in table schemaINSERT INTO customers VALUES( 1, 'William Rubin', '555-1212', NULL, 0.00);

The final form for the INSERT statement allows you to insert one or more rows based on the results of a query:

INSERT INTO table ( column [,...] ) SELECT query;

Examples:

1) INSERT INTO customer_backup SELECT * from customers;2)INSERT INTO customers VALUES (3, 'Panky, Henry', '555-1221', '1968-01-21',0.00);INSERT INTO customers VALUES (1, 'Jones, Henry', '555-1212', '1970-10-10',0.00);INSERT INTO customers VALUES (4, 'Wonderland, Alice N.', '555-1122', '1969-03-05', 3.00);

INSERT INTO customers VALUES (2, 'Rubin, William', '555-2211', '1972-07-10',15.00);

INSERT INTO tapes VALUES ('AB-12345', 'The Godfather');INSERT INTO tapes VALUES ('AB-67472', 'The Godfather');INSERT INTO tapes VALUES ('MC-68873', 'Casablanca');INSERT INTO tapes VALUES ('OW-41221', 'Citizen Kane');INSERT INTO tapes VALUES ('AH-54706', 'Rear Window');


12/46

INSERT INTO rentals(tape_id, rental_date ,customer_id ) VALUES ('AB-12345', '2001-11-25', 1);


INSERT INTO rentals(tape_id, rental_date ,customer_id ) VALUES ('OW-41221', '2001-11-

25', 1);INSERT INTO rentals(tape_id, rental_date ,customer_id ) VALUES ('MC-68873', '2001-11-20', 3);

=== SELECT customer_name, AGE( birth_date ) FROM customers;

SELECT * FROM tapes;UPDATE tapesset duration = '1hour 45 minutes'WHERE tape_id = 'AH-54706';

UPDATE tapesset duration = '55 minutes'WHERE tape_id = 'OW-41221';

UPDATE tapesset duration = '2hour 15 minutes'WHERE tape_id = 'MC-68873';

UPDATE tapesset duration = '1hour 30 minutes'

WHERE tape_id = 'AB-67472';

UPDATE tapesset duration = '45 minutes'WHERE tape_id = 'AB-12345';

SELECT SUM(duration) FROM tapes;SELECT SUM(duration) FROM tapes WHERE title LIKE 'The Godfather'

SELECT title, SUM(duration) FROM tapes WHERE title LIKE 'The Godfather'

GROUP BY title;

The inverse ofCOPY ... TO is COPY ...FROM. COPY ...FROM imports data from an external file into

PostgreSQL table. When you use COPY ...FROM, the format of the text file is very important. The easiest way

to find the correct format is to export a few rows using COPY ... TO, and then examine the text file.

If you decide to create your own text file for use with the COPY ...FROM command, you'll have to worry aboua lot of details like proper quoting, column delimiters, and such. Consult the PostgreSQL reference documentationfor more details.


13/46

Another Example:

create table customer(customer_id integer,customer_first_name varchar(100),

customer_last_name varchar(100));

create table lot(lot_id integer,lot_description varchar(100),lot_size float,lot_district integer,lot_value float,lot_street_address varchar(100));

create table customer_lot(

customer_id integer,lot_id integer) ;

copy lot from 'lot.out'copy customer from 'customer.out'copy customer_lot from 'customer_lot.out'-------------------------------------------------

SELECT Expression

Other values that you might want to see are

select 5; -- returns the number 5 (whoopee)select sqrt(2.0); -- returns the square root of 2select timeofday();-- returns current date/timeselect now(); -- returns time of start of transactionselect version(); -- returns the version of PostgreSQL you are using

select now(), timeofday();

SELECT title FROM tapes;SELECT customer_name, birth_date FROM customers;SELECT customer_name, birth_date, age( birth_date )FROM customers;

customer_name | birth_date | age----------------------+------------+------------------------------

"Panky Henry"; "1968-01-21"; "41 years 7 mons 25 days"


14/46

"Jones Henry"; "1970-10-10"; "38 years 11 mons 5 days""Wonderland Alice"; "1969-03-05"; "40 years 6 mons 10 days""Rubin William"; "1972-07-10"; "37 years 2 mons 5 days"(4 rows)

The expression age(birth_date) is evaluated for each row in the table. Theage() function subtracts the given date from the current date.

Selecting Specific Rows

The preceding few sections have shown you how to specify which columns you want to see in a result set. Now lesee how to choose only the rows that you want.

First, I'll show you to how to eliminate duplicate rows; then I'll introduce the WHERE clause.

SELECT [ALL | DISTINCT | DISTINCT ON]

SELECT title from tapes;

Notice that "The Godfather" is listed twice (you own two copies of that video). You can use the DISTINCT clausto filter out duplicate rows:

SELECT DISTINCT title FROM tapes;

Now, try this

SELECT DISTINCT title, tape_id FROM tapes;

We're back to seeing "The Godfather" twice. What happened? The DISTINCT clause removes duplicate rows, noduplicate column values; and when the tape IDs are added to the result, the rows containing "The Godfather" areno longer identical.

If you want to filter rows that have duplicate values in one (or more) columns, use the DISTINCT ON() form:

SELECT DISTINCT ON (title) title, tape_id FROM tapes;

SELECT DISTINCT ON (title,duration) * FROM tapes;

Notice that one of the "The Godfather" rows has been omitted from the result set. If you don't include an ORDERBY clause (I'll cover that in a moment), you can't predict which row in a set of duplicates will be included in the

result set.

You can list multiple columns (or expressions) in the DISTINCT ON() clause.


15/46

The WHERE Clause

The next form of the SELECT statement includes the WHERE clause. Here is the syntax diagram for this form:

SELECT expression-list FROM table WHERE conditions

Using the WHERE clause, you can filter out rows that you don't want included in the result set. Let's see a simpleexample. First, here is the complete customers table:

SELECT * FROM customers;

Now pick out only those customers who owe you some money:

SELECT * FROM customers WHERE balance > 0;

SELECT customer_name, phone, AGE(birth_date) FROM customersWHERE( balance = 0 ) AND ( extract(years from AGE(birth_date)) > "Panky Henry";"555-1221";"41 years 7 mons 24 days"

"Jones Henry";"555-1212";"38 years 11 mons 4 days"

SELECT customer_name, phone, AGE(birth_date) FROM customersWHERE( balance = 0 ) OR ( extract(years from AGE(birth_date)) > "Panky Henry";"555-1221";"41 years 7 mons 24 days"

"Jones Henry";"555-1212";"38 years 11 mons 4 days""Rubin William";"555-2211";"37 years 2 mons SELECT customer_name, phone,

AGE(birth_date),balance FROM customersWHERE ( extract(years from AGE(birth_date)) = 37);

4 days"

SELECT * FROM customersWHERE NOT ( balance = 0 );

SELECT customer_id, customer_name FROM customersWHERE balance != 0;

Notice that a column of any data type can support NULL values.

The NULL value has a unique property that is often the source of much confusion. NULL is not equal to any value

not even itself. NULL is not less than any value, and NULL is not greater than any value.

SELECT * FROM customers WHERE balance > NULL;


16/46

customer_id | customer_name | phone | birth_date | balance-------------+---------------+-------+------------+---------(0 rows)

Let's add a customer with a NULL balance:

INSERT INTO customers VALUES (5, 'Funkmaster, Freddy', '555-FUNK',null,null);

But remember, NULL is not equal to, greater than, or less than any other value. NULL is not the same as zero. Rath

than using relational operators ( '=', '!=', ''), you should use either the IS orIS NOT operator.

SELECT * FROM customers WHERE balance IS NULL;

SELECT * FROM customers WHERE balance IS NOT NULL;

SELECT customer_id, customer_name, balance, balance + 4 FROM customers;>>3;"Panky Henry";0.00;4.001;"Jones Henry";0.00;4.004;"Wonderland Alice";3.00;7.002;"Rubin William";15.00;19.005;"Funkmaster, Freddy"; // null + 4 = null

This query shows what happens when you try to perform a mathematical operation using NULL. When you try to

add '4' to NULL, you end up with NULL.

NULLIF() and COALESCE()

PostgreSQL offers two operators that can convert a NULL value to some other value or convert a specific value in

NULL.

The COALESCE() operator will substitute a default value whenever it encounters a NULL.

Try the COALESCE function:

Let's add a customer with a NULL phone:

INSERT INTO customers VALUES (6, 'Roxette Nurayama', null, '1975-09-10',9.00);

SELECT customer_id, customer_name, COALESCE(phone, 'No se conoce' ) FROMcustomers;>>


17/46

3;"Panky Henry"; "555-1221"1;"Jones Henry"; "555-1212"4;"Wonderland Alice"; "555-1122"2;"Rubin William"; "555-2211"5;"Funkmaster, Freddy"; "555-FUNK"6;"Roxette Nurayama"; "Unknown"

-----------------------------------

SELECT customer_id, customer_name, COALESCE(balance, 0 ) FROM customers;>>3;"Panky Henry"; 0.001;"Jones Henry"; 0.004;"Wonderland Alice"; 3.002;"Rubin William"; 15.005;"Funkmaster, Freddy"; 06;"Roxette Nurayama"; 9.00

--------------------------------

The NULLIF() operator translates a non-NULL value into NULL. NULLIF() is often used to do the opposite

COALESCE(). COALESCE() TRansforms NULL into a default valueNULLIF() translates a default value in

NULL. In many circumstances, you want to treat a numeric value and a NULL value as being the same thing. F

example, the balance column (in the customers table) is NULL until a customer actually rents a tape: A NU

balance implies that you haven't actually done any business with the customer yet. But a NULL balance a

implies that the customer owes you no money. To convert a NULL balance to 0, use COALESCE( balanc

0 ). To convert a zero balance to NULL, use NULLIF( balance, 0 ). When PostgreSQL evaluates

NULLIF( arg1, arg2 ) expression, it compares the two arguments; if they are equal, the expression evalua

to NULL; if they are not equal, the expression evaluates to the value of arg1.

SELECT customer_id,balance, NULLIF( balance, 0 ) FROM customersORDER BY customer_id;

customer_id / balance / NULLIF( balance, 0 )1; 0.00; null2; 15.00; 15.003; 0.00; null4; 3.00; 3.005; null null

6; 19.00; 19.00--------------------------------------------

Tip: COALESCE and NULLIF are just shorthand for CASE expressions (see below under

subject Case When).

How to program functions thattransforms data:


18/46

A functions in psql

CREATE FUNCTION isEmpty (character varying, character varying)RETURNS character varyingAS $$declare

fnRetTrue alias for $1;fnRetFalse alias for $2;

beginif fnRetTrue = '' OR fnRetTrue is NOT NULL then

return fnRetTrue;else

return fnRetFalse;end if;

end;$$LANGUAGE plpgsql;

>> select isEmpty(null,'Nulo')

CREATE FUNCTION isNumericNull (real, character varying)RETURNS character varyingAS $$declare

fnRetTrue alias for $1;fnRetFalse alias for $2;

beginif fnRetTrue = 0 OR fnRetTrue is NOT NULL then

return fnRetTrue;else

return fnRetFalse;end if;

end;$$LANGUAGE plpgsql;

>> select isNumericNull(null,'Sin Valor')

-----------------------------------------------------------------------


19/46

Truth Tables

Truth Table for Three-Valued ANDOperator

a b aAND b

trUE trUE trUE

trUE FALSE FALSE

trUE NULL NULL

FALSE FALSE FALSE

FALSE NULL FALSE

NULL NULL NULL

Source: PostgreSQL User's Guide

Table Truth Table for Three-Valued OR Operator

a b a ORb

trUE trUE trUE

trUE FALSE TRUE

TRUE NULL trUE

FALSE FALSE FALSE

FALSE NULL NULL

NULL NULL NULL


Table Truth Table for NOTOperator

a NOT a


20/46

Table Truth Table for NOTOperator

a NOT a

trUE FALSE

FALSE trUE

NULL NULL


Th

eCA

SEExpression

The CASE expression is a more generic form of NULLIF and COALESCE(). A CASE expression lets you m

any given value into some other value. You can write a CASE expression in two different forms. The first fo(called the simple form) looks like this:

CASE expression1WHEN value1 THEN result1WHEN value2 THEN result2...[ ELSE resultn]

END

When PostgreSQL evaluates a simple CASE expression, it computes the value ofexpression1 then compares t

result to value1. Ifexpression1 equals value1, the CASE expression evaluates to result1. If not,

PostgreSQL compares expression1 to value2; if they match, the CASE expression evaluates to result2.

PostgreSQL continues searching through the WHEN clauses until it finds a match. If none of the values match

expression1, the expression evaluates to the value specified in the ELSE clause. If PostgreSQL gets all the wato the end of the list and you haven't specified an ELSE clause, the CASE expression evaluates to NULL. Note tha

result1, result2, resultn must all have the same data type.

You can see that NULLIF( balance, 0 ) is equivalent to

CASE balanceWHEN 0 THEN NULLELSE balance

END


21/46

Example:

CREATE TABLE test (

a INTEGER UNIQUE

);

insert into test values(1);



SELECT * FROM test;

SELECT a, CASE WHEN a=1 THEN 'one'

WHEN a=2 THEN 'two'

ELSE 'other'

ENDFROM test;

1;"one"

2;"two"

3;"other"

The second, more flexible form of the CASE expression is called the searched form:

CASEWHEN condition1 THEN result1WHEN condition2 THEN result2...[ ELSE resultn]

END

When PostgreSQL evaluates a searched CASE expression, it first evaluates condition1. If conditio

evaluates to TRue, the value of the CASE expression is result1. If condition1 evaluates to fals

PostgreSQL evaluates condition2. If that condition evaluates to true, the value of the CASE expression

result2. Otherwise, PostgreSQL moves on to the next condition. PostgreSQL continues to evaluate ea

condition until it finds one that evaluates to true. If none of the conditions is true, the CASE expressevaluates to resultn. If PostgreSQL gets all the way to the end of the list and you haven't specified an EL

clause, the CASE expression evaluates to NULL.

Like the simple form, result1, result2, resultn must all have the same data type. However, in thesearched form, the conditions don't have to be similar to each other. For example, if you want to classify the titles

yourtapes collection (and you're a big Jimmy Stewart fan), you might use a CASE expression like this:


22/46

SELECT customer_name, phone, birth_date,balance,CASE

WHEN balance > 10 THEN 'high debt'WHEN extract(years from AGE(birth_date)) > 40 THEN 'mature people'WHEN phone is null THEN 'customer do not have phone'

END

FROM customers;

"Panky Henry"; "555-1221"; "1968-01-21"; 0.00; "mature people""Jones Henry"; "555-1212"; "1970-10-10"; 0.00; null"Wonderland Alice"; "555-1122"; "1969-03-05"; 3.00; null"Rubin William"; "555-2211"; "1972-07-10"; 15.00; "high debt""Funkmaster, Freddy"; "555-FUNK"; null null null"Roxette Nurayama"; null; "1975-09-10"; 9.00; "customer do not havphone"-----------------------------------------------------------------------------------

(6rows)

The ORDER BYClause

So far, all the queries that you have seen return rows in an arbitrary order. You can add an ORDER BY clause to a

SELECT command if you need to impose a predictable ordering. The general form of the ORDER BY clause is

PostgreSQL supports another form for the ORDER BY clause: ORDER BY expression [ USING operator ]

[, ...]. This might seem a little confusing at first. When you specify ASC, PostgreSQL uses the < operator to

determine row ordering. When you specify DESC, PostgreSQL uses the > operator. The second form of the ORDE

BY clause allows you to specify an alternative operator.

ORDER BY expression [ ASC | DESC ] [, ...]

For null values, null is always considered larger than all other values when evaluating an ORDER BYclause.

The ASC and DESC terms mean ascending and descending, respectively. If you don't specify ASC orDESC,

PostgreSQL assumes that you want to see results in ascending order. The expression following ORDER BY iscalled a sort key.

Let's look at a simple example:

SELECT * FROM customers ORDER BY balance;

3; "Panky Henry"; "555-1221"; "1968-01-21"; 0.001; "Jones Henry"; "555-1212"; "1970-10-10"; 0.004; "Wonderland Alice"; "555-1122"; "1969-03-05"; 3.00


23/46

2; "Rubin William"; "555-2211"; "1972-07-10"; 15.006; "Roxette Nurayama"; null "1975-09-10"; 9.005; "Funkmaster, Freddy"; "555-FUNK"; null null---------------------------------------------------------

SELECT * FROM customers ORDER BY balance DESC;

You can include multiple sort keys in the ORDER BY clause. The following query sorts customers in ascendin

balance order, and then in descending birth_date order:

SELECT * FROM customers ORDER BY balance, birth_date DESC;

1; "Jones Henry"; "555-1212"; "1970-10-10"; 0.00

3; "Panky Henry"; "555-1221"; "1968-01-21"; 0.004; "Wonderland Alice"; "555-1122"; "1969-03-05"; 3.00

Occasionally, you will find that you want to answer a question such as "Who are my top 10 salespeople?" In morelational databases, this is a difficult question to ask. PostgreSQL offers two extensions that make it easy to answ

"Top n" or "Bottom n"-type questions. The first extension is the LIMIT clause. The following query shows the tcustomers who owe you the most money:

SELECT * FROM customers ORDER BY balance DESC LIMIT 3;

Let's change this query a little. This time we want the top five customers who have a balance over $10:

SELECT * FROM customers WHERE balance >= 10ORDER BY balance DESC LIMIT 5;

6;"Roxette Nurayama"; null; "1975-09-10"; 9.002;"Rubin William"; "555-2211"; "1972-07-10"; 15.00

The second extension is the OFFSET n clause. The OFFSET n clause tells PostgreSQL to skip the first n rows othe result set. For example, the form would be

SELECT * FROM myTable LIMIT(numbre of rows to get out) OFFSET (skip nrecords ahead).

Example:SELECT * FROM myTable LIMIT 100 OFFSET 0;


24/46

INSERT INTO customers VALUES(7,'Paul Newman','666-1616','1975-09-10',null);

SELECT customer_name, balance FROM customers ORDER BY balance DESC;

customer_name balance

"Funkmaster, Freddy"; null"Paul Newman"; null"Rubin William"; 15.00"Roxette Nurayama"; 9.00"Wonderland Alice"; 3.00"Panky Henry"; 0.00"Jones Henry"; 0.00------------------------------------

SELECT customer_name, balance FROM customers WHERE balance IS NOT NULL ORDERBY balance DESC;

OFFSET

SELECT customer_name, balance FROM customers ORDER BY balance DESC OFFSET 1;

customer_name balance"Paul Newman"; null"Roxette Nurayama"; 9.00"Rubin William"; 15.00"Wonderland Alice"; 3.00"Panky Henry"; 0.00"Jones Henry"; 0.00------------------------------------

Observe the following QUERY: Get the two younger customers;

SELECT customer_name, birth_date,balance FROM customersORDER BY birth_date DESC LIMIT 2;

There is a tie for dates and 'Paul Newman' does not come out in the report!!!

SELECT customer_name, birth_date,balance FROM customersORDER BY birth_date DESC LIMIT 3;


25/46

customer_name birth_date balance"Funkmaster, Freddy"; null null"Roxette Nurayama"; "1975-09-10"; 9.00"Paul Newman"; "1975-09-10"; null // only if you use Limit 3-----------------------------------------------

No way out, we must use SQL: query co-relational

Here is the answer to the query:

SELECT customer_name, birth_date,balance FROM customers aWHERE 2 > (SELECT COUNT(*) FROM customers b WHERE a.birth_date ( SELECT COUNT(*) FROM customers b

WHERE extract(years from AGE(a.birth_date)) > extract(years froAGE(b.birth_date)) )

ORDER BY birth_date DESC;

Customer_name Years-----------------------------------"Funkmaster, Freddy"; null"Roxette Nurayama"; 34"Paul Newman"; 34

Formatting Column Results

SELECT customer_id, customer_name,balance AS "Old balance", balance + 4 AS"New balance"FROM customersORDER BY "New balance";

customer_id customer_name "Old balance" "New balance"

3; "Panky Henry"; 0.00; 4.001; "Jones Henry"; 0.00; 4.004; "Wonderland Alice" 3.00; 7.002; "Rubin William"; 15.00; 19.006; "Roxette Nurayama"; 9.00; 13.005; "Funkmaster, Freddy null null7; "Paul Newman"; null null


26/46

--------------------------------------------------------------------------------

Aggregates

PostgreSQL offers a number of aggregate functions. An aggregate is a collection of thingsyou can think of aggregate as the set of rows returned by a query. An aggregate function is a function that operates on an aggregate

COUNT() returns the number of objects in an aggregate. The COUNT() function comes in four forms:

y COUNT(*)y COUNT( expression )y COUNT( ALL expression )y COUNT( DISTINCT expression )

SELECT * FROM customers

SELECT COUNT(*) FROM customers; // COUNT(*) pays attention to the WHERE

clause. In other words, COUNT(*) returns the number of rowthat filter

through the WHERE clauseSELECT COUNT( balance ) FROM customers; // returns the number of non-NULLvalues in the

aggregate.SELECT COUNT( phone ) FROM customers;

SELECT COUNT( * ) FROM customers; -- 7 all themSELECT COUNT( balance ) FROM customers; -- not considered null values:5SELECT COUNT(*) - COUNT( balance ) FROM customers; -- returns the number ofrows with NULL SELECT COUNT(DISTINCT balance ) FROM customers;

COUNT(), and in fact all the aggregate functions (except forCOUNT(*)), ignore NULL values.

SUM()

The SUM( expression ) function returns the sum of all the values in the aggregate expression. You can also specif

an arbitrarily complex expression as long as that expression results in a numeric value. Considering a very classic

case, we may add SUM((price*quantity)*1.15), then the query can be written as

SELECT SUM( (price * quantity) * 1.15 ) FROM Factura_Detalle

WHERE folio_factura = variablefolio;


27/46

With our database:

SELECT SUM( balance ) FROM customers;SELECT SUM( balance * 1.10 ) FROM customers;

SELECT SUM( duration ) FROM tapes WHERE ;

SELECT ('2 hours'::Interval) - ('1 hour 45 minutes'::Interval);

aqui

Lets update the tapes table:

UPDATE tapesset duration = '1hour 45 minutes'WHERE tape_id = 'AH-54706';

UPDATE tapesset duration = '55 minutes'WHERE tape_id = 'OW-41221';

UPDATE tapesset duration = '2hour 15 minutes'WHERE tape_id = 'MC-68873';

UPDATE tapesset duration = '1hour 30 minutes'WHERE tape_id = 'AB-67472';

UPDATE tapesset duration = '45 minutes'WHERE tape_id = 'AB-12345';

SELECT SUM(duration) FROM tapes; Sum interval

------------"07:10:00"

Sum interval------------


28/46

"02:15:00"

AVG()

The AVG( expression ) function returns the average of an aggregate expression.

SELECT AVG( balance ) FROM customersWHERE balance IS NOT NULL;SELECT SUM(duration) FROM tapes WHERE title LIKE 'The Godfather';

And the next query is equivalent

SELECT SUM( balance ) / COUNT( balance ) FROM customers;

But these queries are not equivalent:

SELECT AVG( balance ) FROM customers;SELECT SUM( balance ) / COUNT( * ) FROM customers;

Why not? Because COUNT( * ) counts all rows whereas COUNT( balance ) omits any rows where the

balance is NULL.

MIN() and MAX()

The MIN( expression ) and MAX( expression ) functions return the minimum and maximum values, respectivelyan aggregate expression. The MIN() and MAX() functions can operate on numeric, date/time, or string aggregates

SELECT MIN( balance ), MAX( balance ) FROM customers;

SELECT MIN( birth_date ), MAX( birth_date ) FROM customers;

Observe this query and compare

SELECT customer_name FROM customersORDER BY customer_name DESC;

With this


29/46

SELECT MIN( customer_name ), MAX( customer_name )

FROM customers;

OtherAggregate Functions

In addition to COUNT(), SUM(), AVG(), MIN(), and MAX(), PostgreSQL also supports the STDDEV(expressiand VARIANCE( expression ) aggregate functions. These last two aggregate functions compute the standadeviation and variance of an aggregate, two common statistical measures of variation within a set of observations

SELECT COUNT(balance),SUM(balance),AVG(balance), MIN(balance), MAX(balance),STDDEV(balance)from customers;

COUNT(balance)/SUM(balance)/ AVG(balance)/ MIN(balance)/ MAX(balance)/ STDDEV(balance)

------------- ------------ ----------- ------------ ------------ --------------5 27.00 5.400000000 0.00 15.00 6.5038450166036398

Grouping Results

The aggregate functions are useful for summarizing information. The result of an aggregate function is a singlevalue. Sometimes, you really want an aggregate function to apply to each of a number of subsets of your data. Forexample, you may find it interesting to compute some demographic information about your customer base.

SELECT COUNT(*) as Cantidad, title as Ttulo FROM tapesGROUP BY title;

Cantidad Ttulo2 "The Godfather"1 "Casablanca"1 "Rear Window"1 "The Birds"1 "Citizen Kane"--------------------------------

5SELECT customer_id, 4COUNT(*) AS Rentas1FROM rentals2GROUP BY customer_id3ORDER BY customer_id;

customer_id Rentas


30/46

1 23 2

-------------------------

EXTRACT and DECADE

A timestamp is a sequence of characters, denoting the date and/or time at which a certain event occurred. This da

is usually presented in a consistent format, allowing for easy comparison of two different records and tracking

progress over time; the practice of recording timestamps in a consistent manner along with the actual data is calle

timestamping.

Timestamps are typically used for logging events, in which case each event in a log is marked with a timestamp. I

filesystems, timestamp may mean the stored date/time of creation or modification of a file.

The extract function is primarily intended for computational processing.

CENTURYSELECT EXTRACT(CENTURY FROM TIMESTAMP '2001-02-1620:38:40');

Result: 21

DAY

SELECT EXTRACT(DAYFROM TIMESTAMP

'2009-0

9-1911:3

8:40');

Result: 19

DOW

The day of the week (0 - 6; Sunday is 0) (for timestamp values only)SELECT EXTRACT(DOWFROM TIMESTAMP '2009-09-1611:38:40');

Result: 3

DAY

Day of the year (1 - 365/366) (for timestamp values only)SELECT EXTRACT(DOY FROM TIMESTAMP '2009-02-0111:38:40')

Result: 32

EPOCH

Useful for interval values, the total number of seconds in the interval :


31/46

SELECT EXTRACT(EPOCH FROM INTERVAL '1 minute');Result: 60

SELECT EXTRACT(EPOCH FROM INTERVAL '1hour');Result: 3600

SELECT EXTRACT(EPOCH FROM INTERVAL '1 days');Result: 86400

VIDEO RENTALS

SELECT * FROM tapes;

SELECT SUM(EXTRACT(EPOCH FROM duration))AS SECONDS FROM tapes;

Result:25800

SELECT SUM(EXTRACT(EPOCH FROM duration)) / 60 AS MINUTES FROM tapes;

Result:430.

SELECT EXTRACT(EPOCH FROM duration) / 60 AS MINUTES FROM tapes WHERE tape_id= 'AH-54706';

Result: 105

SELECT (EXTRACT(EPOCH FROM duration) / 60 ) / 60 AS HOURSFROM tapes WHERE tape_id = 'AH-54706';

Result: 1.75

SELECT EXTRACT(HOUR FROM duration) AS HOURS, EXTRACT(MINUTE FROM duration) A

MINUTES

FROM tapes;

Result:hrs, min of all them.

SELECT EXTRACT(HOUR FROM duration) AS HOURS, EXTRACT(MINUTE FROM duration) AMINUTES

FROM tapes WHERE tape_id = 'AH-54706';

Result: 1 hr, 45 min


32/46

SELECT SUM(EXTRACT(HOUR FROM duration)) AS HOURS, SUM(EXTRACT(MINUTE FROM

duration)) AS MINUTES

FROM tapes;

Result:4hours, 190 minutes.

SELECT SUM( (EXTRACT(HOUR FROM duration)) * 60) + SUM(EXTRACT(MINUTE FROM

duration)) AS MINUTES

FROM tapes;

Result:430 minutes.

Hour

The hour field (0 - 23)

SELECT EXTRACT(HOUR FROM TIMESTAMP '2009-09-1611:38:40');

Result: 11

Minute

The minutes field (0 - 59)

SELECT EXTRACT(MINUTE FROM TIMESTAMP '2009-09-1611:38:40');Result:38

Month

For timestamp values, the number of the month within the year (1 - 12) ; for interval

values the number of months, modulo 12 (0 - 11)

SELECT EXTRACT(MONTH FROM TIMESTAMP '2009-09-1611:38:40');Result: 9

SELECT EXTRACT(MONTH FROM INTERVAL '2 years 3 months');Result:3

SECOND

The seconds field, including fractional parts (0 - 59)

SELECT EXTRACT(SECOND FROM TIMESTAMP '2009-02-0111:38:40');Result: 40

SELECT EXTRACT(SECOND FROM TIME '11:38:40.5');


33/46

Result: 40.5

Week

From a timestamp value, calculate the number of the week of the year that the day is

By definition (ISO 8601), the first week of a year contains January 4 of that year. (The ISweek starts on Monday.) In other words, the first Thursday of a year is in week 1 of th

year.

SELECT EXTRACT(WEEK FROM TIMESTAMP '2009-02-0111:38:40');Result: 5

Year

The year field

SELECT EXTRACT(YEAR FROM TIMESTAMP '2009-09-1612:13:40');Result: 2009

The date_part function is the traditional Postgres equivalent to the SQL-function extract:

date_part('field', source)

Note that here the fieldvalue needs to be a string. The valid field values for date_part

are the same as forextract

.

SELECT date_part('day', TIMESTAMP '2009-09-1612:13:40');Result: 16

SELECT date_part('hour', INTERVAL '4hours 3 minutes')Result: 4

Current Date/Time

The following functions are available to obtain the current date and/or time:

y CURRENT_TIMEy CURRENT_DATEy CURRENT_TIMESTAMP


34/46

Note that because of the requirements of the SQL standard, these functions must not be

called with trailing parentheses.

SELECT CURRENT_TIME;"12:23:59.875-05" // Time with time zone

SELECT CURRENT_DATE;"2009-09-16"

SELECT CURRENT_TIMESTAMP;"2009-09-1612:28:32.376-05"

"2009-09-1612:29:46.772-05"

The function now() is the traditional Postgres equivalent to CURRENT_TIMESTAMP.

There is also timeofday(), which returns current time to higher precision than the

CURRENT_TIMESTAMP family does:

SELECT timeofday();"Wed Sep 1612:30:41.872000 2009 CDT" //time of day, text

timeofday() uses the operating system call gettimeofday(2), which may have resolution

as good as microseconds (depending on your platform); the other functions rely on time(

which is restricted to one-second resolution. For historical reasons,timeofday()returns

itsresult as a textstringratherthan a timestamp value.

It is quite important to realize that CURRENT_TIMESTAMP and related functions all return th

time as of the start of the current transaction; their values do not increment while a

transaction is running. But timeofday() returns the actual current time.

All the date/time data types also accept the special literal value now to specify the current

date and time. Thus, the following three all return the same result:

-SELECT TIMESTAMP

'now

'; // "

2009-0

9-1612:33:54.

875"- SELECT now(); // "2009-09-1612:34:28.758-05"

- SELECT CURRENT_TIMESTAMP; // "2009-09-1612:35:07.056-05"

Note: You do not want to use the third form when specifying a DEFAULT value while

creating a table. The system will convert now to a timestamp as soon as the constant is


35/46

parsed, so that when the default value is needed, the time of the table creation would be

used! The first two forms will not be evaluated until the default value is used, because the

are function calls. Thus they will give the desired behavior of defaulting to the time of row

insertion.

(consider that CURRENT_DATE = 2009/09/16)

SELECT CURRENT_DATE - (CURRENT_DATE - 1); // 1 daySELECT CURRENT_DATE - '2009/09/10'; // 6 daysSELECT ('2009-09-16'::Date - '2008-09-10'::Date); // 371 days

SELECT AGE(CURRENT_DATE, '2008-09-16'::Date); // 1 yearSELECT AGE(CURRENT_DATE, '2008-09-16'); // 1 year

SELECT AGE('2000-09-16'::Date); // 9 yearsSELECT date_part('month','2009-09-16'::Date); // 9

SELECT date_part('month','2009-09-16'::Date) - date_part('month','2009-01-01'::Date); // 9 1 = 8

INTERVALS

SELECT ('2009-09-18 00:00:00'::Timestamp) - ('2009-09-17

00:00:00'::Timestamp);

SELECT ('2009-09-18 00:00:00'::Timestamp) + INTERVAL '1 MONTH 1 DAY';

SELECT ('2009-09-18 00:00:00'::Timestamp) + INTERVAL '1 MONTH 1 DAY 12 HOUR

30 MINUTES';

SELECT ('2009-09-18 10:30:00'::Timestamp) - INTERVAL '35 MINUTES';

SELECT INTERVAL '15 MINUTES' * 4;

SELECT INTERVAL '50 MINUTES' / 4;

SELECT INTERVAL '3 MONTH' - INTERVAL '1 MONTH';

SELECT INTERVAL '3 MONTH 15 DAY' - INTERVAL '1 MONTH'; //2 mons 15 days"

SELECT INTERVAL '3 MONTH 15 DAY' - INTERVAL '1 MONTH 15 DAY'; // "2 mons"

SELECT INTERVAL '3 MONTH 15 DAY' - INTERVAL '1 MONTH 19 DAY'; // "2 mons -

days"

SELECT INTERVAL '3 MONTH 15 DAY' - INTERVAL '19 DAY';//"3 mons - 4 days"


36/46

-------------------------------------------------------------------------------------------------------------------------------------------

Manuals Postgres URL:http://www.postgresql.org/docs/7.1/static/functions-

datetime.html

Table 4-14. Date/Time Functions

Name Return

Type

Description Example Result

current_date date returns current date;

see also below

current_time time returns current time

(of day); see also

below

current_timestamp timestamp returns current date

and time; see also

below

date_part(text,timestamp)

doubleprecision

extract subfield fromdate/time value

(equivalent to

extract); see also

below

date_part('hour',timestamp '2001-

02-16 20:38:40')

20

date_part(text,

interval)

double

precision

extract subfield from

interval value

(equivalent to

extract); see also

below

date_part('month',

interval '2 years 3

months')

3

date_trunc(text,

timestamp)

timestamp truncate date to

specified precision;

see also below

date_trunc('hour',

timestamp '2001-

02-16 20:38:40')

2001-02-16

20:00:00+00


37/46

Name Return

Type

Description Example Result

extract(identifier

from timestamp)

double

precision


date/time value; see

also below

extract(hour from

timestamp '2001-

02-16 20:38:40')

20

extract(identifier

from interval)

double

precision


interval value; see

also below

extract(month

from interval '2

years 3 months')

3

isfinite(timestamp) boolean Returns true if the

time stamp is finite

(neither invalid nor

infinity)

isfinite(timestamp

'2001-02-16

21:28:30')

true

isfinite(interval) boolean Returns true if the

interval is finite in

length

isfinite(interval '4

hours')

true

now() timestamp returns current date

and time (equivalent

to

current_timestamp);

see also below

timeofday() text returns high-precision

date and time; see

also below

timeofday() Wed Feb 21

17:01:13.00012

2001 EST

timestamp(date) timestamp convert date to

timestamp

timestamp(date

'2000-12-25')

2000-12-25

00:00:00

timestamp(date,time)

timestamp combine date andtime into a timestamp

timestamp(date'1998-02-24',time

'23:07')

1998-02-2423:07:00


38/46

Some examples:

SELECT to_char(birth_date, 'YYYY-MM-DD') FROM customers;

To_char text"1968-01-21""1970-10-10""1969-03-05""1972-07-10"

null"1975-09-10""1975-09-10"--------------------

You are doing a migration from MySQL to Postgres. You have a field of type

"timestamp without time zone" and you need get only the date part.

SELECT to_char(TIMESTAMP '2009-09-1611:38:40', 'YYYY-MM-DD'); // formattingit.Result: "2009-09-16"

select extract(hour from TIMESTAMP '2009-09-16 11:38:40');select extract(minute from TIMESTAMP '2009-09-16 11:38:40');select extract(second from TIMESTAMP '2009-09-16 11:38:40');

--------------------------------------------------------------------------------------------

VIDEO CENTRO

CREATE TABLE tapes (tape_id CHARACTER(8) UNIQUE,title CHARACTER VARYING(80),duration INTERVAL

);

CREATE TABLE customers (customer_id INTEGER UNIQUE,customer_name VARCHAR(50),phone CHAR(8),birth_date DATE,balance NUMERIC(7,2)


39/46

);

CREATE TABLE rentals (tape_id CHARACTER(8),customer_id INTEGER,rental_date DATE

);

UPDATE tapesSET duration = '02:55:00'WHERE tape_id = 'AB-12345';

SELECT * FROM tapes;>>"AB-67472";"The Godfather";"""MC-68873";"Casablanca";"""OW-41221";"Citizen Kane";""

"AH-547

06";"Rear

Window";"""AB-12345";"The Godfather";"02:55:00"

UPDATE tapesSET duration = '02:55:00'WHERE tape_id = 'AB-67472';

UPDATE tapesSET duration = '01:42:00'WHERE tape_id = 'MC-68873';

UPDATE tapesSET duration = '01:59:00'WHERE tape_id = 'OW-41221';

SELECT * from tapes;

tape_id | title | duration----------+---------------+--------------

"AB-12345"; "The Godfather"; "02:55:00"

"AB-67472"; "The Godfather"; "02:55:00""MC-68873"; "Casablanca"; "01:42:00""OW-41221"; "Citizen Kane"; "01:59:00""AH-54706"; "Rear Window";

ALTER TABLE tapesADD COLUMN male_lead VARCHAR(20),


40/46

ADD COLUMN female_lead VARCHAR(20);

select * from tapes;

tape_id | title | male_lead | female_lead | duration

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

"AB-12345"; "The Godfather"; "02:55:00""AB-67472"; "The Godfather"; "02:55:00""MC-68873"; "Casablanca"; "01:42:00""OW-41221"; "Citizen Kane"; "01:59:00""AH-54706"; "Rear Window";

UPDATE tapesset male_lead = 'Marlon Brando'WHERE title LIKE

'The Godfat

her';

UPDATE tapesset male_lead = 'Humphrey Bogart', female_lead = 'Ingrid Bergman'WHERE title LIKE 'Casablanca';

UPDATE tapesset male_lead = 'James Stewart', female_lead = 'Grace Kelly'WHERE title LIKE 'Rear Window';

SELECT * FROM tapes;

tape_id | title | male_lead | female_lead | duration----------+-----------------+-------------------+----------------+----------

"OW-41221"; "Citizen Kane"; "01:59:00""AB-12345"; "The Godfather"; "Marlon Brando"; "02:55:00""AB-67472"; "The Godfather"; "Marlon Brando"; "02:55:00""AH-54706"; "Rear Window"; "James Stewart"; "Grace Kelly""MC-68873"; "Casablanca"; "Humphrey Bogart";"Ingrid Bergman""01:42:00"

INSERT INTO tapes(tape_id,title,male_lead,female_lead,duration) VALUES('AH-

44289','The Birds',null,'Tippi Hedren','01:59:00');

SELECT tape_id,title,male_lead,female_lead,duration FROM tapes;

tape_id | title | male_lead | female_lead | duration


41/46

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

"OW-41221"; "Citizen Kane"; "01:59:00"AB-12345"; "The Godfather"; "Marlon Brando"; "02:55:00"AB-67472"; "The Godfather"; "Marlon Brando"; "02:55:00

"AH-54706"; "Rear Window"; "James Stewart"; "Grace Kelly""MC-68873"; "Casablanca"; "Humphrey Bogart"; "Ingrid Bergman";"01:42:00"AH-44289"; "The Birds"; "Tippi Hedren"; "01:59:00

SELECT title, male_lead, duration,CASE

WHEN duration < '1hour 45 min' THEN 'short movie'WHEN male_lead = 'James Stewart' THEN 'great movie'WHEN duration > '2hours' THEN 'long movie'

ENDFROM tapes;

title | male_lead | duration | case---------------+-----------------+----------+-------------The Godfather | Marlon Brando | 02:55:00 | long movieThe Godfather | Marlon Brando | 02:55:00 | long movieCasablanca | Humphrey Bogart | 01:42:00 | short movieCitizen Kane | | 01:59:00 |Rear Window |James Stewart | | great movieThe Birds | | 01:59:00 |(6 rows)----------------

You can use the COALESCE() operator to transform a NULL male_lead into the word 'Unknown':

SELECT title, COALESCE( male_lead, 'Unknown' ) FROM tapes;

SELECT title, COALESCE( male_lead, female_lead, 'Unknown' ) AS "Starring"FROM tapes;

title | Starring---------------+-----------------The Godfather | Marlon Brando

The Godfather | Marlon BrandoCasablanca | Humphrey BogartCitizen Kane | UnknownRear Window |James StewartThe Birds | Tippi Hedren(6 rows)


42/46

The COALESCE() operator is more talented than we've shown hereit can search through a list of values, returnin

the first non-NULL value it finds. For example, the following query prints the male_lead, or, ifmale_lead is

NULL, the female_lead, or if both are NULL, 'Unknown':

SELECT title, COALESCE( male_lead, female_lead, 'Unknown' ) AS "Starring"FROM tapes;

title | Starring---------------+-----------------The Godfather | Marlon BrandoThe Godfather | Marlon BrandoCasablanca | Humphrey BogartCitizen Kane | UnknownRear Window |James StewartThe Birds | Tippi Hedren(6 rows)

You can string together any number of expressions inside of the COALESCE() operator (as long as all expression

evaluate to the same type) and COALESCE() will evaluate to the leftmost non-NULL value in the list. If all of the

expressions inside COALESCE() are NULL, the entire expression evaluates to NULL.

Working with Date Values

PostgreSQL supports six basic date, time, and date/time data types, as shown in next table. I'll use the term tempoto cover date, time, and date/time data types.

Table PostgreSQL Temporal Data Types

Data Type Name Type ofData Stored Earliest Date/Time Latest Date/Time

TIMESTAMP Date/Time 4713 BC 1465001 AD

TIMESTAMP WITH

TIME ZONE

Date/Time 1903 AD 2037 AD

INTERVAL Interval -178000000 years 178000000 years

DATE Date 4713 BC 32767 AD

TIME Time 00:00:00.00 23:59:59.99


43/46

Table PostgreSQL Temporal Data Types

Data Type Name Type ofData Stored Earliest Date/Time Latest Date/Time

TIME WITH

TIME ZONE

Time 00:00:00.00+12 23:59:59.99-12

-------------------------------------------------------------------------------------------------------------------

DROP TABLE

Some columns should not allow NULL values. In most cases, it would not make sense to add a customer to your

customers table unless you know the customer's name. Therefore, the customer_name column should be

mandatory (in other words, customer_name should not allow NULL values).

Let's drop and re-create the customers table so that you can tell PostgreSQL which columns should allow NULvalues:

DROP TABLE customers;NULL usually means that you don't know what value should be entered into a column, but it can also mean that acolumn does not apply. In Create table you can add this constraint.

CREATE TABLE customers (customer_id INTEGERUNIQUENOT NULL,customer_name VARCHAR(50) NOT NULL,phone CHAR(8) NULL,birth_date DATE NULL,balance DECIMAL(7,2) NULL);

-----------------------------------------------------------------------------------------------------------------

Base de Datos Rentals

CREATE TABLE tapes (tape_id CHARACTER(8) PRIMARY KEY,title CHARACTER VARYING(80),duration INTERVAL

);

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY,customer_name VARCHAR(50),


44/46

phone CHAR(8),birth_date DATE,balance NUMERIC(7,2)

);

CREATE TABLE rentals (

tape_id CHARACTER(8),customer_id INTEGER,rental_date DATE

);

INSERT INTO customers VALUES (3, 'Panky, Henry', '555-1221', '1968-01-21',0.00);INSERT INTO customers VALUES (1, 'Jones, Henry', '555-1212', '1970-10-10',0.00);INSERT INTO customers VALUES (4, 'Wonderland, Alice N.', '555-1122', '1969-03-05', 3.00);

INSERT INTO customers VALUES (2,'Rubin,

William

','555

-2211'

,'1972

-07-10',15.00);

INSERT INTO customers VALUES (5, 'Funkmaster, Freddy', '555-FUNK',null,null);INSERT INTO customers VALUES (6, 'Roxette Nurayama', null, '1975-09-10',9.00);INSERT INTO customers VALUES(7,'Paul Newman','666-1616','1975-09-10',null);

INSERT INTO tapes VALUES ('AB-12345', 'The Godfather');INSERT INTO tapes VALUES ('AB-67472', 'The Godfather');INSERT INTO tapes VALUES ('MC-68873', 'Casablanca');INSERT INTO tapes VALUES ('OW-41221', 'Citizen Kane');INSERT INTO tapes VALUES ('AH-54706', 'Rear Window');



INSERT INTO rentals(tape_id, rental_date ,customer_id ) VALUES ('OW-41221', '2001-11-25', 1);

INSERT INTO rentals(tape_id, rental_date ,customer_id )

VALUES ('MC-

68873'

,'2

001-11-20', 3);

UPDATE tapes set duration = '1hour 45 minutes'WHERE tape_id = 'AH-54706';UPDATE tapes set duration = '55 minutes'WHERE tape_id = 'OW-41221';UPDATE tapes set duration = '2hour 15 minutes'WHERE tape_id = 'MC-68873';UPDATE tapes set duration = '1hour 30 minutes'WHERE tape_id = 'AB-67472';UPDATE tapes set duration = '45 minutes'WHERE tape_id = 'AB-12345';


45/46

ALTER TABLE tapesADD COLUMN male_lead VARCHAR(20),ADD COLUMN female_lead VARCHAR(20);

UPDATE tapes

set male_lead = 'Marlon Brando'WHERE title LIKE 'The Godfather';

UPDATE tapesset male_lead = 'Humphrey Bogart', female_lead = 'Ingrid Bergman'WHERE title LIKE 'Casablanca';

UPDATE tapesset male_lead = 'James Stewart', female_lead = 'Grace Kelly'WHERE title LIKE 'Rear Window';

INSERT INTO tapes(tape_id,title,male_lead,female_lead,duration) VALUES('AH-44289','The Birds',null,'Tippi Hedren','01:59:00');

ALTERTABLE customersDROP customers_customer_id_key;

Result: ERROR: column "customers_customer_id_key" of relation "customers" does not exist

ALTERTABLE customersDROP CONSTRAINT customers_customer_id_key;

Result: Query returned successfully with no result in 12 ms.

ALTERTABLE customersDROP CONSTRAINT customers_customer_id_key1;

Repitiendo el comando obtenemos:ERROR: constraint "customers_customer_id_key1" does not exist

ALTERTABLE customersADDCONSTRAINT customers_customer_id_key1 PRIMARY KEY(customer_id);

CREATE VIEW CustomerTienenRentas ASSELECT customer_name AS Nombre, balance AS Saldo FROM customers NATURAL JOIN rentals;

CREATE VIEW CustomerTienenRentas2 AS


46/46

SELECT customer_id,customer_name AS Nombre, balance AS Saldo FROM customers NATURAL JOINrentals;

CREATE VIEW CustomerTienenRentas3 ASSELECT customer_id,customer_name AS Nombre, phone FROM customers

WHERE customer_id IN ( SELECT customer_id FROM rentals );

UPDATE CustomerTienenRentas3SET phone = '888 7777'WHERE customer_id = 1;

ERROR: cannot update a viewHINT: You need an unconditional ON UPDATE DO INSTEAD rule.

SELECT * FROM CustomerTienenRentas3;

http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

POSTGRES SQL Data Types Unidad 5_6

Documents

Transcript of POSTGRES SQL Data Types Unidad 5_6