Why does PostgreSQL have to be different (scheme does not exist)?

Question

I'm not used to working with PostgreSQL, but am using Zend_Db_Select to create my queries. What does the error below mean?

SQL error:
ERROR:  schema "il" does not exist

In statement:
SELECT il.count(*) AS "count" FROM "image_lite" AS "il" 
INNER JOIN "exif_parse" AS "ex" ON il.image_id = ex.image_id 
WHERE (ex.cam_make = 'apple')

Edmund · Accepted Answer · 2010-06-08 02:31:01Z

6

It's parsing il.count(*) as a call to a function "count" in the schema "il". Probably what you want is the expression:

count(il.*)

answered Jun 8, 2010 at 2:31

Edmund

10.8k3 gold badges42 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Khorkrak · Accepted Answer · 2010-06-08 02:30:11Z

5

You can think of schema in PostgreSQL as you would a database in MySQL. Also try removing the double quotes around everything because that's just plain strange and might be causing the problem.

Oh I see il.count(*) make no sense at all.

Just do this instead:

select count(*) cnt       
  from image_lite il 
  join exif_parse ex on il.image_id = ex.image_id 
 where ex.cam_make = 'apple'

answered Jun 8, 2010 at 2:30

Khorkrak

4,0452 gold badges30 silver badges41 bronze badges

3 Comments

Charles Over a year ago

+1 for removing the double-quotes. That irritates me just as much when people backtick the heck out of everything in a MySQL statement... probably where the bad habit came from!

Ben Dauphinee Over a year ago

The quotes are automatically inserted by the Zend_Db_Select

leonbloy Over a year ago

quotes can lead to unexpected behaviours in postgresql if identifiers are not lowercase (not your case but anyway). Khorkrak gives the correct sane syntax

Charles · Accepted Answer · 2010-06-08 02:31:57Z

1

Postgres is interpreting the "il" in "il.count(*)" as a schema name. You can simply omit it and ask for count(*), or if you want to get specific, count(il.*)

If you're coming from MySQL, schemas are what the rest of the world calls what MySQL calls a database.

answered Jun 8, 2010 at 2:31

Charles

51.5k13 gold badges107 silver badges146 bronze badges

Collectives™ on Stack Overflow

Why does PostgreSQL have to be different (scheme does not exist)?

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related