[SQL] need help
I'm running PostgreSQL 8.0.3 on i686-pc-linux-gnu (Fedora Core 2). I've been dealing with Psql for over than 2 years now, but I've never had this case before. I have a table that has about 20 rows in it. Table "public.s_apotik" Column | Type| Modifiers ---+--+-- obat_id| character varying(10)| not null stock | numeric | not null s_min | numeric | not null s_jual | numeric | s_r_jual | numeric | s_order| numeric | s_r_order | numeric | s_bs | numeric | last_receive | timestamp without time zone | Indexes: "s_apotik_pkey" PRIMARY KEY, btree(obat_id) When I try to UPDATE one of the row, nothing happens for a very long time. First, I run it on PgAdminIII, I can see the miliseconds are growing as I waited. Then I stop the query, because the time needed for it is unbelievably wrong. Then I try to run the query from the psql shell. For example, the table has obat_id : A, B, C, D. db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='A'; ( nothing happens.. I press the Ctrl-C to stop it. This is what comes out :) Cancel request sent ERROR: canceling query due to user request (If I try another obat_id) db=# UPDATE s_apotik SET stock = 100 WHERE obat_id='B'; (Less than a second, this is what comes out :) UPDATE 1 I can't do anything to that row. I can't DELETE it. Can't DROP the table. I want this data out of my database. What should I do? It's like there's a falsely pointed index here. Any help would be very much appreciated. Regards, Jenny Tania __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] need help (not anymore)
I run the VACUUM as you suggested, but still no response from the server. So, I decided to DROP the database. I got a message that the database is being used. I closed every application that accessing it. But, the message remains. I checked the server processes (ps -ax). There were lots of 'UPDATE is waiting ...' on the list. I killed them all. I backuped current database and DROP the database, restore to the backup file I just made. Don't really know why this happened, but thankfully now, everything's normal. Thank you, guys. Regards, Jenny Tania __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] JOIN query not working as expected
How is this possible? I have two tables. 'services', and 'messages'. Each message can be assigned to one service, or it can be unnasigned. Therefore 'service_id' column in table 'messages' is not foreign-keyed to 'id' column in services table. services.id is PK for services, messages.id is PK for messages. Now, here goes: pulitzer2=# select * from services where id = 1001; id | keyword | type_id | vpn_id | start_time |end_time | day_boundary | week_boundary | month_boundary | recurrence | random_message_count --+-+-++++--+---+++-- 1001 | cocker | 1 | 1 || 2005-10-20 12:00:00+02 | | || 1 | (1 row) Ok, I have a service with id 1001 which is called 'cocker'. Now, I want all the messages for that service within certain period: pulitzer2=# select * from messages where service_id = 1001 and receiving_time between '2005-10-01' and '2005-10-30'; id | from | to | receiving_time | raw_text | keyword | destination_id | vpn_id | service_id | status | reply +--+++--+-+++++--- (0 rows) Ok, no such messages. Now I want all services which didn't have any messages within certain period: pulitzer2=# select * from services where id not in (select distinct service_id from messages where receiving_time between '2005-10-01' and '2005-10-30'); id | keyword | type_id | vpn_id | start_time | end_time | day_boundary | week_boundary | month_boundary | recurrence | random_message_count +-+-+++--+--+---+++-- (0 rows) Why is that? I 'discovered' above mentioned when I was transforming this query: SELECT services.id AS service_id, (SELECT COUNT(id) FROM messages WHERE (messages.service_id = services.id) AND (messages.receiving_time >= '2005-10-01') AND (messages.receiving_time < '2005-10-30') ) AS "count", services.keyword FROM services WHERE (services.vpn_id = 1) AND ( (services.start_time IS NULL OR services.start_time <= '2005-10-30') AND (services.end_time IS NULL OR services.end_time >= '2005-10-01') ) GROUP BY services.id, services.keyword ORDER BY services.keyword [this query shows correctly, for service 'cocker', that '"count"' column has value 0] I transformed query to this: SELECT services.id AS service_id, count(messages.id) as "count", services.keyword FROM services LEFT OUTER JOIN messages ON services.id = messages.service_id WHERE services.vpn_id = 1 AND messages.receiving_time BETWEEN '2005-10-01' AND '2005-10-30' GROUP BY services.id, services.keyword ORDER BY services.keyword This query runs MUCH faster, but it omits the 'cocker' column, as if I used INNER JOIN. Any clues? I'm stuck here... Mike -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Database with "override" tables
Lane Van Ingen wrote: I think I have a similar situation involving the naming of assets, where the usual asset description is used, but users can enter a description in a separate table which 'overrides' the original name with a name that is more familiar to the individual. IF THIS IS WHAT YOU WANT, it was accomplished by doing a UNION between two select statements, like this: select from foo1 union select from foo2 where ; Hope this helps. That almost works, and it is a much cleaner query than I had before. However, there's a possibility that some columns in the overridden table are NULL (indicating that the original value should be used). So, a particular asset may contain a description and price; the price may be NULL, meaning the read-only value should be used, but the user may have attached a special description as we previously outlined. What I'm looking for is the ability to, perhaps, "overlay" foo2 onto foo1, joined on foo1_id. Then, NULL values in foo2 become "transparent" and we see the foo1 values behind them. Presently I am using COALESCE() for every pair individually, ie. COALESCE(foo2.price, foo1.price), COALESCE(foo2.descr, foo1.descr), ... and then doing a FULL JOIN. This works. I'm starting to wonder if it's worth the extra hassle, I may just use your suggested UNION method instead. Thanks again, Mike. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] JOIN query not working as expected
Mario Splivalo <[EMAIL PROTECTED]> writes: > Now I want all services which didn't have any messages within certain > period: > pulitzer2=# select * from services where id not in (select distinct > service_id from messages where receiving_time between '2005-10-01' and > '2005-10-30'); > (0 rows) > Why is that? Probably, you've got some NULLs in the messages.service_id column ... try excluding those from the sub-select. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] JOIN query not working as expected
On Tue, 2005-12-06 at 09:58 -0500, Tom Lane wrote: > Mario Splivalo <[EMAIL PROTECTED]> writes: > > Now I want all services which didn't have any messages within certain > > period: > > pulitzer2=# select * from services where id not in (select distinct > > service_id from messages where receiving_time between '2005-10-01' and > > '2005-10-30'); > > (0 rows) > > > Why is that? > > Probably, you've got some NULLs in the messages.service_id column ... > try excluding those from the sub-select. Yes, I've discovered that later. Still, I'm confused with the 'biggies' at the bottom of my initial mail. I neglected to metion that I'm using postgresql 7.4.8. I discovered that I can create a FK on a column wich allows NULL values, and I can even insert rows with NULLs in FK column, although PK table where FK is pointing does not allow nuls. Is that 'by design', or...? Mike -- Mario Splivalo Mob-Art [EMAIL PROTECTED] "I can do it quick, I can do it cheap, I can do it well. Pick any two." ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] JOIN query not working as expected
Mario Splivalo <[EMAIL PROTECTED]> writes: > I can create a FK on a column wich allows NULL values, and I can even > insert rows with NULLs in FK column, although PK table where FK is > pointing does not allow nuls. Is that 'by design', or...? It's per SQL spec. Add a NOT NULL constraint to the column if you do not wish to allow NULLs. 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] Database with "override" tables
Not quite sure how to answer this, but one thought does occur to me: I was perhaps assuming that an override table would override an entire record in the 'original' table(that is what we are doing), and we require that critical fields in the override field be NOT NULL (and in some cases, provide DEFAULT values). Will that help? -Original Message- From: Michael Burke [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 06, 2005 9:55 AM To: Lane Van Ingen Cc: PGSQL-SQL Subject: Re: [SQL] Database with "override" tables Lane Van Ingen wrote: >I think I have a similar situation involving the naming of assets, where >the usual asset description is used, but users can enter a description in >a separate table which 'overrides' the original name with a name that is >more familiar to the individual. > >IF THIS IS WHAT YOU WANT, it was accomplished by doing a UNION between two >select statements, like this: > select from foo1 > union > select from foo2 >where ; > >Hope this helps. > > > That almost works, and it is a much cleaner query than I had before. However, there's a possibility that some columns in the overridden table are NULL (indicating that the original value should be used). So, a particular asset may contain a description and price; the price may be NULL, meaning the read-only value should be used, but the user may have attached a special description as we previously outlined. What I'm looking for is the ability to, perhaps, "overlay" foo2 onto foo1, joined on foo1_id. Then, NULL values in foo2 become "transparent" and we see the foo1 values behind them. Presently I am using COALESCE() for every pair individually, ie. COALESCE(foo2.price, foo1.price), COALESCE(foo2.descr, foo1.descr), ... and then doing a FULL JOIN. This works. I'm starting to wonder if it's worth the extra hassle, I may just use your suggested UNION method instead. Thanks again, Mike. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] lost in system tables
I am trying to find the equivalent of these two Sybase system columns : check_on_commit (Y/N) - Indicates whether INSERT and UPDATE commands should wait until the next COMMIT command to check if foreign keys are valid. A foreign key is valid if, for each row in the foreign table, the values in the columns of the foreign key either contain the NULL value or match the primary key values in some row of the primary table. nulls (Y/N) - Indicates whether the columns in the foreign key are allowed to contain the NULL value. Note that this setting is independent of the nulls setting in the columns contained in the foreign key. Maybe pg_constaint.condeferrable is appropriate for check_on_commit? I'll appreciate any help Thanks in advance, Emil __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [GENERAL] lost in system tables
On Tuesday 06 December 2005 08:47, Emil Rachovsky wrote: > I am trying to find the equivalent of these two > Sybase system columns : > > check_on_commit (Y/N) - Indicates whether INSERT and > UPDATE commands should wait until the next COMMIT > command to check if foreign keys are valid. A foreign > key is valid if, for each row in the foreign table, > the values in the columns of the foreign key either > contain the NULL value or match the primary key values > in some row of the primary table. > > nulls (Y/N) - Indicates whether the columns in the > foreign key are allowed to contain the NULL value. > Note that this setting is independent of the nulls > setting in the columns contained in the foreign key. > > Maybe pg_constaint.condeferrable is appropriate for > check_on_commit? I'll appreciate any help > > Thanks in advance, > Emil Those parameters are specified when you declare the foreign key. Look here, in the section describing "references": http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html Luca ---(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
[SQL] Date Interval
Hi, I hale a problem with counting interwal and I can't find what to do with this. I have two fields in the table: Column | Type | Modifiers -+-+--- date_in | date| interwal_months | numeric | -+-+--- Query SELECT date_in + INTERVAL '3 MONTH' FROM any_table works fine of course. However, how to do something like that? SELECT date_in + INTERVAL ' interwal_months MONTH' FROM any_table I hope I'm just blind.. ;-) Thanks for any help. MK ---(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] Date Interval
Magdalena Komorowska wrote:
> Hi,
> I hale a problem with counting interwal and I can't find what to do with
> this.
> I have two fields in the table:
> Column | Type | Modifiers
> -+-+---
> date_in | date|
> interwal_months | numeric |
> -+-+---
>
> Query
> SELECT date_in + INTERVAL '3 MONTH' FROM any_table
> works fine of course.
>
> However, how to do something like that?
> SELECT date_in + INTERVAL ' interwal_months MONTH' FROM any_table
How about this?
test=> CREATE TABLE test(x TEXT);
CREATE TABLE
test=> INSERT INTO test VALUES ('3');
INSERT 0 1
test=> SELECT current_timestamp + cast(x || ' months' AS INTERVAL) FROM
test;
?column?
---
2006-03-06 11:53:05.574279-05
(1 row)
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Date Interval
On Tue, Dec 06, 2005 at 11:54:05AM -0500, Bruce Momjian wrote: > test=> SELECT current_timestamp + cast(x || ' months' AS INTERVAL) FROM > test; >?column? > --- > 2006-03-06 11:53:05.574279-05 > (1 row) Or another way: test=> CREATE TABLE test (x numeric); CREATE TABLE test=> INSERT INTO test VALUES (3); INSERT 0 1 test=> SELECT current_timestamp + x * interval'1 month' FROM test; ?column? --- 2006-03-06 12:07:48.112765-05 (1 row) -- Michael Fuhr ---(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] lost in system tables
Emil Rachovsky <[EMAIL PROTECTED]> writes: > I am trying to find the equivalent of these two > Sybase system columns : > check_on_commit (Y/N) - Indicates whether INSERT and > UPDATE commands should wait until the next COMMIT > command to check if foreign keys are valid. I think you are looking for the DEFERRABLE/DEFERRED option of foreign key constraints. > nulls (Y/N) - Indicates whether the columns in the > foreign key are allowed to contain the NULL value. > Note that this setting is independent of the nulls > setting in the columns contained in the foreign key. No such animal in the SQL standard --- though perhaps MATCH FULL is approximately what you are looking for? Your description as stated makes no sense at all; either the columns are allowed to be null, or they're not. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Date Interval
In article <[EMAIL PROTECTED]>,
Bruce Momjian writes:
> Magdalena Komorowska wrote:
>> Hi,
>> I hale a problem with counting interwal and I can't find what to do with
>> this.
>> I have two fields in the table:
>> Column | Type | Modifiers
>> -+-+---
>> date_in | date|
>> interwal_months | numeric |
>> -+-+---
>>
>> Query
>> SELECT date_in + INTERVAL '3 MONTH' FROM any_table
>> works fine of course.
>>
>> However, how to do something like that?
>> SELECT date_in + INTERVAL ' interwal_months MONTH' FROM any_table
> How about this?
> test=> CREATE TABLE test(x TEXT);
> CREATE TABLE
> test=> INSERT INTO test VALUES ('3');
> INSERT 0 1
> test=> SELECT current_timestamp + cast(x || ' months' AS INTERVAL) FROM
> test;
> ?column?
> ---
>2006-03-06 11:53:05.574279-05
> (1 row)
Since Magdalena doesn't store the number of months in a string, the
following might be more convenient:
SELECT date_in + interwal_months * INTERVAL '1 MONTH' FROM any_table
---(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] Date Interval
It works great, very nice method :-) thanks a lot! MK ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] lost in system tables
Thank you,Tom, As for the description of 'nulls' I have taken it as it is from the Sybase help file :) __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] lost in system tables
> Those parameters are specified when you declare the > foreign key. Look here, > in the section describing "references": > http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html > > Luca Thank you,Luca I know that these parameters are specified when you declare the foreign key, but i need to check them out later from the system tables. The first one must be something like the 'deferrable' column in pg_constaint, but the second is still in vague. Emil __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(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
