Re: [SQL] No triggers visible for different user in information_schema.triggers
Hello again, thanks a lot for the fast and significant help. Michael Fuhr schrieb: On Thu, Feb 17, 2005 at 04:58:24PM +0100, Oliver Brück wrote: > [SNIP] SQL:1999 defines the TRIGGERS view as: Identify the triggers in this catalog that are owned by a given user. SQL:2003 (Working Draft) says: Identify the triggers on tables in this catalog that are accessible to a given user or role. Apparently PostgreSQL implements the SQL:1999 specification of information_schema.triggers. While PostgreSQL implements the SQL:1999 behaviour, is there any way to check if a certain trigger already has been created, though that trigger is owned by another user? Thx, Oliver Brueck ---(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] Speeds using a transaction vrs not
Joel Fradkin wrote: I wrote a .net program to move my data to postgres (works great on SQ_ASCII). In fiddling around I tried it using the odbc driver and a transaction originally, but converted it to using the .net connectivity but no transaction. What I found was it moved my database (4 gig in MSSQL) in 2 hours using the .net, but 12 hours with the odbc and transaction. You *are* using transactions, you don't have a choice. Did you do the transfer of all 4GB in ONE transaction with the ODBC? Please describe the process in more detail. -- Richard Huxton Archonet Ltd ---(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] No triggers visible for different user in information_schema.triggers
On Wed, Feb 23, 2005 at 09:05:39AM +0100, Oliver Brück wrote: > While PostgreSQL implements the SQL:1999 behaviour, is there any way to > check if a certain trigger already has been created, though that trigger > is owned by another user? See the "System Catalogs" chapter in the documentation, in particular the page for pg_trigger. It can also be instructive to run psql with the -E option or execute "\set ECHO_HIDDEN" to make psql show the queries it executes for commands like "\d tablename". What are you trying to do? Why do you want to look for a particular trigger? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] PostgreSQL ignores my indexes
Hello,
I'm having two tables (listed):
CREATE TABLE "public"."device" (
"id" BIGSERIAL,
"name" TEXT,
"serialnumber" TEXT,
-- many more columns --
) WITH OIDS;
CREATE UNIQUE INDEX "device_id_key" ON "public"."device"
USING btree ("id");
CREATE INDEX "device_name_index" ON "public"."device"
USING hash ("name");
CREATE TABLE "public"."tmeevent" (
"id" BIGSERIAL,
"tme_endpointlabel" TEXT,
"tme_ip_address" INET,
"tme_gateway" TEXT,
"tme_tmeserver_id" TEXT,
"tme_action" TEXT,
"tme_argument" TEXT,
"tstamp" TIMESTAMP(6) WITHOUT TIME ZONE DEFAULT now(),
"debug" BOOLEAN DEFAULT false,
"tme_status" TEXT,
CONSTRAINT "tmeevent_pkey" PRIMARY KEY("id")
) WITH OIDS;
CREATE INDEX "tmeevent_tmeendpointlabel_index" ON "public"."tmeevent"
USING hash ("tme_endpointlabel");
Table device contains 5285 rows, tmeevent contains 834912 rows.
I have to make a query like the one below:
SELECT device.id AS device_id, tme_endpointlabel, tme_ip_address,
tme_gateway,
tme_tmeserver_id, tme_action, tme_argument, tstamp, tme_status
FROM (tmeevent LEFT JOIN device ON ((tmeevent.tme_endpointlabel =
device.name)))
Doing an explain analyze on this query gives me:
Hash Join (cost=578.06..91208.59 rows=853428 width=167) (actual
time=35.22..8992.61 rows=835013 loops=1)
Hash Cond: ("outer".tme_endpointlabel = "inner".name)
-> Seq Scan on tmeevent (cost=0.00..23606.12 rows=834912 width=138)
(actual time=0.04..2193.97 rows=834912 loops=1)
-> Hash (cost=564.85..564.85 rows=5285 width=29) (actual
time=35.06..35.06 rows=0 loops=1)
-> Seq Scan on device (cost=0.00..564.85 rows=5285 width=29) (actual
time=0.04..25.07 rows=5285 loops=1)
Total runtime: 9499.58 msec
Why isn't the planner using my indexes? I tried making them as both rtree
and btree, but that doesn't seem to work.
I've been running VACUUM and ANALYZE on both tables, but it doesn't help.
Thanks in advance!
--
Thomas Braad Toft
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] PostgreSQL ignores my indexes
This is probably better on -performance, and is certainly a FAQ. But. . . On Wed, Feb 23, 2005 at 03:01:52PM +0100, Thomas Braad Toft wrote: > > Table device contains 5285 rows, tmeevent contains 834912 rows. ^^ > -> Seq Scan on tmeevent (cost=0.00..23606.12 rows=834912 width=138) > (actual time=0.04..2193.97 rows=834912 loops=1) ^^ > -> Seq Scan on device (cost=0.00..564.85 rows=5285 width=29) (actual > time=0.04..25.07 rows=5285 loops=1) > Why isn't the planner using my indexes? I tried making them as both rtree Because there's no advantage to using an index when you are fetching 100% of both tables. This is the most efficient plan. Of course, it's an open question whether you want to get 100% of both tables. But that's what you're doing, and using the index would be more expoensive than this. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] dblink versus schemas. What to use in this case?
Hi all, I have got two database schemas. They're rather independend. Thus they are in two databases. However there is one function that needs access to the other database. As I found out, I have two choices: *Using schemas and put the schemas tighter together (via interdependencies). Dumping distinct schemas is possible, however quistionable if a restore will work with the dependencies. *Using dblink. Dblink gives me a loose binding of the two databases. Some of us care about the 'contrib' status of dblink. Speed (connect, query, disconnect may sloww down) and it's deadlock resolv capabilities. However the deadlock thingy is just a question of interest. What should I do? to make one fcn of one DB access another DB's tables/fcns Thanks ---(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] Working with XML.
Title: Message
Hi Theo,
Hmm. Well we're getting into territory that's
over my head. I've simply been a user of xml2 and do not know much about
its inner workings. Just out of curiosity, what is the size of Sort_Mem in
your postgresql.conf?
Regards,
George
- Original Message -
From:
Theo Galanakis
To: 'George Weaver'
Cc: 'pgsql-sql@postgresql.org'
Sent: Tuesday, February 22, 2005 4:13
PM
Subject: Re: [SQL] Working with
XML.
George,
I have run this SP in
Cold Fusion, PgAdmin and EMS PostgreSQL Manager 2, with all the same results.
We are currently using Redhat ES3 and Postgres 7.4.5.
Theo
-Original
Message-From: George Weaver [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 23 February 2005 12:46 AMTo: Theo
GalanakisCc: pgsql-sql@postgresql.orgSubject:
Re: [SQL] Working with XML.
Hi Theo,
I am not able to duplicate the problem you
experienced. I tried the query you provided below using pgAdmin
and psql on a 7.3.2 and an 8.0 database with success. I then copied
all the rows and pasted them to the end so that I would have 100 rows, and
the query worked as expected.
Perhaps the client you're using is causing the
problem. Can you run the query in pgAdmin?
Regards,
George
- Original Message -
From:
Theo Galanakis
To: 'George Weaver'
Cc: 'pgsql-sql@postgresql.org'
Sent: Monday, February 21, 2005 11:54
PM
Subject: RE: [SQL] Working with
XML.
Thankyou George,
XML2 compiled ok... next
stummbling block.. when I pass a very long XML string
to xpath_string() it crashes the postgres server. Client receives a
message like:
server closed the connection unexpectedlyThis
probably means the server terminated abnormallybefore or while
processing the request.
Below is the function call, a smaller xml string
with say 40 records works fine, anything bigger crashes the
server.
select xpath_string(' 7741872226632005-02-22
__This
email, including attachments, is intended only for the addresseeand
may be confidential, privileged and subject to copyright. If youhave
received this email in error, please advise the sender and deleteit.
If you are not the intended recipient of this email, you must
notuse, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content
owner.
Re: [SQL] Comments on subquery performance
Typically, this is handled as (omitting '"'s and schema names for clarity) SELECT CatID1, CatID2, CatID3, t1.CatName as CatName1, t2.CatName as CatName2, t3.CatName as CatName3 FROM tblListings JOIN tblCategories t1 on CatId1 = CatID JOIN tblCategories t2 on CatId2 = CatID JOIN tblCategories t3 on CatId3 = CatID; Wether you need to LEFT JOIN tblCategories or not depends on how you want to do if CatID1, CatID2, or CatID3 is NULL. T- Bone wrote: (second attempt in two days to post this message...I appologise if for some reason a duplicate appears) Hello all, I created a query that contains two subqueries and joins and would like some feedback on whether: 1) this approach is logical; and, 2) if this is an optimal approach (performance wise) to return the records I seek. Essentially, I am attempting to perform a 'lookup' on a value in another table 3 times, for three different columns. I have three columns with category codes in tblListings and would like to 'lookup' the actual category text in tblCategory. I have created a functional query that contains two subqueries and joins, but am concerned this may not be the fastest (or logical?) way to achieve what I seek. I thought of another approach to create a function to evaluate the records on a row-by-row and column-by-column approach, but thought that may prove even slower. I would appreciate any comments on my logic or learning of any alternative means that would result in better performance. I have included the SQL for reference. Thanks in advance. Regards, Jim 8<- SELECT "CatID1", "CatID2", "CatID3", c1 AS "CatName1", c2 AS "CatName2", t6."CatName" AS "CatName3" FROM (SELECT "CatID1", "CatID2", "CatID3", c1, t4."CatName" AS c2 FROM (SELECT t1."CatID1", t1."CatID2", t1."CatID3", t2."CatName" AS c1 FROM "MySchema"."tblListings" t1 INNER JOIN "MySchema"."tblCategories" t2 ON (t1."CatID1" = t2."CatID")) t3 LEFT OUTER JOIN "MySchema"."tblCategories" t4 ON (t3."CatID2" = t4."CatID")) t5 LEFT OUTER JOIN "MySchema"."tblCategories" t6 ON (t5."CatID3" = t6."CatID"); 8<- _ Take advantage of powerful junk e-mail filters built on patented Microsoft® SmartScreen Technology. http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines Start enjoying all the benefits of MSN® Premium right now and get the first two months FREE*. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Edmund Bacon <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Row Count
Hai friends, How get Row count in a plpgsql fuction after executing update or delete query to determine no. of rows affected. Sreejith ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Row Count
On Wed, Feb 23, 2005 at 09:48:29PM +0530, sreejith s wrote: > How get Row count in a plpgsql fuction after executing update or > delete query to determine no. of rows affected. See "Obtaining the Result Status" in the PL/pgSQL documentation. http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] VIEW / ORDER BY + UNION
On Thu, Feb 17, 2005 at 23:46:59 +0800, WeiShang <[EMAIL PROTECTED]> wrote: > Hi, I have created a view like this : > > CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where > t1.orderno=t2.orderno); > > if I create a SQL statment: > > (SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno) > UNION > (SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno) > UNION > (SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno); > > Will the whole result will be sorted by the field orderno? If this isn't a made up example, you don't want to do this. You should use IN or OR to select records corresponding to the days of interest and then use ORDER BY to select the ordering. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] VIEW / ORDER BY + UNION
Otherwise you can treat this as a subselect and suround it with another select. Like select * from () order by orderno; C:\> -Original Message- C:\> From: Bruno Wolff III [mailto:[EMAIL PROTECTED] C:\> Sent: Mittwoch, 23. Februar 2005 18:20 C:\> To: WeiShang C:\> Cc: pgsql-sql@postgresql.org C:\> Subject: Re: [SQL] VIEW / ORDER BY + UNION C:\> C:\> C:\> On Thu, Feb 17, 2005 at 23:46:59 +0800, C:\> WeiShang <[EMAIL PROTECTED]> wrote: C:\> > Hi, I have created a view like this : C:\> > C:\> > CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where C:\> > t1.orderno=t2.orderno); C:\> > C:\> > if I create a SQL statment: C:\> > C:\> > (SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno) C:\> > UNION C:\> > (SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno) C:\> > UNION C:\> > (SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno); C:\> > C:\> > Will the whole result will be sorted by the field orderno? C:\> C:\> If this isn't a made up example, you don't want to do this. You C:\> should use IN or OR to select records corresponding to the days C:\> of interest and then use ORDER BY to select the ordering. C:\> C:\> ---(end of C:\> broadcast)--- C:\> TIP 7: don't forget to increase your free space map settings C:\> ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] VIEW / ORDER BY + UNION
On Wed, Feb 23, 2005 at 19:33:07 +0100, KÖPFERL Robert <[EMAIL PROTECTED]> wrote: > > Otherwise you can treat this as a subselect and suround it with another > select. > > Like > select * from () order by orderno; That is still a bad idea in this case. He will end up scanning the table three times to pick up the three days and there will be a sort for each union to remove duplicates (which there shouldn't be if orderno is a candidate key). > > C:\> -Original Message- > C:\> From: Bruno Wolff III [mailto:[EMAIL PROTECTED] > C:\> Sent: Mittwoch, 23. Februar 2005 18:20 > C:\> To: WeiShang > C:\> Cc: pgsql-sql@postgresql.org > C:\> Subject: Re: [SQL] VIEW / ORDER BY + UNION > C:\> > C:\> > C:\> On Thu, Feb 17, 2005 at 23:46:59 +0800, > C:\> WeiShang <[EMAIL PROTECTED]> wrote: > C:\> > Hi, I have created a view like this : > C:\> > > C:\> > CREATE VIEW v1 AS (SELECT orderno,weekday,time FROM t1,t2 where > C:\> > t1.orderno=t2.orderno); > C:\> > > C:\> > if I create a SQL statment: > C:\> > > C:\> > (SELECT orderno FROM v1 WHERE weekday='MON' ORDER BY orderno) > C:\> > UNION > C:\> > (SELECT orderno FROM v1 WHERE weekday='WED' ORDER BY orderno) > C:\> > UNION > C:\> > (SELECT orderno FROM v1 WHERE weekday='FRI' ORDER BY orderno); > C:\> > > C:\> > Will the whole result will be sorted by the field orderno? > C:\> > C:\> If this isn't a made up example, you don't want to do this. You > C:\> should use IN or OR to select records corresponding to the days > C:\> of interest and then use ORDER BY to select the ordering. > C:\> > C:\> ---(end of > C:\> broadcast)--- > C:\> TIP 7: don't forget to increase your free space map settings > C:\> > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Making NULL entries appear first when ORDER BY ASC
How would one know from the reference material that it is possible to include IS NOT NULL in an ORDER BY clause? Similarly, other than the FAQ, I've never been able to tell from the SELECT documentation why ORDER BY random() works. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 16, 2005, at 3:39 AM, Andreas Joseph Krogh wrote: On Wednesday 16 February 2005 04:47, Bruno Wolff III wrote: Now, as you see, touples with NULL in the "start_time"-field appear "after" the others. I would like to make all entries where start_time IS NULL apear *before* all the others. Any idea how to achieve this? SELECT start_date, start_time, end_time, title FROM onp_crm_activity_log WHERE start_date IS NOT NULL ORDER BY start_date ASC, start_time IS NOT NULL ASC, start_time ASC; This assumes you want the NULL start times first within a particular date. Otherwise change the order in the ORDER BY clause. Thanks! This si, IMO, the cleanest solution as it doesn't involve any COALESCE. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Making NULL entries appear first when ORDER BY
Well, for the docs to list every possible conditional-statement for an order by clause would just about include them all, so be all the more confusing. Sub queries, IN, aggregate functions, aliases.. the list goes on and on. I'd say that knowledge (that most conditionals can be used in an order-by or group-by) should be implicit once a person has a basic understanding of the language. Thomas F.O'Connell wrote: How would one know from the reference material that it is possible to include IS NOT NULL in an ORDER BY clause? Similarly, other than the FAQ, I've never been able to tell from the SELECT documentation why ORDER BY random() works. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Feb 16, 2005, at 3:39 AM, Andreas Joseph Krogh wrote: On Wednesday 16 February 2005 04:47, Bruno Wolff III wrote: Now, as you see, touples with NULL in the "start_time"-field appear "after" the others. I would like to make all entries where start_time IS NULL apear *before* all the others. Any idea how to achieve this? SELECT start_date, start_time, end_time, title FROM onp_crm_activity_log WHERE start_date IS NOT NULL ORDER BY start_date ASC, start_time IS NOT NULL ASC, start_time ASC; This assumes you want the NULL start times first within a particular date. Otherwise change the order in the ORDER BY clause. Thanks! This si, IMO, the cleanest solution as it doesn't involve any COALESCE. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Making NULL entries appear first when ORDER BY ASC
On Wed, Feb 23, 2005 at 13:54:50 -0600,
"Thomas F.O'Connell" <[EMAIL PROTECTED]> wrote:
> How would one know from the reference material that it is possible to
> include IS NOT NULL in an ORDER BY clause?
>
> Similarly, other than the FAQ, I've never been able to tell from the
> SELECT documentation why ORDER BY random() works.
>From the SELECT command documentation:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]
Notice that for ORDER BY you can supply an expression. That should be a big
clue why you can use IS NOT NULL and random() in an ORDER BY clause.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Making NULL entries appear first when ORDER BY ASC
Yup. Got it. Wasn't thinking clearly about what expression meant.
Thanks!
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Feb 23, 2005, at 2:33 PM, Bruno Wolff III wrote:
On Wed, Feb 23, 2005 at 13:54:50 -0600,
"Thomas F.O'Connell" <[EMAIL PROTECTED]> wrote:
How would one know from the reference material that it is possible to
include IS NOT NULL in an ORDER BY clause?
Similarly, other than the FAQ, I've never been able to tell from the
SELECT documentation why ORDER BY random() works.
From the SELECT command documentation:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]
Notice that for ORDER BY you can supply an expression. That should be
a big
clue why you can use IS NOT NULL and random() in an ORDER BY clause.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] dblink versus schemas. What to use in this case?
Robert, A couple of possible issues -- Running two different databases (on the same server) implies more use of system resources, but may be slightly more robust (i.e. one could go down but the other would still work). dblink is certainly slower than refering to a table in a schema, but it seems to work reasonably well, as least in talking between two databases on the same server (I've not really tested it between servers but it would obviously be slower depending on one's network). If you want to enforce referential integrity then a schema is the way to go; schemas have permissions so it should be possible to lock out unwanted users almost as effectively as if there were two databases. I have a database that uses schemas fairly heavily (in a postgres 7.4 installation) and I have had to edit my restore scripts -- partly because the script is confused by all of the ALTER statements I needed, and partly to get schema restored in the corect order. Version 8 may be better but might still need some manual editing of the restore script. I used schemas to simplify scripts and maitain references. HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: KÖPFERL Robert [mailto:[EMAIL PROTECTED] Sent: Wed 2/23/2005 6:33 AM To: pgsql-sql@postgresql.org Cc: Subject:[SQL] dblink versus schemas. What to use in this case? Hi all, I have got two database schemas. They're rather independend. Thus they are in two databases. However there is one function that needs access to the other database. As I found out, I have two choices: *Using schemas and put the schemas tighter together (via interdependencies). Dumping distinct schemas is possible, however quistionable if a restore will work with the dependencies. *Using dblink. Dblink gives me a loose binding of the two databases. Some of us care about the 'contrib' status of dblink. Speed (connect, query, disconnect may sloww down) and it's deadlock resolv capabilities. However the deadlock thingy is just a question of interest. What should I do? to make one fcn of one DB access another DB's tables/fcns Thanks ---(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 !DSPAM:421c94cc83679760939685! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Working with XML.
Title: Message
Hi
George,
I sent out a message shortly after this that didn't get
through. However it's good news, I rebooted the server and it appears to work
fine now. Thankyou for all your help, appreciated!!
Theo
-Original Message-From:
George Weaver [mailto:[EMAIL PROTECTED] Sent: Thursday, 24 February
2005 2:00 AMTo: Theo GalanakisCc:
pgsql-sql@postgresql.orgSubject: Re: [SQL] Working with
XML.
Hi Theo,
Hmm. Well we're getting into territory
that's over my head. I've simply been a user of xml2 and do not know
much about its inner workings. Just out of curiosity, what is the size
of Sort_Mem in your postgresql.conf?
Regards,
George
- Original Message -
From:
Theo Galanakis
To: 'George Weaver'
Cc: 'pgsql-sql@postgresql.org'
Sent: Tuesday, February 22, 2005 4:13
PM
Subject: Re: [SQL] Working with
XML.
George,
I have run this SP in
Cold Fusion, PgAdmin and EMS PostgreSQL Manager 2, with all the same
results. We are currently using Redhat ES3 and Postgres
7.4.5.
Theo
-Original
Message-From: George Weaver [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 23 February 2005 12:46 AMTo: Theo
GalanakisCc: pgsql-sql@postgresql.orgSubject:
Re: [SQL] Working with XML.
Hi Theo,
I am not able to duplicate the problem you
experienced. I tried the query you provided below using pgAdmin
and psql on a 7.3.2 and an 8.0 database with success. I then copied
all the rows and pasted them to the end so that I would have 100 rows, and
the query worked as expected.
Perhaps the client you're using is causing
the problem. Can you run the query in pgAdmin?
Regards,
George
- Original Message -
From:
Theo Galanakis
To: 'George Weaver'
Cc: 'pgsql-sql@postgresql.org'
Sent: Monday, February 21, 2005
11:54 PM
Subject: RE: [SQL] Working with
XML.
Thankyou George,
XML2 compiled ok... next
stummbling block.. when I pass a very long XML string
to xpath_string() it crashes the postgres server. Client receives a
message like:
server closed the connection
unexpectedlyThis probably means the server terminated
abnormallybefore or while processing the
request.
Below is the function call, a smaller xml
string with say 40 records works fine, anything bigger crashes the
server.
select xpath_string(' 7741872226632005-02-22
__This
email, including attachments, is intended only for the
addresseeand may be confidential, privileged and subject to
copyright. If youhave received this email in error, please advise
the sender and deleteit. If you are not the intended recipient of
this email, you must notuse, copy or disclose its content to
anyone. You must not copy or communicate to others content that is
confidential or subject to copyright, unless you have the consent
of the content
owner.
Re: [SQL] aggregate / group by question
T E Schmitz wrote:
Hello,
I must apologize for not coming up with a more descriptive subject line.
I am struggling with the following query and I am not even sure
whether what I want to achieve is possible at all:
The problem in real-world terms: The DB stores TRANSAKTIONS - which
are either sales or refunds: each TRANSAKTION has n ITEMS related to
it, which contain their RETAIL_PRICE and DISCOUNT. At the end of day,
a total is run up, which should show the sum of refunds, sales and
discounts.
Tables:
TRANSAKTION
---
KIND ('R' or 'S' for refund or sale)
TRANSAKTION_PK
PAYMENT_METHOD (cheque, cash, CC)
ITEM
TRANSAKTION_FK
ITEM_PK
RETAIL_PRICE
DISCOUNT
Desired result set:
PAYMENT_METHOD | category | SUBTOTAL
Cash | sales| 103,55
Cash | discounts| -0,53
Cash | refunds | -20,99
CC | sales| 203,55
CC | discounts| -5,53
CC | refunds | -25,99
where
sales amount is the sum of RETAIL_PRICE
discount amount is the sum of DISCOUNT
refunds is the sum of (RETAIL_PRICE-DISCOUNT)
I've had a stab at it but my sales amount is short of the
RETAIL_PRICEs of all discounted ITEMs:
select PAYMENT_METHOD,
case
when KIND='R' then 'R'
when KIND='S' and DISCOUNT is not null then 'D'
when KIND='S' and DISCOUNT is null then 'S'
end as CATEGORY,
sum(case
when KIND=2 then -(RETAIL_PRICE-coalesce(DISCOUNT,0))
when KIND=1 and DISCOUNT is not null then -DISCOUNT
when KIND=1 and DISCOUNT is null then RETAIL_PRICE
end) as SUBTOTAL,
from ITEM
inner join TRANSAKTION on TRANSAKTION_PK =TRANSAKTION_FK
where ...
group by PAYMENT_METHOD,CATEGORY
order by PAYMENT_METHOD,CATEGORY
Your comment implies that the amount of retail sales is the sum of all
amounts regardless of whether or not discount IS NULL. So perhaps
losing the 'IS NULL' from you retail_price case may fix your statement.
If not...
This may be a bit heavy handed AND I am still a novice AND I am not on
my system so I can't test it but how about
SELECT merged_data.payment_method,
merged_data.category,
merged_data.subtotal
FROM (
-- Get the refunds. (kind = 'R')
SELECT transaktion.payment_method,
'refunds' AS category,
-1 * sum( item.retail_price - COALESCE(item.discount) )
AS subtotal
FROM transaktion
LEFT OUTER JOIN item
ON ( transaktion.transaktion_pk = item.transaktion_fk )
WHERE transaktion.kind = 'R'
GROUP BY transaktion.payment_method
UNION ALL
-- Get the sales. (kind = 'S')
SELECT transaktion.payment_method,
'sales' AS category,
sum( item.retail_price - COALESCE(item.discount, 0) ) AS
subtotal
FROM transaktion
LEFT OUTER JOIN item
ON ( transaktion.transaktion_pk = item.transaktion_fk )
WHERE transaktion.kind = 'S'
GROUP BY transaktion.payment_method
UNION ALL
-- Get the discounts. (kind = 'S' AND discount IS NOT NULL)
SELECT transaktion.payment_method,
'discounts' AS category,
-1 * sum( COALESCE(item.discount, 0) ) AS subtotal
FROM transaktion
LEFT OUTER JOIN item
ON ( transaktion.transaktion_pk = item.transaktion_fk )
WHERE transaktion.kind = 'S'
AND transaktion.discount IS NOT NULL
GROUP BY transaktion.payment_method
) AS merged_data
ORDER BY merged_data.payment_method,
merged_data.category;
--
HTH
Kind Regards,
Keith
---(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] Postgres performance
Hi, I understand this is an super-older thread!! note: i like postgres and not mysql! I'm a Postgres server user: I've postgres 7.2 and 8.0 in many servers and I've tested performance Postgres vs MySQL and I see Postgres is very slow. But my question is: why? Cannot Postgres team make faster because cannot change internal architecture? Ok, postgres supports SUB-query and many super-feature, but mySQL now supports it and is faster! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Postgres 8 - problem: invalid input syntax for integer
Hi, In previous version di Postgres (7.2) I used this table: CREATE TABLE tablename (id serial, field int1, field2 text); Now this query work: UPDATE tablename SET field1=''; (NOTE: implicit conversion to 0) UPDATE tablename SET field2=''; (this cause of simple code-generation query - I don't know what's field type) Now in postgres 8 this don't work. Why ?(ok, it's the ufficial documentation but I don't understand... why? it's so comfortable!) Can someone help me to create a CAST to re-use this feature? Thank you! Mauro ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Software for database-visualisation
Another question: Which software are you using to visualize your database-structur. We're doing it with Quark, but are not very happy with this. Thanks Kai... -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Viele, die die schaendlichsten Handlungen begehen, fuehren hoechst vernuenftige Reden. (Demokrit, um 460 v. Chr.) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Advanced SELECT
Hi Folks, I hope to have found the right group for my question. I have difficult sql-task. I try to describe it simple: We have a table 'company' with a cid and a table 'sector' with a sid. They are connected m:n via a third table 'company_sector' which contians csid, cid and sid. The normal clause would look like: SELECT c.companyname, s.sectorname FROM company c, sector s, company_sector cs WHERE cs.cid = c.cid AND cs.sid = s.sid ORDER BY c.companyname; This gives a result looking like this: c.companyname | s.sector ---+ company1 | sectora company1 | sectorb company2 | sectora company2 | sectorb company2 | sectorc company3 | sectora company4 | sectorc instead of this I want to have a listing like: c.companyname | ??? (sectors) ---+ company1 | sectora, sectorb company2 | sectora, sectorb, sectorc company3 | sectora company4 | sectorc But I have no idea, how to write a SELECT-command that gives a listing like this :( Maybe anyone can help *please* *Thanks and greetings* Kai... -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Unzeitige Genuesse erzeugen Ekel. (Demokrit, um 460 v. Chr.) ---(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
[SQL] Junk queries with variables?
I really have to be missing something here and this probably a *really* noob question. I don't have a problem running little junk queries in the pgAdmin query window(SELECT blah FROM blah, INSERT INTO blah, etc...), but I can't figure out how to run queries with variables outside of a function. I just want to use variables without having to go about creating and dropping a function for every stupid little query I need to write. Example: amount int4 := 1000; earliest_date timestamp := current_timestamp; SELECT ... I always get the error: "ERROR: syntax error at or near "amount" at character 1". What have I done wrong, or am I missing? Thanks, Steve ---(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] Junk queries with variables?
On Wed, Feb 23, 2005 at 11:12:47PM -0700, Steve - DND wrote: > I really have to be missing something here and this probably a *really* noob > question. I don't have a problem running little junk queries in the pgAdmin > query window(SELECT blah FROM blah, INSERT INTO blah, etc...), but I can't > figure out how to run queries with variables outside of a function. I just > want to use variables without having to go about creating and dropping a > function for every stupid little query I need to write. I don't know about pgAdmin, but in psql you can use \set: \set id 1 SELECT * FROM foo WHERE id = :id; \set name '\'Some Name\'' SELECT * FROM foo WHERE name = :name; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
