Re: [SQL] large IN/NOT IN subqueries result in query returning wrong data
On Tue, Dec 27, 2005 at 02:44:51PM -0500, Tom Lane wrote: George Pavlov [EMAIL PROTECTED] writes: select count(*) from t2 where t2.name not in ( select t1.name from t1 limit 261683) -- 13 select count(*) from t2 where t2.name not in ( select t1.name from t1 limit 261684) -- 0 What is so magical about 261683? Most likely, the 261684'th row of t1 has a NULL value of name. Many people find the behavior of NOT IN with nulls unintuitive, but it's per SQL spec ... regards, tom lane In 8.0 we get: elein=# select 1 in (NULL, 1, 2); ?column? -- t (1 row) elein=# select 3 not in (NULL, 1, 2); ?column? -- (1 row) For consistency, either both should return NULL or both return true/false. For completeness testing, the following are correct. Select NULL in/not in any list returns NULL. elein=# select NULL in (1,2); ?column? -- (1 row) elein=# select NULL not in (1,2); ?column? -- (1 row) elein=# select NULL in (NULL, 1,2); ?column? -- (1 row) elein=# select NULL not in (NULL, 1,2); ?column? -- (1 row) elein -- [EMAIL PROTECTED]Varlena, LLCwww.varlena.com (510)655-2584(o) (510)543-6079(c) PostgreSQL Consulting, Support Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ -- AIM: varlenallc Yahoo: AElein Skype: varlenallc -- I have always depended on the [QA] of strangers. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] large IN/NOT IN subqueries result in query returning wrong data
On Tue, Dec 27, 2005 at 07:25:40PM -0500, Tom Lane wrote: elein [EMAIL PROTECTED] writes: In 8.0 we get: elein=# select 1 in (NULL, 1, 2); ?column? -- t (1 row) elein=# select 3 not in (NULL, 1, 2); ?column? -- (1 row) For consistency, either both should return NULL or both return true/false. The above behavior is correct per spec. Feel free to argue its consistency with the SQL committee ;-) Oh, no! Not the committee! Note that the above are not inverses because you changed the lefthand input. You do get consistent results when you just add or omit NOT: Yes, you are right. I skipped the permutations to get down to the point. regression=# select 1 not in (NULL, 1, 2); ?column? -- f (1 row) regression=# select 3 in (NULL, 1, 2); ?column? -- (1 row) regards, tom lane Thanks for your clarification. ~elein [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] how to create rule as on delete
I think you want a delete trigger which does your insert and then follows through with the delete by returning old. --elein [EMAIL PROTECTED] On Tue, Oct 18, 2005 at 09:43:34PM -0700, efa din wrote: This is my rule for doing the delete event. The rule can be created. But the problem is, after the record has been deleted from the 'mytable', this record cannot be inserted into table 'maytable_log'. CREATE RULE on_delete AS ON DELETE TO mytable DO INSERT INTO mytable_log values (old.id,old.name); If I add the DO INSTEAD,the record can be inserted into 'maytable_log' and also still remain in the table 'maytable'. Which is exactly not exist at all. It just show it as im using the DO INSTEAD. CREATE RULE on_delete AS ON DELETE TO mytable DO INSTEAD INSERT INTO mytable_log values (old.id,old.name); My problem is, how to insert the deleted record into table 'mytable_log' without showing it in table 'maytable'. I really need the solution..please __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] UPDATEABLE VIEWS ... Examples?
There is a write up on these at: http://www.varlena.com/GeneralBits/82.php --elein [EMAIL PROTECTED]Varlena, LLCwww.varlena.com PostgreSQL Consulting, Support Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ = I have always depended on the [QA] of strangers. On Thu, Jun 16, 2005 at 06:05:03PM -0300, Marc G. Fournier wrote: Reading through the docs, both the CREATE VIEW and CREATE RULE pages refer to how you can use a RULE to 'simulate' an updateable VIEW ... but I can't seem to find any examples of this ... Does anyone know of an online example of doing this that I can read through? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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 ---(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] SQL Challenge: Arbitrary Cross-tab
I would use my report writer, but in any case you'd want at least 2 separate queries, maybe three to keep it simple and readable. If you are allowed to use stored procedures you can build up the output by using simple concats instead of text aggregation (which is a procedure of simple concats). Using loops and subqueries you should be to construct the heading (count distinct timekeeper_id) and then select the data row by row concatenating results before you send it out. This is a non-solution which effectively hides the aggregation in a function. Or write it in a client perl app if you must. You can't really do it w/o loops or aggregates. (I wish (hope?) I were wrong about this.) --elein On Tue, Aug 17, 2004 at 07:55:11PM -0700, Josh Berkus wrote: Folks, I have a wierd business case. Annoyingly it has to be written in *portable* SQL92, which means no arrays or custom aggregates. I think it may be impossible to do in SQL which is why I thought I'd give the people on this list a crack at it. Solver gets a free drink/lunch on me if we ever meet at a convention. The Problem: for each case there are from zero to eight timekeepers authorized to work on the case, out of a pool of 150 timekeepers. This data is stored vertically: authorized_timekeepers: case_id | timekeeper_id 213447| 047 132113| 021 132113| 115 132113| 106 etc. But, a client's e-billing application wants to see these timekeepers displayed in the following horizontal format: case_id | tk1 | tk2 | tk3 | tk4 | tk5 | tk6 | tk7 | tk8 213447| 047 | | | | | | | | 132113 | 021 | 115 | 106 | 034 | 109 | 112 | 087 | etc. Order does not matter for timekeepers 1-8. This is a daunting problem because traditional crosstab solutions do not work; timekeepers 1-8 are coming out of a pool of 150. Can it be done? Or are we going to build this with a row-by-row procedural loop? (to reiterate: I'm not allowed to use a custom aggregate or other PostgreSQL advanced feature) -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] searching polygons
You should use some variation of overlaps or contains within. There is some discussion and a list of operators in Issue #61 of General Bits. ( http://www.varlena.com/GeneralBits/61 ) I would also suggest looking at the geometric operators in the documentation. You may have to cast the polygon to a circle to use the operators, but it will still tell you whether the smaller polys are contained within or overlap the larger. elein On Tue, Feb 17, 2004 at 07:01:51PM -, David wrote: What query would i have to use to search for an item using a polygon as a parameter? (i.e a very large polygon that would identify smaller polygons within it) ideally i would like to give postgresq a series of co-ordinates and then have it return all those results whose polygons fall into that set of co-ordinates, is this possible? at the moment all i can think of is select * from species where location between '(0,0)' and '(1000,0)' and '(0, 1000)' and '(1000; 1000)'; I think im way off, any suggestions? Cheers Dave ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
Apparently the ::char is cast to varchar and then text? That explains x || ' ' || x On Tue, Feb 17, 2004 at 05:07:24PM -0700, scott.marlowe wrote: On Tue, 17 Feb 2004, Tom Lane wrote: elein [EMAIL PROTECTED] writes: This is an example of the problem. It used to expand the middle thing to 15. elein=# select 'x' || ' '::char(15) || 'x'; ?column? -- xx (1 row) Still does, but then the spaces go away again when the value goes into the concatenation, because concatenation is a text operator. But then this: select 'x'||' '||'x' should produce xx, but it produces x x. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SQL challenge--top 10 for each key value?
No, it will not work twice in the same query as is. If you want to code two counter buckets and pass in some way to distinguish between the two yada yada yada it is possible. It is also possible to code this to do multi-level counting/breaks/calculations, etc. But the SD dictionary is by connection. So any values stored in it need to be initialized at the appropriate time *outside* of the first use. elein On Sun, Apr 11, 2004 at 12:38:20AM -0400, Greg Stark wrote: elein [EMAIL PROTECTED] writes: create or replace function pycounter(integer) returns integer as ' if args[0] == 0: SD[nextno] = 1 return SD[nextno] try: SD[nextno] += 1 except: SD[nextno] = 1 return SD[nextno] ' language 'plpythonu'; And clearly it can be done faster as a little C function. Does this approach have a hope of working if it's used twice in the same query? -- greg ---(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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SQL challenge--top 10 for each key value?
This solution will be in Monday's edition of PostgreSQL General Bits (http://www.varlena.com/GeneralBits). (In other words, if it doesn't do what you mean, let me know now!) CREATE TYPE topscores AS (id integer, query integer, checksum char(32), score integer); CREATE OR REPLACE FUNCTION topscores(integer) RETURNS SETOF topscores AS ' DECLARE t topscores%ROWTYPE; r RECORD; q RECORD; n alias for $1; BEGIN FOR q IN SELECT distinct query from table70 order by query LOOP FOR t IN SELECT id , query, checksum, score FROM table70 where query = q.query ORDER BY query, score DESC LIMIT n LOOP RETURN NEXT t; END LOOP; END LOOP; RETURN; END; ' language 'plpgsql'; select * from topscores(1) ; select * from topscores(2) ; select * from topscores(3) ; On Thu, Apr 08, 2004 at 07:55:33PM +, Jeff Boes wrote: Offered up for anyone with time on their hands. I fiddled around with this for half an afternoon, then gave up and did it programmatically in Perl. Given a table that looks something like this: id | INTEGER query| INTEGER checksum | char(32) score| INTEGER include | BOOLEAN The table is unique by id. Checksum may be repeated, but I only care if it is repeated within a given group by query. (query is non-null.) I can get the top scorer for each query row by something like this: SELECT * FROM ( SELECT DISTINCT ON (checksum) * FROM my_table ORDER BY checksum, score DESC) ORDER BY query; How would you go about getting the top N (say, the top 10) for each query? And then, if that's too easy for you--consider a further case where I want every row for a given query that has include TRUE, and enough non-include rows to make N. I might end up with more than N rows for a given value of query if there were more than N with include set. I headed off in the direction of groups of SELECTs and UNIONs, and quit when I got to something like four levels of SELECT ... AS FOO ... -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
I guess I am asking about the cast sequence from
char(n) to text.
(' '::char(n))::text trims spaces. This is wrong, imo.
' '::text does not trim spaces.
' '::char(n) does not trim spaces and pads.
char(n) should not trim spaces, right? And
it doesn't on an insert. Text does not trim spaces.
Somewhere the space trimming occurs.
If it is in the operator || then the operator is wrong.
If char(n) is properly defined to not trim spaces then
there should be a separate cat for char(n). It is correct
for it to behave differently than cat for text and varchar
because of the different trimming behaviour.
I can do this patch if there is agreement. But
I may not be able to do it immediately.
elein
On Wed, Feb 18, 2004 at 11:58:37PM -0500, Tom Lane wrote:
elein [EMAIL PROTECTED] writes:
So exactly what is the order of casts that produces
different results with:
'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x'
Are operators being invoked both (text,text)?
The only relevant operator is text || text (there are also some ||
operators for arrays, bytea, and BIT, but these will all be discarded
as not the most plausible match). Therefore, in your first example the
unspecified literals will all be presumed to be text, so the space does
not get trimmed.
One of the things we could think about as a way to tweak the behavior is
creating || variants that are declared to accept char(n) on one or
both sides. These could actually use the same C implementation function
(textcat) of course. But declaring them that way would suppress the
invocation of rtrim() as char-to-text conversion.
However, if we did that then || would behave differently from other
operators on character strings, so it doesn't seem like a very
attractive option to me.
regards, tom lane
---(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
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
So exactly what is the order of casts that produces different results with: 'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x' Are operators being invoked both (text,text)? I'm trying to understand the precedence that causes the different results. elein On Tue, Feb 17, 2004 at 10:53:17PM -0500, Tom Lane wrote: elein [EMAIL PROTECTED] writes: Apparently the ::char is cast to varchar and then text? No, directly to text, because the || operator is defined as taking text inputs. But there's no practical difference between text and varchar on this point. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
This is an example of the problem. It used to expand the middle thing to 15. elein=# select 'x' || ' '::char(15) || 'x'; ?column? -- xx (1 row) On Tue, Feb 17, 2004 at 06:10:56PM -0500, Tom Lane wrote: news.postgresql.org [EMAIL PROTECTED] writes: I just discovered the following change to CHAR(n) (taken from varlena.com, general bits, issue 62). The description you quote doesn't appear to have much of anything to do with the actual behavior of 7.4. 7.4 will trim trailing spaces when converting char(n) to varchar or text, but the example query does not do that. It just coerces query output columns to char(n), and that works the same as it did before. For instance regression=# select 'zit'::char(77); bpchar --- zit (1 row) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
So the problem is there. But blaming it on char was wrong. It should be blamed on the varchar change. Hey, I thought the truncation was for varchar and not text? It was for both? It would be semantically tricky to change the operator. The precendence is to convert to text. Now with the implicit update of the char(n) to text for the operator corrupts the char() value. elein On Tue, Feb 17, 2004 at 06:40:49PM -0500, Tom Lane wrote: elein [EMAIL PROTECTED] writes: This is an example of the problem. It used to expand the middle thing to 15. elein=# select 'x' || ' '::char(15) || 'x'; ?column? -- xx (1 row) Still does, but then the spaces go away again when the value goes into the concatenation, because concatenation is a text operator. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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] Running tally
You can use plpythonu (or tcl or C or R) to do running sums. For plpythonu, you must initialize the SD[] by calling it first with the proper argument. create or replace function runsum(int,int) returns int as ' if args[0] == 1: SD[currval] = 0 return SD[currval] else: try: SD[currval] += args[1] except: SD[currval] = args[1] return SD[currval] ' language 'plpython'; select runsum(1,0); select num, runsum(0,num) from tallytable; Variations on this technique are discussed on General Bits http://www.varlena.com/GeneralBits under the Tidbits area listing talks from OSCON2003. [EMAIL PROTECTED] webstat=# select runsum(0,code), code, doc from temp_rawlogs; n Sat, Oct 04, 2003 at 05:56:38PM +0800, Christopher Kings-Lynne wrote: Hi guys, If I have a table that is just a single column full of numbers, how can I select all the rows from the table with a second column that is the running tally so far down the result set? eg: Num Tally so far 0.3 0.3 1.2 1.5 2.0 3.5 ... Does this require PL/PgSQL coding? If so, how do you actually construct an arbitrary row for returning? The docs are somewhat unclear on this. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Large Objects and Bytea
In this week's General Bits, we talk about using large objects. Perhaps this might help you understand what is involved with using them. http://www.varlena.com/GeneralBits/ cheers, [EMAIL PROTECTED] On Thu, Sep 25, 2003 at 09:41:28AM +0530, Kumar wrote: Hi Friends, I am running Postgres 7.3.4 on RH Linux 7.2. I am migrating MS SQL DB to Postgres DB. I have tables with columns of data type 'Image' in the MS SQL database. IF I choose 'bytea' datatype, I am afraid it may lead to poor performance of the database (which I read from the manual). In this case what is the best data type to use. Please suggest me. The explanation of using LOB objects in a table and to write them with image is not very clear in the documentation. Can any one send me any link or white paper or examples about it. Thanks in advance. Kumar ---(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] obtuse plpgsql function needs
Bruce-- Something for the todo list. This would be extremely handy. At minimum C functions should be able to ask the type of thing that was actually passed in and get a legitimate answer even if the type were a rowtype. This will also lead to the need for unnamed rowtypes, sooner or later. I know, I know, send a patch. --elein On Thu, Jul 24, 2003 at 01:07:18AM -0400, Tom Lane wrote: elein [EMAIL PROTECTED] writes: So, other than C, plperl or pltcl is the way to go. As long as they can input generic composite types (I wasn't sure of that, but I should have known), Come to think of it, that is a problem: we don't have any way to declare a function as taking any tuple type. So even though pltcl or plperl functions could be written to work with such input, we can't declare them. This is a problem even for C functions. You could declare a C function as taking any, but then you can't even check that what you got was a tuple ... Something to work on for 7.5, I suppose. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] obtuse plpgsql function needs
How will you know in your function what the field
names are (you won't) and how many fields to concat
unless the function would only work on a fixed number
of fields?
If it only works on a fixed number of fields,
you still have:
myconcat( text, text, text, text )
called by
select f, myconcat( f,f1,f2,f3) from t1;
and
select f, myconcat( f,f4,f5,f6) from t2;
The cost is typing in the param list.
For a variable length record it is trickier.
You can do it in C, of course.
The key pieces needed to do this are:
* Ability to pass a generic RECORD to a function.
This *might* be in 7.4 but I'm not sure.
myconcat( t1 ); or possibly myconcat (t1.*);
* Ability to know the number of columns in the RECORD
A pg_catalog query
* Ability to access the columns by order in a loop
AFAIK you have to access the columns by name.
If you can work through those issues, then
you'll have it. The pieces are available in
several areas, the generic types and languages
like plpython and plperl which may be able to
loop through a generic tuple, if they could input
a tuple.
I will hang onto this problem and if either of
us finds a solution, I'd like to publish it in
general bits.
elein
On Wed, Jul 23, 2003 at 09:06:49AM -0400, Robert Treat wrote:
On Tue, 2003-07-22 at 19:33, elein wrote:
You'll need to pass the values down to your
concat function (which I suggest you don't call concat)
and have it return a text type.
What exactly is your problem? I must be missing something.
The problem is that I need the function to be generic so that I don't
have to pass the values down to the function, it just grabs the values
automagically based on the table it's being called against.
Robert Treat
elein
On Tue, Jul 22, 2003 at 06:31:52PM -0400, Robert Treat wrote:
given
create table t1 (f,f1,f2,f3);
create table t2 (f,f4,f5,f6);
i'm trying to create a function concat() that does something like:
select f,concat() as info from t1;
which returns a result set equivalent to:
select f,('f1:' || f1 || '- f2:' || f2 || '- f3:' || f3) as x from t1;
or
select f,concat() as info from t2;
returns equivalent
select f,('f4:' || f4 || ' - f5:' || f5 || ' - f6:' || f6) as x from t2;
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] obtuse plpgsql function needs
So, other than C, plperl or pltcl is the way to go. As long as they can input generic composite types (I wasn't sure of that, but I should have known), they can access columns as array elements so you can loop through them. And they'll tell you the number of arguments. Ta da! elein On Wed, Jul 23, 2003 at 03:15:50PM -0400, Tom Lane wrote: elein [EMAIL PROTECTED] writes: You can do it in C, of course. Yeah. Also you could do it easily in plperl or pltcl (composite-type arguments get passed as perl hashes or Tcl arrays respectively). plpgsql does not have any facility for run-time determination of field names, so you're pretty much out of luck in that particular language. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
