[SQL] NULLS and string concatenation
I have a function that uses an execute statement to insert data into a table, I do in my implementation of table partitioning. Anyway, I ran into trouble when NULL values were being passed in (fields are nullable) and my insert statement turned into a big NULL. Here's an equivalent statement that caused trouble: select 'some text, should be null:'|| NULL This returns NULL and no other text. Why is that? I wasn't expecting the "some text.." to disappear altogether. Is this a bug? I was able to work around the problem by using COALESCE (and casting variables since it wants the same data types passed to it). -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 3: 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] NULLS and string concatenation
On Fri, 19 Nov 2004 17:48:34 +, Richard Huxton <[EMAIL PROTECTED]> wrote: > Don Drake wrote: > > select 'some text, should be null:'|| NULL > > > > This returns NULL and no other text. Why is that? I wasn't expecting > > the "some text.." to disappear altogether. > > > > Is this a bug? > > No. Null is "unknown" if you append unknown (null) to a piece of text, > the result is unknown (null) too. > > If you're using NULL to mean something other than unknown, you probably > want to re-examine your reasons why. > I'm using NULL to mean no value. Logically, NULL is unknown, I agree. I'm trying to dynamically create an INSERT statement in a function that sometimes receives NULL values. This is still strange to me. In Oracle, the same query would not replace the *entire* string with a NULL, it treats the NULL as a no value. I can't find in the documentation where string concatenation of any string and NULL is NULL. -Don ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] NULLS and string concatenation
On Fri, 19 Nov 2004 15:01:42 -0500, Tom Lane <[EMAIL PROTECTED]> wrote: > Don Drake <[EMAIL PROTECTED]> writes: > > This is still strange to me. In Oracle, the same query would not > > replace the *entire* string with a NULL, it treats the NULL as a no > > value. > > Oracle is a bit, um, standards-challenged. They fail to make a > distinction between an empty string and a NULL, but such a distinction > is both logically necessary and required by the SQL standard. > > > I can't find in the documentation where string concatenation of any > > string and NULL is NULL. > > SQL92 section 6.13 , General Rule 2a: > > a) If either S1 or S2 is the null value, then the result of the >is the null value. > > regards, tom lane > Thanks for the responses. I now have a better appreciation for the SQL standard and PostgreSQL. -Don ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] plpgsql functions and NULLs
OK, I have a function that finds records that changed in a set of tables and attempts to insert them into a data warehouse. There's a large outer loop of candidate rows and I inspect them to see if the values really changed before inserting. My problem is that when I look to see if the row exists in the warehouse already, based on some IDs, it fails when an ID is NULL. The ID is nullable, so that's not a problem. But I'm forced to write an IF statement looking for the potential NULL and write 2 queries: IF omcr_id is null select * from WHERE omcr_id is NULL AND ... ELSE select * from WHERE omcr_id=candidate.omcr_id AND END IF; IF FOUND ... Is there a way to do the lookup in one statement?? This could get ugly quick. I'm using v7.4. Thanks. -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] plpgsql functions and NULLs
My outer query to get the candidates has an outer join, that works just fine and I get the null OMCR_ID's. It's when I have to query the dimension table (no joins) to see if a row exists with a (sometimes) null OMCR_ID I'm forced to write 2 queries, when I think I should only have to write one. Thanks. -Don On Mon, 31 Jan 2005 14:25:03 -0600, Thomas F. O'Connell <[EMAIL PROTECTED]> wrote: > This sounds like a perfect candidate for a LEFT OUTER JOIN. See: > > http://www.postgresql.org/docs/7.4/static/queries-table- > expressions.html#QUERIES-FROM > > Yours would looks something like: > > SELECT * > FROM ... > LEFT JOIN candidate AS c > ON <...>.omcr_id = c.omcr_id > AND ... > > -tfo > > -- > Thomas F. O'Connell > Co-Founder, Information Architect > Sitening, LLC > http://www.sitening.com/ > 110 30th Avenue North, Suite 6 > Nashville, TN 37203-6320 > 615-260-0005 > > On Jan 30, 2005, at 1:41 PM, Don Drake wrote: > > > OK, I have a function that finds records that changed in a set of > > tables and attempts to insert them into a data warehouse. > > > > There's a large outer loop of candidate rows and I inspect them to see > > if the values really changed before inserting. > > > > My problem is that when I look to see if the row exists in the > > warehouse already, based on some IDs, it fails when an ID is NULL. > > The ID is nullable, so that's not a problem. > > > > But I'm forced to write an IF statement looking for the potential NULL > > and write 2 queries: > > > > IF omcr_id is null > >select * from > >WHERE omcr_id is NULL > >AND ... > > ELSE > >select * from > >WHERE omcr_id=candidate.omcr_id > >AND > > END IF; > > > > IF FOUND > > ... > > > > Is there a way to do the lookup in one statement?? This could get ugly > > quick. I'm using v7.4. > > > > Thanks. > > > > -Don > > > > -- > > Donald Drake > > President > > Drake Consulting > > http://www.drakeconsult.com/ > > 312-560-1574 > > > > ---(end of > > broadcast)--- > > TIP 4: Don't 'kill -9' the postmaster > > -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] plpgsql functions and NULLs
You learn something new everyday. I've never seen that syntax before, and it works like a charm!! Thanks a ton. -Don On Mon, 31 Jan 2005 13:31:34 -0800 (PST), Stephan Szabo <[EMAIL PROTECTED]> wrote: > > On Sun, 30 Jan 2005, Don Drake wrote: > > > OK, I have a function that finds records that changed in a set of > > tables and attempts to insert them into a data warehouse. > > > > There's a large outer loop of candidate rows and I inspect them to see > > if the values really changed before inserting. > > > > My problem is that when I look to see if the row exists in the > > warehouse already, based on some IDs, it fails when an ID is NULL. > > The ID is nullable, so that's not a problem. > > > > But I'm forced to write an IF statement looking for the potential NULL > > and write 2 queries: > > > > IF omcr_id is null > >select * from > >WHERE omcr_id is NULL > >AND ... > > ELSE > >select * from > >WHERE omcr_id=candidate.omcr_id > >AND > > END IF; > > Hmm, perhaps some form like: > > WHERE not(candidate.omcr_id is distinct from omcr_id) > > -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 3: 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] plpgsql functions and NULLs
I'm constraining on other columns as well and it's still picking up the index. Thanks again. -Don On Mon, 31 Jan 2005 16:32:02 -0800 (PST), Stephan Szabo <[EMAIL PROTECTED]> wrote: > On Mon, 31 Jan 2005, Don Drake wrote: > > > You learn something new everyday. I've never seen that syntax before, > > and it works like a charm!! > > Actually, now that I think about it, I wonder if that's a good thing to > use because I don't think that'll use indexes to do the search. You may > want to do some testing to see how it runs for you. > > > On Mon, 31 Jan 2005 13:31:34 -0800 (PST), Stephan Szabo > > <[EMAIL PROTECTED]> wrote: > > > > > > On Sun, 30 Jan 2005, Don Drake wrote: > > > > > > > OK, I have a function that finds records that changed in a set of > > > > tables and attempts to insert them into a data warehouse. > > > > > > > > There's a large outer loop of candidate rows and I inspect them to see > > > > if the values really changed before inserting. > > > > > > > > My problem is that when I look to see if the row exists in the > > > > warehouse already, based on some IDs, it fails when an ID is NULL. > > > > The ID is nullable, so that's not a problem. > > > > > > > > But I'm forced to write an IF statement looking for the potential NULL > > > > and write 2 queries: > > > > > > > > IF omcr_id is null > > > >select * from > > > >WHERE omcr_id is NULL > > > >AND ... > > > > ELSE > > > >select * from > > > >WHERE omcr_id=candidate.omcr_id > > > >AND > > > > END IF; > > > > > > Hmm, perhaps some form like: > > > > > > WHERE not(candidate.omcr_id is distinct from omcr_id) > -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ 312-560-1574 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Determining Rank
I have a query that shows the top N count(*)'s. So it's basically: select some_val, count(*) from big_table group by some_val order by count(*) limit 50 Now, I would like to have the rank included in the result set. The first row would be 1, followed by 2, etc. all the way to 50. I can do this in PHP, but stuffing it into the DB query makes other things easier. Any ideas? Thanks. -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ http://www.MailLaunder.com/ 312-560-1574 ---(end of broadcast)--- TIP 3: 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] Determining Rank
Michael,
That's an excellent solution, but on my table, the explain plan sucks
and the query time is over 3 minutes when implemented. Is there a
simple way to get a row_num without using a temporary sequence?
Thanks for your help.
-Don
On Fri, 4 Feb 2005 14:02:20 +0900, Michael Glaesemann
<[EMAIL PROTECTED]> wrote:
>
> On Feb 4, 2005, at 12:06, Don Drake wrote:
>
> > I have a query that shows the top N count(*)'s.
> >
> > So it's basically:
> >
> > select some_val, count(*)
> > from big_table
> > group by some_val
> > order by count(*)
> > limit 50
> >
> > Now, I would like to have the rank included in the result set. The
> > first row would be 1, followed by 2, etc. all the way to 50.
>
> There are a couple of different ways to go about this. One is just to
> append an extra column that's basically a line number, but I find it
> doesn't handle ties very elegantly. The following example uses a
> correlated subquery using HAVING to determine the rank as "the number
> of items that have a total quantity greater than the current item + 1".
> Note that items bar and baz have exactly the same totals and are tied,
> while the rank of bat shows that there are 3 items that have totals
> greater than bat.
>
> Joe Celko's "SQL for Smarties" has a bunch of things like this in it.
> I've found it quite helpful.
>
> Hope this helps.
>
> Michael Glaesemann
> grzm myrealbox com
>
> create table items (
> item text not null
> , qty integer not null
> ) without oids;
>
> insert into items (item, qty) values ('foo', 1);
> insert into items (item, qty) values ('foo', 2);
> insert into items (item, qty) values ('foo', 1);
> insert into items (item, qty) values ('foo', 3);
> insert into items (item, qty) values ('foo', 3);
> insert into items (item, qty) values ('foo', 20);
> insert into items (item, qty) values ('foo', 1);
> insert into items (item, qty) values ('bar', 3);
> insert into items (item, qty) values ('bar', 1);
> insert into items (item, qty) values ('bar', 3);
> insert into items (item, qty) values ('bar', 13);
> insert into items (item, qty) values ('baz', 2);
> insert into items (item, qty) values ('baz', 4);
> insert into items (item, qty) values ('baz', 14);
> insert into items (item, qty) values ('bat', 3);
> insert into items (item, qty) values ('bat', 4);
>
> select item, sum(qty) as tot_qty
> from items
> group by item
> order by tot_qty desc;
>
> item | tot_qty
> --+-
> foo | 31
> bar | 20
> baz | 20
> bat | 7
> (4 rows)
>
> select i1.item
> , i1.tot_qty
> , ( select count(*)
> from (
> select item
> , sum(qty) as tot_qty
> from items
> group by item
> having sum(qty) > i1.tot_qty
> ) as i2
> ) + 1 as rank
> from (
> select item
> , sum(qty) as tot_qty
> from items
> group by item
> ) as i1
> order by i1.tot_qty desc;
>
> item | tot_qty | rank
> --+-+--
> foo | 31 |1
> bar | 20 |2
> baz | 20 |2
> bat | 7 |4
> (4 rows)
>
>
--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
312-560-1574
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] AutoCommit and DDL
I have a generic function that creates partition tables and their corresponding indexes and constraints. I've tested the function using a GUI and it works great. We implemented in our data loaders (written in perl) and the DB connections have AutoCommit turned off. When we run it in the perl script, I see the code being execute to create the tables, etc. but they are never created, even with a commit at the end. It appears something is causing the table creation to get rolled back. I turned AutoCommit on and re-ran the perl script and the tables get created. Why won't these table's get created when AutoCommit is off? Why do I have to commit DDL? I'm running 7.4.3. Thanks. -Don -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ http://www.MailLaunder.com/ 312-560-1574 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] AutoCommit and DDL
I know it's not failing, I have the server logging the commands and there are no errors. The only change made was turning AutoCommit on. -Don On Sat, 26 Feb 2005 21:20:43 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Sat, Feb 26, 2005 at 02:56:52PM -0600, Don Drake wrote: > > > I turned AutoCommit on and re-ran the perl script and the tables get > > created. Why won't these table's get created when AutoCommit is off? > > Why do I have to commit DDL? > > If you're using DBI, are you using PrintError or RaiseError or > otherwise checking the success of each command? My first guess > would be that some command is failing (e.g., a DROP of an object > that doesn't exist), so the transaction is automatically rolled > back even though you requested a commit. > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ http://www.MailLaunder.com/ 312-560-1574 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] AutoCommit and DDL
I did some traces and it all looks OK.
The problem has to do with multiple concurrent connections to the
server causing problems. I've removed the concurrent connections and
now this works. Strange.
-Don
On Sun, 27 Feb 2005 12:54:34 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Sun, Feb 27, 2005 at 11:55:37AM -0600, Don Drake wrote:
>
> > I know it's not failing, I have the server logging the commands and
> > there are no errors.
> >
> > The only change made was turning AutoCommit on.
>
> Have you used any of DBI's tracing capabilities? Could you post a
> simple test case? The following works for me with Perl 5.8.6, DBI
> 1.47, DBD::Pg 1.32, and PostgreSQL 7.4.7 on FreeBSD 4.11-STABLE:
>
> #!/usr/bin/perl
>
> use strict;
> use warnings;
> use DBI;
>
> my $dbh = DBI->connect("dbi:Pg:dbname=test", "mfuhr", "", {AutoCommit => 0});
> $dbh->do("CREATE TABLE foo (x integer)");
> $dbh->commit;
> $dbh->disconnect;
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>
--
Donald Drake
President
Drake Consulting
http://www.drakeconsult.com/
http://www.MailLaunder.com/
312-560-1574
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] AutoCommit and DDL
I don't think it would be easy to duplicate since our code base is quite extensive. Basically, what was happening was a script would first open a database connection (AutoCommit turned off by default), create a few objects (that also opened independent db connections), the objects would run queries so they have data populated, an insert is done and committed, then we call a generic function that will create a new table (using inherits, part of our partitioning) as well as adding indexes and constraints to this new table. It would get to a point in the function where it was adding a FK constraint and every query against the table would "hang" which appeared to be some exclusive lock not being released. Activity on the DB would be 100% idle during this period, the alter table never came back so we killed it each time. I commented out the code doing the FK constraint add and everything worked just fine. As a test I moved the partition function call to the beginning of the script (before the objects were created) and it worked just fine. I then changed the object declarations passing in the single DB handle, and every now works just fine. I ran DBI traces and everything looked just fine. This was a strange problem, I'm just happy everything is working. -Don On Sun, 27 Feb 2005 20:33:55 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Sun, Feb 27, 2005 at 07:55:35PM -0600, Don Drake wrote: > > > The problem has to do with multiple concurrent connections to the > > server causing problems. I've removed the concurrent connections and > > now this works. Strange. > > Can you elaborate? Can you describe the scenario with enough detail > that somebody else could attempt to duplicate it? It's certainly > possible to issue DDL statements concurrent with other connections, > so we still haven't identified what's really causing the problem. > Investigation shouldn't end until "strange" becomes "aha!" > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > -- Donald Drake President Drake Consulting http://www.drakeconsult.com/ http://www.MailLaunder.com/ 312-560-1574 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Encoding on 8.0.4
I recently upgraded my DB from 7.4.3 to 8.0.4 and I've noticed the following errors appearing in my serverlog: 2005-11-03 05:56:57 CST 127.0.0.1(38858) ERROR: Unicode characters greater than or equal to 0x1 are not supported 2005-11-03 06:04:09 CST 127.0.0.1(38954) ERROR: invalid byte sequence for encoding "UNICODE": 0xe02d76 2005-11-03 06:04:21 CST 127.0.0.1(38964) ERROR: invalid byte sequence for encoding "UNICODE": 0xe02d76 2005-11-03 06:11:35 CST 127.0.0.1(39072) ERROR: Unicode characters greater than or equal to 0x1 are not supported 2005-11-03 06:23:23 CST 127.0.0.1(39657) ERROR: invalid byte sequence for encoding "UNICODE": 0xd40d 2005-11-03 08:10:02 CST 127.0.0.1(44073) ERROR: invalid byte sequence for encoding "UNICODE": 0xe46973 2005-11-03 08:21:13 CST 127.0.0.1(44711) ERROR: Unicode characters greater than or equal to 0x1 are not supported 2005-11-03 08:26:36 CST 127.0.0.1(44745) ERROR: invalid byte sequence for encoding "UNICODE": 0xc447 2005-11-03 08:40:59 CST 127.0.0.1(45087) ERROR: invalid byte sequence for encoding "UNICODE": 0xdd20 2005-11-03 09:14:52 CST 127.0.0.1(46009) ERROR: Unicode characters greater than or equal to 0x1 are not supported I never received these errors on when running 7.4.3. I used the default encodings on 7.4.3 and I tried chaning client_encoding from sql_ascii to UNICODE and I'm still seeing this. I'm storing in a text data type email that contains other characterset characters. Any ideas on how to resolve this? -Don-- Donald DrakePresidentDrake Consultinghttp://www.drakeconsult.com/http://www.MailLaunder.com/ 312-560-1574
