Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

Special Character Symbols

Special character symbols are characters with a pre-defined syntactic meaning in PostgreSQL. They are typically disallowed from being used in identifier names for this reason, though as mentioned in the section on quoted identifiers, this restriction can usually be worked around with quotes if need be.

Punctuation symbols

Some special character symbols help to make up the "punctuation" of a SQL statement, much like parentheses, periods and commas do in the English language. Table 3-5 shows some common PostgreSQL-recognized syntactic symbols.

Table 3-5. Punctuation Symbols

Character

Definition

* (asterisk)

Used with the SELECT command to query all columns in the table, and with the count() aggregate function to count all rows in a table.

() (parentheses)

Used to group expressions, enforce operator precedence, and to make function calls. The use of parentheses is highly subjective to the context in which they are used.

[] (brackets)

Used in the selection of specific elements in an array, or in the declaration of an array type (e.g., with the CREATE TABLE command).

; (semicolon)

Used to terminate a SQL command. The only place it can be used within a statement is within a string constant or quoted identifier.

, (comma)

Some commands use the comma to separate elements within a list.

. (period)

Used in floating-point constants (e.g., 3.1415), as well as to reference column names as children of tables (e.g., table_name.column_name).

: (colon)

Used to select slices from arrays.

$ (dollar sign)

Used in the body of a function definition to represent a positional parameter, or argument.

Operator symbols

An operator is another type of special character symbol; it is used to perform operations on identifiers or constants, returning resultant values. Operators can be used for mathematical operations, such as addition, as well as to perform comparison and logical operations.

Consider again the books table, and its numeric author_id field. Recall that the author_id column is an integer used to identify an author. Now imagine that, due to a system modification, all author identifiers must be incremented by 1,500. This can be achieved by evaluating the result of an operation (an operator expression ) in an UPDATE statement upon the author_id column. This requires use of the addition (+) operator. An example of this can be seen in Example 3-10.

Example 3-10. Operators in statements

booktown=# 
SELECT * FROM books;

  id  |            title            | author_id | subject_id
------+-----------------------------+-----------+------------
 7808 | The Shining                 |      4156 |          9
  156 | The Tell-Tale Heart         |        15 |          9
 4513 | Dune                        |      1866 |         15
 4267 | 2001: A Space Odyssey       |      2001 |         15
 1608 | The Cat in the Hat          |      1809 |          2
 1590 | Bartholomew and the Oobleck |      1809 |          2
(6 rows)

booktown=# 
UPDATE books SET author_id = author_id + 1500; 

UPDATE 6
booktown=# 
SELECT * FROM books;

  id  |            title            | author_id | subject_id
------+-----------------------------+-----------+------------
 7808 | The Shining                 |      5656 |          9
  156 | The Tell-Tale Heart         |      1515 |          9
 4513 | Dune                        |      3366 |         15
 4267 | 2001: A Space Odyssey       |      3501 |         15
 1608 | The Cat in the Hat          |      3309 |          2
 1590 | Bartholomew and the Oobleck |      3309 |          2
(6 rows)

As you can see in Example 3-10, each author_id record is modified with the results of the + operator's operation upon the previous author_id value.

Common operators that you are may already familiar with include the basic mathematical operators: the + sign for the addition of two numeric values, the - sign for the subtraction of one numeric value from another, etc. Some of the more esoteric operators include the bitwise & and | operators, which modify binary values at the bit level.

In addition to these character symbol operators, it's important to remember the SQL keywords, which are frequently called operators as well. Most notably, this includes the logical operators AND, OR, and NOT. While technically keywords, these terms are grouped with the operators because of their operational effect upon constants and identifiers.

Table 3-6 lists some fundamental PostgreSQL operators.

Table 3-6. Fundamental PostgreSQL operators

Category

Operator

Definition

Mathematical operators

+ (addition)

Adds two numeric types

- (subtraction)

Subtracts one numeric type from another

/ (division)

Divides one numeric type by another

* (multiplication)

Multiplies one numeric type by another

! (factorial)

Returns an integer's factorial

@ (absolute value)

Returns the absolute value of a numeric value

Comparison operators

= (equivalence)

Compares two values for equivalence

< (less than)

Evaluates whether or not one number is less than another

> (greater than)

Evaluates whether or not one number is larger than another

~ (regular expression)

Performs a regular expression comparison on text values

Logical operators

AND

Returns true if both Boolean conditions are true

OR

Returns true if at least one of two Boolean conditions is true

NOT

Returns the opposite of a Boolean condition

While many operators have various connotations depending on their context, the = operator is an especially important one due to its meaning when used with an UPDATE statement's SET clause.

While in most expressions the = operator is an equivalence operator (used to compare two values for equivalence), when following the SET clause and an identifier name in an UPDATE statement, the = is read as an assignment operator. This means that it is used to assign a new value to an existing identifier, as the SET term implies.

For more information on operators, see the Section called Operators in Chapter 5."

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire