[SQL] testing for null value in integer field?
How does one check for an unset value in an integer field? I've tried such things as: select . where intnumber = '' select .. where intnumber = ? select . where intnumber = NULL Thanks. -- Until later, Geoffrey [EMAIL PROTECTED] Building secure systems inspite of Microsoft ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] testing for null value in integer field?
Wei Weng wrote: Geoffrey wrote: How does one check for an unset value in an integer field? I've tried such things as: select . where intnumber = '' select .. where intnumber = ? select . where intnumber = NULL Thanks. It is actually WHERE intnumber IS NULL. You don't use operator = to compare with NULLs, you use IS. Thank you muchly.... -- Until later, Geoffrey [EMAIL PROTECTED] Building secure systems inspite of Microsoft ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] \D TO FILE
Eric Anderson Vianet SAO wrote: How could I record the ´ \d table ´ command to a file? echo '\d' | psql rnd > outputfile -- Until later, Geoffrey Registered Linux User #108567 Building secure systems in spite of Microsoft ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] feature request ?
Very simply, a boolean may have to values: true or false. It's also possible that it's not been set to anything (NULL). -- Until later, Geoffrey Registered Linux User #108567 Building secure systems in spite of Microsoft ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] casting BOOL to somthng
sad wrote: you wrote: you can use CREATE CAST to make your own cast from boolean to text. thnx it helps. and i am still desire to know _WHY_ there are no predefined cast for BOOL ? and at the same time there are predefined casts for INT and FLOAT.. I'd like to understand in what context you would find this useful. Don't take me wrong please. I'm by no means a db expert, but I can't see a purpose for such a cast. Can you provide a reasonable example of such usage? Thanks. -- Until later, Geoffrey Registered Linux User #108567 AT&T Certified UNIX System Programmer - 1995 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] casting BOOL to somthng
sad wrote: On Wednesday 01 September 2004 10:38, Michael Glaesemann wrote: On Sep 1, 2004, at 2:41 PM, sad wrote: On Wednesday 01 September 2004 09:24, Stephan Szabo wrote: There's a fairly accepted convention for integer representations. There's no such convention for boolean representations. then why do you print its value on a screen ?! Perhaps because if you don't print *something* you can't see it? since you printed it you poke a convention (of casting to string) if you can print it on screen why not to print it in string? Simply for the sake of being able to recognize the value. If it doesn't have some value printed, how do you know what the value is? Although your example would work (from a previous post), I don't see a real world use for such an effort. There are work arounds that are quite simple. -- Until later, Geoffrey Registered Linux User #108567 AT&T Certified UNIX System Programmer - 1995 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to check postgres running or not ?
Worik wrote: Assuming it is unix The command ps xau|grep post You might want to change that to: ps aux|grep postgres As your suggestion will pick up extraneous data if one is running postfix on the same box. -- Until later, Geoffrey Registered Linux User #108567 AT&T Certified UNIX System Programmer - 1995 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Order of columns in a table important in a stored procedure?
Richard Gintz wrote: Pardon me ya'll, but can you tell me what a "saisies" is? More than one saisy??? -- Until later, Geoffrey ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [despammed] [SQL] Insert psql commands inside a script
Andreas Kretschmer wrote: am 21.04.2005, um 17:19:23 -0500 mailte Letnes, David G. folgendes: I have used the psql -f /tmp/SelectCommands.sql before, but now I want to put the sql statement right in the shell script. I haven't had any luck. Is there a command I can use that will not point to a file for the sql instructions but right on the same line. I use very short psql commands and would like to do it all with 1 file. echo "select bla from fasel" | psql -U database You can also use here documents, shell-variables and so on. Or you can do the following: psql database < sqlcode where sqlcode contains your sql. -- Until later, Geoffrey ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [despammed] [SQL] Insert psql commands inside a script
Michael Fuhr wrote: On Sat, Apr 23, 2005 at 08:15:48AM -0400, Geoffrey wrote: Or you can do the following: psql database < sqlcode where sqlcode contains your sql. That command redirects the standard input from the file named sqlcode; the requirement says NOT to use an external file. My bad, I misread the post as indicating he wanted to use an external file. Too early on a Saturday with a late Friday and not enough coffee yet... -- Until later, Geoffrey ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] passing values into .sql scripts
I want to do something like this: \set tmp :acct 'a value' \i query.sql where query.sql looks like this: select sum(amount), dr_acct from ledger where dr_acct = :acct group by dr_acct; select sum(amount), cr_acct from ledger where cr_acct = :acct group by cr_acct; select (select sum(amount) from ledger where dr_acct = :acct ) - (select sum(amount) from ledger where cr_acct = :acct ); However, this is what I get: psql:pnc.sql:1: ERROR: column "a value" does not exist Is there a different way I should be doing this? (I'm used to Oracle's &1...&n parameters.) Geoffrey -- Geoffrey S. Knauth | http://knauth.org/gsk ---(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] passing values into .sql scripts
That did exactly what I wanted. Thank you! (I had tried \set acct ... before, but it was your quoting that fixed my problem.) Geoffrey -- Geoffrey S. Knauth | http://knauth.org/gsk On Sep 15, 2005, at 18:43, Michael Fuhr wrote: What's your intention here? The above sets the variable tmp to the value of the variable acct concatenated with 'a value', but you don't show acct being set anywhere. Did you mean to set acct? If so then try this: \set acct '\'a value\'' ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] listen_addresses = '*' ok, specific address(es) no
I'm running PostgreSQL 8.1.3. In my postgresql.conf, the following works: listen_addresses = '*' but the following does not: listen_addresses = '192.168.1.33' I get an error: WARNING: could not create listen socket for "192.168.1.33" FATAL: could not create any TCP/IP sockets I'm running Mac OS X 10.4.6 on PPC, if that makes a difference. For now, listen_addresses = '*' works for me, but I was curious why I couldn't use the more restrictive listen_addresses. Geoffrey -- Geoffrey S. Knauth | http://knauth.org/gsk ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] listen_addresses = '*' ok, specific address(es) no
Tom, I omitted the LOG and HINT lines before. LOG: could not bind IPv4 socket: Can't assign requested address HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for "192.168.1.33" FATAL: could not create any TCP/IP sockets This works fine if I use '*' instead of '192.168.1.33'. Andrew Sullivan wrote: Well, do you actually have an interface with that address? I think I do, in that the machine's wireless interface is set up with a 192.168.1.x/24 address and 1.33 is on the same subnet. Or maybe I'm misunderstanding. I thought the purpose of listen_addresses was to allowing incoming connections only from listed addresses. Geoff On Jun 15, 2006, at 10:40, Tom Lane wrote: Geoffrey Knauth <[EMAIL PROTECTED]> writes: I'm running PostgreSQL 8.1.3. In my postgresql.conf, the following works: listen_addresses = '*' but the following does not: listen_addresses = '192.168.1.33' I get an error: WARNING: could not create listen socket for "192.168.1.33" FATAL: could not create any TCP/IP sockets There should be more info than that --- AFAICS all the failure paths in that code emit LOG messages. Perhaps you have log_min_messages set too high to allow the info to come out? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] listen_addresses = '*' ok, specific address(es) no
Thank you Tom Lane and Phillip Smith, you've answered my questions. --Geoff On Jun 15, 2006, at 22:46, Tom Lane wrote: Geoffrey Knauth <[EMAIL PROTECTED]> writes: Andrew Sullivan wrote: Well, do you actually have an interface with that address? I think I do, in that the machine's wireless interface is set up with a 192.168.1.x/24 address and 1.33 is on the same subnet. Or maybe I'm misunderstanding. I thought the purpose of listen_addresses was to allowing incoming connections only from listed addresses. You're misunderstanding then. What listen_addresses can bind to is IP addresses of *your own machine*. For example, if you bind to only 127.0.0.1 then only local loopback connections will work. Binding to just one external IP address is only interesting if your machine has more than one such address; then it prevents connections that're coming in through one of the other addresses. The right way to limit incoming connections to only come *from* particular IP addresses is to use pg_hba.conf. regards, tom lane ---(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
