Re: [SQL] Column with recycled sequence value

[PFC]

	You could update all the fields which use this sequence number. You say  
you have a lot of activity so you must have mahy holes in your sequence,  
probably of the possible 2^32 values, only a fes millions are used.

You can do the following :
	- Take down the database, back it up, and restart it with a single user,  
so only you can connect, using psql.
	- Create a table :
CREATE TABLE translate ( new_id SERIAL PRIMARY KEY, old_id INTEGER,  
UNIQUE(old_id) ) WITHOUT OIDS;

	- Insert into this table all the used sequence values you have in your  
database. If you have all the proper constraints, these should come from  
only one table, so it should be straightformard :

INSERT INTO translate (old_id) SELECT id FROM your_table;
	Thus the "translate" table maps old id's to a new sequence that you just  
started, and that means your new id's will be compactly arranged, starting  
at 1.

	- Update your existing table, joining it to the translate table, to  
replace the old id by the new id.


On Thu, Jan 13, 2005 at 06:08:20PM +0100, KÖPFERL Robert wrote:
Hi,
suppose I have a let's say heavy used table. There's a column containing
UNIQUE in4
values. The data type musn't exceed 32-Bit. Since however the table is  
heavy
used 2^32 will be reached soon and then? There are far less than  
4G-records
saved thus these values may be reused. How can this be accomplished?
You can set the sequence up to cycle (so once it gets to the end, it
wraps around to the beginning again).  The keyword is CYCLE at CREATE
SEQUENCE time.  It defaults to NO CYCLE.
One potential problem, of course, are collisions on the table,
because some value wasn't cleared out.  It sounds like you don't have
that problem though.
A

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] assign the row count of a query to a variable

[PFC]

   var := count(*) from T;
or :
   SELECT INTO var count(*) from T;
---(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] SQL Query Performance tips

[PFC]

	If I understand well a person has all the free weapons which have a level  
<= to his own level, and of course all the weapons he bought.

1) get da weapons
One query can only use one index. Bad for you !
Let's split the free and non-free weapons.
1a) free weapons
SELECT weapon_alignment, count(1) as cnt
FROM weapons
WHERE weapon_level < (user_level)
AND weapon_cost = 0
GROUP BY weapon_alignment;
	No need for distinct anymore ! Note also that distinct'ing on weapon_name  
is a slower than on weapon_id.
	You can create an index on (weapon_cost,weapon_level) but I don't think  
it'll be useful.
	For ultimate speed, as this does not depend on the user_id, only the  
level, you can store the results of this in a table, precalculating the  
results for all levels (if there are like 10 levels, it'll be a big win).

1b) weapons bought by the user
SELECT w.weapon_alignment, count(1) as cnt
FROM weapons w, user_weapons uw
WHERE w.weapon_id = uw.weapon_id
AND uw.user_id = (the user_id)
AND w.weapon_cost > 0
GROUP BY weapon_alignment;
	You'll note that the weapons in 1a) had cose=0 so they cannot appear  
here, no need to distinct the two.

2) combine the two
SELECT weapon_alignment, sum(cnt) FROM
(SELECT weapon_alignment, count(1) as cnt
FROM weapons
WHERE weapon_level < (user_level)
AND weapon_cost = 0
GROUP BY weapon_alignment)
UNION ALL
SELECT w.weapon_alignment, count(1) as cnt
FROM weapons w, user_weapons uw
WHERE w.weapon_id = uw.weapon_id
AND uw.user_id = (the user_id)
AND w.weapon_cost > 0
GROUP BY weapon_alignment)
GROUP BY weapon_alignment;
You can also do this :
SELECT weapon_alignment, count(1) as cnt FROM
(SELECT weapon_alignment
FROM weapons
WHERE weapon_level < (user_level)
AND weapon_cost = 0)
UNION ALL
SELECT w.weapon_alignment
FROM weapons w, user_weapons uw
WHERE w.weapon_id = uw.weapon_id
AND uw.user_id = (the user_id)
AND w.weapon_cost > 0)
GROUP BY weapon_alignment;
How does it turn out ?


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] SQL design question: null vs. boolean values

[PFC]

create table xyz
(
field_foo   char(1) check (field_foo in 'y', 'n'),
foo_detail varchar(255),
check (
  case
when field_foo='y' and foo_detail is null
then false
else true
  end
  )
);
A simpler check would be :
CHECK(
(field_foo = 'y' AND foo_detail IS NOT NULL)
OR  ( (field_foo = 'n' OR field_foo IS NULL) AND foo_detail IS NULL)
)
	Which means " field_foo can be y, n, or NULL, and foo_detail should be  
null except if field_foo is 'y' "

	Also, IMHO, the Y/N/unknown case should have three values, NULL meaning  
'the user has not answered this question'. Because if you insert a blank  
row in the table and fill it afterwards, you'll know if it was 'answered  
unknown' or 'unanswered'.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] "How do I ..." SQL question

[PFC]

 Return only four rows beginning at second row:
SELECT count(*) AS count, name, year FROM a
  GROUP BY name, year
  ORDER BY count DESC, name ASC
  LIMIT 4 OFFSET 1;
 count   name   year
--- -- --
   3 joe2004 s,e,e
   2 bob2003 w,e
   2 kim2003 s,s
   2 sue2004 s,w
Select only places visited included in LIMITed query:

Is this :
SELECT DISTINCT place FROM a,(
SELECT count(*) AS count, name, year FROM a
   GROUP BY name, year
   ORDER BY count DESC, name ASC
   LIMIT 4 OFFSET 1
) as foo WHERE name=foo.name AND year=foo.year
	Problem with this approach is that you'll have to run the query twice,  
one to get the hitlist by user, one for the places...

SELECT DISTINCT place FROM a ;
 place
---
 south
 west
 east
Note that the place north does not appear in the last result
because north was only visited by bob in 2005 and kim in 2004,
records which are not included in the limited result.
Any help appreciated.
-Bob
---(end of broadcast)---
TIP 8: explain analyze is your friend

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] testing/predicting optimization using indexes

[PFC]

I'm quite happy with the speedup in 3, but puzzled over the slowdown in  
2.
Could you provide :
- SELECT count(*) FROM structure;
=> NRows
- SELECT avg(length(smiles)) FROM structure;
Then VACUUM FULL ANALYZE structure
Redo your timings and this time post EXPLAIN ANALYZE
Also your query returns 1313 rows, so wan you post :
EXPLAIN ANALYZE SELECT oe_matches(smiles,'c1c1CC(=O)NC') FROM  
structure;
=> time T1
EXPLAIN ANALYZE SELECT smiles FROM structure;
=> time T2

(T1-T2)/(NRows) will give you an estimate of the time spent in each  
oe_matches call.

	Also note that for postgres (a,b) > (c,d) means ((a>c) and (b>d)), which  
can be misleading, but I think that's what you wanted.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] testing/predicting optimization using indexes

[PFC]

Finally, I built the table with all the additional columns created
during the initial creation of the table.  The original speed was  
obtained!
	Quite strange !
	Did you vacuum full ? analyze ? Did you set a default value for the  
columns ? mmm maybe it's not the fact of adding the columns, but the  
fact of filling them with values, which screws up the vacuum if your fsm  
setting is too small ?
	Try vacuum verbose, good luck parsing the results ;)

The secondary issue was one of using an index on the additional columns.
This greatly speeds up the overall search, by limiting the number of
rows needing to use oe_matches.  I am currently working on optimizing the
number and nature of these extra columns.  However, my initial question
still remains.  Once I find a good set of columns to use as an index,
will I then get even greater speed by defining a new data type and an
index method equivalent to my multi-column index?
	You'll know that by counting the rows matched by the pre-filter (your  
columns), counting the rows actually matched, which will give you the  
number of calls to oe_match you saved, then look at the mean time for  
oe_match...

SELECT count(*) FROM structure
237597
SELECT avg(length(smiles)) FROM structure
37.6528912402092619
	Well, your rows have 26 bytes header + then about 45 bytes of TEXT, and 4  
bytes per integer column... I don't think the bytes spent in your columns  
are significant... They could have been if your smiles string had been  
shorter.

EXPLAIN ANALYZE SELECT oe_matches(smiles,'c1c1CC(=O)NC') FROM   
structure
Seq Scan on structure  (cost=0.00..7573.96 rows=237597 width=41) (actual  
time=17.443..15025.974 rows=237597 loops=1)
Total runtime: 16786.542 ms

EXPLAIN ANALYZE SELECT smiles FROM structure
Seq Scan on structure  (cost=0.00..6979.97 rows=237597 width=41) (actual  
time=0.067..735.884 rows=237597 loops=1)
Total runtime: 1200.661 ms

	OK so it takes 1.2 secs to actually read the data, and 16.8 secs to run  
oe_match... so a call is about 65 microseconds...  Note that this time  
could depend a lot on the smiles column and also on the query string !

	What you need now is to estimate the selectivity of your pre filtering  
columns, to be able to select the best possible columns : for various  
smiles queries, compute the row count which gets past the filter, and the  
row count that actually matches the oe_match. Ideally you want the first  
to be as close as possible to the second, but for your test query, as you  
return 0.5% of the table, even an inefficient pre-filter which would let  
10% of the rows through would yield a 10x speed improvement. You'd want to  
get below the 2-3% bar so that postgres will use an index scan, which will  
be even faster. Don't forget to do a sanity-check that all the rows that  
match your smiles query also match your columns filter !

	Also, using several columns (say a,b,c,d) is not optimal. Say a,b,c,d  
each contain integers between 0 and 10 with linear distribution ; then a  
query starting with 'a>=0' will automatically match more than 90% of the  
data and not use the index. You'll get a seq scan. So, either you can  
always get your first column very selective, or you'll have to use a gist  
index and integer arrays.

	If you get times that you like, then you're done ; else there may be  
another path for optimization, getting your hands dirty in the code, but  
not to the point of creating index types :

	You'll have noted that the 'c1c1CC(=O)NC' string gets reparsed for  
every processed row. You should benchmark how much time is lost in this  
parsing. You probably won't be able to do this with postgres (maybe  
matching 'c1c1CC(=O)NC' with an empty smiles string ?), so you may  
have to call the C++ functions directly.
	If this time is significant, you might want to create a datatype which  
will contain a compiled query string. You'll have to write a few C  
functions for that (dont ask me) but it should be a lot simpler than  
coding a new index type. Then you'd create a special version of oe_match  
which would take a precompiled query string. Depending on the time  
necessary to parse it, it may work.






---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] plpgsql select into with multiple target variables

[PFC]

Try removing the comma after varz
SELECT into varx, vary, varz,
   colx, coly, colz, FROM 
I've tried parens and various other things but no luck.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(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] MSSQL versus Postgres timing

[PFC]

CREATE OR REPLACE VIEW viwassoclist AS
 SELECT a.clientnum, a.associateid, a.associatenum, a.lastname,  
a.firstname,
jt.value AS jobtitle, l.name AS "location", l.locationid AS  
mainlocationid,
l.divisionid, l.regionid, l.districtid, (a.lastname::text || ', '::text)  
||
a.firstname::text AS assocname, a.isactive, a.isdeleted
   FROM tblassociate a
   LEFT JOIN tbljobtitle jt ON a.jobtitleid = jt.id AND  
jt.clientnum::text =
a.clientnum::text AND 1 = jt.presentationid
   JOIN tbllocation l ON a.locationid = l.locationid AND  
l.clientnum::text =
a.clientnum::text;

Try removing those ::text casts...
and creating two column indexes on the columns you use in your Join
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] PL/PgSQL - returning multiple columns ...

[PFC]

On Thu, 3 Feb 2005 12:48:11 -0400 (AST), Marc G. Fournier  
<[EMAIL PROTECTED]> wrote:

Perfect, worked like a charm ... but the RETURNS still needs to be a  
SETOF, other then that, I'm 'away to the races' ... thanks :)
No SETOF necessary :
CREATE TYPE mytype AS ( number INTEGER, blah TEXT );
CREATE OR REPLACE FUNCTION myfunc( INTEGER ) RETURNS mytype LANGUAGE  
plpgsql AS  $$ DECLARE _retval mytype; BEGIN _retval.number=$1;  
_retval.blah='yeah'; RETURN _retval; END;$$;

SELECT myfunc(22);
  myfunc
---
 (22,yeah)
(1 ligne)
SELECT * FROM myfunc(22);
 number | blah
+--
 22 | yeah
(1 ligne)
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Determining Rank

[PFC]

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
	Make your query a set returning function which iterates over the query  
results and returns a number as well ?

---(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] the best way to get the first record from each group

[PFC]

I don't really gr0k your field names so I'll use an easier example :
CREATE TABLE groups ( group_id SERIAL PRIMARY KEY, group_name TEXT NULL )  
WITHOUT OIDS;

CREATE TABLE people
 ( user_id SERIAL PRIMARY KEY,
group_id INTEGER NOT NULL REFERENCES groups(group_id),
score INTEGER NOT NULL )
WITHOUT OIDS;
CREATE INDEX people_scored ON people( group_id, score );
... put 1K rows into groups, vacuum analyze
... put 128K rows into people, vacuum analyze
So you want the user in each group with the highest score (or the lowest  
subno... thats the same).

0-- DISTINCT ON

SELECT DISTINCT ON (group_id) group_id, user_id, score FROM people ORDER  
BY group_id, score;
 Unique  (cost=0.00..4968.17 rows=996 width=12) (actual  
time=0.144..539.667 rows=1000 loops=1)
   ->  Index Scan using people_scored on people  (cost=0.00..4640.49  
rows=131072 width=12) (actual time=0.141..454.893 rows=131072 loops=1)
 Total runtime: 540.212 ms


	It works but is about the slowest thing imaginable : index-scanning (or  
sorting) the entire table.
	DISTINCT ON can be very convenient nonetheless !

seq scan => disqualified.
1-- min(), max()
	max() will give you the score but it won't give you the user_id so you  
have to resort to a trick just like you did.
	And it does a seq scan => disqualified.

2-- custom aggregate
	You could possibly write an aggregate which takes a row from people as an  
argument, or an ARRAY[score,user_id] and which acts like max and returns  
the ARRAY[score,user_id] with the highest score so you can have its  
user_id. As array comparison works as expected in pgsql, you could use  
max(), unfortunately for you, max() does not work on integer arrays (Why  
is that so ?) so this solution needs you to write a custom aggregate.

	Note that this would still need a seq scan, and chould be slower than the  
DISTINCT => disqualified.

2-- subquery
	The problem is that you'll need a list of your groups. If you don't have  
a table, you'll have to extract them from the table people with a (SELECT  
group_id FROM people GROUP BY group_id) which is a sequential scan. So  
I'll presume there is a groups table, which is why I put a 'REFERENCES  
groups(group_id)' in the table declaration above.

To get the best score in a group of id GID we write :

SELECT user_id FROM people WHERE group_id=5 ORDER BY group_id DESC, score  
DESC LIMIT 1;

Limit  (cost=0.00..3.69 rows=1 width=12) (actual time=0.054..0.055 rows=1  
loops=1)
   ->  Index Scan Backward using people_scored on people   
(cost=0.00..480.02 rows=130 width=12) (actual time=0.051..0.051 rows=1  
loops=1)
 Index Cond: (group_id = 5)
 Total runtime: 0.143 ms


To get the best scores for all groups we apply this SELECT to all 
groups.
You see now why we need a groups table to precalculate the groups.

SELECT g.group_id, (SELECT user_id FROM people WHERE group_id=g.group_id  
ORDER BY group_id DESC, score DESC LIMIT 1) as user_id FROM groups g;

 Seq Scan on groups g  (cost=0.00..3702.48 rows=1000 width=4) (actual  
time=0.079..18.942 rows=1000 loops=1)
   SubPlan
 ->  Limit  (cost=0.00..3.69 rows=1 width=12) (actual  
time=0.014..0.014 rows=1 loops=1000)
   ->  Index Scan Backward using people_scored on people   
(cost=0.00..486.75 rows=132 width=12) (actual time=0.011..0.011 rows=1  
loops=1000)
 Index Cond: (group_id = $0)
 Total runtime: 19.475 ms


	Note that the subselect here can only yield ONE column so another join  
comes in to get the score :

-- Take 1

SELECT * FROM people WHERE user_id IN (SELECT (SELECT user_id FROM people  
WHERE group_id=g.group_id ORDER BY group_id DESC, score DESC LIMIT 1) as  
user_id FROM groups g);

 Nested Loop  (cost=21.19..10418.45 rows=1000 width=12) (actual  
time=29.851..87.289 rows=1000 loops=1)
   ->  HashAggregate  (cost=17.50..3704.98 rows=1000 width=4) (actual  
time=29.789..32.174 rows=1000 loops=1)
 ->  Seq Scan on groups g  (cost=0.00..15.00 rows=1000 width=4)  
(actual time=0.119..27.982 rows=1000 loops=1)
   SubPlan
 ->  Limit  (cost=0.00..3.69 rows=1 width=12) (actual  
time=0.023..0.023 rows=1 loops=1000)
   ->  Index Scan Backward using people_scored on  
people  (cost=0.00..486.75 rows=132 width=12) (actual time=0.020..0.020  
rows=1 loops=1000)
 Index C

Re: [SQL] How to iterate through arrays?

[PFC]

- use TEXT instead of CHAR (what is CHAR without (n) ?)
- inintialize your array with '{}' because it is created as NULL if you  
just declare it without setting it to an empty array.

Hi there,
I'm trying to iterate through arrays in PL/PGSQL:

DECLARE
update_query CHAR;
update_query_params CHAR ARRAY[6];
BEGIN
update_query_params[1]:='some text';

RAISE NOTICE 'Testing element %', 
update_query_params[1];
END
.
It does not 'compile'... :-(. Can you tell me what is the problem?
Thanks,
Nosy
---(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 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] triggers

[PFC]

update trigger  working on same table???
	If an UPDATE trigger does an update on its own table, it can trigger  
itself and explode...

---(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] More efficient OR

[PFC]

	You sound like you don't like the performance you get with OR or IN, from  
this I deduce that you have a very large list of values to OR from. These  
make huge queries which are not necessarily very fast ; also they are  
un-preparable by their very nature (ie. the planner has to look at each  
value, ponder its stats, think about it...) Basically a query with, say,  
'column IN (100 values)' will make postgres work a lot more than a query  
with 'column in (SELECT something which yields 100 values)'.

I have tested the following with good results :
- Write a very simple set returning function which takes an array as a  
parameter and RETURN NEXT each array element in turn. It's just a FOR...  
RETURN NEXT. Say you call it array_flatten( INTEGER[] ) or something.

- Then, instead of doing SELECT * FROM table WHERE id IN (1,4,77,586,1025)
do:
SELECT * FROM table WHERE id IN (SELECT * FROM  
array_flatten( '{1,4,77,586,1025}' ) );
or :
SELECT t.* FROM table t, (SELECT * FROM array_flatten( '{1,4,77,586,1025}'  
) ) foo WHERE t.id=foo.id;

The first one will do a uniqu'ing on the array, the second one will not.
You can also LEFT JOIN against your SRF to get the id's of the rows that  
were not in the table (you cannot do this with IN)

And you can PREPARE the statement to something that will take an array as  
a parameter and won't have to be parsed everytime.

Sometimes it can be a big performance boost. Try it !
However, if some value in your array matches a lot of rows in the table,  
it will be slower than the seq scan which would have been triggered by the  
planner actually seeing that value in the IN list and acting on it. But if  
you KNOW your column is unique, there is no point in forcing the planner  
to ponder each value in your list !

For an additional performance boost (likely negligible), you could sort  
your array in the function (or even in your application code) to ease the  
work of the index scanner, which will get a better cache hit rate.

If you have, say, 20.000 values to get, this is the only way.
Note that you could ask yourself why you need to get a lot of values. Are  
you fetching stuff from the database, computing a list of rows to get,  
then SELECTing them ? Then maybe you put something in the application that  
should really be in the database ?

As a side note, it would be nice :
- if that set returning function was a fast C built-in (I think there's  
one in contrib/intagg but obviously it works only for integers) because  
it's a useful tool and building brick ; same for array_accum, and some  
other commonly used five-lines aggregates and functions that everybody  
recodes once.

- if postgresql used this kind of optimization for the SELECT * FROM table  
WHERE id =ANY( array ) which currently uses a seq scan.

However, what IS nice from pg is that you can actually do the array SRF  
trick and pull a lot of rows by id's, at blazing speed, just by writing a  
three line function and tweaking your wuery.


It sounds like IN will save some typing and code space but not decrease  
the
execution time.

BETWEEN won't work for my real life query because the limiting values are
quite disparate.
Kind Regards,
Keith
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
   http://www.postgresql.org/docs/faq

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Relation in tables

[PFC]

Hello all...
I am starting in Postgresql...
And I have a question:
I am developing a DB system to manage products, but the products may be
separated by departaments (with its respectives coluns)... Like:
CREATE TABLE products(
   id   serial  primary key,
   desc valchar(100),
   ...
);
Okay, but the products is typed by "amount departament" and this  
departament
should not have access to other coluns like "values, Money, etc...".
The "finances departament" may modify the data into products table, but  
this
departament should not have access to coluns like "amounts, etc...".

I' ve tried to create the products table with INHERITS but its not  
right...
look:
	Use a view per department, which show/hide the columns according to your  
liking. Give each department a schema and put everything related to it  
inside for cleanliness. Use UPDATE triggers on the views, which in fact  
write to the products table, so that the departments can only update the  
columns you like. You can even make some columns readable but not  
writeable, by raising an exception if a modification is attempted on that  
column.

	If you want to reuse your code between departments, you will want all the  
views to have the same columns, so make them return NULL for the fields  
that they cannot see.

	Finally don't forget to make the products table inaccessible the  
departments.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Matching a column against values in code

[PFC]

	This has been discussed a few hours ago on the mailing list on the  
subject '[SQL] More efficient OR'

On Fri, 11 Feb 2005 10:12:52 -0600, Tim <[EMAIL PROTECTED]> wrote:
Hello all.
I sometimes find myself needing an SQL query that will return all the  
rows of a table in which one column equals any one of a list of values I  
have in an array in code.

Does anyone know of a better way to do this than to loop through the  
array and append an "or" comparison to the sql statement, like this?

sqlString = sqlString + " or this_column='" + arrayOfValues[i] +"' ";
If someone knows a command or function I can look up in the docs, just  
say the name and I'll look there. Thanks a lot everyone.

--
Tim
---(end of broadcast)---
TIP 6: Have you searched our list archives?
   http://archives.postgresql.org

---(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] Order of columns in a table important in a stored procedure?

[PFC]

French verb "saisir" : here, to enter data in a system by typing it.
noun "saisie" : the action of doing so.
	It has other meanings :
	"Saisir" :
		- (commonly) to grab or get hold of something swiftly
		- (sometimes) to understand something
		- (lawspeak) that is also what the Oracle layers do to your house when  
they find out about your benchmark publications. More in the "grab" sense.

On Wed, 16 Feb 2005 15:51:00 -0500, Geoffrey <[EMAIL PROTECTED]> wrote:
Richard Gintz wrote:
Pardon me ya'll, but can you tell me what a "saisies" is?
More than one saisy???

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: UPDATE TRIGGER on view WAS: Re: [SQL] Relation in tables

[PFC]

And all this time I thought that you couldn't write to a view.
You can't.
But you can make it seem so you can.
	You can create an ON UPDATE/INSERT trigger on a view which intercepts the  
UPDATE/INSERT to the view (which would otherwise fail) and do whatever you  
want with it, including doing the operation on the real table.

Search for "postgresql materialized views" for some examples.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [SQL] Postgres performance

[PFC]

The reason PostgreSQL is slower is because it (and by extension the team
behind it) cares about your data.
	Sure, postgres is (a bit but not much) slower for a simple query like  
SELECT * FROM one table WHERE id=some number, and postgres is a lot slower  
for UPDATES (although I heard that it's faster than MySQL InnoDB)... but  
try a query with a join on few tables, even a simple one, and postgres  
will outperform mysql, sometimes by 2x, sometimes 1000 times. I had a case  
with a join between 4 tables, two of them having 50k records ; I was only  
pulling 6 records... mysql spent half a second and postgres 0.5 ms... hell  
!

---(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] Postgres performance

[PFC]

I don't require transaction because the query aren't
complex and update a single tuple (in SELECT
transactions are useless)
	You mean, you have no foreign keys in your database ?
	In SELECT they are definitely useful (think select for update, isolation  
level serializable...)

- start quote -
 You'll find inserts/updates with lots of users is
 where PostgreSQL works
 well compared to other systems.
- end quote -
Uhhmm.. this is interesting...
	pg does not lock the whole table everytime anyone wants to write in it.  
In MySQL when you run a big select, all write activity stops during that.  
If you run a big update, all activity other than this update has to wait.

- why postgres use a new process for every query ?
(mySQL, if I'm not wrong, use threads... I think its
faster)
Not for every query, for every CONNECTION.
You are using persistant connections are you. Are you ?
- why connection time is slower? (compared to mySQL)?
This is of no importance as everyone uses persistent connections anyway.
- why postgres require analyze? (mySQL, if I'm not
wrong, don't require it)
Yours answers will be very apreciated! Thx
	So it has a planner which knows what it's doing ;) instead of just  
guessing in the dark.

	And MySQL requires analyze too (read the docs), optimize table which  
looks like vacuum to me, and sometimes repair table...

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Simple delete takes hours

[PFC]

	Every time a row is removed from pwd_name, the ON DELETE CASCADE trigger  
will look in pwd_name_rev if there is a row to delete... Does it have an  
index on pwd_name_rev( rev_of ) ? If not you'll get a full table scan for  
every row deleted in pwd_name...


On Thu, 03 Mar 2005 22:44:58 +0100, Thomas Mueller  
<[EMAIL PROTECTED]> wrote:

Hi there,
I have a simple database:
CREATE TABLE pwd_description (
   id SERIALNOT NULL UNIQUE PRIMARY KEY,
   name varchar(50) NOT NULL
);
CREATE TABLE pwd_name (
   id SERIALNOT NULL UNIQUE PRIMARY KEY,
   description integer  NOT NULL REFERENCES pwd_description(id),
   name varchar(50) NOT NULL,
   added timestamp  DEFAULT now()
);
CREATE TABLE pwd_name_rev (
   id SERIALNOT NULL UNIQUE PRIMARY KEY,
   description integer  NOT NULL REFERENCES pwd_description(id),
   rev_of integer   NOT NULL REFERENCES pwd_name(id) ON DELETE  
CASCADE,
   name varchar(50) NOT NULL
);

The indexes shouldn't matter I think.
pwd_name_rev is filled by a stored procedure and a trigger (ON INSERT)  
when something is inserted to pwd_name. Both tables contain about  
4.500.000 emtries each.

I stopped 'delete from pwd_name where description=1' after about 8 hours  
(!). The query should delete about 500.000 records.
Then I tried 'delete from pwd_name_rev where description=1' - this took  
23 seconds (!).
Then I retried the delete on pwd_name but it's running for 6 hours now.

I use PostgreSQL 7.4.7 on Linux 2.6.10. The machine is a Celeron 2 GHz  
with 512 MB RAM.

PostgreSQL should do a full table scan I think, get all records with  
description=1 and remove them - I don't understand what's happening for  
 >8 hours.

Any help is appreciated.
Thomas
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
   http://www.postgresql.org/docs/faq

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Postgres performance

[PFC]

No, I haven't foreign keys in the older version, in
that new I've it... however I manage relations from
app code (PHP)...
	Really ?
	In my experience this is a sure way to get inconsistencies slowly  
creeping into your database, and you also get a load of funky concurrency  
issues.

doesn't MYSQL allow to use 'foreign
keys' in sure and fast way then?
	It does, IF you use the InnoDB engine... which is slower than postgres...  
and there are a lot of gotchas.>

Not for every query, for every CONNECTION.
You are using persistant connections are you. Are
you ?
I'm using PHP and every user (can be from 1 user to
100 users) must connect to the database... do you know
how I can use persistant connection? I think it's
impossible... I'm wrong?
	Well, first, I get a connection establishment time of about 20 ms in  
mysql and 60 ms in postgres. This information is useless as I use  
persistent connections, obviously, because it is crazy to spend 20 ms  
connecting just to make a 0.5 ms query.

	Now, in PHP, you can use mysql_pconnect instead of mysql_connect to get a  
persistent connection. mod_php keeps a pool of connections. The same thing  
probably applies for postgres, but as I don't use it with PHP (only with  
Python) I can't tell. Look in the docs for "persistent connections".

	This way, each Apache server process keeps a persistent connection open,  
and re-uses it for every page. You save the connection establishment time  
and load.

> - why connection time is slower? (compared to
mySQL)?
Because MySQL forks a thread whereas Postgres forks a process.
This is of no importance as everyone uses
persistent connections anyway.
See last answer...
I hope my explanations are useful.
And MySQL requires analyze too (read the docs),
optimize table which
looks like vacuum to me, and sometimes repair
table...
Ok... they are conceptually implemented in the same
mode...
Well, not really.
	For instance when you make joins, postgres will look the ANALYZE stats  
and say "Hm, this value seems rare, I'll use an index scan to get these  
few values" or "This column has few distinct values, I'll better load them  
all into a hash before joining to this big table instead of making a lot  
of index scans"... it can get a lot more complicated.

	MySQL thinks "I see indexed column, I don't know what a hash join is,  
thus I use index."

	Both try to estimate the size of result sets to choose plans, postgres  
generally does it well, mysql sometimes can do something which happens to  
work, most of the time it makes no diference.

	But using the MySQL analyze seems to speed up some of my queries, though.  
I don't think it has such detailed stats as postgres, though.

Point is, if the query gets complex, forget MySQL...




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Links between rows in a table

[PFC]

It would probably be better to always have either both or neither of
the symmetric relationships in the table. You could make a set of  
triggers
to enforce this.
	Because your relation is symmetric, you should not name them "user" and  
"friend".
	The duplication is useless if you add a constraint : see this

create table friendship (
user_id_1 integer   references ... on delete cascade,
user_id_2 integer references ... on delete cascade,
CHECK( user_id_1 < user_id_2 )
);
	user_id_1 < user_id_2 means :
	- a user can't be his own friend
	- only one row per friend
	- when you want to know if A is friend of B, no need to make two selects,  
just select where user_id_1 = min(user_id_A, user_id_B) AND user_id_2 =  
max(user_id_A, user_id_B)

	To get the list of friends for a user, you still need the union, but that  
is no real problem. Making two queries will be marginally slower than one  
query on a bigger table, but youu save precious cache space, so in the end  
it could be faster.

---(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] Postgres performance

[PFC]

Really ?
In my experience this is a sure way to get
inconsistencies slowly
creeping into your database, and you also get a load
of funky concurrency
issues.
Yes, you are rigth... my insert/update are very simple
and without problems and so I think to use 'foreign
key' coded to make faster/simpler the management and
don't overloading the db (and use exception code
management )... but I had a problem with pgSQL because
server was very busy and the same query was replicate
(because users refresh the page... :( ) so now I've
foreign keys...
	It's a typical case, program being stopped between insertion of parent  
and child row. Although in this case FK's is not the solution,  
transactions are.

What about it? (i think it's the same with mySQL...)
I don't know how users can connect to... 1, 10,
1000... I must create a pool with 1000 connections? is
this fine? if  connections aren't released I must
reset manually, it is dangerous... do you think?
	Just set the max number of connections for postgres a bit higher than the  
max number of apache processes in apache.conf.

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] Links between rows in a table

[PFC]

The trouble with this approach is that for some ways of using this data
you will need to worry about the ordering of of the values.
	Tradeoffs, always tradeoffs...
	It depends on the application. Note also that it eliminates duplicates ;  
moreover without such a condition, any relation A-B could have the rows  
[(A,B)] or [(B,A)] or [(A,B),(B,A)] which promises to cause headaches if  
you need to get rid of the duplicates...
	I used this scheme for an "also purchased products" thingy on a website,  
it works well. In this case the row must be unique because we have  
(A,B,count) which is the number of times products A and B have been  
purchased together, in this case having rows (B,A) and (A,B) separated  
wouldn't help in sorting by this count, which is in my case very fast  
thanks to a multicolumn index.

Note that you can't literally use 'min' and 'max' as above, as those  
functions
don't do that. You could use 'case' to do that.
	... yes, it was just a way of saying it. You can define functions that  
take integers as arguments (I wish these basic bricks were defined by  
default)...


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
   http://www.postgresql.org/docs/faq

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Parameterized views proposition

[PFC]

What about using PREPARE ?
---(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] group by before and after date

[PFC]

I have 2 tables 1 has a date field and component need by that date and  
the
other has all the upcoming orders.
I am trying to build a query that will give me the Date and ComponentNeed
and also how many components have been ordered before that date and how  
many
after.
PostGreSQL is telling me I need to group on DatePromisedBy. I have tried  
a
number of different possibilities which haven't worked and now I have run
into brain freeze. Any help would be appreciated.

	You could :
	SELECT ..., sum( stuff ), ..., (DatePromisedBy > a_particular_date) as  
after GROUP BY after

	You'll get two lines, one the sum of things before  a_particular_date,  
one of things after  a_particular_date. Look in the 'after' field to know  
which is which.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Newbie wonder...

[PFC]

	If you want to add a SERIAL field to an existing table, create a sequence  
and then create an integer field with default nextval(seq) and postgres  
will fill it automatically. The order in which it will fill it is not  
guaranteed though !

	However, you might also like to de-dupe your data once it's in the  
additional tables, thus you might need more complicated measures.


(2) How should I go to create a sequence for an existing table? For all
futures data entry, after this conversion, I want the unique ID for each
row to come from a sequence, but if I know how to create a table using
serial, I am not sure how to modify one for this.
Thanks,
Bernard
---(end of broadcast)---
TIP 6: Have you searched our list archives?
   http://archives.postgresql.org

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] select multiple immediate values, but in multiple rows

[PFC]

You need a function like this :
CREATE OR REPLACE FUNCTION array_srf( integer[] ) RETURNS SETOF integer  
LANGUAGE PLPGSQL  etc... AS
$$
DECLARE
_data   ALIAS FOR $1;
_i  INTEGER;
BEGIN
FOR _i IN 1..icount(_data) LOOP
RETURN NEXT _data[_i];
END LOOP;
RETURN;
END;
$$

select * from array_srf('{1,2,3,4}');
 array_srf
---
 1
 2
 3
 4
(4 lignes)
This will avoid you the UNION.

Hello all.  I'd like to write a query does a set subtraction A - B, but  
A is
is a set of constants that I need to provide in the query as immediate
values.  I thought of something like

select a from (1,2,3.4)
except
select col_name from table;
but I don't know the syntax to specify my set of constants.  I thought of
doing
 select 1 union select 2 union select 3 union ... except ...
but I figure there must be a better way.  The size of the set would be
anywhere between 1 and about 5 or 6 elements.  Is there a better way to  
do
this?

Thanks.
Luca
---(end of broadcast)---
TIP 8: explain analyze is your friend

---(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] Generic Function

[PFC]

Look in the plpgsql docs on EXECUTE.
But for something that simple, why don't you just generate a query ?

---(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] Query performance problem

[PFC]

Can anyone tell me why does the following code chokes ... literally -  
this
works almost invisbly under mysql - pg takes more than an hour even on a  
very
small 30 record database.
- You should really use 8.0
- How much time toes it takes without the INSERT/UPDATES ?
- Please post EXPLAIN ANALYZE of all the queries
- You could do all that with only two queries

(The table chartmaster is just a list of general ledger accounts  
accountcode
and accountdescription. PK = accountcode)

$ChartAccounts = DB_query('SELECT accountcode FROM chartmaster',$db);
While ($AccountRow = DB_fetch_array($ChartAccounts)){
for ($PeriodNo=$CreateFrom;$PeriodNo <= $CreateTo;$PeriodNo++) {
echo '' . _('Period Number') . ' ' . $PeriodNo . 
'';
// Check if there is an chart details record set up
$sql = 'SELECT count(*) FROM chartdetails
WHERE 
accountcode='.$AccountRow['accountcode'].'
AND period=' . $PeriodNo;
$InsChartDetails = DB_query($sql,$db,'','','',false);
$CountRows = DB_fetch_row($InsChartDetails);
$AccountExistsAlready = $CountRows[0];
DB_free_result($InsChartDetails);
if(! $AccountExistsAlready) {
$sql = 'INSERT INTO chartdetails (accountcode,
period)
VALUES (' . $AccountRow['accountcode'] 
. ',
' . $PeriodNo . ')';
$InsChartDetails = DB_query($sql,$db);
DB_free_result($InsChartDetails);
}
}
	/*Now run through each of the new chartdetail records created for each
account and update them with the B/Fwd and B/Fwd budget no updates would  
be
required where there were previously no chart details set up ie
FirstPeriodPostedTo > 0 */

for ($PeriodNo=$CreateFrom;$PeriodNo<=$CreateTo; $PeriodNo++) {
$sql = 'SELECT accountcode,
period,
actual + bfwd AS cfwd,
budget + bfwdbudget AS cfwdbudget
FROM chartdetails WHERE period =' . ($PeriodNo  
- 1);
$ChartDetailsCFwd = DB_query($sql,$db);
while ($myrow = DB_fetch_array($ChartDetailsCFwd)){
$sql = 'UPDATE chartdetails SET bfwd =' . 
$myrow['cfwd'] . ',
bfwdbudget =' . 
$myrow['cfwdbudget'] . '
WHERE accountcode = ' . 
$myrow['accountcode'] . '
AND period >=' . $PeriodNo;
$UpdChartDetails = DB_query($sql,$db, '', '', 
'', false);
DB_free_result($UpdChartDetails);
}
DB_free_result($ChartDetailsCFwd);
}
}
function DB_query ($SQL,
&$Conn,
$ErrorMessage='',
$DebugMessage= '',
$Transaction=false,
$TrapErrors=true){
global $debug;
	$result = pg_query($Conn, $SQL);
	if ($DebugMessage == '') {
		$DebugMessage = _('The SQL that failed was:');
	}
	//if (DB_error_no($Conn) != 0){
	if ( !$result AND $TrapErrors){
		prnMsg($ErrorMessage.'' . DB_error_msg($Conn),'error', _('DB  
ERROR:'));
		if ($debug==1){
			echo '' . $DebugMessage. "$SQL";
		}
		if ($Transaction){
			$SQL = 'rollback';
			$Result = DB_query($SQL,$Conn);
			if (DB_error_no($Conn) !=0){
prnMsg(''.  _('Error Rolling Back Transaction!!'), '', _('DB
DEBUG:') );
			}
		}
		if ($TrapErrors){
			include('includes/footer.inc');
			exit;
		}
	}
	return $result;

}
I am hoping that someone will be able to see an alternative simpler  
method or
suggest a method of indexing the pg tables to optmise the required  
queries. I
would appreciate any help here men.

Many thanks in advance 
--
Phil Daintree
webERP Project Admin
---

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [SQL] Query performance problem

[PFC]

although may be not relevant to your question, as i have noticed this
before with mysql 'sql', what is the point of having a NOT NULL field
that defaults to 0? the whole idea of a NOT NULL field is to have the
value filled in compulsorily and having a default of 0 or '' defeats
the purpose
	Well if you define your field as NOT NULL mysql will automatically set it  
to 0 if you store a NULL anyway, so you might as well specify it in your  
table definitions so it looks like you wanted it...

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [SQL] Query performance problem

[PFC]

DEFAULT applies to INSERTs, NOT NULL applies to UPDATEs too.
	In MySQL it applies to both (ie. if you UPDATE to an invalid value, it  
sets it to 'something').

	NOT NULL without default is useful when you want to be sure you'll never  
forget to put a value in that column, when there is no meaningful default.  
Also for foreign keys : what would be the default value of a foreign key ?

gnari

---(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

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] best way to swap two records (computer details)

[PFC]

My question is what's the best way to swap settings between the two
computer records and swap any software installed?  Ideally I'd like it
in the form of a function where I can pass the two p_id's and return a
boolean reflecting success (true) or fail (false).

I'd say something like that (generic table names) :
If you're confident :
UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE 'A' END) WHERE  
owner IN ('A','B')

If you're paranoid :
UPDATE stuff SET owner = (CASE IF owner='A' THEN 'B' ELSE IF owner='B'  
THEN 'A' ELSE owner END) WHERE owner IN ('A','B')

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] "Flattening" query result into columns

[PFC]

You could use the array_accum aggregate. See examples in the docs.
On Mon, 21 Mar 2005 22:57:13 +0100, Thomas Borg Salling <[EMAIL PROTECTED]>  
wrote:

I am looking for a way to "flatten" a query result, so that rows are
"transposed" into columns, just as asked here for oracle:
http://groups.google.dk/groups?hl=da

&lr=&client=firefox-a&rls=org.mozilla:en-US:official&selm=aad10be0.040129232
2.7b6c320b%40posting.google.com
Is there any way to do this with pgsql  ?
Thanks,
/Thomas.


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] can you change an FK constraint from NOT DEFERRABLE to DEFERRABLE

[PFC]

Checks the docs on ALTER TABLE ... ALTER CONSTRAINT ...
On Wed, 30 Mar 2005 11:07:32 +0200, <[EMAIL PROTECTED]> wrote:
Hello. Is it possible to change an FK constraint from NOT DEFERRABLE
(the default) to DEFERRABLE without dropping and re-creating it? One idea
that came up was to create a parallel set of constraints which perform
the same checks as the existing ones as DEFERRABLE (and then drop the
old set), but the objection there was that it'd lock the tables during
the initial check.
We're having a fairly serious deadlock issue and the thinking goes that
Tom's suggestion here
http://www.webservertalk.com/archive139-2004-8-364172.html
to defer FK checks until transaction commit would maybe help. Right now
we can't try this because all the FK checks where created with default
settings. We'd like to avoid taking the database down for recreating
foreign keys.
Regards,
Frank
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
   http://www.postgresql.org/docs/faq

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] New record position

[PFC]

Why it? I can't undestand why the new record location was change.  
Shouldn't it
apper at the LAST record???
What need I do??
Thank you.

	The SQL spec specifies that if you don't use ORDER BY, well, the records  
come out in any order they want. Actually it's the order they are on disk,  
which is more or less random as inserting new records will fill the space  
left by deleted ones, and vacuum full will compact them. If you want  
order, use ORDER BY. If you want to order them in the order they were  
inserted, order by a SERIAL PRIMARY KEY field...

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Query history file

[PFC]

~/.psql_history is everything you typed in psql
On Sat, 02 Apr 2005 01:42:05 +0200, Mauro Bertoli <[EMAIL PROTECTED]>  
wrote:

Hi,
 I've installed a Postgres 8.0.
There's a history file with all executed queries?
Thanks!
		
___
Nuovo Yahoo! Messenger: E' molto pià divertente: Audibles, Avatar,  
Webcam, Giochi, Rubricaâ Scaricalo ora!
http://it.messenger.yahoo.it

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] How to store directory like structures?

[PFC]

	On gentoo (at least on my box) it's installed by default in 8.0, I  
believe it was installed by default, too, on 7.4.X

On Sun, 03 Apr 2005 19:26:03 +0200, Axel Straschil <[EMAIL PROTECTED]>  
wrote:

Hello!
I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems
what's a problem with ltree ?
I think ltree would be exactly what I need, the Problem ist that I've
got absolutly no Idea how to get that thing into a running Pg 7.4 under
Gentoo and a Pg 7.3 under Fedora?
Is there a possibility to use ltree without using the original sources
from postgresql and keep using gentoo's portage and fedora's rpm-version
of postgres?
Thanks, AXEL.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] How to store directory like structures?

[PFC]

Use the ltree datatype !
It's made specifically for this purpose.
http://www.sai.msu.su/~megera/postgres/gist/
On Sun, 03 Apr 2005 12:13:48 +0200, Axel Straschil <[EMAIL PROTECTED]>  
wrote:

Hello!
I want to store some structure like:
CREATE TABLE node
(
nodeid  SERIAL PRIMARY KEY,
parent  INT REFERENCES node(nodeid)
ON UPDATE CASCADE ON DELETE CASCADE,
label   TEXT,
UNIQUE (parent, label),
...
data
...
);
The label is used to map a node to a directory like strukture, so i can
have a function directory_for(nodeid) which gives me
/root_label/parent_label/parent_label/my_label (root labels have NULL as  
parent)

The problem is the ammount of queries when i've got deep nodes, and I
often have to query if a node is "in path" of another node.
Is there a good solution to build directory-tree like datastruktures?
I found, http://www.sai.msu.su/~megera/postgres/gist/ltree/ which seems
to do what i want, but I've no idea (and probalbly no chance) to get
that running on my system ;-(
Thanks,
AXEL.
---(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

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] a very big table

[PFC]

I'd suggest modifying your query generator to make it smarter :
FROM
 	pubblicita
 	LEFT OUTER JOIN materiali ON   
(pubblicita.codice_materiale=materiali.codice_materiale)
 	LEFT OUTER JOIN inserzionisti ON
(pubblicita.codice_inserzionista=inserzionisti.codice_inserzionista)
(snip)
 WHERE
 	pubblicazioni.anno ILIKE '2003%'
 	AND  inserzionisti.sigla ILIKE 'starline%'
(snip)

	Here you don't need to LEFT JOIN, you can use a straight simple  
unconstrained join because the rows generated by the LEFT JOINs which have  
NULL in the right columns will be rejected by the WHERE clause anyway :

FROM
pubblicita, materiali, inserzionisti
(remainder of LEFT JOINs for table which have nothing in the WHERE)
(snip)
 WHERE
pubblicazioni.anno ILIKE '2003%'
AND  inserzionisti.sigla ILIKE 'starline%'
AND pubblicita.codice_materiale=materiali.codice_materiale
AND pubblicita.codice_inserzionista=inserzionisti.codice_inserzionista
(snip)
	Doing this, you leave more options for the planner to choose good plans,  
and also to generate less of the joins (ie for instance starting on  
publicazioni, taking only the rows with the date condition, and then  
joining them to the other tables).

	Now, other comments :
	ILIKE cant' ever use an index. If you must use LIKE, use lower(column)  
LIKE 'something%' and create a functional index on lower(column).
	WHY IS THE DATE STORED AS TEXT ?? You could use a DATE field and query  
"pubblicazioni.anno BETWEEN '2003-01-01' AND '2003-12-31'" or any other  
date range. Always use the appropriate datatype. BETWEEN uses indexes.

On Tue, 29 Mar 2005 18:25:55 +0200, _moray <[EMAIL PROTECTED]> wrote:
hullo all,
I have a problem with a table containing a lot of data.
referred tables "inserzionista" and "pubblicazioni" (referenced 2 times)  
have resp. 1909 tuples and 8300 tuples, while this one 54942.

now the problem is that it is slow, also a simple "select * from  
pubblicita". (it takes 5-6 seconds on my [EMAIL PROTECTED],6Ghz laptop...)

I tried using some indexes, but the main problem is that I am using a  
php script to access the data that builds the query according to user  
input.

f.i. I made a simple interface where a user can specify multiple filters  
on almost all the columns of the table and a resulting query could be:

===
SELECT
	ripete.numero as ripete_numero,
	pubblicita.soggetto,pubblicita.colore,
	pubblicazioni.anno,pubblicazioni.numero,
	pubblicita.codice_pubblicita,pubblicita.annullata,
	pubblicita.codice_pagina,pubblicita.codice_materiale,
	pubblicita.note,pubblicita.prezzo,
	testate.testata AS testata,
	inserzionisti.sigla AS inserzionista,
	materiali.descrizione AS materiale,
	pagine.descrizione AS pagina
FROM
	pubblicita
	LEFT OUTER JOIN materiali ON  
(pubblicita.codice_materiale=materiali.codice_materiale)
	LEFT OUTER JOIN pagine ON  
(pubblicita.codice_pagina=pagine.codice_pagina)
	LEFT OUTER JOIN inserzionisti ON  
(pubblicita.codice_inserzionista=inserzionisti.codice_inserzionista)
	LEFT OUTER JOIN pubblicazioni ON  
(pubblicita.codice_pubblicazione=pubblicazioni.codice_pubblicazione)
	LEFT OUTER JOIN testate ON  
(pubblicazioni.codice_testata=testate.codice_testata)
	LEFT OUTER JOIN pubblicazioni ripete ON  
(pubblicita.ripete_da=ripete.codice_pubblicazione)
WHERE
	pubblicazioni.anno ILIKE '2003%'
	AND  inserzionisti.sigla ILIKE 'starline%'
	ORDER BY testate.testata ASC LIMIT 15 OFFSET 0
===

As you can see it is a quite heavy query...but also with simple queries:
===
cioe2=# explain SELECT * from pubblicita;
 QUERY PLAN
---
  Seq Scan on pubblicita  (cost=0.00..2863.42 rows=54942 width=325)
(1 row)
cioe2=# explain SELECT * from pubblicita where soggetto ilike 'a%';
 QUERY PLAN
---
  Seq Scan on pubblicita  (cost=0.00..3000.78 rows=54942 width=325)
Filter: (soggetto ~~* 'a%'::text)
(2 rows)
===
suggestions on how to make things smoother?
(the table is below)
thnx
Ciro.
===
create table pubblicita (
codice_pubblicita   bigserial,
codice_inserzionistaint NOT NULL,
codice_pagina   varchar(2),
codice_materialevarchar(2),
codice_pubblicazionebigint  NOT NULL,

data_registrazione  timestamp,

ripete_da   bigint,
soggettotext,
inserto text,

prezzo  numeric,
ns_fattura  int,
ns_fattura_data date,
vs_fattura  int,
vs_fattura_data date,

colore  bool,
data_prenotazione   date,
data_arrivo date,
data_consegna   date,

Re: [SQL] How to store directory like structures?

[PFC]

you need to load ltree into your database !
psql yourdb < ltree.sql
use 'locate ltree.sql' to find if ltree is installed
	Yeah, I remember now having to do that, but the binary module definitely  
was here without having to do anything besides "emerge postgresql" :

[EMAIL PROTECTED] peufeu $ locate ltree.so
/usr/lib/postgresql/ltree.so
[EMAIL PROTECTED] peufeu $ locate ltree.sql
/usr/share/postgresql/contrib/ltree.sql
So just :
psql yourdb < /usr/share/postgresql/contrib/ltree.sql
	And you should be OK.
	Really this module is amazing. You can put a trigger on the table so  
that, for instance, the path is created automatically from the element  
name and the parent_id, that kind of things. I remember search was also  
really fast and you can use complex queries without CONNECT BY...

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [SQL] getting count for a specific querry

[PFC]

Since it is a count of matched condition records I may not have a way
around.
	What you could do is cache the search results (just caching the id's of  
the rows to display is enough and uses little space) in a cache table,  
numbering them with your sort order using a temporary sequence, so that  
you can :
	SELECT ... FROM cache WHERE row_position BETWEEN page_no*per_page AND  
(page_no+1)*per_page-1
	to get the count :
	SELECT row_position FROM CACHE ORDER BY row_position DESC LIMIT 1

	Add a session_id referencing your sessions table with an ON DELETE  
CASCADE and the cache will be auto-purged when sessions expire.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] getting count for a specific querry

[PFC]

Please run this disk throughput test on your system :
http://boutiquenumerique.com/pf/multi_io.py
	It just spawns N threads which will write a lot of data simultaneously to  
the disk, then measures the total time. Same for read. Modify the  
parameters in the source... it's set to generate 10G of files in the  
current directory and re-read them, all with 8 threads.

How much I/O do you get ?
Also  hdparm -t /dev/hd? would be interesting.
On Fri, 08 Apr 2005 21:51:02 +0200, Joel Fradkin <[EMAIL PROTECTED]>  
wrote:

I will also look at doing it the way you describe, they do have wide
liberty. Thanks so much for the ideas. Sorry I did not do a perusal of  
the
archives first (I normally try that, but think I am brain dead today).

Joel Fradkin
Wazagua, Inc.
2520 Trailmate Dr
Sarasota, Florida 34243
Tel.  941-753-7111 ext 305
[EMAIL PROTECTED]
www.wazagua.com
Powered by Wazagua
Providing you with the latest Web-based technology & advanced tools.
C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc
 This email message is for the use of the intended recipient(s) and may
contain confidential and privileged information.  Any unauthorized  
review,
use, disclosure or distribution is prohibited.  If you are not the  
intended
recipient, please contact the sender by reply email and delete and  
destroy
all copies of the original message, including attachments.

-Original Message-
From: [EMAIL PROTECTED]  
[mailto:[EMAIL PROTECTED]
On Behalf Of Mischa Sandberg
Sent: Friday, April 08, 2005 2:40 PM
To: Scott Marlowe
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] getting count for a specific querry

Quoting Scott Marlowe <[EMAIL PROTECTED]>:
On Fri, 2005-04-08 at 12:08, Joel Fradkin wrote:
> I might have to add a button to do the count on command so they don't
get
> the hit.
> I would want it to return the count of the condition, not the  
currently
> displayed number of rows.

Judging postgresql on one single data point (count(*) performance) is
quite unfair.  Unless your system only operates on static data and is
used to mostly do things like counting, in which case, why are you using
a database?
For the general discussion of slowness of count(*),
and given no entry on the subject in
   http://www.postgresql.org/docs/faqs.FAQ.html
... I guess everyone has to be pointed at:
 http://archives.postgresql.org/pgsql-hackers/2005-01/msg00247.php
However, the gist of this person's problem is that an adhoc query,
NOT just a 'select count(*) from table', can take remarkably long.
Again, the problem is that PG can't just scan an index.
--
One workaround for this is to use EXPLAIN.
THIS APPLIES TO CASES WHERE THE USER HAS WIDE LIBERTY IN QUERIES.
It's pointless overhead, otherwise.
default_statistics_target is cranked up to 200 on all such tables,
and pg_autovacuum is running. (If there were anything to improve,
it would be refining the thresholds on this).
If the "(cost...rows=" string returns a number higher than the
QUERY row limit, the user is derailed ("That's not specific enough to  
answer
immediately; do you want an emailed report?").

Otherwise, it runs EXPLAIN ANALYZE, which is still faster than the query
itself.
If the "(actual...rows=...)" is higher than the RESULT row limit  
(PAGE
limit).

It then runs the query, with the PAGE rows offset and limit --- and  
happily,
practically everything that query needs is now in shared_buffers.
The count from the EXPLAIN analyze is displayed in the web page.


---(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] Getting the output of a function used in a where clause

[PFC]

try:
SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes  
where distance <= $dist;â;

OR you could use a gist index with a geometric datatype to get it a lot  
faster.

On Sat, 09 Apr 2005 03:43:39 +0200, Bill Lawrence <[EMAIL PROTECTED]>  
wrote:

HI,
Iâm a newbie so please bear with me. I have a function defined (got it  
from
one of your threadsâ thanks Joe Conway) which calculates the distance
between 2 zip code centeroids (in lat,long). This thing works great.
However, I want to sort my results by distance without incurring the
additional burden of executing the function twice. A simplified version  
of
my current SQL (written in a perl cgi)  that returns a set of zip codes
within a given radius is:

What I want to write is something like:
$sql = âSELECT zipcode, distance from zipcodes where distance <= $dist  
order
by distance;â;

But I donât the magic SQL phrase to populate the distance variable using  
my
nifty function. Do I need to create an output type for distance?

Thanks in advance!
Bill


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Very low performance on table with only 298 rows

[PFC]

nbeweb=> EXPLAIN ANALYZE select count(*) from onp_web_index;
 Total runtime: 179748.993 ms
WOW.
It's possible your table is bloated.
What version of PG are you using ?
VACUUM FULL ANALYZE VERBOSE onp_web_index
if it solves your problem, good, else post the results from that 
command.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Getting the output of a function used in a where clause

[PFC]

Thanks Tom and Rod.
There are indeed several additional conditions on the "real" query which
prune the search space (I formulate a quick search box and filter on
Lat/Lon's within the box). Since my user interface limits the search to  
a 30
mile radius, there are at most 81 results (in New York city, far fewer,  
for
Why don't you use a GiST index which will index this bounding box 
search ?
---(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] interesting SQL puzzle - concatenating column with itself.

[PFC]

BTW the concatenation function you suggest works nicely except that as  
you noted, it concatenates in an unpredictable order, so I'm now trying  
to solve that problem.
memo_id | sequence | memo_text
---
666 | 1| The quick
666 | 2| red fox
666 | 3| jumped over
666 | 4| the lazy brown dog
You have :
SELECT your_concat( memo_text ) FROM table GROUP BY memo_id
You can use :
SELECT your_concat( memo_text ) FROM
(SELECT memo_id, sequence, memo_text FROM table ORDER BY memo_id, sequence  
OFFSET 0) AS foo
GROUP BY memo_id

the OFFSET 0 may be necessary (or not). Try it !
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [SQL] Significance of Database Encoding

[PFC]

+--+
| 私はガラス
+--+
	You say it displays correctly in xterm (ie. you didn't see these in your  
xterm).
	There are HTML/XML unicode character entities, probably generated by your  
mailer from your Unicode cut'n'paste.
	Using SQL ASCII to store UTF8 encoded data will work, but postgres won't  
know that it's manipulating multibyte characters, so for instance the  
length of a string will be its Byte length instead of correctly counting  
the characters, collation rules will be funky, etc. And substring() may  
well cut in the middle of an UTF8 multibyte char which will then screw  
your application side processing...
	Apart from that, it'll work ;)

---(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] Significance of Database Encoding

[PFC]

$ iconv -f US-ASCII -t UTF-8  < test.sql > out.sql
iconv: illegal input sequence at position 114500
Any ideas how the job can be accomplised reliably.
Also my database may contain data in multiple encodings
like WINDOWS-1251 and WINDOWS-1256 in various places
as data has been inserted by different peoples using
different sources and client software.
You could use a simple program like that (in Python):
output = open( "unidump", "w" )
for line in open( "your dump" ):
for encoding in "utf-8", "iso-8859-15", "whatever":
try:
output.write( unicode( line, encoding ).encode( "utf-8" 
))
break
except UnicodeError:
pass
else:
print "No suitable encoding for line..."
	I'd say this might work, if UTF-8 cannot absorb an apostrophe inside a  
multibit character. Can it ?

	Or you could do that to all your table using SELECTs but it's going to be  
painful...

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] choosing index to use

[PFC]

Try indexing on client, time instead of time, client...
putting the equality condition on the first column of the index can make  
it faster.
Else, analyze, increase your stats, etc...

On Mon, 16 May 2005 13:39:40 +0200, Ilya A. Kovalenko <[EMAIL PROTECTED]>  
wrote:

Greetings,
  How can I control which indexes will or won't be used by query ?
  I never think, that I'll need something like this.
Short version:
  Simple SELECT query perfomance speeds up (10x-20x) after _removing_
one of indexes. Because (as EXPLAIN shows), after removing, query
switches to another index.
  How to make such without index removing ?
  PostgreSQL 8.0.2, OpenBSD 3.7, i386
Thank you.
Ilya A. Kovalenko
Special EQ SW section
JSC Oganer-Service
Details:
CREATE TABLE traffic_stat
(
  time  timestamptz NOT NULL,
  clientinet NOT NULL,
  remoteinet NOT NULL,
  count_in  int8 NOT NULL,
  count_out int8 NOT NULL
) WITHOUT OIDS;
CREATE INDEX traffic_client_idx
  ON traffic_stat
  USING btree
  (client);
CREATE INDEX traffic_date_idx
  ON traffic_stat
  USING btree
  ("time");
CREATE INDEX traffic_remote_idx
  ON traffic_stat
  USING btree
  (remote);
CREATE INDEX traffic_multy_idx
  ON traffic_stat
  USING btree
  ("time", client, remote);
CREATE INDEX traffic_date_client_idx
  ON traffic_stat
  USING btree
  ("time", client);
SELECT count(*) FROM traffic_stat;
135511
Query is:
SELECT to_char(time, 'DD.MM. HH24:MI.SS'), remote, count_in,  
count_out
  FROM traffic_stat WHERE client = '192.168.xxx.xxx' AND
  time > '2005-05-16' AND time < '2005-05-16'::date + '1 days'::interval
  ORDER BY time;

Case 1:
SELECT ...
Total query runtime: 2643 ms.
Data retrieval runtime: 20 ms.
39 rows retrieved.
EXPLAIN SELECT ...
Index Scan using traffic_date_idx on traffic_stat  (cost=0.00..3.08  
rows=1 width=35)
  Index Cond: (("time" > '2005-05-16 00:00:00+08'::timestamp with time  
zone) AND ("time" < '2005-05-17 00:00:00'::timestamp without time zone))
  Filter: (client = '192.168.114.31'::inet)

Case 2:
DROP INDEX traffic_date_idx;
SELECT ...
Total query runtime: 290 ms.
Data retrieval runtime: 20 ms.
41 rows retrieved.
EXPLAIN SELECT ...
Index Scan using traffic_date_client_idx on traffic_stat   
(cost=0.00..4.37 rows=1 width=35)
  Index Cond: (("time" > '2005-05-16 00:00:00+08'::timestamp with time  
zone) AND ("time" < '2005-05-17 00:00:00'::timestamp without time zone)  
AND (client = '192.168.114.31'::inet))


---(end of broadcast)---
TIP 8: explain analyze is your friend

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] ORDER BY handling mixed integer and varchar values

[PFC]

Is there any elegent query you folks can think of that combines the
two so I can one query that has alpha sorting on alpha categories and
numeric sorting on numeric values that are in the same column??
solution 1 (fast)
make a separate column which contains the integer value (updated via a  
trigger) or NULL if it's a textual value, then sort on it

solution 2
order by the string padded to a fixed length by adding spaces to the left :
(here an underscore is a space):
1
___10
_ABCD
I think there's a LPAD function (look in the docs) to do that... you can  
use only the first N (like 10) chars of the string...

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] sub-selects

[PFC]

SELECT foo.*,  npoints( foo.g )
FROM
(SELECT a, (select b from c where d = e limit 1) AS g FROM f WHERE  
isValid( g ))
AS foo

?
---(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] interesting SQL puzzle - concatenating column with itself.

[PFC]

I'm just curious - what's the 'OFFSET 0' for?
	Trick to fool postgres into thinking it can't rewrite out your subquery  
and eliminate it ...

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Does Postgresql have a similar pseudo-column "ROWNUM" as

[PFC]

your subsequent selects are
select ... from tab WHERE skey>skey_last
   OR (skey=skey_last AND pkey>pkey_last)
ORDER BY skey,pkey
LIMIT 100 OFFSET 100;
	why offset ?
	you should be able to use the skey, pkey values of the last row on the  
page to show the next page, no need for offset then.

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Changed to: how to solve the get next 100 records problem

[PFC]

The only strange thing is that without the 3rd order by, the order is  
wrong. I didn't expect it because each select is created ordered. Is it  
expected that UNION mixes it all up? (using postgre 7.4.1)
	That's because UNION removes duplicates, which it will probably doing  
using a hash (EXPLAIN ANALYZE is your friend).
	Use UNION ALL because your WHERE condition allows no duplicates anyway.  
UNION ALL preserves the order.
	So you can get the LIMIT out of the subqueries and put it around the  
UNION ALL.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [SQL] Turning column into *sorted* array?

[PFC]

SELECT array_accum(x) FROM (SELECT * FROM some_view ORDER BY x) AS tmp;
	If you're using integers, you could use the int_array_accum or something  
from the intarray module which is a lot faster.
	I believe intarray also has a function for sorting integer arrays...

BTW, the best alternative (in terms of execution performance) that comes
into my mind is to create an aggregate that does the sorting right away
while the values "come in" from the rows.  But that'd probably take me
some time to get right.

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Duplicated records

[PFC]

How can I delete the duplicated records with "DELETE FROM TABLE WHERE..."
clause??
The problem is becouse I have imported data from Dbase (dbf) file, and  
this
function have not built the Constraint (unique, primary key, ...), and  
this

function is usually executed.


	If you have no primary key how can you reference a record in order to  
delete it ?

I'd say use a temporary table...
If you have complete row dupes (ie. the entire row is duplicated) use
SELECT * FROM table GROUP BY *
(or select distinct)

	If only the primary key is duplicated but other fields change, then you  
have to decide which one you wanna keep !


---(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] Sum() rows

[PFC]

The simplest would be to create a stored procedure like this :

declare  row as TB1%rowtype, and ret as (id integer, value numeric, subtot  
numeric) then :


ret.subtot = 0

FOR row IN SELECT * FROM TB1 ORDER BY id DO
ret.id = row.id
ret.value = row.value
ret.subtot = ret.subtot + row.value
RETURN NEXT ret
END

etc...

SQL doesn't really work well for this kind of things whereas plpgsql works  
really well and it's plenty fast too.




CREATE TABLE TB1 (id integer primary key, value numeric);
insert into tb1 values (1,20);
insert into tb1 values (2,2);
insert into tb1 values (3,3);
insert into tb1 values (4,17);
insert into tb1 values (5,-0.5);
insert into tb1 values (6,3);

I want a query that returns:
-id- | --- value --- | --- subtot ---
   1 |20.00  | 20.00
   2 | 2.00  | 22.00
   3 | 3.00  | 25.00
   4 |17.00  | 42.00
   5 |-0.50  | 41.50
   6 | 3.00  | 44.50

The subtot colum will be the "prev. subtot colum"+"value colum". :-/
I dont know how to make the "subtot" colum, I tried to use the sum()  
function

but it not works correctly.
Any idea???

Thanks.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq





---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] getting details about integrity constraint violation

[PFC]

The "error fields" facility in the FE/BE protocol could be extended
in that direction, and I think there's already been some discussion
about it; but no one has stepped up with a concrete proposal, much
less volunteered to do the work ...


	Um, if changing the protocol is a bother, you could also add parseable  
infos to the error messages...


instead of  :
"ERROR: duplicate key violates unique constraint "testinteg_one_key""

it would say
"ERROR: duplicate key violates unique constraint "testinteg_one_key"
[code:"" error:"integrity" type:"unique" column:"something"  
constraint:"testinteg_one_key"]"


	Which could be hackfully added by a "parseable" locale (but with a  more  
restrained form...)

SET lc_messages TO parseable



regards, tom lane

---(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





---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] SELECT very slow

[PFC]

The problem is, that a SELECT * FROM foobar; takes ages (roughly 3  
minutes) to return the first row. I played around with the fetchSize()  
to disable the result set caching in the Java program first (before I  
tried psql) but that did not change anything.


Hello,

Yours seemed strange so I tried this :
	Created a table with 128K lines, 4 TEXT columns containing about 70 chars  
each...


-
\d bigtest;
 Colonne |  Type   |  Modificateurs
-+-+-
 id  | integer | not null default  
nextval('public.bigtest_id_seq'::text)

 data1   | text|
 data2   | text|
 data3   | text|
 data4   | text|
Index :
«bigtest_pkey» PRIMARY KEY, btree (id)

-

SELECT count(*) from bigtest;
 count

 131072

-

explain analyze select * from bigtest;
QUERY PLAN
--
 Seq Scan on bigtest  (cost=0.00..7001.72 rows=131072 width=308) (actual  
time=0.035..484.249 rows=131072 loops=1)

 Total runtime: 875.095 ms

So grabbing the data takes 0.875 seconds.

-

SELECT avg(length(data1)),  
avg(length(data2)),avg(length(data3)),avg(length(data3)) from bigtest;
 avg | avg | avg |  
avg

-+-+-+-
 72.1629180908203125 | 72.2342376708984375 | 72.3680572509765625 |  
72.3680572509765625


Here you see the average data sizes.

-

	Now I fire up python, do a SELECT * from the table and retrieve all the  
data as native objects... Hm, it takes about 1.3 seconds... on my  
Pentium-M 1600 laptop...


	I was about to suggest you use a less slow and bloated language than  
Java, but then on my machine psql takes about 5 seconds to display the  
results, so it looks like it ain't Java. psql is slow because it has to  
format the result and compute the column widths.


	Don't you have a problem somewhere ? Are you sure it's not swapping ? did  
you check memory ? Are you transferring all this data over the network ?  
Might an obscure cabling problem have reverted your connection to 10 Mbps ?


I'm using pg 8.0.something on Linux.

	Ouch. I saw you're on Windows so I tried it on the windows machine there  
which has a postgres installed, over a 100Mbps network, querying from my  
linux laptop. The windows machine is a piece of crap, Pentium-II 300 and  
256 MB Ram, it takes 7 seconds to retrieve the whole table in a python  
native object.


So...












---(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] SELECT very slow

[PFC]

It's not the program or Java. The same program takes about 20 seconds  
with Firebird and the exactly same data.


	Hm, that's still very slow (it should do it in a couple seconds like my  
PC does... maybe the problem is common to postgres and firebird ?)


	Try eliminating disk IO by writing a set returning function which returns  
100 rows, something simple like just a sequence number and a text  
value... if this is slow too... i don't know... do you have an antivirus  
or zonealarm or something ?


Have you tried connecting from another machine ?



Thomas



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org





---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] SELECT very slow

[PFC]

If autocommit is on (or fetch size is zero) then the driver will build  
the whole

result set before returning to the caller.


	Sure, but that is not your problem : even building the whole result set  
should not take longer than a few seconds (I gave you test timings in a  
previous message).

So... what ?
What does the taskman say ? CPU at 100% ? how much kernel time ?



http://jdbc.postgresql.org/documentation/80/query.html#query-with-cursor

Thomas


---(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 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] Dynamic PL/pgSQL

[PFC]

Within a PL/pgSQL function this would be easy, but I need to store the
complete initialization script in a text file and execute it as a whole.


In your scritp, put a CREATE FUNCTION and then call it and drop it ;)

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] people who buy A, also buy C, D, E

[PFC]

The goal of my query is: given a book, what did other people who
bought this book also buy?  I plan the list the 5 most popular such
books.


You can use the table listing ordered products directly, for example :

table ordered_products: order_id,  product_id,  quantity

SELECT b.product_id, sum(quantity) as rank FROM ordered_products a,  
ordered_products b WHERE a.product_id=(the product id) AND  
b.order_id=a.order_id AND b.product_id != a.product_id GROUP BY  
b.product_id ORDER BY rank DESC LIMIT 6;


This will need indexes on order_id and product_id that you probably  
already have.

It will also be slow.

You can also have a cache table :

cache   prod_id_a, prod_id_b, quantity
With a constraint that prod_id_a < prod_id_b

You add a trigger on insert, update or delete to ordered_products to  
insert or update rows in this table, modifying the quantity according to  
the purchase.


To select you do :

SELECT * FROM
(
(SELECT prod_id_b as pid, quantity FROM cache WHERE prod_id_a=(your id)  
ORDER BY prod_id_a DESC, quantity DESC LIMIT 5)

UNION ALL
(SELECT prod_id_a as pid, quantity FROM cache WHERE prod_id_b=(your id)  
ORDER BY prod_id_b DESC, quantity DESC LIMIT 5)

) as foo
ORDER BY quantity DESC
LIMIT 5;

It will be probably very fast but the table will grow huge and need  
various indexes :

(prod_id_a, quantity)
(prod_id_b quantity)
(prod_id_a, prod_id_b)  (the primary key)

You'll get 1/2 * N * (N-1) rows, N being the number of products on your  
site. If you remove the constraint  prod_id_a < prod_id_b

 you'll get N^2 rows which is worse.

Another solution :

Table cache : product_id integer, also_purchased integer[]

After every order, update also_purchased with the results of the query  
using the self join on ordered_products tables above.
This query should not be fast enough to use in a product webpage but it  
shouldn't be slow enough to be used like thi, only when orders are made.


To get the "also purchased products" all you have to do is read a line in  
this table.














---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[SQL] assorted problems with intarray and other GiST contribs.

[PFC]

Hello !

I'm using postgresql 8.0.1-r2 on gentoo linux.
So, here are the problems :


* int_array_aggregate crashes

SELECT int_array_aggregate(id) FROM (SELECT id FROM shop.products LIMIT X)  
as foo;


This one works fine if X <= 512 and crashes postgres if X > 512.
ie. if the aggregate accumulates more than 512 values it crashes.

On another example query :

SELECT int_array_aggregate(product_id),
int_array_aggregate(category_id)
FROM (SELECT * FROM shop.products_to_categories LIMIT N) as foo;

OK if N <= 8, crashes if N > 9


* integer[] intersection is slow

int[] & int[]   - returns intersection of arrays

This is a useful function but it is very slow.
Computing the intersection of two int[] of length 100 takes about 40 ms.
	in Python for instance, computing a similar intersection takes about 0.1  
millisecond, including building both arrays, converting them to sets, and  
computing the set intersection.

Maybe some information can be extracted from the Python souce code.


* ltree

	First of all congratulations for this extremely useful datatype and  
assorted indexes, it really rocks and makes SQL tree handling from  
nightmare to fun.


It would be nice to have functions to :
- know the length of a ltree (ie. number of elements)
- access it like an array (ie. get element N).
	This is to be able to iterate over the elements and fetch each of them to  
get the list of rows which make up a path upto a given object in my tree.


	Also a function returning the comon prefix between two ltrees would be  
really useful.



Thanks and sorry to come bother you !
Regards,
PF Caillaud



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] assorted problems with intarray and other GiST contribs.

[PFC]

Thanks for the quick response !


* int_array_aggregate crashes


I don't remember this function :)


You mean it's not part of intarray contrib ?
I thought so !
Sorry !
Then who's responsible for maintenance of it ?


int4 nlevel(ltree) - returns level of the node.


I had missed this one.
The summer heat has burnt by brain it seems ;)



- access it like an array (ie. get element N).


subltree, subpath


I know, but I was hoping for something like :
myltree[pos]

instead of the usable, but a bit huge, and probably slower :
ltree2text(subltree(myltree, pos-1,pos ));

	Also a function returning the comon prefix between two ltrees would be  
really useful.


 ltree lca(ltree,ltree,...) (up to 8 arguments)
 ltree lca(ltree[])
 Returns Lowest Common Ancestor (lca)


I know, too, but :

test=> SELECT lca( '1.2.3'::ltree, '1.2.4'::ltree );
 lca
-
 1.2
(1 ligne)

test=> SELECT lca( '1.2.3'::ltree, '1.2'::ltree );
 lca
-
 1
(1 ligne)

	In the case of the 'longest common prefix' I'd need the second SELECT to  
return the same as the first. This is to determine a 'parent' path that  
includes a bunch of other paths, in that case, if the parent is part of  
the original set, I get the parent's parent instead of what I want.



Have you read README.ltree ?


Yes ! But I had missed nlevel() to my shame.

thanks !


---(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] Unique primary index?

[PFC]

index is... an index !

UNIQUE is an index which won't allow duplicate values (except for NULLS)

PRIMARY KEY is exactly like UNIQUE NOT NULL, with the bonus that the  
database knows this column is the primary key so you can use stuff like  
NATURAL JOIN without telling which column you want to use.






What are the major differences between Unique, primary index & just
plain index?

When creating tables I nominate one of these, but not sure what the
difference is?


---(end of broadcast)---
TIP 8: explain analyze is your friend





---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] ENUM like data type

[PFC]

Here is where I get uncertain as to if this is possible. My idea is to
create a pseudo type that triggers the creation of it's lookup tables
the same way the SERIAL type triggers creation of a sequence and returns
an int with the right default value.


	Personnally I use one table which has columns (domain, name) and which  
stores all enum values for all different enums.
	I have then CHECK( is_in_domain( column, 'domain_name' )) which is a  
simple function which checks existence of the value in this domain (SELECT  
1 FROM domains WHERE domain=$2 AND name=$1 LIMIT 1) for instance.

You can also use integers.

---(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] getting back autonumber just inserted

[PFC]

That's a different issue than whether currval() is subject to
interference from other transactions.  And just wait until PostgreSQL
8.1 comes out and people start using lastval() -- then it could get
*really* confusing which sequence value you're getting.


	What happens if an INSERT trigger inserts something into another table  
which also has a sequence ?



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] getting back autonumber just inserted

[PFC]

Do you mean with lastval()?  Here's what happens:


	Hm, interesting, you mean the return value of lastval() also depends if  
you set your constraints  to deferred or immediate ?

I wond

---(end of broadcast)---
TIP 6: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] getting back autonumber just inserted

[PFC]

What happens if an INSERT trigger inserts something into another
table  which also has a sequence ?


Using what, lastval()?  The app will get very confused, because it'll
get the value from the sequence used in the trigger.  Using currval
there is no problem, but you already knew that.


I knew but I forgot since I use an ORM which handles all the mess ;)



---(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] getting back autonumber just inserted

[PFC]

On Fri, Jul 08, 2005 at 01:56:26AM +0200, PFC wrote:

>Do you mean with lastval()?  Here's what happens:

Hm, interesting, you mean the return value of lastval() also depends
if  you set your constraints  to deferred or immediate ?


My mind's ablank trying to contrive a situation where that would
matter.  Can you provide an example?


	It's rather perverse and farfetched, but what would stop one from putting  
some insert statements in a function that happens to be called somewhere  
inside of a check constraint... although one could agree that it's a bit  
shooting oneself in the foot...



In any case, I simply meant to point out that 8.1's lastval() will
be seductively convenient because you don't have to pass it a
sequence name, but the value it returns might not be the value you
want.


It's the first time I see a MySQLism in postgres !
	However I like it, cos it might subvert some MySQL users, and provide  
easy answers to The Weekly Question on the mailing list (ie where is  
AUTO_INCREMENT) ?
	I've just noticed that I forgot a close() somewhere hence my data import  
files missed a few chunks at the end and MySQL said "3 warnings, 0 errors"  
(if it had failed I would have noticed it a week ago but no, more  
-00-00:00:00:00 hand pruning for me now). Uh oh, SHOW WARNINGS isn't  
implemented in that version. There are warnings, but I won't tell you  
where. Somewhere. Hm... grep logs ? yeah, but the error message doesn't  
mention the table name... nor what kind of warning it is that is not an  
error even though half of the row has stayed in an unclosed() file  
buffer...




---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] two sums in one query

[PFC]

   SELECT  SUM( CASE WHEN COALESCE( DEBIT , 0 ) <> 0 THEN
COALESCE( AMOUNT , 0 ) ELSE 0 END ) AS DEBIT_AMT , SUM( CASE WHEN
COALESCE( CREDIT , 0 ) <> 0 THEN   COALESCE( AMOUNT , 0 ) ELSE 0
END ) AS CREDIT_AMT FROM 


	I don't know if it will use indexes (bitmapped OR indexes in 8.1 ?)... if  
you have indexes on debit and on credit, you could do


SELECT (SELECT sum(amount) FROM table WHERE debit=X) AS debit, (SELECT  
sum(amount) FROM table WHERE credit=x) AS credit;




actually, all the rows have both 'debit' and 'credit', but based on
the hint you gave i made this query which works:

select
sum(case when debit=account then amount else 0 end) as debtotal,
sum(case when credit=account then amount else 0 end) as credtotal
from voucherrows
where
debit = account
or
credit = account

thanks for taking the trouble - i never believed this was possible,
just asked on the off chance that it *may* be possible




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] two sums in one query

[PFC]

SELECT (SELECT sum(amount) FROM table WHERE debit=X) AS debit, (SELECT
sum(amount) FROM table WHERE credit=x) AS credit;


If most of the records are credits or debits you don't want to do this.
A single sequential scan through the table will be the best plan.


	I thought that debit = source account # and credit = dest account #, and  
there are a lot of different account...




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Make COUNT(*) Faster?

[PFC]

which wouldn't have helped.)  If true, I can handle that parsing myself
easily enough without exposing RelationGetNumberOfBlocks.


	Is there a way to get EXPLAIN results in a non-text-formatted way for  
easier use ?
	I'm asking, because it seems the feature set grows by the minute in  
posgres nowadays...




---(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] Clustering problem

[PFC]

Is it even possible to cluster a table based on the clustering scheme  
(which is

not the link_id ...) from the master table?

Can you gurus think of a better strategy? :) (Please??) :)


	You can create a functional index on a function which returns the desired  
order by looking in the main table, cluster it, then drop the index...


---(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] Create trigger for auto update function

[PFC]

CREATE TRIGGER triger_users_pass_md5
 AFTER INSERT OR UPDATE
 ON hoy
EXECUTE PROCEDURE update_pass(integer);


Try : FOR EACH ROW EXECUTE

---(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] Counting Row

[PFC]

I'd suggest :

- first getting the 'order fields' value for the ticket you want :
SELECT field_order FROM mytable WHERE condition AND  
identifier=the_one_you're_looking_for ORDER BY field_order DESC LIMIT 1


- then counting all the tickets up to this order :

SELECT count(*) FROM mytable WHERE condition AND field_order <= (SELECT  
field_order FROM mytable WHERE condition AND  
identifier=the_one_you're_looking_for ORDER BY field_order DESC LIMIT 1)


You could also use a plpgsql function to reimplement your PHP thing, which  
should be faster than doing it in PHP, but probably slower than the  
example above.



# But there is a simpler solution.

How do you model the position of a ticket in the queue ? What is the  
'order field' you mention ? If your table has a SERIAL PRIMARY KEY, it's  
monotonous, so you can use this without further worries.


table tickets_queues (id SERIAL PRIMARY KEY, queue_id, ticket_id,  
UNIQUE( queue_id, ticket_id)  )


to get the position of a ticket (by its ticket_id) in a queue :

SELECT count(*) FROM tickets_queue WHERE queue_id=# AND id <= (SELECT id  
FROM tickets_queue WHERE ticket_id=# AND queue_id=#)


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] sum but not grouped by?

[PFC]

On Fri, 05 Aug 2005 19:53:14 +0200, Henry Ortega <[EMAIL PROTECTED]>  
wrote:



Is it possible at all to do this without any joins
or subselect?


I don't think so.
You could always hide them in a view...

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] [SQL] Caracter é

[PFC]

Hello,

	This is probably a character encoding issue ; try issuing a SET  
client_encoding TO whatever encoding you're using ; it is possible that  
your connection through PHP defaults to SQL_ASCII in which "é" is illegal  
(mine did). Or, check your browser, form etc. encoding to make sure some  
Unicode doesn't get mixed up with some non-Unicode stuff.
	Note that I tried PEAR::DB and found it to be, literally, a piece of junk  
(sorry, no other words).




I compiled Postgresql 8 on my server and installed it
I am trying to insert a string using php and PEAR::DB

The string contain: é and this cause
$db-query to fail with an Unkown error as error message.

If i insert the data using pgAdminIII the string is inserted into the  
table.


I don't think it is Postgres related, do i need to ask to PEAR::DB team ?

Thanks in adavance,

CN

---(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] insert into / select from / serial problem

[PFC]

On Wed, 10 Aug 2005 05:03:47 +0200, tgh002 <[EMAIL PROTECTED]> wrote:


I am using a insert statement like:

INSERT INTO newtable
SELECT field1, field2 FROM anothertable

newtable structure is: serial, varchar, varchar

What syntax do I use to insert the serial field?   Ive tried something
like:


Try :

INSERT INTO newtable (col1, col2) SELECT field1, field2 FROM anothertable

col1, col2 being the names of your columns that you want to put field1 and  
field2 into...


The serial will take care of itself.

---(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] diary constraints

[PFC]

a) the finish is after the start


well, finish > start


b) two allocations for a single vehicle don't overlap.


this one is a bit tricky !

- Check that there is no allocation in the table whose time period start,  
end includes either the start of the end of the reservation to insert, and  
that the time period of the reservation to insert does not contain either  
the start or end of any reservation in the table.


This is 4 selects, playing with order by limit 1 and indexes, it will be  
fast.


---(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] PostgreSQL help

[PFC]

Check your database encoding, client encoding, and the encoding you use in  
your file. If your database is UNICODE, pgadmin will convert accordingly,  
but your file has to be in the right encoding.



On Mon, 29 Aug 2005 12:27:41 +0200, Shavonne Marietta Wijesinghe  
<[EMAIL PROTECTED]> wrote:



Hey!!

I have a problem and i hope this is the correct section to post it!!!

When i use the COPY  Table Name FROM Location command to insert values  
to a table using a txt file, the programme gives me errors when he finds  
letter as "ò, è, à" inside the txt file.
But when i use the insert command and I write some word with any of the  
letters it seems to work. Why is that ?? What can i do to resolve it??


i use the programme PgAdmin 3 to write my commands...

Thank you.  
---(end of broadcast)---

TIP 2: Don't 'kill -9' the postmaster





---(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] automatic update or insert

[PFC]

In a system of mine i need to insert records into table [tbStat], and
if the records exist i need to update them instead and increase a
column [cQuantity] for every update.

I.e. the first insert sets cQuantity to 1, and for every other run
cQuantity is increased.

Currently i have implemented this as a stored procedure in the plpgsql
language. This means that in my stored procedure i first do a select to
find out if the row exists or not, then i do a insert or update
depending if the row existed.


There are two ways you can do this :

* If you will have more updates than inserts (ie. more items with a  
quantity >1 than 1) :


UPDATE
If the update updated no rows, then INSERT

* If you have more inserts than updates (ie. more items with quantity 1  
than >1) :


INSERT
if it fails due to violating the unique constraint, then UPDATE

	None of these involve a SELECT. The first one is very cheap if you end up  
doing more updates than inserts, because it just does the update.


	You will of course need a UNIQUE index to identify your rows, and prevent  
insertion of duplicates. I suppose you have this already.
	There is a subtility in the second form : the INSERT will fail on  
duplicate key, so you have to either rollback the transaction if you send  
the queries raw from your app, or catch the exception in your plpgsql  
function.
	Also a race condition might exist if someone deletes a row in-between, or  
the first procedure is executed twice at the same time by different  
threads. Be prepared to retry your transaction.


Something like the ON DUPLICATE KEY UPDATE in MySQL would be nice to 
have.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Help with simple query

[PFC]

If you want the latest by user, you can cheat a bit and use the fact that  
the id's are incrementing, thus ordering by the id
is about the same as ordering by the date field. I know it can be inexact  
in some corner cases, but it's a good approximation, and

very useful in practice :

SELECT user_id, max(note_id) FROM notes GROUP by user_id;

So :

SELECT * FROM notes WHERE id IN (SELECT max(note_id) FROM notes GROUP by  
user_id) ;


Can postgres use the index on these max() clauses now ?


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] How to implement Microsoft Access boolean (YESNO)

[PFC]

On Tue, 24 Jan 2006 06:03:48 +0100, Greg Stark <[EMAIL PROTECTED]> wrote:


Tom Lane <[EMAIL PROTECTED]> writes:


Greg Stark <[EMAIL PROTECTED]> writes:
> "Jesper K. Pedersen" <[EMAIL PROTECTED]> writes:
>> Having checked the I/O format it seems that MS Access exports the
>> values of a YESNO field as 0 and 1

> If only Postgres's boolean type were as helpful.

There's a cast to int in 8.1, and you can roll-your-own easily in prior
releases ...


The annoying thing about is that in just about any client language  
you'll get
't' and 'f' by default and both will evaluate to false. So any user who  
tries

to do things the obvious way like this will get a surprise:


I guess this depends on the smartness of the language's client library.
	psycopg2 on python happily converts pg's types to and from python's  
native types (bool, int, datetime, arrays of these,  etc...). All types  
are supported except GIST stuff like polygons (but you can write a type  
converter for these). Never ever quote an argument again ! Life is good.



DB.execute("SELECT (1=1)::bool, (1=0)::bool"); DB.fetchone()

(True, False)

DB.execute("SELECT '{1,2,3,4}'::INTEGER[]"); DB.fetchone()

([1, 2, 3, 4],)

DB.execute("SELECT '{1,2,3,4}'::NUMERIC[]"); DB.fetchone()

([Decimal("1"), Decimal("2"), Decimal("3"), Decimal("4")],)

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] 'locking' the SELECTs based on indices...

[PFC]

I lock just that particular row, which is no good. I need to have all
the codes for the service 1 locked, so if it happens that two users send
the very same code, one has to fail. Therefore, from within plpgsql I
first do:


I'm a bit tired tonight so I'll simplify your example :

CREATE TABLE stuff ( a INT, b INT );

	Basically you want to lock ALL rows with a certain value of a, in order  
to perform an operation on only one of them.

You could do this :

CREATE TABLE all_as ( a INT PRIMARY KEY )
CREATE TABLE stuff ( a INT REFERENCES all_as(a), b INT );

	Now all the rows in "stuff" that have the same value of "a" reference the  
same row in "all_as".

All you have to do is

SELECT * FROM all_as WHERE a=the value FOR UPDATE

and you lock all rows having that particular value of a in the big 
table.



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] 'locking' the SELECTs based on indices...

[PFC]

Now, If some other user want's his prize, when checking his code, if he
sends code for some other service then service 1, that's ok. If he's
sends code for the service 1 the PERFORM will wait untill I'm finished
with previous user.


Sorry for the double post.

	If the rows in your table represent associations between codes and  
services that are one-use only, you could simply use UPDATE or DELETE, to  
mark the row in question as having been "consumed".
	Then, you check how many rows were deleted or updated. If it's 1, good.  
If it's 0, the code has been used already.


	If the code itself is one-use only, you should have a codes table and a  
codes_to_services table, with an ON DELETE CASCADE so that, when you use a  
code, you delete it from the codes table and it's "consumed" for all  
services.


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] [SQL] Interval subtracting

[PFC]

For same reasons, i.e. a need for precision, I find it hard to accept
the idea of mixing positive and negative units in the same interval.
The plus or minus sign should be outside of the interval.


	The interval data type is really useful. I see no reason to restrict its  
usefulness with an arbitrary constraint. Date arithmetic is treacherous  
and INTERVAL is a lifesaver.


Forcing a global sign on the interval would break interval arithmetic.
How would you compute '1 month'::interval - '1 week'::interval ?

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] How can I selet rows which have 2 columns values cross equal?

[PFC]

What are your conditions on a and b ? Can a be equal to b on a row ? If  
so, do you want this row ?
If you want to avoid duplicates, I suggest first removing them, then  
adding a constraint CHECK( aapplication (or in an ON INSERT trigger), swap a and b if a>b.


I added some values to your table for completeness :

SELECT * FROM test;
  a  |  b  | id
-+-+
 100 | 101 |  1
 101 | 100 |  2
 100 |   3 |  3
  20 |  30 |  4
  11 |  13 |  5
   3 |  33 |  6
  30 |  20 |  7
 666 | 666 |  8
 666 | 666 |  9
 500 | 666 | 10
 666 | 500 | 11
 123 | 123 | 12
 456 | 789 | 13
 456 | 789 | 14

Try :

SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS  
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a;


 xid | yid | xa  | xb  | ya  | yb
-+-+-+-+-+-
   1 |   2 | 100 | 101 | 101 | 100
   2 |   1 | 101 | 100 | 100 | 101
   4 |   7 |  20 |  30 |  30 |  20
   7 |   4 |  30 |  20 |  20 |  30
   8 |   9 | 666 | 666 | 666 | 666
   8 |   8 | 666 | 666 | 666 | 666
   9 |   9 | 666 | 666 | 666 | 666
   9 |   8 | 666 | 666 | 666 | 666
  10 |  11 | 500 | 666 | 666 | 500
  11 |  10 | 666 | 500 | 500 | 666
  12 |  12 | 123 | 123 | 123 | 123


You'll get 2 rows for each match. You can add a condition to remove the  
dupes :


SELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS  
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a AND x.id<=y.id;


 xid | yid | xa  | xb  | ya  | yb
-+-+-+-+-+-
   1 |   2 | 100 | 101 | 101 | 100
   4 |   7 |  20 |  30 |  30 |  20
   8 |   9 | 666 | 666 | 666 | 666
   8 |   8 | 666 | 666 | 666 | 666
   9 |   9 | 666 | 666 | 666 | 666
  10 |  11 | 500 | 666 | 666 | 500
  12 |  12 | 123 | 123 | 123 | 123

If you don't want the rows with a=b, replace x.id<=y.id with x.idSELECT x.id AS xid, y.id AS yid, x.a AS xa, x.b AS xb, y.a AS ya, y.b AS  
yb FROM test x, test y WHERE x.a=y.b AND x.b=y.a AND x.id


 xid | yid | xa  | xb  | ya  | yb
-+-+-+-+-+-
   1 |   2 | 100 | 101 | 101 | 100
   4 |   7 |  20 |  30 |  30 |  20
   8 |   9 | 666 | 666 | 666 | 666
  10 |  11 | 500 | 666 | 666 | 500

It is going to be slow, though. Basically a full self join. Let's hack  
this :


CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

I'll leave this one as an exercice to the reader ;)))

SELECT array_accum(id), CASE WHEN aasum(sign(a-b)) = 0 AND count(*)>1;

 array_accum | low | high
-+-+--
 {10,11} | 500 |  666
 {4,7}   |  20 |   30
 {1,2}   | 100 |  101
 {8,9}   | 666 |  666



On Fri, 10 Mar 2006 14:24:44 +0100, Fay Du <[EMAIL PROTECTED]> wrote:


Hi All:
I have a table like this:

   Table test
Id   |  a | b
-++---
1| 100| 101
2| 101| 100
3| 100| 3
4| 20 | 30
5| 11 | 13
6| 3  | 33
7| 30 | 20

I want to get row 1, 2,4 and 7 selected. Because their values of a and b
are cross equal. i.e., for each pair of rows,
a.Row1 = b.Row2 and b.Ro1 = a.Row2
a.Row4 = b.Row7 and b.Ro4 = a.Row7

How can I construct a sql statement to select them?
Thanks in advance.
Fay


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster




---(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] input from a external text file......!

[PFC]

inside psql, type :

\i filename



On Sat, 11 Mar 2006 11:29:20 +0100, AKHILESH GUPTA  
<[EMAIL PROTECTED]> wrote:



Hi All.!
I just want to know one thing that is it possible with PGSQL that,
if I want to insert and execute a query from a external text file  
instead of

giving it at the pgsql prompt?
just like in Oracle the file having query is executed with a '@ filename'
statement at the sql prompt..!
plz help me and mail me @ [EMAIL PROTECTED], it's urgent.
thanks in advance...!
(i have searched alot, but didn't found anything)

--
Thanks & Regards,
Akhilesh
DAV Institute of Management
Faridabad(Haryana)
GSM:-(+919891606064)
   (+911744293789)

"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Permission to Select

[PFC]

What information can be retrieved from a structure by being able to
update all rows?


	Write a plpgsql function snoop(x) which inserts x into a table 'log'  
created by you, and also returns x.

UPDATE users SET password=snoop(password).
Read log table.
Done.

	If you have WHERE rights on a table, you can guess any column content  
pretty quick. Just do a binary search. Some time ago I had a friend whose  
website had some SQL injection holes, and he said "so what ? who cares ? I  
have magicquotes" (lol), so I coded a python script which injected a  
"password BETWEEN x AND y" (using CHR() to avoid quotes) and narrowed the  
search. It took about 1 minute to get the password (which turned out to be  
md5 that resisted a few seconds to dictionary attack using whatever evil  
hax0rz tool).


---(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] connectby documentation

[PFC]

7.4?! Huh... Is there any sources.list a bit more updated?  Where can I  
download PostgreSQL contrib modules.  The documentation 8.1 doesn't help  
so much.  Where can I find more documentation on available contrib.  
modules?


	gentoo automatically compiles and installs the contribs (you just have to  
execute the SQL scripts for the pnes you want to put in your database).  
Maybe you already have them ? trl "locate contrib", who knows...


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] PostgreSQL Handling of Special Characters

[PFC]

My Database uses SQL_ASCII encoding.


I just received an email with all accented characters destroyed. UNICODE
should be the default for anything in 2006.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] have you feel anything when you read this ?

[PFC]

have you feel anything when you read this ?


Business as usual...

It's more fun to grep "crash" on this page, which gets about 27 
results...

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] have you feel anything when you read this ?

[PFC]

the problem is: you'll get this four byte sequence '\000' _instead_ of  
NUL-byte anyway.



http://www.postgresql.org/docs/8.1/interactive/datatype-binary.html says :

"A binary string is a sequence of octets (or bytes). Binary strings are  
distinguished from character strings by two characteristics: First, binary  
strings specifically allow storing octets of value zero and other  
"non-printable" octets (usually, octets outside the range 32 to 126).  
Character strings disallow zero octets, and also disallow any other octet  
values and sequences of octet values that are invalid according to the  
database's selected character set encoding. Second, operations on binary  
strings process the actual bytes, whereas the processing of character  
strings depends on locale settings. In short, binary strings are  
appropriate for storing data that the programmer thinks of as "raw bytes",  
whereas character strings are appropriate for storing text."


That's the whole point of escaping, so that data generated by COPY, for  
instance, does not include any funky characters, including the \0 (zero)  
character, so that you can use any standard tool on it, including grep...


I LIKE the fact that TEXT refuses invalid characters. It helps find bugs  
in my applications, like when I forget to process some 8-bit string before  
inserting it in my database which uses UNICODE. I definitely prefer an  
error than finding a month later that half my data has had all its  
accented characters screwed up.


in psql, you have to use the escaped syntax :

SELECT length('\\000'::BYTEA), length('\\001'::BYTEA),  
length('\\000'::TEXT), length('\\001'::TEXT);

 length | length | length | length
+++
  1 |  1 |  4 |  4

Your client library should take care of escaping and de-escaping. Here, in  
python :


cursor.execute( "SELECT %s,%s::BYTEA", ("this is a normal string",  
psycopg2.Binary( ">\x00< this is a string with a zero byte" ),) )

r = cursor.fetchone()
print r
['this is a normal string', offset 0 at 0x2ce27c70>]

print str(r[1])

< this is a string with a zero byte

print repr(str(r[1]))

'>\x00< this is a string with a zero byte'

ord(r[1][1])

0


Note : \x00 is python's escaping for the null byte

---(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] have you feel anything when you read this ?

[PFC]

I wrote:

the problem is: you'll get this four byte sequence '\000' _instead_  
of  NUL-byte anyway.


You wrote:


Your client library should take care of escaping and de-escaping.


We both agree as you see.

Then i am asking:
WHY should a client take care of de-escaping ? Why not to get his data  
unchanged ?


	I can understand why you say that for something as simple as a BYTEA, but  
if the value to be passed to the client is an ARRAY of geometric types or  
something, you gonna need an open, platform-agnostic exchange format  
between the way postgres internally represents it and the way the client  
represents it (in my case, a python list containing instances of python  
classes representing boxes, etc, it'll be different for every language).


	Exporting data from postgres in binary is only useful to C programmers  
who can import the required struct definitions, and you still have to  
manage the format, it's just that you walk struct's instead of unescaping  
\'s


---(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] Update counter when row SELECT'd ... ?

[PFC]

On Tue, 21 Mar 2006 04:33:22 +0100, Daniel CAUNE <[EMAIL PROTECTED]> wrote:


I have a simple table:

name, url, counter

I want to be able to do:

SELECT * FROM table ORDER BY counter limit 5;

But, I want counter to be incremented by 1 *if* the row is included in
that 5 ... so that those 5 basically move to the bottom of the list, and
the next 5 come up ...

I've checked CREATE TRIGGER, and that doesn't work 'ON SELECT' ... is
there anything that I *can* do, other then fire back an UPDATE based on
the records I've received?

Thanks ...



	You could also have a "new" table (which gets new rows) and an "archive  
table", and move the rows from "new" to "archive" while selecting them,  
using a plpgsql set-returning function .



---(end of broadcast)---
TIP 6: explain analyze is your friend