[SQL] Arrays, multidimensional ANY (v 8.2)
Hello everyone. Can anyone tell me how I can test for the occurrence of an array in an array? Intuitively, this should work: SELECT ARRAY[1,2,3] = ANY(ARRAY[[1,2,3],[2,3,4]]); However, this gives me "operator does not exist: integer[] = integer". I'm running 8.2. -- Niklas Bergius ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Differentiate Between Zero-Length String and NULL Column Values
On Tue, Jan 30, 2007 at 04:32:22PM +1100, Phillip Smith wrote: > SHAMPOO","EQUEST","401600","0.00","0.00","0.00","0.00","10.00","" ^^ > SELECT * FROM tmpstk WHERE ean = NULL; Along with what Michael Fuhr said in his post about equality and NULL, Postgres doesn't treat the empty string and NULL as equivalent (because they're not). Only Oracle has that dodgy interpretation of SQL, as far as I know. If you want to use the empty string, you need WHERE ean = '' If you want instead ean to be NULL, use the traditional \N to signify NULL on your way in, or define null some other way. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values
Andrew, I think you're wrong stating that Oracle would interpret NULL and empty string as equal. The Oracle databases I use (8, 9 and 10) certainly make a distiction between both values. Maybe earlier versions did so, that I don't know. >>> Andrew Sullivan <[EMAIL PROTECTED]> 2007-01-30 14:13 >>> On Tue, Jan 30, 2007 at 04:32:22PM +1100, Phillip Smith wrote: > SHAMPOO","EQUEST","401600","0.00","0.00","0.00","0.00","10.00","" ^^ > SELECT * FROM tmpstk WHERE ean = NULL; Along with what Michael Fuhr said in his post about equality and NULL, Postgres doesn't treat the empty string and NULL as equivalent (because they're not). Only Oracle has that dodgy interpretation of SQL, as far as I know. If you want to use the empty string, you need WHERE ean = '' If you want instead ean to be NULL, use the traditional \N to signify NULL on your way in, or define null some other way. A -- Andrew Sullivan | [EMAIL PROTECTED] Users never remark, "Wow, this software may be buggy and hard to use, but at least there is a lot of code underneath." --Damien Katz ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values
On Tue, Jan 30, 2007 at 02:38:07PM +0100, Bart Degryse wrote: > Andrew, I think you're wrong stating that Oracle would interpret > NULL and empty string as equal. The Oracle databases I use (8, 9 > and 10) certainly make a distiction between both values. Maybe > earlier versions did so, that I don't know. Hmm. Well, I'm not an Oracle guy, so I don't really know. All I know is that we occasionally get people coming from Oracle who are surprised by this difference. What I've been _told_ is that '' and NULL are under some circumstances (maybe integers?) the same thing, whereas of course ' ' and NULL are not. But since I'm not an Oracle user, people should feel free to ignore me :) A -- Andrew Sullivan | [EMAIL PROTECTED] Everything that happens in the world happens at some place. --Jane Jacobs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values
Andrew Sullivan Wrote:
> On Tue, Jan 30, 2007 at 02:38:07PM +0100, Bart Degryse wrote:
> > Andrew, I think you're wrong stating that Oracle would interpret
> > NULL and empty string as equal. The Oracle databases I use (8, 9
> > and 10) certainly make a distiction between both values. Maybe
> > earlier versions did so, that I don't know.
>
> Hmm. Well, I'm not an Oracle guy, so I don't really know. All I
> know is that we occasionally get people coming from Oracle who are
> surprised by this difference. What I've been _told_ is that '' and
> NULL are under some circumstances (maybe integers?) the same thing,
> whereas of course ' ' and NULL are not. But since I'm not an Oracle
> user, people should feel free to ignore me :)
Sybase does something like that... In sybase, null and empty string are the
same. However, to avoid the equality ''=NULL, they actually interpret '' as a
single space. So if you do something like SELECT 'A'+''+'C' (concatenation is
+ in sybase), it results in 'A C'. Null is a "real" empty string in that its
length is zero, and if you insert a trim('') into a column, it will treat it
as NULL.
Herouth
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
[SQL] Linked Databases
Hi list, I don't know how it occurs but in some way my work and test databases are connected. If I drop my work database the test database is dropped and vice versa. I suppose it occurs becouse I am mantaining this two databases making the following. - Backup the main database (work) - Restore all data on test (I already criated before) Other times I just need to create the test database to it appears identically the main database (work). Could anybody tell me what to do ? I am using pgAdmin and COMPRESS method to make my backup (I don't know if pgAdmin has already capability to work with plain backups today). any help would be welcomed. I think if I didn't do a COMPRESS option and change all work names to test it wouldn't happens. =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values
On Tue, 30 Jan 2007 09:23:32 -0500 Andrew Sullivan <[EMAIL PROTECTED]> wrote: > Hmm. Well, I'm not an Oracle guy, so I don't really know. All I > know is that we occasionally get people coming from Oracle who are > surprised by this difference. What I've been _told_ is that '' and > NULL are under some circumstances (maybe integers?) the same thing, > whereas of course ' ' and NULL are not. But since I'm not an Oracle > user, people should feel free to ignore me :) I don't have an Oracle installation here and I haven't used it much but I wonder if they treat the following two statements differently. SELECT * FROM table WHERE column IS NULL; SELECT * FROM table WHERE column = NULL; The latter violates the SQL spec and is not allowed by PostgreSQL without setting a special flag. Is it possible that Oracle accepts "=" against NULL and also treats it slightly differently? -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values
On Tue, Jan 30, 2007 at 11:38:34AM -0500, D'Arcy J.M. Cain wrote: > I don't have an Oracle installation here and I haven't used it much but > I wonder if they treat the following two statements differently. > >SELECT * FROM table WHERE column IS NULL; >SELECT * FROM table WHERE column = NULL; AFAIK they don't accept the latter any more than we do. But again, I'm an Oracle ignoramous. I _do_ know that people of my acquaintance who have historically only developed against Oracle have given me queries with things like value = '' in it, and been surprised. Or at least, I think it's like that. I do recall hearing a lot about how stupid Postgres was because it didn't like something that worked "perfectly well" on Oracle, which I was assured was the most SQL-compliant system on the planet. Happily, I no longer work with any of those people :) A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Log, Logs and more Logs
On Tue, Jan 30, 2007 at 03:01:05PM -0200, Ezequias Rodrigues da Rocha wrote: > I know there are many logs in postgresql but I don't have many > familiarity with all them. It's not totally plain what you want to have happen -- whether you want all queries, whether you merely want ERRORs to show up in your logs, or whether you want only ERRORS to show their queries. If it's the latter, then my suggestion is to set log_min_messages to ERROR and log_min_error_statement to ERROR as well. The section of the manual you really want to read is http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Log, Logs and more Logs
So you are tell me that it is impossible to retrieve it ok ? Just by changing this values (what I did now) it is possible to get the error messages and their statements OK?. My best Regards Ezequias 2007/1/30, Andrew Sullivan <[EMAIL PROTECTED]>: On Tue, Jan 30, 2007 at 03:01:05PM -0200, Ezequias Rodrigues da Rocha wrote: > I know there are many logs in postgresql but I don't have many > familiarity with all them. It's not totally plain what you want to have happen -- whether you want all queries, whether you merely want ERRORs to show up in your logs, or whether you want only ERRORS to show their queries. If it's the latter, then my suggestion is to set log_min_messages to ERROR and log_min_error_statement to ERROR as well. The section of the manual you really want to read is http://www.postgresql.org/docs/8.2/static/runtime-config-logging.html A -- Andrew Sullivan | [EMAIL PROTECTED] If they don't do anything, we don't need their acronym. --Josh Hamilton, on the US FEMA ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values
Andrew Sullivan wrote: AFAIK they don't accept the latter any more than we do. But again, I'm an Oracle ignoramous. I _do_ know that people of my acquaintance who have historically only developed against Oracle have given me queries with things like value = '' in it, and been surprised. Or at least, I think it's like that. I do recall hearing a lot about how stupid Postgres was because it didn't like something that worked "perfectly well" on Oracle, which I was assured was the most SQL-compliant system on the planet. Happily, I no longer work with any of those people :) At my last job I used Oracle 8i and 9i (standard editions), and I very definitely remember the pain of it interpreting the empty string as a NULL (especially relevant when input needed trimming). - Geoff ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values
D'Arcy J.M. Cain wrote: >SELECT * FROM table WHERE column IS NULL; >SELECT * FROM table WHERE column = NULL; > > The latter violates the SQL spec and is not allowed by PostgreSQL > without setting a special flag. It doesn't violate any spec and it's certainly allowed by PostgreSQL without any flags. It's just that the result is not what some people expect. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Differentiate Between Zero-Length String and NULLColumn Values
> On Tue, Jan 30, 2007 at 02:38:07PM +0100, Bart Degryse wrote: > >> Andrew, I think you're wrong stating that Oracle would interpret >> NULL and empty string as equal. The Oracle databases I use (8, 9 >> and 10) certainly make a distiction between both values. Maybe >> earlier versions did so, that I don't know. > > Hmm. Well, I'm not an Oracle guy, so I don't really know. All I > know is that we occasionally get people coming from Oracle who are > surprised by this difference. What I've been _told_ is that '' and > NULL are under some circumstances (maybe integers?) the same thing, > whereas of course ' ' and NULL are not. But since I'm not an Oracle > user, people should feel free to ignore me :) I've recently read some books on Oracle, so probably the best thing I can do is to quote a paragraph on this from "Oracle PL/SQL programming" from O'Reilly: In Oracle SQL and PL/SQL, a null string is 'usually' indistiguishable from a literal of zero characters, represented literally as ''. For example the following expression will evaluate to TRUE both in SQL and PL/SQL: '' IS NULL Assigning a zero-length string to a VARCHAR2(n) variable in PL/SQL also yields a NULL result: DECLARE str VARCHAR2(1) := ''; BEGIN IF str IS NULL -- will be TRUE This behavior is consistent with Oracle's treatment of VARCHAR2 table columns. ... These examples illustrate Oracle's partial adherence to the 92 and 99 versions of the ANSI SQL standard, which mandates a difference between a zero-length string and a NULL string. Oracle admits the difference, and says they may fully adopt the standard in the future. They've been issuing that warning for about 10 years, though, and it hasn't happened yet. ... Note: This does not apply to the CHAR(n) columns - these are blank-padded. Tomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Log, Logs and more Logs
Hi list, I lost some data becouse my application does not throws an exception when an update statement report an error. It is possible to find out if the PostgreSQL could have this error (and more important the complete statement that make this errors occurs) ? I know there are many logs in postgresql but I don't have many familiarity with all them. Any help would be glad. Regards ... -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Atenciosamente (Sincerely) Ezequias Rodrigues da Rocha =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- A pior das democracias ainda é melhor do que a melhor das ditaduras The worst of democracies is still better than the better of dictatorships http://ezequiasrocha.blogspot.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Log, Logs and more Logs
On Tue, Jan 30, 2007 at 04:41:13PM -0200, Ezequias Rodrigues da Rocha wrote: > So you are tell me that it is impossible to retrieve it ok ? No. > Just by changing this values (what I did now) it is possible to get > the error messages and their statements OK?. Yes, but you need to signal the postmaster to reload its config file. Either kill -SIGHUP or restart the postmaster. After that, you should see the statement that caused your error. Note that if the UPDATE itself doesn't cause the error, but say a later COMMIT (you can get this in serializable mode easily), you'll see the COMMIT as the thing that caused the error. A -- Andrew Sullivan | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
