[SQL] ::text problem
Hi , I am running postgres 7.3.4 on Linux 7.2. I wanna print 'Not Supplied' if 'c1' column of datatype - 'timestamp' is null. For that I am using case statement SELECT CASE WHEN c1 IS NULL THEN 'Record_Not_Found'ELSE c1 END as approval_date FROM t1; ERROR: Bad timestamp external representation 'Record_Not_Found' SELECT CASE WHEN c1 IS NULL THEN 'Record_Not_Found'::text ELSE c1 END as approval_date FROM t1; ERROR: CASE types 'timestamp without time zone' and 'text' not matched Where I am doing wrong? Please shed some light. Regards Senthil Kumar S DBA
Re: [SQL] ::text problem
On Tuesday 25 November 2003 12:37, Kumar wrote: > > SELECT > CASE WHEN c1 IS NULL THEN 'Record_Not_Found'ELSE c1 END as > approval_date FROM t1; > ERROR: Bad timestamp external representation 'Record_Not_Found' > > > SELECT > CASE WHEN c1 IS NULL THEN 'Record_Not_Found'::text ELSE c1 END as > approval_date FROM t1; > ERROR: CASE types 'timestamp without time zone' and 'text' not matched Almost - you're on the right lines. Try: SELECT CASE WHEN c1 IS NULL THEN 'Record_Not_Found'::text ELSE c1::text END as approval_date FROM t1; You need to make sure both options return type text. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] "Permission denied" on view
I'm having a strange problem that, though I found an appropriate thread in the mailing list archive, archives.postgresql.org seems to be down so I cannot see what the resolution was. I have a database "scamp", with my own administrator user and a user "dialup". Inside the "scamp" database I've created the "dialup" schema, where a few tables reside. I've created a view called "Dialup.UserGroups" as the administrator user, and then granted select access to the dialup user on all the tables that view uses (which are all in the public schema). When I log into the database with the user dialup, if I perform the raw SQL statement it returns all the expected results, but I get the following when I try to select from the view: scamp=> select * from Dialup.UserGroup; ERROR: dialup: permission denied I have tried to create a similar, but differently named, view in the public schema to no avail. Essentially, I want to have the dialup user have access to the tables it needs, but except for one table, they should all be read-only. As such, I haven't set the ownership for the "dialup" schema to be owned by the "dialup" user. Could this perhaps be causing some problems? Any help on the matter would be appreciated (or getting the mailing list archives website up and running again ;) -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "He expanded his chest to make it totally clear that here was the sort of man you only dared to cross if you had a team of Sherpas with you. " ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] "Permission denied" on view
Michael A Nachbaur <[EMAIL PROTECTED]> writes: > scamp=> select * from Dialup.UserGroup; > ERROR: dialup: permission denied You haven't granted USAGE permission on the "dialup" schema to this user. You need that in addition to select permission on the view itself. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] "Permission denied" on view
On Tuesday 25 November 2003 09:09 am, Tom Lane wrote: > Michael A Nachbaur <[EMAIL PROTECTED]> writes: > > scamp=> select * from Dialup.UserGroup; > > ERROR: dialup: permission denied > > You haven't granted USAGE permission on the "dialup" schema to this > user. You need that in addition to select permission on the view itself. Thank you; apparently I didn't RTFM closely enough. /me goes sheepishly back to work -- /* Michael A. Nachbaur <[EMAIL PROTECTED]> * http://nachbaur.com/pgpkey.asc */ "`Credit?' he said. `Aaaargggh...' These two words are usually coupled together in the Old Pink Dog Bar." ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
