[SQL] hi i am getting error...can u solve this
Hi can u tell me about this problem.. cda_no bp npa rp dp pay_change_date 234597D 7900 400 1000 1000 2006-09-05 234597D 8250 400 1000 1000 2005-09-05 234575E 4000 200 333 111 2004-02-02 234575E 4000 300 333 111 2004-09-02 This is the table it contains some sample data.. but I want to see difference between Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com 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 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 mail from your records. 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.
Re: [SQL] hi i am getting error...can u solve this
On Thu, Nov 02, 2006 at 06:19:45PM +0530, Penchalaiah P. wrote: > > This is the table it contains some sample data.. but I want to see > difference between Between what? A -- Andrew Sullivan | [EMAIL PROTECTED] "The year's penultimate month" is not in truth a good way of saying November. --H.W. Fowler ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Case Preservation disregarding case
"Simon Riggs" <[EMAIL PROTECTED]> writes: > We have namespaces to differentiate between two sources of object names, > so anybody who creates a schema where MyColumn is not the same thing as > myColumn is not following sensible rules for conceptual distance. I'd agree that that is not a good design practice, but the fact remains that they *are* different per spec. > Would be better to make this behaviour a userset > switchable between the exactly compliant and the more intuitive. That's certainly not happening --- if you make any changes in the semantics of equality of type name, it would have to be frozen no later than initdb time, for exactly the same reasons we freeze locale then (hint: index ordering). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] hi i am getting error...can u solve this
What difference do you want to see? . Looks like your email got truncated. Please resend. There is not enough information to determine what is wanted. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Penchalaiah P. Sent: Thursday, November 02, 2006 4:50 AM To: [email protected] Subject: [SQL] hi i am getting error...can u solve this Hi can u tell me about this problem.. cda_no bp npa rp dp pay_change_date 234597D 7900 400 1000 1000 2006-09-05 234597D 8250 400 1000 1000 2005-09-05 234575E 4000 200 333 111 2004-02-02 234575E 4000 300 333 111 2004-09-02 This is the table it contains some sample data.. but I want to see difference between Thanks & Regards Penchal reddy | Software Engineer Infinite Computer Solutions | Exciting Times…Infinite Possibilities... SEI-CMMI level 5 | ISO 9001:2000 IT SERVICES | BPO Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government Tel +91-80-5193-(Ext:503)| Fax +91-80-51930009 | Cell No +91-9980012376|www.infics.com 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 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 mail from your records. 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.
Re: [SQL] [GENERAL] Is there anyway to...
am Thu, dem 02.11.2006, um 13:14:22 -0500 mailte louis gonzales folgendes: > Hello all, > Is there an existing mechanism is postgresql that can automatically > increment/decrement on a daily basis w/out user interaction? The use You can use CRON for such tasks. I hope for, you have a operating system with a CRON... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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] [GENERAL] Is there anyway to...
Or you can probably use a PostgreSQL administration tool for scheduled jobs. I know a number of such tools which provide this feature and EnterpriseDB Management Server is one of them. --Imad www.EnterpriseDB.com On 11/2/06, A. Kretschmer <[EMAIL PROTECTED]> wrote: am Thu, dem 02.11.2006, um 13:14:22 -0500 mailte louis gonzales folgendes: > Hello all, > Is there an existing mechanism is postgresql that can automatically > increment/decrement on a daily basis w/out user interaction? The use You can use CRON for such tasks. I hope for, you have a operating system with a CRON... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] [GENERAL] Is there anyway to...
am Fri, dem 03.11.2006, um 0:04:50 +0500 mailte imad folgendes: > Or you can probably use a PostgreSQL administration tool for scheduled > jobs. I know a number of such tools which provide this feature and > EnterpriseDB Management Server is one of them. > > --Imad > www.EnterpriseDB.com Yeah! ;-) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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] [GENERAL] Is there anyway to...
Yeah, and EnterpriseDB Management Server is a community project and can be used for free, off course! --Imad www.EnterpriseDB.com On 11/3/06, A. Kretschmer <[EMAIL PROTECTED]> wrote: am Fri, dem 03.11.2006, um 0:04:50 +0500 mailte imad folgendes: > Or you can probably use a PostgreSQL administration tool for scheduled > jobs. I know a number of such tools which provide this feature and > EnterpriseDB Management Server is one of them. > > --Imad > www.EnterpriseDB.com Yeah! ;-) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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 2: Don't 'kill -9' the postmaster
[SQL] Determining correct table order for insert or drop statements to satisfy foreign keys
Hello, First, sorry if this has already been covered, though I didn't find anything similar in the archives. Also sorry if this is the wrong list for this type of question, though it seemed to be the most relevant list for this question. On to the question: I'm writing a simple import script that reads in an excel file that a customer fills out. Each worksheet represents a different table and the tables' fields are columns in each worksheet. The tables represented in the file are variable, I have no idea what table combinations will be in the excel file when it's sent to the script. What I need is a way for the script to determine what order it should process the worksheets in the excel file. For example, you have this table structure (each table would be a worksheet): PERSON (1) <--> (many) ORDER (1) <--> (many) ORDER_ITEM Obviously, the people would have to be inserted first, then the orders, and finally the items, to keep the foreign keys between the tables happy. Is there a mechanism in PostgreSQL that would tell me this order? Or is it something that would have to be assembled from analyzing the schema? Anybody needed to perform an operation like this before? Any help/direction would be greatly appreciated. Thanks for your time and help, Pete Hanson ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] [GENERAL] Is there anyway to...
imad wrote: Or you can probably use a PostgreSQL administration tool for scheduled jobs. I know a number of such tools which provide this feature and EnterpriseDB Management Server is one of them. As is pgAdmin's pgAgent. Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] The empty list?
Hi. Is there someone who can elaborate on why the "empty list" is'nt implemented in Postgresql? This works: # select 1 in (1,2,3); ?column? -- t (1 row) And this works: jesper=# select * from test; id (0 rows) jesper=# select 1 in (select id from test); ?column? -- f (1 row) Whereas this gives a syntax error: # select 1 in (); ERROR: syntax error at or near ")" at character 14 LINE 1: select 1 in (); ^ If everyone where writing their SQL by hand .. this would probably not be a problem, but nowadays lots of people use ORM's for accessing the database.. thus it would be nice if the SQL implementation was a bit more generic like a "language". Jesper -- Jesper Krogh, [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] The empty list?
Jesper Krogh <[EMAIL PROTECTED]> writes: > Whereas this gives a syntax error: > # select 1 in (); > ERROR: syntax error at or near ")" at character 14 > LINE 1: select 1 in (); > ^ If we took that syntax it would probably mean a zero-element row, not an empty IN list. But I'm disclined to allow either, as it'd seem entirely too likely to convert plain old typos into queries the system will think are valid ... I think the short answer why the spec disallows this (which it does) is that it considers the right-hand side to be a table, and with absolutely nothing there, there is no way to impute a rowtype to the table. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] The empty list?
On 11/2/06, Tom Lane <[EMAIL PROTECTED]> wrote: Jesper Krogh <[EMAIL PROTECTED]> writes:> Whereas this gives a syntax error:> # select 1 in ();> ERROR: syntax error at or near ")" at character 14 > LINE 1: select 1 in ();> ^I think the short answer why the spec disallows this (which it does)is that it considers the right-hand side to be a table, and withabsolutely nothing there, there is no way to impute a rowtype to the table.So if this syntax violates the specs then the ORM is the what needs to be change. The ORM should not attempt to do this.I have to use DB2 sometimes and it also does not allow empty (). What ORM are you using that is trying to do this?-- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] Database recovery in postgres 7.2.4.
"Santosh" <[EMAIL PROTECTED]> writes: > Hi All. > > My setup is as follows: > OS: Sun Solaris 5.8. > Postgres: 7.2.4 > > I have very large database, which contain 15 tables and each table is > contain more than 10,00,000 records. > > My application is parsing text data files and inserting records into > database. > > When this process was running last night, machine was got down because > of power failure. > > Today when I come back to office and I have compaired record count in > data files and in database and find that some records are missing in > database. > > Then I have checked postgres log and found log similar to as follows: > = > DEBUG: The DataBase system was not properly shut down > Automatic recovery is in progress... > DEBUG: Redo starts at (0, 1064) > = > > I have read some WAL related stuff on postgres site but not found any > solution to recover those uncommited records. > > Can any one tell me how to recover those missing records? If they were not committed, then there is no way to do so. Uncommitted work is properly discarded when the database is brought back online. -- let name="cbbrowne" and tld="linuxfinances.info" in name ^ "@" ^ tld;; http://linuxdatabases.info/info/sgml.html "I've seen a look in dogs' eyes, a quickly vanishing look of amazed contempt, and I am convinced that basically dogs think humans are nuts." -- John Steinbeck ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Determining correct table order for insert or drop statements to satisfy foreign keys
On 11/2/06, Peter Hanson <[EMAIL PROTECTED]> wrote: Hello,First, sorry if this has already been covered, though I didn't find anythingsimilar in the archives. Also sorry if this is the wrong list for this typeof question, though it seemed to be the most relevant list for this question. On to the question:I'm writing a simple import script that reads in an excel file that a customerfills out. Each worksheet represents a different table and the tables' fieldsare columns in each worksheet. The tables represented in the file are variable, I have no idea what tablecombinations will be in the excel file when it's sent to the script.What I need is a way for the script to determine what order it should process the worksheets in the excel file.For example, you have this table structure (each table would be a worksheet):PERSON (1) <--> (many) ORDER (1) <--> (many) ORDER_ITEMObviously, the people would have to be inserted first, then the orders, and finally the items, to keep the foreign keys between the tables happy.Is there a mechanism in PostgreSQL that would tell me this order? Or is itsomething that would have to be assembled from analyzing the schema? Anybody needed to perform an operation like this before? Any help/direction would begreatly appreciated.What programming language are you using? Many languages provide mechanisms to ask the database for metadata about tables including primary keys, indexes, data types and foreign keys. That is one option for you. -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] Determining correct table order for insert or drop statements to satisfy foreign keys
Hi Aaron, I'm using PHP5 as the programming language. Thanks, Pete On Thursday 02 November 2006 16:58, Aaron Bono wrote: > On 11/2/06, Peter Hanson <[EMAIL PROTECTED]> wrote: > > Hello, > > > > First, sorry if this has already been covered, though I didn't find > > anything > > similar in the archives. Also sorry if this is the wrong list for this > > type > > of question, though it seemed to be the most relevant list for this > > question. > > > > On to the question: > > > > I'm writing a simple import script that reads in an excel file that a > > customer > > fills out. Each worksheet represents a different table and the tables' > > fields > > are columns in each worksheet. > > > > The tables represented in the file are variable, I have no idea what > > table combinations will be in the excel file when it's sent to the > > script. > > > > What I need is a way for the script to determine what order it should > > process > > the worksheets in the excel file. > > > > For example, you have this table structure (each table would be a > > worksheet): > > > > PERSON (1) <--> (many) ORDER (1) <--> (many) ORDER_ITEM > > > > Obviously, the people would have to be inserted first, then the orders, > > and > > finally the items, to keep the foreign keys between the tables happy. > > > > Is there a mechanism in PostgreSQL that would tell me this order? Or is > > it > > something that would have to be assembled from analyzing the > > schema? Anybody > > needed to perform an operation like this before? Any help/direction would > > be > > greatly appreciated. > > What programming language are you using? Many languages provide mechanisms > to ask the database for metadata about tables including primary keys, > indexes, data types and foreign keys. That is one option for you. ---(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
[SQL] delete and select with IN clause issues
I'm having problem with a cleanup script that runs nightly. The script calls the following query: delete from visit where id not in (select distinct visit_id from page_view); This yields the following error: ERROR: update or delete on "visit" violates foreign key constraint "fk34afd255fbacabec" on "page_view" DETAIL: Key (id)=(38635629) is still referenced from table "page_view". Ok, that seems fine, but when I do a select instead of delete, I do not find the referenced id in my list: select id from visit where id not in (select distinct visit_id from page_view) and id = 38635629 ; id (0 rows) Also, if I don't specify the id = bit at the end, I still don't find it in the output when I search through with less, so why is it trying to delete that row? Rewriting the query like so, yields the same problem: delete from visit where NOT EXISTS ( select * from page_view WHERE visit.id = page_view.visit_id); ERROR: update or delete on "visit" violates foreign key constraint "fk34afd255fbacabec" on "page_view" DETAIL: Key (id)=(38638264) is still referenced from table "page_view". The plan looks like this: explain analyze delete from visit where id not in (select distinct visit_id from page_view); QUERY PLAN - Seq Scan on visit (cost=165027.49..189106.89 rows=211976 width=6) (actual time=4789.595..5330.367 rows=150677 loops=1) Filter: (NOT (hashed subplan)) SubPlan -> Unique (cost=0.00..165017.77 rows=3889 width=8) (actual time=2.717..4388.928 rows=273285 loops=1) -> Index Scan using page_view_visit_idx on page_view (cost=0.00..159625.41 rows=2156946 width=8) (actual time=2.713..2914.944 rows=2249576 loops=1) Trigger for constraint fk34afd255fbacabec: time=7174.540 calls=150677 Total runtime: 32772.345 ms (7 rows) \d page_view Table "public.page_view" Column |Type | Modifiers +-+- id | bigint | not null visit_id | bigint | not null uri| character varying(255) | params | text| stamp | timestamp without time zone | cindex | integer | not null default -1 tindex | integer | not null default -1 method | character varying(7)| not null source_address | character varying(16) | server_name| character varying(255) | Indexes: "page_view_pkey" PRIMARY KEY, btree (id) "page_view_stamp_idx" btree (stamp) "page_view_uri_idx" btree (uri) "page_view_visit_idx" btree (visit_id) Foreign-key constraints: "fk34afd255fbacabec" FOREIGN KEY (visit_id) REFERENCES visit(id) What kind of silliness am I forgetting? -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] delete and select with IN clause issues
On Thu, 2 Nov 2006, Jeff Frost wrote: I'm having problem with a cleanup script that runs nightly. The script calls the following query: delete from visit where id not in (select distinct visit_id from page_view); This yields the following error: ERROR: update or delete on "visit" violates foreign key constraint "fk34afd255fbacabec" on "page_view" DETAIL: Key (id)=(38635629) is still referenced from table "page_view". Ok, that seems fine, but when I do a select instead of delete, I do not find the referenced id in my list: select id from visit where id not in (select distinct visit_id from page_view) and id = 38635629 ; id (0 rows) Also, if I don't specify the id = bit at the end, I still don't find it in the output when I search through with less, so why is it trying to delete that row? Rewriting the query like so, yields the same problem: delete from visit where NOT EXISTS ( select * from page_view WHERE visit.id = page_view.visit_id); ERROR: update or delete on "visit" violates foreign key constraint "fk34afd255fbacabec" on "page_view" DETAIL: Key (id)=(38638264) is still referenced from table "page_view". The plan looks like this: explain analyze delete from visit where id not in (select distinct visit_id from page_view); QUERY PLAN - Seq Scan on visit (cost=165027.49..189106.89 rows=211976 width=6) (actual time=4789.595..5330.367 rows=150677 loops=1) Filter: (NOT (hashed subplan)) SubPlan -> Unique (cost=0.00..165017.77 rows=3889 width=8) (actual time=2.717..4388.928 rows=273285 loops=1) -> Index Scan using page_view_visit_idx on page_view (cost=0.00..159625.41 rows=2156946 width=8) (actual time=2.713..2914.944 rows=2249576 loops=1) Trigger for constraint fk34afd255fbacabec: time=7174.540 calls=150677 Total runtime: 32772.345 ms (7 rows) \d page_view Table "public.page_view" Column |Type | Modifiers +-+- id | bigint | not null visit_id | bigint | not null uri| character varying(255) | params | text| stamp | timestamp without time zone | cindex | integer | not null default -1 tindex | integer | not null default -1 method | character varying(7)| not null source_address | character varying(16) | server_name| character varying(255) | Indexes: "page_view_pkey" PRIMARY KEY, btree (id) "page_view_stamp_idx" btree (stamp) "page_view_uri_idx" btree (uri) "page_view_visit_idx" btree (visit_id) Foreign-key constraints: "fk34afd255fbacabec" FOREIGN KEY (visit_id) REFERENCES visit(id) What kind of silliness am I forgetting? Almost forgot: server_version 8.1.4 (1 row) -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(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] delete and select with IN clause issues
Jeff Frost <[EMAIL PROTECTED]> writes: > delete from visit where id not in (select distinct visit_id from page_view); > This yields the following error: > ERROR: update or delete on "visit" violates foreign key constraint > "fk34afd255fbacabec" on "page_view" > DETAIL: Key (id)=(38635629) is still referenced from table "page_view". This seems pretty darn weird. I am wondering about corrupt indexes --- can you find the indicated key in either table if you set enable_indexscan and enable_bitmapscan to 0? Also, this is a long shot, but does visit by any chance have a cascading deletion self-reference? regards, tom lane ---(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] delete and select with IN clause issues
On Thu, 2 Nov 2006, Tom Lane wrote: Jeff Frost <[EMAIL PROTECTED]> writes: delete from visit where id not in (select distinct visit_id from page_view); This yields the following error: ERROR: update or delete on "visit" violates foreign key constraint "fk34afd255fbacabec" on "page_view" DETAIL: Key (id)=(38635629) is still referenced from table "page_view". This seems pretty darn weird. I am wondering about corrupt indexes --- can you find the indicated key in either table if you set enable_indexscan and enable_bitmapscan to 0? test_tracking=# begin; BEGIN test_tracking=# set enable_seqscan TO false; SET test_tracking=# set enable_bitmapscan to false; SET test_tracking=# delete from visit where id not in (select distinct visit_id from test_tracking(# page_view); DELETE 150660 test_tracking=# ROLLBACK ; So, it seems everything worked fine with those two set to false. with seqscan enabled, it fails: test_tracking=# begin; BEGIN test_tracking=# set enable_bitmapscan to false; SET test_tracking=# show enable_seqscan ; enable_seqscan on (1 row) test_tracking=# delete from visit where id not in (select distinct visit_id from page_view ); ERROR: update or delete on "visit" violates foreign key constraint "fk34afd255fbacabec" on "page_view" DETAIL: Key (id)=(38710245) is still referenced from table "page_view". Looks like with just enable_seqscan disabled it works: test_tracking=# begin; BEGIN test_tracking=# set enable_seqscan to false; SET test_tracking=# show enable_bitmapscan ; enable_bitmapscan --- on (1 row) test_tracking=# delete from visit where id not in (select distinct visit_id from page_view ); DELETE 150661 test_tracking=# ROLLBACK ; ROLLBACK However, this doesn't yield anything: select id from visit where id not in (select distinct visit_id from page_view) and id = 38710245 ; id (0 rows) with them set to true or false. Also, this is a long shot, but does visit by any chance have a cascading deletion self-reference? Nope, I guess I didn't \d visit..thought I did...it's below. I'm going to guess that there is index corruption and a reindex page_view will fix it. Do you want me to gather any information in case this is a reproducible bug before I issue the reindex? Table "public.visit" Column|Type | Modifiers -+-+ id | bigint | not null visitor_id | bigint | not null campaign_id | bigint | session_id | character varying(32) | not null uri | character varying(2000) | referer | character varying(2000) | user_agent | character varying(2000) | remote_host | bigint | not null outcome | character(1)| not null stamp | timestamp without time zone | email_key | character varying(16) | bot | boolean | not null default false status | character(1)| not null Indexes: "visit_pkey" PRIMARY KEY, btree (id) "visit_un" UNIQUE, btree (session_id) "visit_bot_idx" btree (bot) "visit_remote_host_ix" btree (remote_host) "visit_stamp_ix" btree (stamp) "visit_visitor_ix" btree (visitor_id) Foreign-key constraints: "fk6b04d4bbfc2fa3d" FOREIGN KEY (remote_host) REFERENCES remote_host(id) "fk6b04d4be5dc468" FOREIGN KEY (campaign_id) REFERENCES campaign(id) -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] The empty list?
Aaron Bono wrote: > On 11/2/06, Tom Lane <[EMAIL PROTECTED]> wrote: >> >> Jesper Krogh <[EMAIL PROTECTED]> writes: >> > Whereas this gives a syntax error: >> >> > # select 1 in (); >> > ERROR: syntax error at or near ")" at character 14 >> > LINE 1: select 1 in (); >> > ^ >> >> I think the short answer why the spec disallows this (which it does) >> is that it considers the right-hand side to be a table, and with >> absolutely nothing there, there is no way to impute a rowtype to the >> table. >> >> > So if this syntax violates the specs then the ORM is the what needs to be > change. The ORM should not attempt to do this. The SQL-spec is not widely available.. (as far as I know), so I cannot tell if it violates the spec. But Sqlite3 actually supports the empty list syntax (). We agree that another place to fix this would be inside the ORM, but it seems odd that this is considered as a list (1,2,3) whereas this is an empty row () and not the empty list () cause the right-hand-side of on in operation will allways be a list (if I havent forgotten something). (Trying to stuff a row into the right-hand-side of an in operation) jesper=# insert into test(id,description) values(1,'teststring'); INSERT 0 1 jesper=# select 1 in (select * from test);ERROR: subquery has too many columns And as the original example showed, if the empty list is returned from a select statement, then it works flawless. > I have to use DB2 sometimes and it also does not allow empty (). I know that it is quite unimplemented, mysql doesn't do this either > What ORM are you using that is trying to do this? Perl DBIx::Class.. but it is as much about not having to encode all SQL-strange-cases into a large piece of application logic (the ORM) it seems quite natural to encode the "in list" in the application as a list(or array), but missing SQL for the empty list basically means that we cant handle the empty set encoded in the empty array without explicitly introducing code for this size of array. From a programatically viewpoint, this seems quite odd. Jesper -- Jesper Krogh, [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Add calculated fields from one table to other table
> I am sending you the create statement of tables & few insert statements as
> well. Hope this
> helps to solve the problem.
where are the insert statements? ;)
>
> CREATE TABLE ticks
> (
> tick_id int8 NOT NULL DEFAULT nextval(('ticks_s'::text)::regclass),
> ric varchar(30) NOT NULL,
> tick_date date NOT NULL,
> tick_time time NOT NULL,
> price float8,
> volume int4,
> CONSTRAINT ticks_pkey PRIMARY KEY (tick_id),
> )
> WITHOUT OIDS;
>
> CREATE TABLE times
> (
> times_time time NOT NULL,
> count int4,
> CONSTRAINT times_pkey PRIMARY KEY (times_time)
> )
>
> selct statement of ticks table
>ric | tick_date | tick_time | price
> -++--+---
> A | 2006-04-04 | 00:00:55.023 | 4.05
> AA | 2006-04-04 | 00:00:55.023 | 9.05
> A | 2006-04-04 | 00:00:59.023 | 6.05
> A | 2006-04-04 | 00:01:00.023 | 5.05
> ABC | 2006-04-04 | 00:01:00.509 |12.00
> ABI | 2006-04-04 | 00:01:03.511 |13.00
> AA | 2006-04-04 | 00:01:08.023 | 6.05
> ABT | 2006-04-04 | 00:01:08.518 | 3.06
> ABT | 2006-04-04 | 00:01:09.518 | 7.06
>
> select statement of times table
> times_time
> ---
> 00:00:00
> 00:01:00
> 00:02:00
>
>
> I want the query result to look
> ric | times_time | count | avg_price
> ++---+---
> A | 00:00:00 | 2 | 5.05
> AA | 00:00:00 | 1 | 9.05
> ABC | 00:00:00 | 0 |
> ABI | 00:00:00 | 0 |
> ABT | 00:00:00 | 0 |
> A | 00:01:00 | 1 | 5.05
> AA | 00:01:00 | 1 | 6.05
> ABC | 00:01:00 | 1 |12.00
> ABI | 00:01:00 | 1 |13.00
> ABT | 00:01:00 | 2 | 5.06
Here is what I got:
ric | minute | count |avg_price
-+--+---+--
ABC | 00:00:00 | 0 |0
ABT | 00:00:00 | 0 |0
AA | 00:00:00 | 2 | 9.05
ABI | 00:00:00 | 0 |0
A | 00:00:00 | 6 | 5.05
A | 00:01:00 | 3 | 5.05
ABI | 00:01:00 | 1 | 13
AA | 00:01:00 | 2 | 6.05
ABT | 00:01:00 | 9 | 5.726667
ABC | 00:01:00 | 1 | 12
A | 00:02:00 | 0 |0
AA | 00:02:00 | 0 |0
ABI | 00:02:00 | 0 |0
ABC | 00:02:00 | 0 |0
ABT | 00:02:00 | 0 |0
(15 rows)
And here is how I got it:
SELECT
A.ric,
A.minute,
count(B.*) as count,
COALESCE(avg(B.price),0) as avg_price
FROM
(
SELECT T.ric,
M.times_time as minute
FROM
ticks T
CROSS JOIN
times M
WHERE
M.times_time
BETWEEN
'00:00:00'
AND
'00:03:00'
) A
LEFT JOIN
ticks B
ON
A.ric = B.ric
AND
A.minute = date_trunc('minute', B.tick_time)
GROUP BY
A.ric,
A.minute
ORDER BY
A.minute
;
Hope this is what you were looking for. This is the first time I've ever had
to employ a cross
join get what I wanted. Just realize that this query will explode with a very
large number to
records returned as the times table grows. You should expect a quantity of
results like (total
ticks * total times)
Regards,
Richard Broersma Jr.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] delete and select with IN clause issues
Jeff Frost <[EMAIL PROTECTED]> writes: > On Thu, 2 Nov 2006, Tom Lane wrote: >> This seems pretty darn weird. I am wondering about corrupt indexes --- >> can you find the indicated key in either table if you set >> enable_indexscan and enable_bitmapscan to 0? > test_tracking=# begin; > BEGIN > test_tracking=# set enable_seqscan TO false; > SET > test_tracking=# set enable_bitmapscan to false; > SET That's not actually the test case I asked for ... > Nope, I guess I didn't \d visit..thought I did...it's below. I'm going to > guess that there is index corruption and a reindex page_view will fix it. Do > you want me to gather any information in case this is a reproducible bug > before I issue the reindex? I'm thinking the same --- please save a physical copy of page_view_visit_idx before you reindex it, and if that fixes the problem, please send me copies of both the before and after states of the index. regards, tom lane ---(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] The empty list?
Jesper Krogh <[EMAIL PROTECTED]> writes:
> ... the right-hand-side of on
> in operation will allways be a list (if I havent forgotten something).
IN (SELECT ...) for one thing.
> ... but missing SQL for the empty list basically means that
> we cant handle the empty set encoded in the empty array without
> explicitly introducing code for this size of array. From a
> programatically viewpoint, this seems quite odd.
FWIW, as of 8.2 the best option will probably be to use
"col = ANY (array)", which does support zero-length arrays
if you use either an out-of-line parameter or an array literal.
regression=# prepare foo(int[]) as select * from tenk1 where unique1 = any ($1);
PREPARE
regression=# explain analyze execute foo('{1,9,42}');
QUERY PLAN
-
Bitmap Heap Scan on tenk1 (cost=36.38..424.30 rows=489 width=244) (actual
time=0.487..0.524 rows=3 loops=1)
Recheck Cond: (unique1 = ANY ($1))
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..36.38 rows=489 width=0)
(actual time=0.420..0.420 rows=3 loops=1)
Index Cond: (unique1 = ANY ($1))
Total runtime: 1.155 ms
(5 rows)
regression=# explain analyze execute foo('{}');
QUERY PLAN
-
Bitmap Heap Scan on tenk1 (cost=36.38..424.30 rows=489 width=244) (actual
time=0.044..0.044 rows=0 loops=1)
Recheck Cond: (unique1 = ANY ($1))
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..36.38 rows=489 width=0)
(actual time=0.027..0.027 rows=0 loops=1)
Index Cond: (unique1 = ANY ($1))
Total runtime: 0.478 ms
(5 rows)
This is not SQL-standard syntax IIRC, but then "foo IN ()" would
certainly not be either.
regards, tom lane
---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
Re: [SQL] delete and select with IN clause issues
On Fri, 3 Nov 2006, Tom Lane wrote: Jeff Frost <[EMAIL PROTECTED]> writes: On Thu, 2 Nov 2006, Tom Lane wrote: This seems pretty darn weird. I am wondering about corrupt indexes --- can you find the indicated key in either table if you set enable_indexscan and enable_bitmapscan to 0? That's not actually the test case I asked for ... hahah, I wondered why you wanted me to disable seqscan...turns out you didn't! I just misread it. :-) Nope, I guess I didn't \d visit..thought I did...it's below. I'm going to guess that there is index corruption and a reindex page_view will fix it. Do you want me to gather any information in case this is a reproducible bug before I issue the reindex? I'm thinking the same --- please save a physical copy of page_view_visit_idx before you reindex it, and if that fixes the problem, please send me copies of both the before and after states of the index. Well, interestingly, it seems to work now for some reason even though the cleanup script has failed three nights in a row and I haven't reindexed yet. Seems quite strange. I guess now I'll have to wait and see if it fails again tonight. BTW, just to make sure I get the right file to ship over if we have this again, it would be: /var/lib/pgsql/data/base/9366228/16204210 yes? select * from pg_stat_user_indexes where indexrelname = 'page_view_visit_idx'; relid | indexrelid | schemaname | relname |indexrelname | idx_scan | idx_tup_read | idx_tup_fetch -+++---+-+--+--+--- 9366257 | 16204210 | public | page_view | page_view_visit_idx | 5652735 | 1540722403 |1524420263 (1 row) -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(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] The empty list?
Tom Lane wrote: > Jesper Krogh <[EMAIL PROTECTED]> writes: >> ... the right-hand-side of on >> in operation will allways be a list (if I havent forgotten something). > > IN (SELECT ...) for one thing. Isn't that "just" a list of rows(complex value) instead of an list of types(simple values)? >> ... but missing SQL for the empty list basically means that >> we cant handle the empty set encoded in the empty array without >> explicitly introducing code for this size of array. From a >> programatically viewpoint, this seems quite odd. > > FWIW, as of 8.2 the best option will probably be to use > "col = ANY (array)", which does support zero-length arrays > if you use either an out-of-line parameter or an array literal. That looks nice.. is ANY in the SQL-spec? Jesper -- Jesper Krogh, [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] delete and select with IN clause issues
Jeff Frost <[EMAIL PROTECTED]> writes: > BTW, just to make sure I get the right file to ship over if we have this > again, it would be: /var/lib/pgsql/data/base/9366228/16204210 yes? Not necessarily --- the filename is initially the same as the index OID, but it wouldn't be anymore after a REINDEX. Instead use what you get from select relfilenode from pg_class where relname = 'page_view_visit_idx'; and note you will need to recheck it after reindexing. Also, it's a good idea to do CHECKPOINT right before copying the physical file, to make sure you've got a consistent file image on-disk. If you want to be 100% certain, shut down the postmaster while copying, but unless the index file is pretty large I think that's not necessary. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] The empty list?
Jesper Krogh <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> FWIW, as of 8.2 the best option will probably be to use >> "col = ANY (array)", which does support zero-length arrays >> if you use either an out-of-line parameter or an array literal. > That looks nice.. is ANY in the SQL-spec? ANY is, but I think only in the form "var = ANY (SELECT ...)". regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] delete and select with IN clause issues
On Fri, 3 Nov 2006, Tom Lane wrote: Jeff Frost <[EMAIL PROTECTED]> writes: BTW, just to make sure I get the right file to ship over if we have this again, it would be: /var/lib/pgsql/data/base/9366228/16204210 yes? Not necessarily --- the filename is initially the same as the index OID, but it wouldn't be anymore after a REINDEX. Instead use what you get from select relfilenode from pg_class where relname = 'page_view_visit_idx'; and note you will need to recheck it after reindexing. Also, it's a good idea to do CHECKPOINT right before copying the physical file, to make sure you've got a consistent file image on-disk. If you want to be 100% certain, shut down the postmaster while copying, but unless the index file is pretty large I think that's not necessary. Well, I spoke to soon on the it all works front. So, it's been reindexed and appears to be working properly now. I guess I'll keep an eye on it for a while. I didn't get your query suggestion in time, so hopefully I grabbed the right binary file..though it did seem to disappear after the reindex, so I think it's likely the correct one. Definitely got the correct second one. You can grab them here: http://www.frostconsultingllc.com/fortom.tar.gz And thanks again for the help! -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 6: explain analyze is your friend
