Skip site navigation (1) Skip section navigation (2)

CASTING in JOIN or WHERE

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: CASTING in JOIN or WHERE
Date: 2005-02-16 16:47:46
Message-ID: 20050216163641.M46189@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi All,

I have written some SQL and have noticed that PostgreSQL seems to be modifying
the syntax slightly.  I do not care per se but if it impacts performance I
want to understand how to write the SQL in the more efficient form.  I am
recalling posts commenting on columns with dissimilar types and the lack of
index usage.

I have a column of type varchar
   id | character varying(20) | not null

I use this in a WHERE clause
   WHERE id = 'SN'
PostgreSQL changes the statement to
   WHERE id::text = 'SN'::text

I notice a similar behavior when doing JOINs
Given in table 1
   item_id | character varying(20) | not null
and in table 2
   id | character varying(20) | not null

The JOIN
ON ( tbl_line_item.item_id = tbl_item.id )
is changed to
ON ( tbl_line_item.item_id::text = tbl_item.id::text )

Kind Regards,
Keith

pgsql-novice by date

Next:From: Van Ingen, LaneDate: 2005-02-16 20:07:47
Subject: Front End Languages for PostgreSQL
Previous:From: Kevin CrenshawDate: 2005-02-16 13:54:32
Subject: Re: Revision Control

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group