Database Reference
In-Depth Information
Shorthand Casting
ANSI SQL defines a construct called
CAST
that allows you to morph one data type to
another. For example,
CAST('2011-1-11' AS date)
casts the text
2011-1-1
to a date.
PostgreSQL has a shorthand for doing this using a pair of colons, as in
'2011-1-1'::date
. This syntax is shorter and easier to apply for cases in which you
can't directly cast from one type to another and have to intercede with one or more
intermediary types, such as
someXML::text::integer
.
Multirow Insert
PostgreSQL supports the multirow constructor to insert more than one record at a time.
Example 7-11
demonstrates how to use a multirow construction to insert data into the
table we created in
Example 6-2
.
Example 7-11. Using multirow constructor to insert data
INSERT
INTO
logs_2011
(
user_name
,
description
,
log_ts
)
VALUES
(
'robe'
,
'logged in'
,
'2011-01-10 10:15 AM EST'
),
(
'lhsu'
,
'logged out'
,
'2011-01-11 10:20 AM EST'
);
The latter portion of the multirow constructor starting with the
VALUES
keyword is often
referred to as a
values list
. A values list can stand alone and effectively creates a table on
the fly, as in
Example 7-12
.
Example 7-12. Using multirow constructor as a virtual table
SELECT
*
FROM
(
VALUES
(
'robe'
,
'logged in'
,
'2011-01-10 10:15 AM EST'
::
timestamptz
),
(
'lhsu'
,
'logged out'
,
'2011-01-11 10:20 AM EST'
::
timestamptz
)
)
AS
l
(
user_name
,
description
,
log_ts
);
When you use
VALUES
as stand-in for a virtual table, you need to specify the names for
the columns and explicitly cast the values to the data types in the table, if the parser can't
infer the data type from the data.
ILIKE for Case-Insensitive Search
PostgreSQL is case-sensitive. However, it does have mechanisms in place to do a case-
insensitive search. You can apply the
upper
function to both sides of the ANSI
LIKE
operator, or you can simply use the
ILIKE
(~) operator found only in PostgreSQL:
SELECT
tract_name
FROM
census
.
lu_tracts
WHERE
tract_name
ILIKE
'%duke%'
;
tract_name
------------------------------------------------
Census Tract 2001, Dukes County, Massachusetts
