Re: [SQL] need help
On 5/14/07, Penchalaiah P. <[EMAIL PROTECTED]> wrote: Any one can help in this Operating system? Postgres version? How does psql behave? Anything in the logs? Cheers, Andrej ---(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] need help
Anyone else is using this table simulteniously? With Regards Ashish... - Original Message - From: Penchalaiah P. To: pgsql-sql@postgresql.org Sent: Monday, May 14, 2007 12:20 PM Subject: [SQL] need help Hi . Create table cdano_nya(cdano int4,nyano int4) . I created this table and then I inserted some values to this( 234576,86). Now when I am updating this table .. its not updating ..query is continuously running. When I am stopping query its giving this message..ERROR: canceling statement due to user request.. May I know the reason y its not running.. and I am unable to drop this table also.when I am selecting this table in pgAdmin..its strucking the pgAdmin... Any one can help in this Thanks & Regards Penchal Reddy Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
[SQL] select ..... not in .....
I folks.
I'm struggling with a select which should be easy.
select v_d_code, v_o_number, v_vin, v_status from vista_details where
v_d_code='64340' and v_o_number='C0023';
v_d_code | v_o_number |v_vin| v_status
--++-+-
64340| C0023 | GCDW7D37645 | COMPOUND IN
(1 row)
shows that the record exists in vista.
select w_vin from walon where substring(w_vin from '(.{11}$)')
= 'GCDW7D37645';
w_vin
---
(0 rows)
shows that it doesn't exist in walon.
So, who does this select not return the row?
select v_d_code, v_o_number, v_vin, v_status from vista_details where v_vin
not in (
goole(# select substring(w_vin from '(.{11}$)') from walon);
v_d_code | v_o_number | v_vin | v_status
--++---+--
(0 rows)
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] select ..... not in .....
Gary Stainburn <[EMAIL PROTECTED]> writes:
> So, who does this select not return the row?
> select v_d_code, v_o_number, v_vin, v_status from vista_details where v_vin
> not in (
> goole(# select substring(w_vin from '(.{11}$)') from walon);
NOT IN with a sub-select that returns any NULL values cannot succeed;
the result is either FALSE (definite match) or NULL (because of the
NULL comparison results).
You could work around that with a COALESCE, but I think a less klugy
and better-performing answer would be to write it as a left join:
select v_d_code, v_o_number, v_vin, v_status from vista_details
left join walon on (v_vin = substring(w_vin from '(.{11}$)'))
where walon.some-never-null-column IS NULL;
The where-clause rejects any actual join matches...
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Temporal Table Relations and Referential Integrity
On May 7, 2007, at 9:59 , Richard Broersma Jr wrote: However, since temporal UPDATEs and DELETEs do not behave in the same way as they do in non-temporal tables, is anyone able to successfully use Referential Integrity constraints between Parent and Child tables? Or are custom triggers the only solution to maintain temporal relations between Parents and Children? Currently constraint triggers are the only way I know how to get this to work. My question deals specifically with the "Current" style of temporal relations, where only the currently known attributes and attribute histories are stored. It'd be helpful if you provided links to what you refer to as the Current style. The only reference for managing temporal data in ANSI SQL I'm aware of is Snodgrass' "Developing Time-Oriented Database Applications in SQL"[1]. Date, Darwen, and Lorentzos' "Temporal Data and the Relational Model"[2] is a more general text on the same topic, but isn't directly applicable to ANSI SQL. As a second question, would anyone know if temporal referential integrity is targeted for future inclusion into the SQL standard? I'm not privy to discussions of the SQL committees :) AIUI, temporal support was included in some of drafts for but it did not make it into the final standard. Snodgrass' book and Darwen & Date's paper "An Overview and Analysis of TSQL2"[3] have some discussion and critique of the draft. I would be happy to elaborate on what I mean by Temporal Referential-Integrity (RFI) if my questions are unclear. I'm not sure what *you* mean by Temporal Referential-Integrity, but I hope the links I've provided help a bit. Michael Glaesemann grzm seespotcode net [1](http://www.cs.arizona.edu/~rts/tdbbook.pdf) [2](http://www.amazon.com/gp/product/1558608559/) [3](http://www.dcs.warwick.ac.uk/~hugh/TTM/OnTSQL2.pdf) ---(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] Temporal Table Relations and Referential Integrity
--- Michael Glaesemann <[EMAIL PROTECTED]> wrote: > It'd be helpful if you provided links to what you refer to as the > Current style. The only reference for managing temporal data in ANSI > SQL I'm aware of is Snodgrass' "Developing Time-Oriented Database > Applications in SQL"[1]. Date, Darwen, and Lorentzos' "Temporal Data > and the Relational Model"[2] is a more general text on the same > topic, but isn't directly applicable to ANSI SQL. The "Current style" should actually be called "Current State". There are three temporal models discussed in the Snodgrass book: Current, Sequenced, and Non-Sequenced State Tables. This is described in Chapter 7 sections 1-3. The Current State table only models currently known and historical values of the database. Sequenced and Non-Sequenced can model past/present/future data. The temporal relationship that I am interested in modeling is a temporal parent that is related to a temporal child. I wanted to see if any method exists to support Referential Integrity that can handle Temporal ON UPDATE CASCADE and ON DELETE CASCADE between the parent and child tables. Snodgrass page 127 code fragment 5.20 uses a "CREATE ASSERTION" to maintain RI for CURRENT STATE data between a temporal parent and child. However, I am looking for a method to extend this example to allow ON UPDATE CASCADE and ON DELETE CASCADE, since the CREATE ASSERTION method will only prevent you from from altering the data in a way so as to make it inconsistent between the two tables. > [2](http://www.amazon.com/gp/product/1558608559/) I did purchase the Chris Date book, but I haven't spend any time reading it since its illustrations use Tutorial-D instead of SQL. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] How to retrieve a n-ary tree in SQL?
Hi, Is there a way to get a query (of function) to retrieve a n-ary tree: 1 parent node has n children and the tree is n levels deep? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(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] need help
On 5/14/07, Penchalaiah P. <[EMAIL PROTECTED]> wrote: Hi … Create table cdano_nya(cdano int4,nyano int4) … I created this table and then I inserted some values to this( 234576,86)… Now when I am updating this table .. its not updating ..query is continuously running… When I am stopping query its giving this message….ERROR: canceling statement due to user request.. May I know the reason y its not running.. and I am unable to drop this table also…when I am selecting this table in pgAdmin..its strucking the pgAdmin….. Any one can help in this What does your query look like? Are you using locking or transactions where other queries are blocking your query from running? -Aaron -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
Re: [SQL] How to retrieve a n-ary tree in SQL?
On 5/14/07, Joost Kraaijeveld <[EMAIL PROTECTED]> wrote: Is there a way to get a query (of function) to retrieve a n-ary tree: 1 parent node has n children and the tree is n levels deep? See: http://search.postgresql.org/search?q=tree&m=1&l=4&d=365&s=r ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] pg_dump?
PostgreSQL begginer. Whats the best way of exporting(dumping) a database/table/view/query to a file? I am using PostgreSQL v 8.2. I am starting postgres server with C:\WINDOWS\system32\net.exe start pgsql-8.2, (start service) C:\postgresql-8.2.3-1\bin\psql.exe -h localhost -p 5432 postgres "Richard" (psql to 'postgres') both of which are run from shortcuts in the start menu. my cli starts with "postgres #", then "databasename #" after connecting using \c. Richard. Get the free Yahoo! toolbar and rest assured with the added security of spyware protection. http://new.toolbar.yahoo.com/toolbar/features/norton/index.php ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Doing a conditional aggregate (e.g. count(*) if x=y) in postgres?
All; Is there a way to get a conditional aggregate? I have this two column view: SELECT count(*) AS count, xx_plan.plan_name FROM xx_membership JOIN xx_account USING (account_id) JOIN xx_plan USING (plan_id) WHERE xx_membership.status = 10 GROUP BY xx_plan.plan_name; And would like to add additional columns (not rows) breaking out "status=20" and "status=30" totals. Is this possible without a stored procedure? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] pg_dump?
Richard Dunne wrote: PostgreSQL begginer. Whats the best way of exporting(dumping) a database/table/view/query to a file? I am using PostgreSQL v 8.2. I am starting postgres server with C:\WINDOWS\system32\net.exe start pgsql-8.2, (start service) C:\postgresql-8.2.3-1\bin\psql.exe -h localhost -p 5432 postgres "Richard" (psql to 'postgres') both of which are run from shortcuts in the start menu. my cli starts with "postgres #", then "databasename #" after connecting using \c. Richard. With exactly what you said in the subject... pg_dump http://www.postgresql.org/docs/8.2/static/app-pgdump.html Your other option would be with an SQL 'COPY' http://www.postgresql.org/docs/8.2/static/sql-copy.html The difference between the two is dump will give you output including the SQL used to put it back into the database, i.e. INSERT INTO statements whereas copy will give you a flat delimited file. -- Paul Lambert Database Administrator AutoLedgers ---(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] Doing a conditional aggregate (e.g. count(*) if x=y) in postgres?
Use case statement and sum to get a count where status=20... For example Select sum(case when status=20 then 1 else 0 end) as status20, Sum(case when status=30 then 1 else 0 end) as status30 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bryce Nesbitt Sent: Monday, May 14, 2007 6:56 PM To: pgsql-sql@postgresql.org Subject: [SQL] Doing a conditional aggregate (e.g. count(*) if x=y) in postgres? All; Is there a way to get a conditional aggregate? I have this two column view: SELECT count(*) AS count, xx_plan.plan_name FROM xx_membership JOIN xx_account USING (account_id) JOIN xx_plan USING (plan_id) WHERE xx_membership.status = 10 GROUP BY xx_plan.plan_name; And would like to add additional columns (not rows) breaking out "status=20" and "status=30" totals. Is this possible without a stored procedure? ---(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] Doing a conditional aggregate (e.g. count(*) if x=y) in postgres?
Bryce Nesbitt wrote: All; Is there a way to get a conditional aggregate? I have this two column view: SELECT count(*) AS count, xx_plan.plan_name FROM xx_membership JOIN xx_account USING (account_id) JOIN xx_plan USING (plan_id) WHERE xx_membership.status = 10 GROUP BY xx_plan.plan_name; And would like to add additional columns (not rows) breaking out "status=20" and "status=30" totals. Is this possible without a stored procedure? SELECT sum(CASE WHEN xx_membership.status = 10 THEN 1 ELSE 0 END) AS sum10, sum(CASE WHEN xx_membership.status = 20 THEN 1 ELSE 0 END) AS sum20, sum(CASE WHEN xx_membership.status = 30 THEN 1 ELSE 0 END) AS sum30, xx_plan.plan_name FROM xx_membership JOIN xx_account USING (account_id) JOIN xx_plan USING (plan_id) WHERE xx_membership.status IN (10,20,30) GROUP BY xx_plan.plan_name; You may or may not care about including the WHERE clause there depending upon its selectivity and whether there's an index for the planner to use. HTH, Geoff ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Doing a conditional aggregate (e.g. count(*) if x=y) in postgres?
Ah perfect. I was struggling with CASE outside the SUM(), which was not working. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...)';"?
Look at this problem:
when
execute 'insert into lse_installations values(' || ||obj_id|| || ',' ||
||div|| || ',' || ||sub|| || ',' || ||obj_type|| || ','
|| ||obj_name|| || ',' || ||pstcd|| || ',' || ||rdcd||
|| ',' || ||blkno|| || ',' || ||vldunt|| || ','|| cenlat || ','
|| cenlon || ')';
because obj_name from another table has value like this:S'pore High Polymer.
Following error raises:
ERROR: syntax error at or near "pore"
SQL state: 42601
Context: PL/pgSQL function "lse_installations" line 64 at execute statement
So how to process the single inverted comma in char variable?It makes me so
desperate.
_
与世界各地的朋友进行交流，免费下载 Live Messenger; http://get.live.com/messenger/overview
---(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] How to process inverted comma in "EXECUTE 'insert into xxx values(...)';"?
On 5/14/07, Nemo Terry <[EMAIL PROTECTED]> wrote:
Look at this problem:
when
execute 'insert into lse_installations values(' || ||obj_id|| || ',' ||
||div|| || ',' || ||sub|| || ',' || ||obj_type|| || ','
|| ||obj_name|| || ',' || ||pstcd|| || ',' || ||rdcd||
|| ',' || ||blkno|| || ',' || ||vldunt|| || ','|| cenlat || ','
|| cenlon || ')';
because obj_name from another table has value like this:S'pore High Polymer.
Following error raises:
ERROR: syntax error at or near "pore"
SQL state: 42601
Context: PL/pgSQL function "lse_installations" line 64 at execute statement
So how to process the single inverted comma in char variable?It makes me so
desperate.
Why are you EXECUTEing the INSERT command? It's directly supported in
plpgsql, since it is a superset of SQL. That is, you can do:
INSERT INTO lse_installations
VALUES (obj_id, div, sub, obj_type, obj_name, pstcd, rdcd, blkno, vldunt
, cenlat, cenlon);
Good luck.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [SQL] How to process inverted comma in "EXECUTE 'insert into xxx values(...)
But I must use it in function,so...
Do you have another solution?
From: "Rodrigo De Le�n" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
CC: "Nemo Terry" <[EMAIL PROTECTED]>
Subject: Re: [SQL] How to process inverted comma in "EXECUTE 'insert into xxx
values(...)';"?
Date: Tue, 15 May 2007 01:25:25 -0500
On 5/14/07, Nemo Terry <[EMAIL PROTECTED]> wrote:
Look at this problem:
when
execute 'insert into lse_installations values(' ||
||obj_id|| || ',' || ||div|| || ',' ||
||sub|| || ',' || ||obj_type|| || ',' ||
||obj_name|| || ',' || ||pstcd|| || ',' ||
||rdcd|| || ',' || ||blkno|| || ',' ||
||vldunt|| || ','|| cenlat || ',' || cenlon || ')';
because obj_name from another table has value like this:S'pore High
Polymer.
Following error raises:
ERROR: syntax error at or near "pore"
SQL state: 42601
Context: PL/pgSQL function "lse_installations" line 64 at execute
statement
So how to process the single inverted comma in char variable?It
makes me so desperate.
Why are you EXECUTEing the INSERT command? It's directly supported
in
plpgsql, since it is a superset of SQL. That is, you can do:
INSERT INTO lse_installations
VALUES (obj_id, div, sub, obj_type, obj_name, pstcd, rdcd,
blkno, vldunt
, cenlat, cenlon);
Good luck.
---(end of
broadcast)---
TIP 6: explain analyze is your friend
_
与联机的朋友进行交流，请使用 Live Messenger; http://get.live.com/messenger/overview
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
