[SQL] User defined types
Hello: I have a user define type called VALID_TIME. It is declared as follows: CREATE TYPE VALID_TIME AS (t_s TIMESTAMP, t_e TIMESTAMP); Once I have used this in a table declaration, is it possible to extract parts of it, for example to only read t_s or t_e? If so, how do I do this? Thank you. -Reza ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] NULLS and string concatenation
There is an easy solution anyway, use coalesce to ensure you are never returning a null result for any components of the concat. e.g. select 'some text, blah:' || coalesce(NULL, '') equates to 'some text, blah:' || '' hence 'some text, blah:' Terry Fielder Manager Software Development and Deployment Great Gulf Homes / Ashton Woods Homes [EMAIL PROTECTED] Fax: (416) 441-9085 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Stephan Szabo > Sent: Friday, November 19, 2004 2:04 PM > To: Don Drake > Cc: Richard Huxton; [EMAIL PROTECTED] > Subject: Re: [SQL] NULLS and string concatenation > > > > On Fri, 19 Nov 2004, Don Drake wrote: > > > On Fri, 19 Nov 2004 17:48:34 +, Richard Huxton > <[EMAIL PROTECTED]> wrote: > > > Don Drake wrote: > > > > select 'some text, should be null:'|| NULL > > > > > > > > This returns NULL and no other text. Why is that? I > wasn't expecting > > > > the "some text.." to disappear altogether. > > > > > > > > Is this a bug? > > > > > > No. Null is "unknown" if you append unknown (null) to a > piece of text, > > > the result is unknown (null) too. > > > > > > If you're using NULL to mean something other than > unknown, you probably > > > want to re-examine your reasons why. > > > > > > > I'm using NULL to mean no value. Logically, NULL is > unknown, I agree. > > > > I'm trying to dynamically create an INSERT statement in a function > > that sometimes receives NULL values. > > > > This is still strange to me. In Oracle, the same query would not > > replace the *entire* string with a NULL, it treats the NULL as a no > > value. > > Oracle has some incompatibilities with the SQL spec (at least > 92/99) wrt > NULLs and empty strings so it isn't a good comparison point. > The spec is > pretty clear that if either argument to concatenation is NULL > the output > is NULL. > > > I can't find in the documentation where string concatenation of any > > string and NULL is NULL. > > I'm not sure it does actually. I'd have expected to see some > general text > on how most operators return NULL for NULL input but a quick > scan didn't > find any. > > > ---(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 > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] PG7.4.5: query not using index on date column
Hi Folks, I was hoping someone could help me to improve the performance of a
query I've got that insists on doing a seq. scan on a large table. I'm trying
to do some reporting based on my spam logs which I've partly digested and
stored in a table. Here are the particulars:
The messages table:
Column | Type | Modifiers
---+---+--
message_id| integer | not null default
nextval('spamreport.messages_message_id_seq'::text)
received_date | date | not null
score | numeric | not null
user_threshold| numeric | not null
raw_email_address | character varying(64) | not null
processing_time | numeric | not null
size | integer | not null
fuzzed_address| character varying(64) | not null
domain| character varying(64) | not null
Indexes:
"messages_pkey" primary key, btree (message_id)
"domain_idx" btree ("domain")
"fuzzy_idx" btree (fuzzed_address)
"received_date_idx" btree (received_date)
And here's the primary query I run, along with explain analyze output:
>> explain analyze SELECT * FROM ( SELECT
domain,
count(*) as count,
max(score) as max_score,
avg(score) as average_score,
stddev(score) as stddev_score,
sum(CASE WHEN score > user_threshold THEN 1 ELSE 0 END) as spam_count,
avg(processing_time) as average_time,
avg(size) as average_size
FROM messages
WHERE received_date BETWEEN '2004-11-01' AND '2004-11-30'
GROUP BY domain ) AS aggs
ORDER BY count DESC;
QUERY PLAN
---
Sort (cost=30303.51..30303.60 rows=35 width=226) (actual
time=29869.716..29869.883 rows=69 loops=1)
Sort Key: count
-> Subquery Scan aggs (cost=30301.56..30302.61 rows=35 width=226) (actual
time=29861.705..29869.240 rows=69 loops=1)
-> HashAggregate (cost=30301.56..30302.26 rows=35 width=54) (actual
time=29861.681..29868.261 rows=69 loops=1)
-> Seq Scan on messages (cost=0.00..21573.04 rows=436426
width=54) (actual time=5.523..6304.657 rows=462931 loops=1)
Filter: ((received_date >= '2004-11-01'::date) AND
(received_date <= '2004-11-30'::date))
Total runtime: 29870.437 ms
This database gets vacuumed nightly. Also, the query plan stays the same even
if I restrict the received_date column down to a single day.
Thanks in advance,
--
Dave Steinberg
http://www.geekisp.com/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] User defined types
Reza Shanbehbazari Mirzaei <[EMAIL PROTECTED]> writes: > I have a user define type called VALID_TIME. It is declared as follows: > CREATE TYPE VALID_TIME AS (t_s TIMESTAMP, t_e TIMESTAMP); > Once I have used this in a table declaration, is it possible to extract > parts of it, for example to only read t_s or t_e? If so, how do I do this? 8.0 supports using composite types as table columns, but prior versions don't really. In 8.0 you'd do something like create table myt (vt valid_time); select (vt).t_s from myt; or select (myt.vt).t_s from myt; The parentheses are essential --- without them, you'd have for instance select vt.t_s from myt; which looks like a reference to field t_s of table vt, not what you want. You can hack around the problem in earlier versions by creating helper functions, eg select get_t_s(vt) from myt; but it's ugly enough to make one wonder why bother with a composite type. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] HowTo change encoding type....
Hi, how do I change the encoding type in postgreSQL (8) from UTF-8 to ISO-8859-1? many thanks Andrew <> +The home of urban music + http://www.beyarecords.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] PG7.4.5: query not using index on date column
Dave Steinberg <[EMAIL PROTECTED]> writes: >-> Seq Scan on messages (cost=0.00..21573.04 rows=436426 > width=54) (actual time=5.523..6304.657 rows=462931 loops=1) > Filter: ((received_date >= '2004-11-01'::date) AND > (received_date <= '2004-11-30'::date)) How many rows in the table altogether? A rough guess is a few million based on the estimated seqscan cost. That would mean that this query is retrieving about 10% of the table, which is a large enough fraction that the planner will probably think a seqscan is best. It may be right. If you do "set enable_seqscan = off", how does the EXPLAIN ANALYZE output change? If it's not right, you may want to try to adjust random_page_cost and/or effective_cache_size so that the planner's estimated costs are more in line with reality. Beware of making such adjustments on the basis of only one test case, though. 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] Type Inheritance
Does anyone know how to implement type inheritance in postgresql? in oracle you just use the word UNDER in ur code i.e: CREATE TYPE test2_UDT UNDER test1_UDT AS (abc INT); any ideas? -- __ Check out the latest SMS services @ http://www.linuxmail.org This allows you to send and receive SMS through your mailbox. Powered by Outblaze ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] select with a function
Hi all,
i need some help
i wanna do a select with a function call like this:
SELECT academico.aca_f_siguientecurso(
academico.aca_t_alumnocurso.ent_codigo,
academico.aca_t_alumnocurso.sec_codigo,
academico.aca_t_alumnocurso.ani_codigo,
academico.aca_t_alumnocurso.cic_codigo,
academico.aca_t_alumnocurso.esp_codigo,
academico.aca_t_alumnocurso.cur_codigo,
academico.aca_t_alumnocurso.cur_paralelo,
academico.aca_t_alumnocurso.est_codigo),
NULL as asp_codigo,
academico.aca_t_alumnocurso.alu_codigo
FROM academico.aca_t_alumnocurso
WHERE academico.aca_t_alumnocurso.est_codigo IN
('AP', 'RP', 'RT');
the function academico.aca_f_siguientecurso returns a
custom type (a record).
ok, in the IRC AndrewSN told me this won't work in a
pre 8 pgsql, so i will need do it in a subselect but
the function has to be called once per row in
academico.aca_t_alumnocurso that matches the where.
but I don't know any easy way of doing that
for each row in a query.
any ideas?
regards,
Jaime Casanova
_
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] HowTo change encoding type....
Andrew M wrote: > how do I change the encoding type in postgreSQL (8) from UTF-8 to > ISO-8859-1? Dump your database, drop your database, recreate your database with the different encoding, reload your data. Make sure the client encoding is set correctly during all this. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
