No database schema updates for PGSQL
matreides - June 11, 2009 - 16:37
| Project: | Page Title |
| Version: | 6.x-2.2 |
| Component: | Code |
| Category: | bug report |
| Priority: | critical |
| Assigned: | nicholasThompson |
| Status: | needs review |
Description
I am fairly new to drupal, and this was working in page_title-6.x-2.0 after updating the mod I get this error:
warning: pg_query() [function.pg-query]: Query failed: ERROR: column "node" does not exist LINE 1: SELECT page_title FROM page_title WHERE type = "node" AND id... ^ in /usr/local/www/apache22/drupal.techally.com/public/includes/database.pgsql.inc on line 139.
user warning: query: SELECT page_title FROM page_title WHERE type = "node" AND id = 2 in /usr/local/www/apache22/drupal.techally.com/public/sites/all/modules/page_title/page_title.module on line 302.
I included a screen shot just in case.
| Attachment | Size |
|---|---|
| page_title_error.png | 36.77 KB |
#1
I'm having exactly the same problem but on D5. When I de-activate page_title module the error goes away. Any ideas?
#2
Ah this would be my fault as I've not included an upgrade path for PGSQL (completely forgot about that! Sorry!)...
Does anyone have any experience with PGSQL? I've never used it and the syntax looks a little different to MySQL. I believe it's update 5203 and 6200 which do the MySQL updating...
#3
The problem is that MySQL uses non-standard quoting, so your SQL won't work on Postgres. For Postgres, you need to quote identifiers (table names,column names,view names,etc...) using ", and literal values (text,timestamps,etc...) using '. So your above query should look like this:
SELECT page_title FROM page_title WHERE type = 'node' AND id = 2;
Note: I believe there is a way to get MySQL to accept the standard quoting, but don't recall off-hand how it's done. HTH.
#4
xzilla, what do you mean by "The problem is that MySQL uses non-standard quoting..."?
As much as I know, it is just fine for MySQL to query:
SELECT page_title FROM page_title WHERE type = "node" AND id = 2or
SELECT page_title FROM page_title WHERE type = 'node' AND id = 2Both shall work well, and return the same result.
You shall of course switch the whole query sentence from single quotes to double quotes, to make a difference... ;-)
#5
Problem is caused by the fact PostgreSQL distincts ' from "
Attached (very small) patch fixes the problem
#6
PostgreSQL uses quotes for only field names.
Attached patch fixes this problem not only for SELECT query but also INSERT and DELETEs.
#7
Like valthebald said, postgres makes a distinction between single-quotes and double-quotes. Second Problem is with column "type" which is a reserved word in postgres.
So I made a patch against page_title version 6.x-2.2 featuring :
- "type" as a column name should always be double-quoted just to make sure
- 'node' / 'user' / 'term' as a plain string must be single-quoted
Hopefully, I didn't miss something,
Cheers
#8
The double quoted string values does not work with MySQL either if it is configured to accept ANSI SQL. I'm having the same problem with MySQL and the page_title module.