Thread: Problems with join (no such attribute, but it exists)
Hi,
I do probably something very silly, but I am total novice in SQL world
(and social scientist, so no CS professional), but I need PostgreSQL
back-end for Grass, so I thought I could try to do some light SELECTing.
Well, I stumbled pretty early:
grass=> \d tracts_towns2000
Table "tracts_towns2000"
Column | Type | Modifiers
-----------+-----------------------+-----------
COMPL_ID | character varying(18) | not null
COUNTY_ID | smallint | not null
TRACT_ID | integer | not null
TOWN_ID | smallint | not null
AREA_ID | smallint | not null
Primary key: tracts_towns2000_pkey
grass=> \d tracts2000
Table "tracts2000"
Column | Type | Modifiers
------------+---------+-----------
objectid | bigint |
area | real |
perimeter | real |
state | text |
county | text |
tract | text |
ct_id | text |
logrecno | text |
blk_count | integer |
total_pop | bigint |
samp_pop | bigint |
dry_pct | real |
dry_acres | real |
dry_sqmi | real |
dry_sqkm | real |
shape_area | text |
shape_len | text |
grass=> SELECT * FROM tracts_towns2000 conv, tracts2000 tr LIMIT 1;
COMPL_ID | COUNTY_ID | TRACT_ID | TOWN_ID | AREA_ID
| objectid | area | perimeter | state | county | tract
| ct_id | logrecno | blk_count | total_pop | samp_pop | dry_pct
| dry_acres | dry_sqmi | dry_sqkm | shape_area
| shape_len
14000US25017341100 | 17 | 341100
| 5 | 5 | 1 | 2.55159e+07 | 45288.6 | 25 | 001
| 010100 | 25001010100 | 0000856 | 137 | 3431 | 3431
| 0.9805 | 6181.92 | 9.659 | 25.017 | 2.55158949367e+007
4.52885899988e+004 |
(1 row)
grass=> SELECT conv.tract_id, tr.tract FROM tracts_towns2000 conv,
tracts2000 tr LIMIT 5;
ERROR: No such attribute or function 'tract_id'
Can anybody explain me, which stupidy I have commited?
Thanks,
Matej Cepl
--
Matej Cepl,
GPG Finger: 89EF 4BC6 288A BF43 1BAB 25C3 E09F EF25 D964 84AC
138 Highland Ave. #10, Somerville, Ma 02143, (617) 623-1488
PostgreSql doesn't recognize the column because the column name is in uppercase and you typed in lowercase. You need to surround the uppercase with quotes as well. Your query should have been written like: grass=> SELECT conv."TRACT_ID", tr.tract FROM tracts_towns2000 conv, tracts2000 tr LIMIT 5; I hope that helps. Julie ----- Original Message ----- From: "Matej Cepl" <cepl@surfbest.net> To: <pgsql-novice@postgresql.org> Sent: Wednesday, May 14, 2003 10:30 AM Subject: [NOVICE] Problems with join (no such attribute, but it exists) > Hi, > > I do probably something very silly, but I am total novice in SQL world > (and social scientist, so no CS professional), but I need PostgreSQL > back-end for Grass, so I thought I could try to do some light SELECTing. > Well, I stumbled pretty early: > > grass=> \d tracts_towns2000 > Table "tracts_towns2000" > Column | Type | Modifiers > -----------+-----------------------+----------- > COMPL_ID | character varying(18) | not null > COUNTY_ID | smallint | not null > TRACT_ID | integer | not null > TOWN_ID | smallint | not null > AREA_ID | smallint | not null > Primary key: tracts_towns2000_pkey > > grass=> \d tracts2000 > Table "tracts2000" > Column | Type | Modifiers > ------------+---------+----------- > objectid | bigint | > area | real | > perimeter | real | > state | text | > county | text | > tract | text | > ct_id | text | > logrecno | text | > blk_count | integer | > total_pop | bigint | > samp_pop | bigint | > dry_pct | real | > dry_acres | real | > dry_sqmi | real | > dry_sqkm | real | > shape_area | text | > shape_len | text | > > grass=> SELECT * FROM tracts_towns2000 conv, tracts2000 tr LIMIT 1; > COMPL_ID | COUNTY_ID | TRACT_ID | TOWN_ID | AREA_ID > | objectid | area | perimeter | state | county | tract > | ct_id | logrecno | blk_count | total_pop | samp_pop | dry_pct > | dry_acres | dry_sqmi | dry_sqkm | shape_area > | shape_len > 14000US25017341100 | 17 | 341100 > | 5 | 5 | 1 | 2.55159e+07 | 45288.6 | 25 | 001 > | 010100 | 25001010100 | 0000856 | 137 | 3431 | 3431 > | 0.9805 | 6181.92 | 9.659 | 25.017 | 2.55158949367e+007 > 4.52885899988e+004 | > (1 row) > > grass=> SELECT conv.tract_id, tr.tract FROM tracts_towns2000 conv, > tracts2000 tr LIMIT 5; > ERROR: No such attribute or function 'tract_id' > > Can anybody explain me, which stupidy I have commited? > > Thanks, > > Matej Cepl > > -- > Matej Cepl, > GPG Finger: 89EF 4BC6 288A BF43 1BAB 25C3 E09F EF25 D964 84AC > 138 Highland Ave. #10, Somerville, Ma 02143, (617) 623-1488 > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
On Wed, 14 May 2003 12:30:07 -0400, Matej Cepl <cepl@surfbest.net>
wrote:
> grass=> \d tracts_towns2000
> Table "tracts_towns2000"
> Column | Type | Modifiers
> -----------+-----------------------+-----------
> COMPL_ID | character varying(18) | not null
> COUNTY_ID | smallint | not null
> TRACT_ID | integer | not null
> TOWN_ID | smallint | not null
> AREA_ID | smallint | not null
> Primary key: tracts_towns2000_pkey
Somehow you managed to create your table with all uppercase column
names. So you have to keep double quoting these names.
> grass=> SELECT conv.tract_id, tr.tract FROM tracts_towns2000 conv,
> tracts2000 tr LIMIT 5;
> ERROR: No such attribute or function 'tract_id'
SELECT conv."TRACT_ID", tr.tract FROM tracts_towns2000 conv,
tracts2000 tr LIMIT 5;
BTW, unless one of these tables has only one row, you almost certainly
want to add a WHERE clause to your query, something like
WHERE tr.thiscolumn = conv.thatcolumn
Servus
Manfred
On 2003-05-16, 17:46 GMT, Juliet May wrote: > PostgreSql doesn't recognize the column because the column name is in > uppercase and you typed in lowercase. You need to surround the uppercase > with quotes as well. Your query should have been written like: > > grass=> SELECT conv."TRACT_ID", tr.tract FROM tracts_towns2000 conv, > tracts2000 tr LIMIT 5; > > I hope that helps. Thanks, it helped, but it seems to me silly (to be case-senstitive and case-minimalizing in the same time). Thanks anyway Matej -- Matej Cepl, GPG Finger: 89EF 4BC6 288A BF43 1BAB 25C3 E09F EF25 D964 84AC 138 Highland Ave. #10, Somerville, Ma 02143, (617) 623-1488
> > > >BTW, unless one of these tables has only one row, you almost certainly >want to add a WHERE clause to your query, something like > > WHERE tr.thiscolumn = conv.thatcolumn > >Servus > Manfred > > Hi, this tip from Manfred is very important. If you do not do that, you will get any possible combinations of the rows of the 2 tables, so if both tables have 100 entries, you would get 10000 results, while most of them don't make any sense. Think of a join of 3 tables containing 1000 rows each: you would get a result of 1000'000'000 rows, this can slow down your server quite a bit... Cheers, Dani