pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
View - Join based on dis-similar data types 4+ messages / 3 participants [nested] [flat]
* View - Join based on dis-similar data types @ 2003-03-05 19:59 speakeasy <nospam_artd@speakeasy.net> 2003-03-12 22:32 ` Re: View - Join based on dis-similar data types Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 4+ messages in thread From: speakeasy @ 2003-03-05 19:59 UTC (permalink / raw) To: pgsql-sql I have a field defined as a character(50) data type, and the same field stored in a transition table as a text type. The view itself work based on the join, however sub-queries against the view on that field do not return any data. Example: Table1 ---- T1Data - Character(50) Table2 ---- T2Data - Text View1: --- SELECT T1Data from Table1 JOIN Table2 ON (Table1.T1Data = Table2.T2Data); A select against View1 SELECT * FROM View1 returns all relevant records, however, adding a WHERE clause produces no output. Please advise. ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View - Join based on dis-similar data types 2003-03-05 19:59 View - Join based on dis-similar data types speakeasy <nospam_artd@speakeasy.net> @ 2003-03-12 22:32 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 4+ messages in thread From: Tom Lane @ 2003-03-12 22:32 UTC (permalink / raw) To: speakeasy <nospam_artd@speakeasy.net>; +Cc: pgsql-sql "speakeasy" <nospam_artd@speakeasy.net> writes: > I have a field defined as a character(50) data type, and the same field > stored in a transition table as a text type. > The view itself work based on the join, however sub-queries against the view > on that field do not return any data. You probably need to rtrim() the CHAR field so that the trailing blanks in it don't foul up the comparison to the TEXT field. Trailing blanks are considered insignificant in CHAR comparisons ... but not in TEXT comparisons. So, WHERE rtrim(charfield) = textfield should work. regards, tom lane ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View - Join based on dis-similar data types @ 2003-03-13 13:48 speakeasy <nospam_artd@speakeasy.net> 2003-03-14 10:52 ` Re: View - Join based on dis-similar data types Richard Huxton <dev@archonet.com> 0 siblings, 1 reply; 4+ messages in thread From: speakeasy @ 2003-03-13 13:48 UTC (permalink / raw) To: pgsql-sql Not sure if this ever made it to the group, I can't seem to find it in the recent message lists. "speakeasy" <nospam_artd@speakeasy.net> wrote in message news:... > I have a field defined as a character(50) data type, and the same field > stored in a transition table as a text type. > > The view itself work based on the join, however sub-queries against the view > on that field do not return any data. > > Example: > > Table1 > ---- > T1Data - Character(50) > > > Table2 > ---- > T2Data - Text > > > View1: > --- > SELECT T1Data from Table1 JOIN Table2 ON (Table1.T1Data = Table2.T2Data); > > > A select against View1 > SELECT * FROM View1 returns all relevant records, however, adding a WHERE > clause produces no output. > > Please advise. > > ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: View - Join based on dis-similar data types 2003-03-13 13:48 Re: View - Join based on dis-similar data types speakeasy <nospam_artd@speakeasy.net> @ 2003-03-14 10:52 ` Richard Huxton <dev@archonet.com> 0 siblings, 0 replies; 4+ messages in thread From: Richard Huxton @ 2003-03-14 10:52 UTC (permalink / raw) To: pgsql-sql On Thursday 13 Mar 2003 1:48 pm, speakeasy wrote: > Not sure if this ever made it to the group, I can't seem to find it in the > recent message lists. > > "speakeasy" <nospam_artd@speakeasy.net> wrote in message news:... > > > I have a field defined as a character(50) data type, and the same field > > stored in a transition table as a text type. > > > > The view itself work based on the join, however sub-queries against the > view > > on that field do not return any data. > > > > Example: > > > > Table1 > > T1Data - Character(50) > > Table2 > > T2Data - Text > > View1: > > --- > > SELECT T1Data from Table1 JOIN Table2 ON (Table1.T1Data = Table2.T2Data); > > > > > > A select against View1 > > SELECT * FROM View1 returns all relevant records, however, adding a WHERE > > clause produces no output. Can you please provide a real example, otherwise we're just guessing. Are you sure you've not forgotten about the spaces padding your char(50) field? -- Richard Huxton ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-03-14 10:52 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-03-05 19:59 View - Join based on dis-similar data types speakeasy <nospam_artd@speakeasy.net> 2003-03-12 22:32 ` Tom Lane <tgl@sss.pgh.pa.us> 2003-03-13 13:48 Re: View - Join based on dis-similar data types speakeasy <nospam_artd@speakeasy.net> 2003-03-14 10:52 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox