[SQL] output
Hello all, I am interested on how can i put output data for debugging purposes. Any sample will be very good! Thanks, /Marian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] output
On Thursday 02 October 2003 09:52, Popeanga Marian wrote: > Hello all, > > > I am interested on how can i put output data for debugging > purposes. Any sample will be very good! Output what data when? To what? If you can describe your situation, I'm sure we can come up with something to help. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] output
Richard Huxton wrote: On Thursday 02 October 2003 09:52, Popeanga Marian wrote: Hello all, I am interested on how can i put output data for debugging purposes. Any sample will be very good! Output what data when? To what? If you can describe your situation, I'm sure we can come up with something to help. Inside a plpgsql block . RAISE NOTICE ''Operation performed!''; Now if i don't use psql from where i can read the output ? Thanks, /Marian
Re: [SQL] Creating Index
Stephan and Tom,
Many thanks!
I am trying to explain the meaning of tables and views:
The tables in the first SELECT in the UNION of view1 are jorunal, whose
rows are entered daily.
The table in the second SELECT in the UNION of view1 is budget, whose
rows use year+month as key.
View1 unions journal and budget to present users a pseudo table that can
be selected to produce comparison reports - budget vs. actual.
User will be prompted to enter the interested year+month before selecting
the view.
I think I have found out a way to make the index from year and month.
Although this approach produces reduntant data but it should be feasible:
- Create 2 more columns, year and month for table2.
- Feed NEW.c3 and NEW.f4 with values EXTRACT(YEAR FROM c2) and
EXTRACT(MONTH FROM c2), respectively in
plpgsql trigger function.
- Create composit index for year+month.
There is still one thing I don't know why - query on view1 being
extermely slow. I also removed the subquery
from view1 to form view2. The query on view2 is swift. (postgresql
v7.3.2)
Both queries return 0 rows. The results are correct because table2
contains data of years on or before 2003
and table4 contains no rows.
Again, I would much appreciate any idea helping me speed up view1.
One off-topic issue is that I wish postgresql could be smarter to make
use of the index even there are INT2 columns in composit index. As my
case shows, INT2 is big enough for columns year and month, isn't it?
Best Regards,
CN
-
--This table contains 1036 rows.
CREATE TABLE table1 (
c1 VARCHAR(20) PRIMARY KEY,
c2 "char"
)WITHOUT OIDS;
-
--This table contains 9255 rows.
CREATE TABLE table2 (
c1 VARCHAR(20) PRIMARY KEY,
c2 DATE,
c3 INTEGER, --year part of c2
c4 INTEGER --month part of c2
)WITHOUT OIDS;
CREATE INDEX i2c3c4 ON table2 (c3,c4);
-
--This table contains 28482 rows.
CREATE TABLE table3 (
CONSTRAINT fk2c1 FOREIGN KEY (c1) REFERENCES table2 (c1) ON UPDATE
CASCADE ON DELETE CASCADE,
CONSTRAINT fk2c3 FOREIGN KEY (c3) REFERENCES table1 (c1),
PRIMARY KEY (c1,c2),
c1 VARCHAR(20),
c2 INTEGER,
c3 VARCHAR(20),
c4 "char",
c5 NUMERIC --amount
)WITHOUT OIDS;
-
--This table contains 0 row.
CREATE TABLE table4 (
PRIMARY KEY (c1,c2),
c1 INTEGER, --year
c2 INTEGER, --month
c3 INTEGER
)WITHOUT OIDS;
-
CREATE OR REPLACE VIEW view1 AS
SELECT
table2.c3 AS year
,table2.c4 AS month
,(SELECT CASE
WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D'
OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C'
THEN table3.c5 ELSE 0-table3.c5 END
AS amount
FROM table1
WHERE table1.c1=table3.c3
)
FROM table2,table3
WHERE table3.c1=table2.c1
UNION ALL
SELECT c1,c2,c3 FROM table4;
-
CREATE OR REPLACE VIEW view2 AS
SELECT table2.c3 AS year,table2.c4 AS month
FROM table2,table3
WHERE table3.c1=table2.c1
UNION ALL
SELECT c1,c2 FROM table4;
-
-
EXPLAIN ANALYZE SELECT * FROM view1 WHERE year > 2003;
-
Subquery Scan view1 (cost=0.00..141.50 rows=2000 width=185) (actual
time=4348.92..4348.92 rows=0 loops=1)
Filter: ("year" > 2003)
-> Append (cost=0.00..141.50 rows=2000 width=185) (actual
time=2.65..4230.44 rows=28482 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..121.50 rows=1000
width=185) (actual time=2.64..4127.71
rows=28482 loops=1)
-> Merge Join (cost=0.00..121.50 rows=1000 width=185)
(actual time=2.62..3875.23 rows=28482
loops=1)
Merge Cond: ("outer".c1 = "inner".c1)
-> Index Scan using table2_pkey on table2
(cost=0.00..52.00 rows=1000 width=56)
(actual time=0.81..183.37 rows=9255 loops=1)
-> Index Scan using table3_pkey on table3
(cost=0.00..52.00 rows=1000 width=129)
(actual time=0.74..649.32 rows=28482 loops=1)
SubPlan
-> Index Scan using table1_pkey on table1
(cost=0.00..4.82 rows=1 width=1) (actual
time=0.07..0.07 rows=1 loops=28482)
Index Cond: (c1 = $2)
-> Subquery Scan "*SELECT* 2" (cost=0.00..20.00 rows=1000
width=12) (actual time=0.02..0.02 rows=0
loops=1)
-> Seq Scan on table4 (cost=0.00..20.00 rows=1000
width=12) (actual time=0.01..0.01 rows=0
loops=1)
Total runtime: 4350.24 msec
-
-
EXPLAIN ANALYZE SELECT * FROM view2 WHERE year > 2003;
-
Subquery Scan view2 (cost=36.47..119.30 rows=667 width=104) (actual
time=40.90..40.90 rows=0 loops=1)
-> Append (cost=36.47..119.30 rows=667 width=104) (actual
time=40.88..40.88 rows=0 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=36.47..96.80 rows=333
width=104) (actual time=40.85..40
Re: [SQL] output
On Thursday 02 October 2003 12:09, Popeanga Marian wrote: > > Inside a plpgsql block . > RAISE NOTICE ''Operation performed!''; > > Now if i don't use psql from where i can read the output ? Ah - I think you need to set CLIENT_MIN_MESSAGES accordingly and then you can check for a non-fatal error code in your result-code. Never done it myself mind. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] help with rule and notification
ok if the only info I can pass is that the notify happened and then I need to go look at the notifying table is it possible to create a rule that on update of jobstable insert into notifytable the jobnumber has there been any real discussion about adding this to the todo list? I was really pretty excited about using notify,... Ted --- Tom Lane <[EMAIL PROTECTED]> wrote: > Theodore Petrosky <[EMAIL PROTECTED]> writes: > > create rule r1 as on update to table1 do (update > > table2 set jobno = table1.jobno; notify table2;) > > > so anyone listening for notifications on table2 > can > > ask table2 for the jobno that was updated. then if > > they were viewing that jobno, update their > display. if > > not just ignore the notify. > > At the moment, a NOTIFY cannot convey very much > information beyond > "something happened, better look to see what". > (There have been > discussions about making the notification carry more > info, see the > pgsql-hackers archives.) In a previous lifetime I > had a moderately > complex application that used NOTIFY to trigger > display updates for > multiple client apps viewing a shared database. If > memory serves, > I did it by having a "sequence number" column that > was assigned from > a nextval() operation on every insert or update. In > addition the > inserts and updates triggered NOTIFY events. When > the clients > got NOTIFY they'd do "select from tab where seqno > > last-seqno-seen" > and then update their local state from the rows they > got back. > > This solution doesn't directly handle deletes. I > think I finessed the > problem by treating "delete" as "update to a 'dead' > state" and only > cleaning out the dead rows later. > > regards, tom lane > > ---(end of > broadcast)--- > TIP 7: don't forget to increase your free space map settings __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] output
Popeanga Marian <[EMAIL PROTECTED]> writes: > Now if i don't use psql from where i can read the output ? If you're using libpq directly, you can install a notice processor hook routine to catch NOTICE messages. Otherwise they go to stderr. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Creating Index
On Thu, 2 Oct 2003, CN wrote:
> I am trying to explain the meaning of tables and views:
> The tables in the first SELECT in the UNION of view1 are jorunal, whose
> rows are entered daily.
> The table in the second SELECT in the UNION of view1 is budget, whose
> rows use year+month as key.
> View1 unions journal and budget to present users a pseudo table that can
> be selected to produce comparison reports - budget vs. actual.
> User will be prompted to enter the interested year+month before selecting
> the view.
So, in practice you'll actually be doing queries with equality rather than
ranges?
> I think I have found out a way to make the index from year and month.
> Although this approach produces reduntant data but it should be feasible:
> - Create 2 more columns, year and month for table2.
> - Feed NEW.c3 and NEW.f4 with values EXTRACT(YEAR FROM c2) and
> EXTRACT(MONTH FROM c2), respectively in
> plpgsql trigger function.
> - Create composit index for year+month.
>
> There is still one thing I don't know why - query on view1 being
> extermely slow. I also removed the subquery
> from view1 to form view2. The query on view2 is swift. (postgresql
> v7.3.2)
> Both queries return 0 rows. The results are correct because table2
> contains data of years on or before 2003
> and table4 contains no rows.
>
> Again, I would much appreciate any idea helping me speed up view1.
Well, you should probably again analyze the tables. I think it's running
on default statistics again. It might do better with stats.
> -
> --This table contains 1036 rows.
> CREATE TABLE table1 (
> c1 VARCHAR(20) PRIMARY KEY,
> c2 "char"
> )WITHOUT OIDS;
> -
> --This table contains 9255 rows.
> CREATE TABLE table2 (
> c1 VARCHAR(20) PRIMARY KEY,
> c2 DATE,
> c3 INTEGER, --year part of c2
> c4 INTEGER --month part of c2
> )WITHOUT OIDS;
> CREATE INDEX i2c3c4 ON table2 (c3,c4);
> -
> --This table contains 28482 rows.
> CREATE TABLE table3 (
> CONSTRAINT fk2c1 FOREIGN KEY (c1) REFERENCES table2 (c1) ON UPDATE
> CASCADE ON DELETE CASCADE,
> CONSTRAINT fk2c3 FOREIGN KEY (c3) REFERENCES table1 (c1),
> PRIMARY KEY (c1,c2),
> c1 VARCHAR(20),
> c2 INTEGER,
> c3 VARCHAR(20),
> c4 "char",
> c5 NUMERIC --amount
> )WITHOUT OIDS;
> -
> --This table contains 0 row.
> CREATE TABLE table4 (
> PRIMARY KEY (c1,c2),
> c1 INTEGER, --year
> c2 INTEGER, --month
> c3 INTEGER
> )WITHOUT OIDS;
> -
> CREATE OR REPLACE VIEW view1 AS
> SELECT
> table2.c3 AS year
> ,table2.c4 AS month
> ,(SELECT CASE
> WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D'
> OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C'
> THEN table3.c5 ELSE 0-table3.c5 END
> AS amount
> FROM table1
> WHERE table1.c1=table3.c3
>)
> FROM table2,table3
> WHERE table3.c1=table2.c1
>
> UNION ALL
>
> SELECT c1,c2,c3 FROM table4;
> -
> CREATE OR REPLACE VIEW view2 AS
> SELECT table2.c3 AS year,table2.c4 AS month
> FROM table2,table3
> WHERE table3.c1=table2.c1
>
> UNION ALL
>
> SELECT c1,c2 FROM table4;
> -
> -
> EXPLAIN ANALYZE SELECT * FROM view1 WHERE year > 2003;
> -
> Subquery Scan view1 (cost=0.00..141.50 rows=2000 width=185) (actual
> time=4348.92..4348.92 rows=0 loops=1)
>Filter: ("year" > 2003)
>-> Append (cost=0.00..141.50 rows=2000 width=185) (actual
>time=2.65..4230.44 rows=28482 loops=1)
> -> Subquery Scan "*SELECT* 1" (cost=0.00..121.50 rows=1000
> width=185) (actual time=2.64..4127.71
> rows=28482 loops=1)
>-> Merge Join (cost=0.00..121.50 rows=1000 width=185)
>(actual time=2.62..3875.23 rows=28482
> loops=1)
> Merge Cond: ("outer".c1 = "inner".c1)
> -> Index Scan using table2_pkey on table2
> (cost=0.00..52.00 rows=1000 width=56)
> (actual time=0.81..183.37 rows=9255 loops=1)
> -> Index Scan using table3_pkey on table3
> (cost=0.00..52.00 rows=1000 width=129)
> (actual time=0.74..649.32 rows=28482 loops=1)
> SubPlan
>-> Index Scan using table1_pkey on table1
>(cost=0.00..4.82 rows=1 width=1) (actual
> time=0.07..0.07 rows=1 loops=28482)
> Index Cond: (c1 = $2)
> -> Subquery Scan "*SELECT* 2" (cost=0.00..20.00 rows=1000
> width=12) (actual time=0.02..0.02 rows=0
> loops=1)
>-> Seq Scan on table4 (cost=0.00..20.00 rows=1000
>width=12) (actual time=0.01..0.01 rows=0
> loops=1)
> Total runtime: 4350.24 msec
> -
> -
> EXPLAIN ANALYZE SELECT * FROM view2 WHERE year > 2003;
> -
> Subquery Scan view2 (cost=36.47..119.30 rows=667 width=104) (actual
> time=40.90..4
Re: [SQL] Creating Index
Greetings! Stephan,
> So, in practice you'll actually be doing queries with equality rather than ranges?
I'm so glad being understood :-)
> > There is still one thing I don't know why - query on view1 being
> > extermely slow. I also removed the subquery
> > from view1 to form view2. The query on view2 is swift.
> Well, you should probably again analyze the tables. I think it's running
> on default statistics again. It might do better with stats.
Not much is improved on view1 after "vacuumdb -z db1".
I thought since both
SELECT * FROM view1 WHERE year > 2003
and
SELECT * FROM view2 WHERE year > 2003
returns 0 rows, subquery in view1 should consume no CPU and thus both
queries should consume roughly the same amount of time. Why the result is
contrary to my expection?
Subquery Scan view1 (cost=180.69..1579.97 rows=28483 width=56) (actual
time=4676.80..4676.80 rows=0 loops=1)
Filter: ("year" > 2003)
-> Append (cost=180.69..1579.97 rows=28483 width=56) (actual
time=168.35..4526.08 rows=28482 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=180.69..1579.97 rows=28482
width=56) (actual
time=168.34..4413.81 rows=28482 loops=1)
-> Hash Join (cost=180.69..1579.97 rows=28482 width=56)
(actual time=168.33..4197.19
rows=28482 loops=1)
Hash Cond: ("outer".c1 = "inner".c1)
-> Seq Scan on table3 (cost=0.00..544.82
rows=28482 width=35) (actual
time=0.24..376.60 rows=28482 loops=1)
-> Hash (cost=157.55..157.55 rows=9255 width=21)
(actual time=166.66..166.66 rows=0
loops=1)
-> Seq Scan on table2 (cost=0.00..157.55
rows=9255 width=21) (actual
time=0.24..97.23 rows=9255 loops=1)
SubPlan
-> Index Scan using table1_pkey on table1
(cost=0.00..3.01 rows=1 width=1) (actual
time=0.07..0.07 rows=1 loops=28482)
Index Cond: (c1 = $2)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.00 rows=1
width=12) (actual time=0.02..0.02 rows=0
loops=1)
-> Seq Scan on table4 (cost=0.00..0.00 rows=1 width=12)
(actual time=0.01..0.01 rows=0
loops=1)
Total runtime: 4677.39 msec
===
Subquery Scan view2 (cost=0.00..35.57 rows=4 width=34) (actual
time=0.62..0.62 rows=0 loops=1)
-> Append (cost=0.00..35.57 rows=4 width=34) (actual time=0.61..0.61
rows=0 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=0.00..35.57 rows=3
width=34) (actual time=0.59..0.59 rows=0
loops=1)
-> Nested Loop (cost=0.00..35.57 rows=3 width=34)
(actual time=0.58..0.58 rows=0 loops=1)
-> Index Scan using i2c3c4 on table2
(cost=0.00..5.04 rows=1 width=21) (actual
time=0.57..0.57 rows=0 loops=1)
Index Cond: (c3 > 2003)
-> Index Scan using table3_pkey on table3
(cost=0.00..30.42 rows=9 width=13) (never
executed)
Index Cond: (table3.c1 = "outer".c1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.00 rows=1 width=8)
(actual time=0.02..0.02 rows=0
loops=1)
-> Seq Scan on table4 (cost=0.00..0.00 rows=1 width=8)
(actual time=0.01..0.01 rows=0
loops=1)
Filter: (c1 > 2003)
Total runtime: 1.09 msec
(12 rows)
> > -
> > -
> > EXPLAIN ANALYZE SELECT * FROM view1 WHERE year > 2003;
> > -
> > Subquery Scan view1 (cost=0.00..141.50 rows=2000 width=185) (actual
> > time=4348.92..4348.92 rows=0 loops=1)
> >Filter: ("year" > 2003)
> >-> Append (cost=0.00..141.50 rows=2000 width=185) (actual
> >time=2.65..4230.44 rows=28482 loops=1)
> > -> Subquery Scan "*SELECT* 1" (cost=0.00..121.50 rows=1000
> > width=185) (actual time=2.64..4127.71
> > rows=28482 loops=1)
> >-> Merge Join (cost=0.00..121.50 rows=1000 width=185)
> >(actual time=2.62..3875.23 rows=28482
> > loops=1)
> > Merge Cond: ("outer".c1 = "inner".c1)
> > -> Index Scan using table2_pkey on table2
> > (cost=0.00..52.00 rows=1000 width=56)
> > (actual time=0.81..183.37 rows=9255 loops=1)
> > -> Index Scan using table3_pkey on table3
> > (cost=0.00..52.00 rows=1000 width=129)
> > (actual time=0.74..649.32 rows=28482 loops=1)
> > SubPlan
> >-> Index Scan using table1_pkey on table1
> >(cost=0.00..4.82 rows=1 width=1) (actual
> > time=0.07..0.07 rows=1 loops=28482)
> > Index Cond: (c1 = $2)
> > -> Subquery Scan "*SELECT* 2" (cost=0.00..20.00
Re: [SQL] Creating Index
"CN" <[EMAIL PROTECTED]> writes:
> I thought since both
> SELECT * FROM view1 WHERE year > 2003
> and
> SELECT * FROM view2 WHERE year > 2003
> returns 0 rows, subquery in view1 should consume no CPU and thus both
> queries should consume roughly the same amount of time.
The reason view1 is a lot slower is that the condition on "year" isn't
getting pushed down into the plan; instead the entire view result is
computed and then filtered at the top step:
> Subquery Scan view1 (cost=180.69..1579.97 rows=28483 width=56) (actual
> time=4676.80..4676.80 rows=0 loops=1)
>Filter: ("year" > 2003)
>-> Append (cost=180.69..1579.97 rows=28483 width=56) (actual
>time=168.35..4526.08 rows=28482 loops=1)
In view2 the year condition is successfully pushed all the way to the
bottom scans:
> -> Index Scan using i2c3c4 on table2
> (cost=0.00..5.04 rows=1 width=21) (actual
> time=0.57..0.57 rows=0 loops=1)
>Index Cond: (c3 > 2003)
thus eliminating a lot of join work.
The reason why view1 isn't well optimized is that you've been sloppy
about datatypes. It looks to me like the "SELECT CASE" business yields
NUMERIC while the other arm of the UNION yields INTEGER for table4.c3.
For various subtle semantic reasons we do not try to push down
conditions into UNIONs when the UNION arms yield different datatypes.
7.4 would optimize this query successfully anyway because the condition
isn't actually on the column with inconsistent datatype --- but 7.3 just
punts if *any* of the UNION columns have inconsistent datatypes.
In short: add a cast to make the UNION arms have the same datatypes.
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] inherited indexes
Hi I had a use for table inheritance then I noticed the comments in the interactive docs which say that because indexes aren't inherited with tables that the feature is pretty useless. Can anyone tell me if that's fixed in 7.4, and if not when it may be? Thanks Ritchie ---(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] How to figure out when was a table created
Hi folks, I posted this question a few days ago and got no response so I guess it cannot be done (surprising!) So that leaves me with my business problem. We create a table for each days activity. After N days (typically 7 days) we can drop the table. The table name is not known so cannot force business to make tablename something like mydata_MMDDYY I'd like to be able to do something like: SELECT tablename FROM pg_??? WHERE to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7 days', 'dd/mm/yy' ) Any suggestions? --- Prior msg was: Folks, I have a list of tables for which I want to get the date they were created...and if possible the date last updateded. I suspect there is a pg_??? table that can answer this question but I don't know what it is and I cannot find it mentioned in any docs. Any suggestions...tia -D p.s. Love this forum! ---(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] Creating Index
> The reason why view1 isn't well optimized is that you've been sloppy > about datatypes. It looks to me like the "SELECT CASE" business yields > NUMERIC while the other arm of the UNION yields INTEGER for table4.c3. > For various subtle semantic reasons we do not try to push down > conditions into UNIONs when the UNION arms yield different datatypes. Absolutely right! After switching table3.c5 to INTEGER, query to view1 is lightening fast: -- Subquery Scan view1 (cost=0.00..23.18 rows=4 width=48) (actual time=0.13..0.13 rows=0 loops=1) -> Append (cost=0.00..23.18 rows=4 width=48) (actual time=0.12..0.12 rows=0 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..23.18 rows=3 width=48) (actual time=0.11..0.11 rows=0 loops=1) -> Nested Loop (cost=0.00..23.18 rows=3 width=48) (actual time=0.10..0.10 rows=0 loops=1) -> Index Scan using i2c3c4 on table2 (cost=0.00..5.04 rows=1 width=21) (actual time=0.09..0.09 rows=0 loops=1) Index Cond: (c3 > 2003) -> Index Scan using table3_pkey on table3 (cost=0.00..18.09 rows=4 width=27) (never executed) Index Cond: (table3.c1 = "outer".c1) SubPlan -> Index Scan using table1_pkey on table1 (cost=0.00..3.01 rows=1 width=1) (never executed) Index Cond: (c1 = $2) -> Subquery Scan "*SELECT* 2" (cost=0.00..0.00 rows=1 width=12) (actual time=0.01..0.01 rows=0 loops=1) -> Seq Scan on table4 (cost=0.00..0.00 rows=1 width=12) (actual time=0.01..0.01 rows=0 loops=1) Filter: (c1 > 2003) Total runtime: 0.69 msec > 7.4 would optimize this query successfully anyway because the condition > isn't actually on the column with inconsistent datatype --- but 7.3 just > punts if *any* of the UNION columns have inconsistent datatypes. Apparently this postgreSQL beast has always been well under control by the fingers of you genious developers! Long live the king! Ooops! I'm sorry! Please pardon my English! Long live postgreSQL, the no. 1 DBMS, and its masters - the developers! Best Regards, CN -- http://www.fastmail.fm - Email service worth paying for. Try it for free ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Creating Index
Hi!
I thought I have got no more question in this thread, and I was wrong :-(
Is the extra 300ms in UNION essential?
Best Regards,
CN
--This table contains 1036 rows.
CREATE TABLE table1 (
c1 VARCHAR(20) PRIMARY KEY,
c2 "char"
)WITHOUT OIDS;
-
--This table contains 9429 rows.
CREATE TABLE table2 (
c1 VARCHAR(20) PRIMARY KEY,
c2 DATE
)WITHOUT OIDS;
-
--This table contains 28482 rows.
CREATE TABLE table3 (
CONSTRAINT fk2c1 FOREIGN KEY (c1) REFERENCES table2 (c1) ON UPDATE
CASCADE ON DELETE CASCADE,
CONSTRAINT fk2c3 FOREIGN KEY (c3) REFERENCES table1 (c1),
PRIMARY KEY (c1,c2),
c1 VARCHAR(20),
c2 INTEGER,
c3 VARCHAR(20),
c4 "char",
c5 INTEGER
)WITHOUT OIDS;
-
--This table contains 0 row.
CREATE TABLE table4 (
PRIMARY KEY (c1,c2),
c1 INTEGER, --year
c2 INTEGER, --month
c3 INTEGER
)WITHOUT OIDS;
-
EXPLAIN ANALYZE
SELECT
table2.c2 AS date
,CASE
WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D'
OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C'
THEN table3.c5 ELSE 0-table3.c5 END
AS amount
FROM table2,table3,table1
WHERE table3.c1=table2.c1
AND table1.c1=table3.c3
UNION ALL
SELECT TO_DATE(c1 || ' ' || c2 || ' 1',' MM DD'),c3 FROM table4;
---
Append (cost=199.64..2446.56 rows=28483 width=54) (actual
time=154.06..2200.53 rows=28482 loops=1)
-> Subquery Scan "*SELECT* 1" (cost=199.64..2446.56 rows=28482
width=54) (actual time=154.05..2116.96
rows=28482 loops=1)
-> Hash Join (cost=199.64..2446.56 rows=28482 width=54)
(actual time=154.03..1941.40 rows=28482
loops=1)
Hash Cond: ("outer".c1 = "inner".c1)
-> Hash Join (cost=18.95..1411.41 rows=28482 width=37)
(actual time=22.85..934.94 rows=28482
loops=1)
Hash Cond: ("outer".c3 = "inner".c1)
-> Seq Scan on table3 (cost=0.00..822.82
rows=28482 width=27) (actual
time=8.26..403.61 rows=28482 loops=1)
-> Hash (cost=16.36..16.36 rows=1036 width=10)
(actual time=14.18..14.18 rows=0
loops=1)
-> Seq Scan on table1 (cost=0.00..16.36
rows=1036 width=10) (actual
time=0.23..8.26 rows=1036 loops=1)
-> Hash (cost=157.55..157.55 rows=9255 width=17) (actual
time=130.63..130.63 rows=0 loops=1)
-> Seq Scan on table2 (cost=0.00..157.55 rows=9255
width=17) (actual time=0.24..78.22
rows=9255 loops=1)
-> Subquery Scan "*SELECT* 2" (cost=0.00..0.00 rows=1 width=12)
(actual time=0.02..0.02 rows=0 loops=1)
-> Seq Scan on table4 (cost=0.00..0.00 rows=1 width=12)
(actual time=0.01..0.01 rows=0 loops=1)
Total runtime: 2259.10 msec
-
EXPLAIN ANALYZE
SELECT
table2.c2 AS date
,CASE
WHEN (table1.c2 = 'A' OR table1.c2 = 'E') AND table3.c4 = 'D'
OR table1.c2 IN ('L','O','I') AND table3.c4 = 'C'
THEN table3.c5 ELSE 0-table3.c5 END
AS amount
FROM table2,table3,table1
WHERE table3.c1=table2.c1
AND table1.c1=table3.c3;
---
Hash Join (cost=199.64..2446.56 rows=28482 width=54) (actual
time=155.37..1857.25 rows=28482 loops=1)
Hash Cond: ("outer".c1 = "inner".c1)
-> Hash Join (cost=18.95..1411.41 rows=28482 width=37) (actual
time=23.08..891.03 rows=28482 loops=1)
Hash Cond: ("outer".c3 = "inner".c1)
-> Seq Scan on table3 (cost=0.00..822.82 rows=28482 width=27)
(actual time=8.12..386.64 rows=28482
loops=1)
-> Hash (cost=16.36..16.36 rows=1036 width=10) (actual
time=14.58..14.58 rows=0 loops=1)
-> Seq Scan on table1 (cost=0.00..16.36 rows=1036
width=10) (actual time=0.26..9.19
rows=1036 loops=1)
-> Hash (cost=157.55..157.55 rows=9255 width=17) (actual
time=131.84..131.84 rows=0 loops=1)
-> Seq Scan on table2 (cost=0.00..157.55 rows=9255 width=17)
(actual time=0.24..77.11 rows=9255
loops=1)
Total runtime: 1915.33 msec
--
http://www.fastmail.fm - A no graphics, no pop-ups email service
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] output
I am using libpg inside a plugin. For oracle conections i can read the server output with this package dbms_output.read_line (... ). For pgsql is something similar for reading server output ? Tom Lane wrote: Popeanga Marian <[EMAIL PROTECTED]> writes: Now if i don't use psql from where i can read the output ? If you're using libpq directly, you can install a notice processor hook routine to catch NOTICE messages. Otherwise they go to stderr. regards, tom lane
Re: [SQL] How to figure out when was a table created
Well, in certain filesystems you can have the birth time (like ufs2) stored in the inode struct. So you find the file name in your $PGDATA/base directory using the oid of your table (in pg_class), and then you open that file with stat (2) or utimes (2) (or from perl) to read creation data. All that apply for FreeBSD, see if creation time is supported in ext2/3. On Thu, 2 Oct 2003, David B wrote: > Hi folks, > > I posted this question a few days ago and got no response so I guess it > cannot be done (surprising!) > So that leaves me with my business problem. > > We create a table for each days activity. > After N days (typically 7 days) we can drop the table. > The table name is not known so cannot force business to make tablename > something like mydata_MMDDYY > > I'd like to be able to do something like: > SELECT tablename > FROM pg_??? > WHERE to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7 days', > 'dd/mm/yy' ) > > Any suggestions? > > --- Prior msg was: > > Folks, > > I have a list of tables for which I want to get the date they were > created...and if possible the date last updateded. > > I suspect there is a pg_??? table that can answer this question but I don't > know what it is and I cannot find it mentioned in any docs. > > Any suggestions...tia > -D > > p.s. Love this forum! > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
