Re: [SQL] How to cast, if type has spaces in the name
am Thu, dem 26.07.2007, um 0:33:09 -0700 mailte Bryce Nesbitt folgendes: How do I specify a cast, if the type name has spaces? foo::integer is easy, but foo::'timestamp without time zone' is more murky. Hehe, it works without the ', see: test=# select '2007-01-01'::timestamp without time zone; timestamp - 2007-01-01 00:00:00 (1 row) test=*# select '2007-01-01'::timestamp with time zone; timestamptz 2007-01-01 00:00:00+01 (1 row) ndreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Join question
Phillip Smith wrote: Whoops, I forgot the JOIN conditions! Fixed below -Original Message- From: Phillip Smith [mailto:[EMAIL PROTECTED] Sent: Friday, 27 July 2007 11:47 To: 'pgsql-sql@postgresql.org' Subject: RE: [SQL] Join question This might give you a starting point if I understand you correctly... SELECT h.invoice_number, h.customer, l.item, l.amount FROMlines AS l JOINheaders AS h ON l.invoice_number = h.invoice_number UNION SELECT h.invoice_number, h.customer, s.item, s.amount FROMsundries AS s JOINheaders AS h ON s.invoice_number = h.invoice_number ORDER BY invoice_number, item Forgot all about union - I had two individual views, one for invoice+parts, one for invoice+sundries... didn't think of union to combine the two views together. Perfect solution, you've done it for me again Phillip, thanks. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Join question
I have a database in a parts sales environment that I am having a little difficulty with a join query - trying to figure out which way to join things. I have a parts_invoice_header table, containing the header record for each invoice. I have a parts_invoice_lines table, containing the parts details for each invoice. I have a parts_invoice_sundries table, containing additional sundry charges (freight, delivery etc) on each invoice. For each record in the invoice_header table, there can be 0 or more records in either of the two other tables. I want to search for an invoice in the header file and get the details of all matching records from the lines and sundries. Assuming the lines and sundries tables both have the same column names, I should be able to end up with something like: table |header header line/sundries | column| Invoice numbercustomeritem amount line | abc 457ABC 10.00 sundry| abc FREIGHT 5.00 line | abc FGOIL 15.00 What would be the best method of joining to create a result-set such as this? TIA for any assistance, P. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Join question
Whoops, I forgot the JOIN conditions! Fixed below -Original Message- From: Phillip Smith [mailto:[EMAIL PROTECTED] Sent: Friday, 27 July 2007 11:47 To: 'pgsql-sql@postgresql.org' Subject: RE: [SQL] Join question This might give you a starting point if I understand you correctly... SELECT h.invoice_number, h.customer, l.item, l.amount FROMlines AS l JOINheaders AS h ON l.invoice_number = h.invoice_number UNION SELECT h.invoice_number, h.customer, s.item, s.amount FROMsundries AS s JOINheaders AS h ON s.invoice_number = h.invoice_number ORDER BY invoice_number, item ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments THINK BEFORE YOU PRINT - Save paper if you don't really need to print this e-mail. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Join question
This might give you a starting point if I understand you correctly... SELECT h.invoice_number, h.customer, l.item, l.amount FROMlines AS l JOINheaders AS h UNION SELECT h.invoice_number, h.customer, s.item, s.amount FROMsundries AS s JOINheaders AS h ORDER BY invoice_number, item ***Confidentiality and Privilege Notice*** The material contained in this message is privileged and confidential to the addressee. If you are not the addressee indicated in this message or responsible for delivery of the message to such person, you may not copy or deliver this message to anyone, and you should destroy it and kindly notify the sender by reply email. Information in this message that does not relate to the official business of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta. Weatherbeeta, its employees, contractors or associates shall not be liable for direct, indirect or consequential loss arising from transmission of this message or any attachments THINK BEFORE YOU PRINT - Save paper if you don't really need to print this e-mail. ---(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] unique index on fields with possible null values
Hello pgsql-sql, I'm trying to find a solution for unique index on fields with possible null values. Example table: CREATE TABLE test ( a integer NOT NULL, b integer NULL ); As long as unique index can't check if there are records with null values i found the only one solution for this problem: CREATE UNIQUE INDEX test_uniq1 ON test (a, COALESCE(b,0)); Are there any other ways of doing this? -- Best regards, Dmitry mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to cast, if type has spaces in the name
On Thu, 26 Jul 2007, Bryce Nesbitt wrote: How do I specify a cast, if the type name has spaces? foo::integer is easy, but foo::'timestamp without time zone' is more murky. foo::timestamp without time zone should work (no quotes). Another alternative if you don't like the way that looks is to use the SQL cast syntax, CAST(foo AS timestamp without time zone). ---(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] How to cast, if type has spaces in the name
How do I specify a cast, if the type name has spaces? foo::integer is easy, but foo::'timestamp without time zone' is more murky. In my case I have a table, and a view. For no apparent reason the table has timestamp without time zone, but I need timestamp with time zone. I'm using select column at time zone 'PST8PDT' to solve this problem. But wonder about the general case of typecasting when the type name is not a single symbol. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to cast, if type has spaces in the name
am Thu, dem 26.07.2007, um 0:33:09 -0700 mailte Bryce Nesbitt folgendes: How do I specify a cast, if the type name has spaces? foo::integer is easy, but foo::'timestamp without time zone' is more murky. Use timestamp instead ;-) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] The nested view from hell - Restricting a subquerry
One down. Total runtime of the simplest query went from 34661.572 ms to
.634 ms (45,000 times faster).
stage= explain analyze select * from eg_order_summary_view where
invoice_id=1432655;
QUERY
PLAN
---
HashAggregate (cost=47.75..48.60 rows=13 width=214) (actual
time=0.444..0.467 rows=9 loops=1)
- Nested Loop Left Join (cost=0.00..46.76 rows=21 width=214)
(actual time=0.037..0.175 rows=14 loops=1)
- Index Scan using ix522779518edf278d on eg_order
(cost=0.00..4.70 rows=13 width=200) (actual time=0.020..0.034 rows=9
loops=1)
Index Cond: (invoice_id = 1432655)
- Index Scan using ixf8331222783867cc on eg_order_line
(cost=0.00..3.21 rows=2 width=18) (actual time=0.007..0.010 rows=2 loops=9)
Index Cond: (outer.order_id = eg_order_line.order_id)
Total runtime: 0.645 ms
(7 rows)
stage= \d eg_order_summary_view;
View definition:
SELECT eg_order.order_id, 'D' AS d, max(eg_order.cso_id) AS cso_id,
eg_order.invoice_id, max(eg_order.period_id) AS period_id,
max(eg_order.ref_id) AS ref_id, max(eg_order.order_type::integer) AS
order_type, max(eg_order.desc1::text) AS desc1,
max(eg_order.desc2::text) AS desc2, max(eg_order.desc3::text) AS desc3,
max(eg_order.desc4::text) AS desc4, max(eg_order.desc5::text) AS desc5,
max(eg_order.desc6::text) AS desc6, max(eg_order.desc7::text) AS desc7,
max(eg_order.desc8::text) AS desc8, max(timezone('PST8PDT'::text,
eg_order.order_from)) AS order_from, max(timezone('PST8PDT'::text,
eg_order.order_to)) AS order_to, sum(
CASE
WHEN eg_order_line.order_line_type 20 THEN
eg_order_line.quantity
ELSE 0::double precision
END) AS hours, sum(
CASE
WHEN eg_order_line.order_line_type = 20 THEN
eg_order_line.quantity
ELSE 0::double precision
END) AS mileage, sum(eg_order_line.amt_value) AS amount
FROM eg_order
LEFT JOIN eg_order_line USING (order_id)
GROUP BY eg_order.order_id, eg_order.invoice_id;
---(end of broadcast)---
TIP 6: explain analyze is your friend
