SQL Syntax Reference
A Reference to PSQL Supported Syntax
The following topics cover the SQL grammar supported by PSQL:
Literal Values
PSQL supports all of the standard literal formats. This topic provides some examples of the most common literal formats.
String Values
String constants may be expressed in SQL statements by surrounding the given string of characters with single quotes. If the string itself contains a single-quote or apostrophe, the character must be preceded by another single-quote.
String literals have type VARCHAR. Characters are encoded using the database code page. If the literal is preceded by the letter N, the literal has type NVARCHAR and characters are encoded using UCS-2. A literal embedded in a SQL query string may go through additional encoding conversions in the SQL access methods prior to final conversion in the SQL engine. In particular, if the SQL text is converted to an encoding that does not support all Unicode characters, characters in the SQL text may be lost prior to the engine converting the string literal to NVARCHAR.
Examples
In the first example, the apostrophe or single quote contained within the string must be escaped by another single quote.
SELECT * FROM t1 WHERE c1 = 'Roberta''s Restaurant'
SELECT STREET FROM address WHERE city LIKE 'san%'
Number Values
Date Values
Date constants may be expressed in SQL statements as a character string or embedded in a vendor string. The first case is treated as data type CHAR and the vendor string representation is treated as a value of type DATE. This becomes important when conversions are attempted.
PSQL partially supports extended SQL grammar, as outlined in this function.
PSQL supports the following date literal format: 'YYYY-MM-DD'.
Dates may be in the range of year 0 to 9999.
Examples
The next two statements return all the classes whose start date is after 1995-06-05.
SELECT * FROM Class WHERE Start_Date > '1995-06-05'
SELECT * FROM Class WHERE Start_Date > {d '1995-06-05'}
Time Values
PSQL supports the following time literal format: 'HH:MM:SS'.
Time constants may be expressed in SQL statements as a character string or embedded in a vendor string. Character string representation is treated as a string of type CHAR and the vendor string representation as a value of type TIME.
PSQL partially supports extended SQL grammar, as outlined in this function.
Examples
The following two statements retrieve records from the class table where the start time for the classes is 14:00:00.
SELECT * FROM Class WHERE Start_time = '14:00:00'
SELECT * FROM Class WHERE Start_time = {t '14:00:00'}
Timestamp Values
Timestamp constants may be expressed in SQL statements as a character string or embedded in a vendor string. PSQL treats the character string representation as a string of type CHAR and the vendor string representation as a value of type TIMESTAMP.
PSQL supports the following time stamp literal format: 'YYYY-MM-DD HH:MM:SS.MMM'
Examples
The next two statements retrieve records from the Billing table where the start day and time for the log is 1996-03-28 at 17:40:49.
SELECT * FROM Billing WHERE log = '1996-03-28 17:40:49'
SELECT * FROM Billing WHERE log = {ts '1996-03-28 17:40:49'}
SQL Grammar in PSQL
The following topics cover the SQL grammar supported by PSQL. Statements and keywords are listed in alphabetical order.
Note You can use the SQL Editor in with PSQL Control Center to test most of the SQL examples. Exceptions are noted in the discussion of the grammar elements. For more information, see
SQL Editor in
PSQL User's Guide.