headers
Phil Jackson | 1 Jul 2010 18:46
Picon

Cannot open table in new database

We've set up a Sql database for the first time and get an error reported 
back to our application from the ODBC session object when we try to open 
one of the tables.

[42p01][7]ERROR Relation "SqlAnal" does not exist; table not found!

Here is my database outline in the Admin tool

Servers(1)
  Concept Patterns (localhost:5432)
    Databases(1)
      Postgres
       casts
       languages
       schemas
       Public
         -
         -
         Tables(2)
           DocHeader
           SqlAnal
       Replication
      Tablespaces(2)
       pg_default
       pg_global
      Group Roles
      Login Roles(1)
       Concept

We can access the list of tables from the ODBC driver which shows the
(Continue reading)

Permalink | Reply | 5 comments |
headers
Phil Jackson | 1 Jul 2010 20:11
Picon

Re: Cannot open table in new database

Hi Adrian

The link says that;

"Identifier and key word names are case insensitive."

But I have renamed the source table in lowercase and this gets me one 
step further.

I'll carry on and see what happens next.

Cheers

Phil Jackson

On 6/30/2010 3:18 PM, Adrian Klaver wrote:
> On 07/01/2010 09:46 AM, Phil Jackson wrote:
>> We've set up a Sql database for the first time and get an error reported
>> back to our application from the ODBC session object when we try to open
>> one of the tables.
>>
>> [42p01][7]ERROR Relation "SqlAnal" does not exist; table not found!
>>
>> Here is my database outline in the Admin tool
>>
>> Servers(1)
>> Concept Patterns (localhost:5432)
>> Databases(1)
>> Postgres
>> casts
(Continue reading)

Permalink | Reply | 3 comments |
headers
Rick.Casey | 1 Jul 2010 01:01
Picon
Favicon

left outer join fails because "column .. does not exist in left table?"

I have a JOIN error that is rather opaque...at least to me.

I've using other JOIN queries on this project, which seem very similar to
this one, which looks like:

SELECT S.subjectid,STY.studyabrv,labID,boxnumber,wellrow,wellcolumn
FROM DNASample D, IBG_Studies STY, Subjects S, ibg_projects P
  LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid)
WHERE
	D.subjectidkey=S.id
	AND STY.studyindex=D.studyindex
	AND IPJ.projects_index=P.ibg_projects_index
ORDER BY studyabrv,boxnumber,wellcolumn,wellrow

But when I run it I get this error:

ERROR:  column "dnasampleid" specified in USING clause does not exist in
left table

I am rather mystified by this, since this field is definitely in the
dnasample table, as the primary key. Nor do not see how to start debugging
such an error. Any suggestions appreciated...

--Rick

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
(Continue reading)

Permalink | Reply | 3 comments |
headers
Scott Marlowe | 1 Jul 2010 01:25
Picon

Re: left outer join fails because "column .. does not exist in left table?"

On Wed, Jun 30, 2010 at 7:01 PM,  <Rick.Casey <at> colorado.edu> wrote:
> I have a JOIN error that is rather opaque...at least to me.
>
> I've using other JOIN queries on this project, which seem very similar to
> this one, which looks like:
>
> SELECT S.subjectid,STY.studyabrv,labID,boxnumber,wellrow,wellcolumn
> FROM DNASample D, IBG_Studies STY, Subjects S, ibg_projects P
>  LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid)
> WHERE
>        D.subjectidkey=S.id
>        AND STY.studyindex=D.studyindex
>        AND IPJ.projects_index=P.ibg_projects_index
> ORDER BY studyabrv,boxnumber,wellcolumn,wellrow
>
> But when I run it I get this error:
>
> ERROR:  column "dnasampleid" specified in USING clause does not exist in
> left table
>
> I am rather mystified by this, since this field is definitely in the
> dnasample table, as the primary key. Nor do not see how to start debugging
> such an error. Any suggestions appreciated...

Capitalization maybe?  pgsql folds to lower case.

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
(Continue reading)

Permalink | Reply | 0 comments |
headers
Michael Glaesemann | 1 Jul 2010 01:27
Gravatar

Re: Can't EXTRACT number of months from an INTERVAL


On Jun 30, 2010, at 18:45 , Eliot, Christopher wrote:

> I need to read a timestamp from the database and turn that into an integer describing how many months ago the
event happened, rounding downward.  The events are guaranteed to be in the past.

=# select timestamp '2010-06-26 00:00:00' - timestamp  '2008-11-07 00:00:00';
 ?column? 
----------
 596 days
(1 row)

=# select justify_interval(timestamp '2010-06-26 00:00:00' - timestamp  '2008-11-07 00:00:00');
   justify_interval    
-----------------------
 1 year 7 mons 26 days
(1 row)

=# select extract('months' from justify_interval(timestamp '2010-06-26 00:00:00' - timestamp 
'2008-11-07 00:00:00'));
 date_part 
-----------
         7
(1 row)

This is likely not what you want: you're probably looking for 19.

One way would be:

=# select 12 * extract('years' from a.i) + extract('months' from a.i)
(Continue reading)

Permalink | Reply | 1 comment |
headers
Adrian Klaver | 1 Jul 2010 02:04
Picon

Re: Cannot open table in new database

On Thursday 01 July 2010 11:11:29 am Phil Jackson wrote:
> Hi Adrian
>
> The link says that;
>
> "Identifier and key word names are case insensitive."
>
> But I have renamed the source table in lowercase and this gets me one
> step further.
>
> I'll carry on and see what happens next.
>
> Cheers
>
> Phil Jackson
>

You need to go to bottom of that section where you would find :)

"Quoting an identifier also makes it case-sensitive, whereas unquoted names are 
always folded to lower case. For example, the identifiers FOO, foo, and "foo" 
are considered the same by PostgreSQL, but "Foo"  and "FOO" are different from 
these three and each other. (The folding of unquoted names to lower case in 
PostgreSQL is incompatible with the SQL standard, which says that unquoted 
names should be folded to upper case. Thus, foo should be equivalent to "FOO" 
not "foo" according to the standard. If you want to write portable applications 
you are advised to always quote a particular name or never quote it.) "

--

-- 
Adrian Klaver
(Continue reading)

Permalink | Reply | 2 comments |
headers
Tom Lane | 1 Jul 2010 02:05
Picon

Re: left outer join fails because "column .. does not exist in left table?"

Rick.Casey <at> colorado.edu writes:
> SELECT S.subjectid,STY.studyabrv,labID,boxnumber,wellrow,wellcolumn
> FROM DNASample D, IBG_Studies STY, Subjects S, ibg_projects P
>   LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid)
> WHERE
> 	D.subjectidkey=S.id
> 	AND STY.studyindex=D.studyindex
> 	AND IPJ.projects_index=P.ibg_projects_index
> ORDER BY studyabrv,boxnumber,wellcolumn,wellrow
> ERROR:  column "dnasampleid" specified in USING clause does not exist in
> left table

> I am rather mystified by this, since this field is definitely in the
> dnasample table, as the primary key.

It appears you're used to mysql, which processes commas and JOINs
left-to-right (more or less, I've never bothered to figure out their
behavior exactly).  We follow the SQL standard, which says JOIN binds
tighter than comma.  Therefore, the left-hand argument of the JOIN is
only ibg_projects not the cross join of DNASample x IBG_Studies x
Subjects x ibg_projects.

You could probably get the behavior you're expecting by writing

... FROM (DNASample D CROSS JOIN IBG_Studies STY CROSS JOIN Subjects S
CROSS JOIN ibg_projects P) LEFT OUTER JOIN ibg_ps_join IPJ USING ...

Or it might be enough to rearrange to

... FROM DNASample D LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid),
(Continue reading)

Permalink | Reply | 1 comment |
headers
Tom Lane | 1 Jul 2010 02:12
Picon

Re: Cannot open table in new database

Adrian Klaver <adrian.klaver <at> gmail.com> writes:
> On Thursday 01 July 2010 11:11:29 am Phil Jackson wrote:
>> The link says that;
>> "Identifier and key word names are case insensitive."
>> 
>> But I have renamed the source table in lowercase and this gets me one
>> step further.

> You need to go to bottom of that section where you would find :)

> ... If you want to write portable applications 
> you are advised to always quote a particular name or never quote it.) "

In particular, what probably bit you here is that you created the tables
using a tool that double-quoted the mixed-case names.  Once you've done
that, you are condemned to always double-quote those names forevermore.

			regards, tom lane

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Permalink | Reply | 0 comments |
headers
Phil Jackson | 1 Jul 2010 21:11
Picon

Re: Cannot open table in new database

Hi Adrian

I had missed that bit. That makes sense now.

Cheers

Phil Jackson
On 6/30/2010 5:04 PM, Adrian Klaver wrote:
> On Thursday 01 July 2010 11:11:29 am Phil Jackson wrote:
>    
>> Hi Adrian
>>
>> The link says that;
>>
>> "Identifier and key word names are case insensitive."
>>
>> But I have renamed the source table in lowercase and this gets me one
>> step further.
>>
>> I'll carry on and see what happens next.
>>
>> Cheers
>>
>> Phil Jackson
>>
>>      
> You need to go to bottom of that section where you would find :)
>
> "Quoting an identifier also makes it case-sensitive, whereas unquoted names are
> always folded to lower case. For example, the identifiers FOO, foo, and "foo"
(Continue reading)

Permalink | Reply | 0 comments |
headers
Scott Marlowe | 1 Jul 2010 02:16
Picon

Re: left outer join fails because "column .. does not exist in left table?"

On Wed, Jun 30, 2010 at 8:05 PM, Tom Lane <tgl <at> sss.pgh.pa.us> wrote:
> Rick.Casey <at> colorado.edu writes:
>> SELECT S.subjectid,STY.studyabrv,labID,boxnumber,wellrow,wellcolumn
>> FROM DNASample D, IBG_Studies STY, Subjects S, ibg_projects P
>>   LEFT OUTER JOIN ibg_ps_join IPJ USING (dnasampleid)
>> WHERE
>>       D.subjectidkey=S.id
>>       AND STY.studyindex=D.studyindex
>>       AND IPJ.projects_index=P.ibg_projects_index
>> ORDER BY studyabrv,boxnumber,wellcolumn,wellrow
>> ERROR:  column "dnasampleid" specified in USING clause does not exist in
>> left table
>
>> I am rather mystified by this, since this field is definitely in the
>> dnasample table, as the primary key.
>
> It appears you're used to mysql, which processes commas and JOINs
> left-to-right (more or less, I've never bothered to figure out their
> behavior exactly).

Note that even MySQL now follows the standard on this, without needing
some special strict switch or anything.  Of course, a lot of folks are
still using older versions that are in fact still broken.

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
(Continue reading)

Permalink | Reply | 0 comments |

Gmane