[SQL] 7.0.3 and 7.1.3 different results?
A query such as "select sum(pages) from job_documents where delivery_type='print'" returned 0 in version 7.0.3 if there were no rows matching the query. In 7.1.3 the result is NULL if no rows match the query. Why the change? Which result is "correct" according to the SQL standard? I'm running PostgreSQL 7.1.3 on FreeBSD 4.3 (x86). TIA. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] jdbc driver
hi. who to knows about the java + postgresql (jdbc)? I need to know where can I get the driver for conection postgres and java 1.2? thanks. ---(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] aggregate functions, COUNT
* Tom Lane <[EMAIL PROTECTED]> [02-10-01 18:02]: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > I think you could use EXISTS for that, > > select EXISTS (); should give a true/false on whether the > > query returned any rows. I'm not sure if it stops after one row > > or not, but if it doesn't you can add a limit 1 to the query. > > Yes it does stop after one row; and furthermore, the planner knows to > generate a fast-start plan for it. (Or at least it's supposed to > ... hmm, this seems to be broken in current sources ...) Anyway, > there's no need for LIMIT 1 inside an EXISTS, because the planner > assumes that automatically. Thank you muchly, I did some profiling and SELECT EXISTS is indeed exactly what I wanted. -Kevin Way -- Kevin Way <[EMAIL PROTECTED]> http://www.overtone.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] aggregate functions, COUNT
On Tue, 2 Oct 2001, Kevin Way wrote: > I'm currently using a SELECT count(*) when all I really want to know is > if 1 or more records exist. Is there a standard way to just find out if > a record exists? If not, is there a way to avoid iterating over all the > records by writing an aggregate function? Given what I've read of how > they work, I don't see how to make the function return before parsing > all the results anyway, am I wrong here? I think you could use EXISTS for that, select EXISTS (); should give a true/false on whether the query returned any rows. I'm not sure if it stops after one row or not, but if it doesn't you can add a limit 1 to the query. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Stupid Question
<<< No Message Collected >>> ---(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
[SQL] Function return rows?
Can I use a function to execute a query and return a row or set of rows? If so, can you point me to some examples or perhaps give me an example of a function that would do roughly the same thing as: select * from mytable where mytable.name ~* 'aname'; I can't seem to find any examples that return rows, just single ints and bools and stuff. ---(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] aggregate functions, COUNT
Stephan Szabo <[EMAIL PROTECTED]> writes: > I think you could use EXISTS for that, > select EXISTS (); should give a true/false on whether the > query returned any rows. I'm not sure if it stops after one row > or not, but if it doesn't you can add a limit 1 to the query. Yes it does stop after one row; and furthermore, the planner knows to generate a fast-start plan for it. (Or at least it's supposed to ... hmm, this seems to be broken in current sources ...) Anyway, there's no need for LIMIT 1 inside an EXISTS, because the planner assumes that automatically. 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
[SQL] faster and faster!!
Two questions: - Does the order of WHERE clauses make a difference? I was trying to make some of my code faster and tried switching two clauses that should have effected the query, but there was no speed difference. - I have a query: SELECT stamp FROM table WHERE timestamp > now() - INTERVAL '1 0:00' This query takes 13 seconds to run. If I were to run another query such as: WHERE sid > 1000 it would return almost instantly. There are indexes on both columns. Is dealing with timestamps that slow? Is there any way to speed it up? Thanks all, - Brandon c: 646-456-5455h: 201-798-4983 b. palmer, [EMAIL PROTECTED] pgp:crimelabs.net/bpalmer.pgp5 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Stupid Question
I want to create a table and for whatever reason it will not accept the command, can anyone say what is wrong as I am very new to Postgres, thanks: CREATE TABLE cmpnycontacts ( ContactNo integer(10) NOT NULL SERIAL, CmpnyNo varchar(6) NOT NULL, FirstName varchar(30) NOT NULL, Surname varchar(30) NOT NULL, Phone varchar(20) NOT NULL, Mobile varchar(20) NOT NULL, Email varchar(40) NOT NULL, Country char(2) NOT NULL, CONSTRAINT cmpnycontacts_pkey PRIMARY KEY (ContactNo) ); ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL]
hi how can i acheive AutoIncrement in postgresql. please what is the equivalent way of stored procedures in postgresql. please mail to below address also. regards, sreedhar mail : [EMAIL PROTECTED]
[SQL] strange query execution times
Hi guys, cenes_test=# select version(); version --- PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc 2.95.2 (1 row) cenes_test=# \d personen Table "personen" Attribute| Type | Modifier -+---+-- personen_id | integer | not null login | char(10) | not null passwort| char(32) | not null deaktiviert | smallint | not null default '0' firma | char(60) | nachname| varchar(60) | not null vorname | varchar(60) | not null telefon | varchar(50) | telefax | varchar(50) | email | varchar(80) | not null use_perm| smallint | not null default '0' titel | varchar(20) | mobiltelefon| varchar(50) | abteilung | varchar(60) | funktion| varchar(60) | erfass_datum| timestamp | not null zeitstempel | timestamp | not null kreditlimit | numeric(11,5) | bild| varchar(100) | anbieter_tpl| varchar(100) | firma_2 | varchar(255) | url | varchar(100) | hrb | varchar(100) | crefo | varchar(100) | ssl_client_s_dn | varchar(255) | Indices: erfass_datum_personen_key, personen_login_key, personen_pkey Constraints: ((use_perm = 0::int2) OR (use_perm = 1::int2)) ((deaktiviert = 0::int2) OR (deaktiviert = 1::int2)) cenes_test=# \d r_kunden_anbieter Table "r_kunden_anbieter" Attribute | Type | Modifier ---+--+-- k_id | bigint | not null a_id | bigint | not null beziehung | smallint | not null Indices: beziehung_r_kunden_anbieter_key, r_kunden_anbieter_a_id_key, r_kunden_anbieter_k_id_key cenes_test=# explain select p.* , rka.beziehung from personen p join r_kunden_anbieter rka on p.personen_id = rka.k_id where rka.a_id = 620 and rka.beziehung != 0 and rka.beziehung != 2 and rka.beziehung != 1 and rka.beziehung != 4 order by erfass_datum; NOTICE: QUERY PLAN: Sort (cost=59.88..59.88 rows=2 width=274) -> Merge Join (cost=53.79..59.87 rows=2 width=274) -> Sort (cost=11.37..11.37 rows=2 width=10) -> Seq Scan on r_kunden_anbieter rka (cost=0.00..11.36 rows=2 width=10) -> Sort (cost=42.42..42.42 rows=484 width=264) -> Seq Scan on personen p (cost=0.00..20.84 rows=484 width=264) EXPLAIN cenes_test=# explain select p.* , rka.beziehung from personen p join r_kunden_anbieter rka on p.personen_id = rka.k_id where rka.a_id = 620 and rka.beziehung = 3 order by erfass_datum; NOTICE: QUERY PLAN: Sort (cost=35.80..35.80 rows=1 width=274) -> Nested Loop (cost=0.00..35.80 rows=1 width=274) -> Seq Scan on r_kunden_anbieter rka (cost=0.00..8.90 rows=1 width=10) -> Seq Scan on personen p (cost=0.00..20.84 rows=484 width=264) EXPLAIN cenes_test=# table personen holds not only customers but also suppliers. table r_kunden_anbieter describes the relationship between customers and suppliers. there are five status, 0 to 4 in attribute beziehung. both queries return the same results. they select all customers which have a certain relationship (beziehung = 3) to a given supplier. personen has 484 rows, r_kunden_anbieter 327. the database is freshly vacuum analyzed. The first query takes 0.038 sec, the second 0.879 secs. Why is the negotiation of all values except the one we are looking for faster than to look for equality of the one we are looking for? Markus Bertheau & Horst Schwarz Cenes Data GmbH ---(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
[SQL] Question about indexing!
I made the table like this;
dsn=# \d pgsql_bbs
Table "pgsql_bbs"
Attribute | Type |Modifier
-+-+-
id | integer | not null default nextval('pgsql_bbs_seq'::text)
topic | text|
cdate | integer | default (abstime(now()))::int4
subject | text|
content | text|
deleted | integer | default 0
gid | integer | not null
pos | integer | not null
pid | integer | not null
rdepth | integer | not null
Indices: pgsql_bbs_pkey,
pgsql_cdate,
pgsql_deleted,
pgsql_gid,
pgsql_gid_pos,
pgsql_pid,
pgsql_pos,
pgsql_topic,
pgsql_topic_deleted,
pgsql_uid
And I tried next query;
select * from pgsql_bbs where topic = 'qna' and deleted < 2
order by gid desc, pos asc limit 20, 0;
But this query is not using index!
The next is result of explain of this query:
dsn=# explain select * from pgsql_bbs where topic = 'qna' and deleted < 2
dsn-#order by gid desc, pos asc limit 20, 0;
NOTICE: QUERY PLAN:
Limit (cost=15.71..15.71 rows=11 width=245)
-> Sort (cost=15.71..15.71 rows=11 width=245)
-> Index Scan using pgsql_topic_deleted on pgsql_bbs (cost=0.00..15.51
rows=11 width=245)
EXPLAIN
How method is exist enhancing performance of this query?
Thank you for read this question.
--
mailto:[EMAIL PROTECTED]
http://database.sarang.net
Dept of Neurosurgery, Dong-eui Medical Center
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] A bug in triggers PG 7.1.3 or misunderstand ?
[EMAIL PROTECTED] (Tom Lane) wrote in message news:<[EMAIL PROTECTED]>... > I think you need "return old", not "return new", in the body of the > trigger if you want the delete to take place. new would be NULL in > a delete situation ... > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html Ok I saw that in the documentation so I tryied a variant of it: if TG_OP = 'DELETE' then return old; else return new; end if; But the TG_OP comes empty and it allawys try return new preventing the delete to happen. And that was supposed to work isn't it ? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] strange query execution times
Markus Bertheau <[EMAIL PROTECTED]> writes: > r_kunden_anbieter describes the relationship between customers and > suppliers. there are five status, 0 to 4 in attribute beziehung. both > queries return the same results. they select all customers which have a > certain relationship (beziehung = 3) to a given supplier. > personen has 484 rows, r_kunden_anbieter 327. How many rows actually satisfy "rka.a_id = 620 and rka.beziehung = 3"? The issue appears to be that the planner estimates one matching row in the one case and two matching rows in the second. Given the estimate of one row, it decides to go for the low-overhead nested loop plan. I am guessing that there are really considerably more than two matching rows, and so the nested loop plan loses badly compared to the mergejoin, which takes longer to set up but is better able to cope with many rows. FWIW, 7.2 has better statistics and should be better able to pick the right plan in this context ... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 7.0.3 and 7.1.3 different results?
Joel Mc Graw <[EMAIL PROTECTED]> writes: > A query such as "select sum(pages) from job_documents where > delivery_type='print'" returned 0 in version 7.0.3 if there were no rows > matching the query. In 7.1.3 the result is NULL if no rows match the > query. Why the change? Which result is "correct" according to the SQL > standard? NULL is correct according to the SQL standard (yes, I think it's brain-dead too). SQL92 section 6.5 saith: b) If AVG, MAX, MIN, or SUM is specified, then Case: i) If TXA is empty, then the result is the null value. -- ii) If AVG is specified, then the result is the average of the values in TXA. iii) If MAX or MIN is specified, then the result is respec- tively the maximum or minimum value in TXA. These results are determined using the comparison rules specified in Subclause 8.2, "". iv) If SUM is specified, then the result is the sum of the values in TXA. If the sum is not within the range of the data type of the result, then an exception condition is raised: data exception-numeric value out of range. I'd suggest COALESCE(SUM(foo), 0) if you need a zero result. However, I dispute your assertion that 7.0.3 returned zero. I just double checked, and I get a NULL there too. If we ever returned zero, it was a long time ago. 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
Re: [SQL] faster and faster!!
bpalmer <[EMAIL PROTECTED]> writes:
> - I have a query:
> SELECT stamp
> FROM table
> WHERE
> timestamp > now() - INTERVAL '1 0:00'
> This query takes 13 seconds to run.
It's probably not being indexed (use EXPLAIN to check). The problem
is that "now() - INTERVAL '1 0:00'" is not considered a constant, so
the planner can't use an indexscan.
The usual workaround is to make a function like so:
tgl=# create function ago(interval) returns timestamp as
tgl-# 'select now() - $1' language 'sql' with (iscachable);
CREATE
tgl=# select ago('1 0:00');
ago
---
2001-10-01 11:31:13.62-04
(1 row)
and then write
WHERE timestamp > ago('1 0:00');
The "iscachable" attribute of the function tells the planner it's okay
to reduce the function call to a constant during planning, and then it
becomes possible to use an indexscan. Strictly speaking, marking this
function iscachable is a lie, but it works just fine in interactive
queries. (You might have trouble with it if you tried to put such
a query into a plpgsql function; you'd probably find that the function
call gets reduced sooner than you want.)
See past discussions of this issue in the archives --- a search for
"iscachable" should turn up some threads. In the long term we probably
need an intermediate concept between "cachable" and "not cachable",
along the lines of "result is constant within a query", so that the
behavior of now() can be described more accurately.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Stupid Question
Rebekah Kirk wrote: > > I want to create a table and for whatever reason it will not accept the > command, can anyone say what is wrong as I am very new to Postgres, thanks: > > CREATE TABLE cmpnycontacts ( > ContactNo integer(10) NOT NULL SERIAL, > CmpnyNo varchar(6) NOT NULL, > FirstName varchar(30) NOT NULL, > Surname varchar(30) NOT NULL, > Phone varchar(20) NOT NULL, > Mobile varchar(20) NOT NULL, > Email varchar(40) NOT NULL, > Country char(2) NOT NULL, > CONSTRAINT cmpnycontacts_pkey PRIMARY KEY (ContactNo) > ); Your problem is with the ContactNo, two things: - there is no specified length for 'integer', so integer(10) would fail (integer is defined IIRC as int4, meaning an integer that uses four bytes to store its values). - the SERIAL keyword defines a integer coupled to a sequence. The following statement would work: CREATE TABLE cmpnycontacts ( ContactNo SERIAL primary key, CmpnyNo varchar(6) NOT NULL, FirstName varchar(30) NOT NULL, Surname varchar(30) NOT NULL, Phone varchar(20) NOT NULL, Mobile varchar(20) NOT NULL, Email varchar(40) NOT NULL, Country char(2) NOT NULL ); Its looks like this table has a relationship to a table that holds the company data. Since PostgreSQL supports referential integrity you could extend CmpnyNo in the following way: CmpnyNo varchar(6) not null references Company on update cascade on delete cascade, Hope this helps, Nils Zonneveld -- Alles van waarde is weerloos Lucebert ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] TEXT in select
Yes, you are right. The error was, that i saved long text as part of a
prepared statement by using the ascii-stream. Yesterday i recognized (at
least), that i can save without any problem long text as part of a normal
insert-string. And then the queries are working like they should.
Sometimes even the simple things can be that difficult :-)
Best, Michael
> -Ursprüngliche Nachricht-
> Von: Haller Christoph [mailto:[EMAIL PROTECTED]]
> Gesendet: Freitag, 28. September 2001 16:11
> An: Michael Remme
> Cc: [EMAIL PROTECTED]
> Betreff: Re: [SQL] TEXT in select
>
>
> I've tried (column indexdef is of type text)
> select indexdef from pg_indexes where indexdef like '%pg_proc%' ;
> select indexdef from pg_indexes where position('pg_proc' in
> indexdef) > 0 ;
> select upper(indexdef) from pg_indexes where position('pg_proc'
> in indexdef) > 0 ;
> and all three worked as intended.
> Sorry, but I can't see any reason why it doesn't work on your machine.
> Regards, Christoph
>
> > Hi,
> >
> > does anybody know a way, how to implement the content of a field of type
> > TEXT
> > into a query?
> >
> > if i am trying:
> > select * from testtable WHERE testTEXT LIKE '%testString%'
> >
> > i am getting always an empty selection, although there is a
> record existing.
> > The same with
> >
> > select * from testtable WHERE position ('testString' in
> testTEXT) >
> > 0;
> >
> > Additional, is there a way to use the UPPER()-function to the
> content of the
> > TEXT-field?
> >
> > Thanks in advance,
> > Michael
>
>
Virengeprüft vom G DATA AntiVirenKit
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Aggregate Aggravation
Indeed I do. I need to add another constraint on that query. It never would have occurred to me had I not asked. Problem solved. Thank you, thank you! -Robin On Thu, 27 Sep 2001, Tom Lane wrote: > "Robin's PG-SQL List" <[EMAIL PROTECTED]> writes: > > I have a query using the SUM() function that is not returning the > > appropriate results. > > I'm guessing that you have two rows in bolcustomer matching > bol_number = 88738, so that the 14 gets added in twice. > > regards, tom lane > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(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
[SQL] aggregate functions, COUNT
I'm currently using a SELECT count(*) when all I really want to know is if 1 or more records exist. Is there a standard way to just find out if a record exists? If not, is there a way to avoid iterating over all the records by writing an aggregate function? Given what I've read of how they work, I don't see how to make the function return before parsing all the results anyway, am I wrong here? Thanks for any help, or 2x4s, Kevin Way -- Kevin Way <[EMAIL PROTECTED]> http://www.overtone.org/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Question about indexing!
On Tue, 2 Oct 2001, [euc-kr] Jeong Jaeick, Á¤ÀçÀÍ wrote: > select * from pgsql_bbs where topic = 'qna' and deleted < 2 >order by gid desc, pos asc limit 20, 0; > > But this query is not using index! > > The next is result of explain of this query: > > dsn=# explain select * from pgsql_bbs where topic = 'qna' and deleted < 2 > dsn-#order by gid desc, pos asc limit 20, 0; > NOTICE: QUERY PLAN: > > Limit (cost=15.71..15.71 rows=11 width=245) > -> Sort (cost=15.71..15.71 rows=11 width=245) > -> Index Scan using pgsql_topic_deleted on pgsql_bbs (cost=0.00..15.51 >rows=11 width=245) It appears to be using the topic_deleted index according to this explain output. How many rows actually have topic='qna' and deleted<2? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Indexing behavior
Folks, Q: Are brand-new rows included in PostgreSQL indexed immediately? Detail: I have several "keyset holding" tables where lists of primary key ids are inserted for immediate retrieval by the user. After a few retrievals, these keysets are not used again. The purpose of this arrangement is to get around the fact that Postgres 7.1.2 does not have recordset-returning procedures/functions. Since these keyset tables contain only brand new and deleted rows, the only way that indexes on the keyset columns will be useful is if the new rows are added to indexes immediately by the PostgreSQL engine. Otherwise, such indexes are useless and may even slow down queries. Feedback? Answers? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] jdbc driver
Esteban, Try http://jdbc.postgresql.org thanks, --Barry PS. There is a pgsql-jdbc mail list which would be a more appropriate place for this question. Esteban Gutierrez Abarzua wrote: > hi. > > who to knows about the java + postgresql (jdbc)? > > I need to know where can I get the driver for conection postgres and > java 1.2? > > thanks. > > > > > ---(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]
