Re: [SQL] Return relation table data in a single value CSV
On Tuesday 17 February 2004 23:33, Tom Lane wrote: > Richard Huxton <[EMAIL PROTECTED]> writes: > > 2. Write a custom aggregate function (like sum()) to do the > > concatenation. This is easy to do, but the order your ABC get processed > > in is undefined. > > Actually, as of 7.4 it is possible to control the order of inputs to a > custom aggregate. You do something like this: > > SELECT foo, myagg(bar) FROM > (SELECT foo, bar FROM table ORDER BY foo, baz) AS ss > GROUP BY foo > > The inner sub-select must order by the columns that the outer will group > on; it can then order by additional columns that determine the sort > order within each group. Here, myagg() will see its input ordered by > increasing values of baz. Hmm - good to know, but I'm always wary of doing this sort of thing. It's exactly the sort of trick I look at 18 months later, fail to read my own comments and "tidy" it. -- Richard Huxton Archonet Ltd ---(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] CHAR(n) always trims trailing spaces in 7.4
On Wednesday 18 February 2004 00:25, Tom Lane wrote: > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > But then this: > > select 'x'||' '||'x' > > should produce xx, but it produces x x. > > No, because the imputed type of those literals is text. You'd have to > cast the middle guy to char(n) explicitly to make its trailing spaces go > away when it's reconverted to text. > > The real issue here is that trailing spaces in char(n) are semantically > insignificant according to the SQL spec. The spec is pretty vague about > which operations should actually honor that insignificance --- it's > clear that comparisons should, less clear about other things. I think > the 7.4 behavior is more consistent than what we had before, but I'm > willing to be persuaded to change it again if someone can give an > alternate definition that's more workable than this one. [rant on] I've never really understood the rationale behind char(n) in SQL databases (other than as backward compatibility with some old mainframe DB). Insignificant spaces? If it's not significant, why is it there? You could have a formatting rule that specifies left-aligned strings space-padded (as printf) but that's not the same as mucking about appending and trimming spaces. The only sensible definition of char(n) that I can see would be: A text value of type char(n) is always "n" characters in length. If you assign less than "n" characters, it is right-padded with spaces. In all other respects it behaves as any other text type of length "n" with right-trailing spaces. [rant off - ah, feel better for that :-] -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] bytea or blobs?
On Wednesday 18 February 2004 06:44, Dana Hudes wrote: > > At least with base64 I have ample libraries and can convert my data > before sending to sql or after receiving from sql. It becomes my > application's issue. Mind, this bloats the data considerably. > escape is less bloat but I have to recreate the encode/decode in my app, > so far as I see. Less bloat than you might expect - large values are TOASTed and compressed. I'm guessing a lot of your redundancy will be eliminated. Having said that, bytea's purpose in life is to store your binary data. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] bytea or blobs?
How can one measure the result of the compression -- can I see this in some table or with some pgsql command? At what threshold does it take place, I think its 8192? The nasty bit is not one picture of 100kb. Its 20 pictures of 5kb. On Wed, 18 Feb 2004, Richard Huxton wrote: > On Wednesday 18 February 2004 06:44, Dana Hudes wrote: > > > > At least with base64 I have ample libraries and can convert my data > > before sending to sql or after receiving from sql. It becomes my > > application's issue. Mind, this bloats the data considerably. > > escape is less bloat but I have to recreate the encode/decode in my app, > > so far as I see. > > Less bloat than you might expect - large values are TOASTed and compressed. > I'm guessing a lot of your redundancy will be eliminated. > > Having said that, bytea's purpose in life is to store your binary data. > > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Indexes and statistics
"David Witham" <[EMAIL PROTECTED]> writes: > Does this make it a "wide" table? Nope. A rough estimate is that your rows will be about 160 bytes wide, which means you can fit about 50 per 8K page. So a query that needs to select 8% of the table will *on average* need to hit about 4 rows per page. In the absence of any data clumping this would certainly mean that the scan would need to touch every page anyway, and thus that using the index could provide no I/O savings. However, > The data arrives ordered by service_num, day, time. This customer has > one primary service_num that most of the calls are made from. So you do have very strong clumping, which the planner is evidently failing to account for properly. Could we see the pg_stats rows for service_num and cust_id? I'm curious whether the ANALYZE stats picked up the effect at all. As far as actually solving the problem is concerned, you have a few options. I wouldn't recommend turning off enable_seqscan globally, but you could perhaps turn it off locally (just do a SET command) just for this query. Another possibility, if you care a lot about the speed of this particular type of query, is to make a partial index tuned to the query: create index my_idx on cdr (cust_id) WHERE bill_id IS NULL; I gather from your previously shown results that "bill_id IS NULL" covers only a small fraction of the table, so this index would be pretty small and should look quite attractive to the planner. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
Richard Huxton <[EMAIL PROTECTED]> writes:
> I've never really understood the rationale behind char(n) in SQL databases
> (other than as backward compatibility with some old mainframe DB).
There are (or were) systems in which the benefit of using fixed-width
columns is a lot higher than it is in Postgres. The spec is evidently
trying to cater to them. Too bad the writers whacked the semantics
around so cruelly to do it :-(
> The only sensible definition of char(n) that I can see would be:
> A text value of type char(n) is always "n" characters in length.
If the SQL spec were willing to leave it at that, I'd be happy. But
we've got this problem that the trailing spaces are supposed to be
insignificant in at least some contexts. I find the pre-7.4 behavior
to be pretty inconsistent. For example, 7.3 and 7.4 agree on this:
regression=# select ('foo '::char(6)) = ('foo');
?column?
--
t
(1 row)
Now given the above, wouldn't it stand to reason that
regression=# select ('foo '::char(6) || 'bar') = ('foo' || 'bar');
?column?
--
f
(1 row)
or how about
regression=# select ('bar' || 'foo '::char(6)) = ('bar' || 'foo');
?column?
--
f
(1 row)
In 7.4 both of these do yield true. A closely related example is
regression=# select ('foo '::char(6)) = ('foo'::text);
which yields false in 7.3 and true in 7.4.
I don't object to revisiting the behavior again, but 7.3 was not so
ideal that I want to just go back to it.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] bytea or blobs?
On Wednesday 18 February 2004 15:17, Dana Hudes wrote: > How can one measure the result of the compression -- can I see this in > some table or with some pgsql command? Hmm - not so far as I know. > At what threshold does it take place, I think its 8192? > The nasty bit is not one picture of 100kb. > Its 20 pictures of 5kb. I'd have thought 5KB would trigger it - 8192 is the limit for a row (which TOASTing is designed to remove). There are/were some technical notes on TOAST when it was being built/introduced - googling might well find them. What I'd suggest is grab some suitably representative images, base64 encode them and see how much it takes to store 100,000 copies of them. -- Richard Huxton Archonet Ltd ---(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
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
Also, to make char(n) even more annoying, I had the one character value "K" stored in a column that was char(2). When I pulled it from the database and tried to compare it to a variable with a value of "K" it came out inequal. Of course in mysql, that was not a problem. Jeremy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Richard Huxton Sent: Wednesday, February 18, 2004 4:40 AM To: Tom Lane; scott.marlowe Cc: elein; news.postgresql.org; [EMAIL PROTECTED] Subject: Re: [SQL] CHAR(n) always trims trailing spaces in 7.4 On Wednesday 18 February 2004 00:25, Tom Lane wrote: > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > But then this: > > select 'x'||' '||'x' > > should produce xx, but it produces x x. > > No, because the imputed type of those literals is text. You'd have to > cast the middle guy to char(n) explicitly to make its trailing spaces go > away when it's reconverted to text. > > The real issue here is that trailing spaces in char(n) are semantically > insignificant according to the SQL spec. The spec is pretty vague about > which operations should actually honor that insignificance --- it's > clear that comparisons should, less clear about other things. I think > the 7.4 behavior is more consistent than what we had before, but I'm > willing to be persuaded to change it again if someone can give an > alternate definition that's more workable than this one. [rant on] I've never really understood the rationale behind char(n) in SQL databases (other than as backward compatibility with some old mainframe DB). Insignificant spaces? If it's not significant, why is it there? You could have a formatting rule that specifies left-aligned strings space-padded (as printf) but that's not the same as mucking about appending and trimming spaces. The only sensible definition of char(n) that I can see would be: A text value of type char(n) is always "n" characters in length. If you assign less than "n" characters, it is right-padded with spaces. In all other respects it behaves as any other text type of length "n" with right-trailing spaces. [rant off - ah, feel better for that :-] -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Inserting NULL into Integer column
Hi,
in mysql I was able to make an insert such as:
INSERT INTO TABLE (integervariable) VALUES ('')
and have it either insert that variable, or insert the default if it had
been assigned. In postgresql it gives and error every time that this is
attempted. Since I have so many queries that do this on my site already, is
there any way to set up a table so that it just accepts this sort of query?
Thanks,
Jeremy
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Jeremy Smith wrote:
> Hi,
>
> in mysql I was able to make an insert such as:
>
> INSERT INTO TABLE (integervariable) VALUES ('')
>
> and have it either insert that variable, or insert the default if it had
> been assigned. In postgresql it gives and error every time that this is
> attempted. Since I have so many queries that do this on my site already, is
> there any way to set up a table so that it just accepts this sort of query?
First off, the reason for this problem is that Postgresql adheres to the
SQL standard while MySQL heads off on their own, making it up as they go
along. This causes many problems for people migrating from MySQL to
almost ANY database.
Phew, now that that's out of the way, here's the standard ways of doing
it.
Use DEFAULT: If no default is it will insert a NULL, otherwise the
default will be inserted:
insert into table (integervar) values (DEFAULT);
OR
Leave it out of the list of vars to be inserted
insert into table (othervars, othervars2) values ('abc',123);
OR
Insert a NULL if that's what you want:
insert into table (integervar) values (NULL);
Note that NULL and DEFAULT are not quoted.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] CHAR(n) always trims trailing spaces in 7.4
So exactly what is the order of casts that produces different results with: 'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x' Are operators being invoked both (text,text)? I'm trying to understand the precedence that causes the different results. elein On Tue, Feb 17, 2004 at 10:53:17PM -0500, Tom Lane wrote: > elein <[EMAIL PROTECTED]> writes: > > Apparently the ::char is cast to varchar and then text? > > No, directly to text, because the || operator is defined as taking text > inputs. But there's no practical difference between text and varchar on > this point. > > regards, tom lane > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Inserting NULL into Integer column
Scott,
I understand that MySQL's adherence to the standards must be lazy as I am
running into frequent issues as I transfer my site. Unfortunately I have
over 2500 queries, and many more of them needed to be rewritten than I ever
would have imagined. I guess MySQL is the IE of open source DB, and
PostgreSQL is Netscape / Mozilla, in more ways than one.
I guess in some sense, since I relied on MySQL's laziness, my code also
became a bit lazy. There are many locations where I accept user input from
a form, and then have a process page. And on that process page I might have
hundreds of variables that look like:
$input = $_POST['input'];
and in the old days, if that was an empty value and inserted into a mysql
query, it would just revert to the default. Now it looks like I need to:
$input = $_POST['input'];
if (!$input) {
$input = DEFAULT;
}
over and over and over and over :) I guess I am just looking for a
shortcut since the site conversion has already taken a week and counting,
when I originally was misguided enough to think it would take hours.
Anyway, the help on this list is much appreciated..
Jeremy
-Original Message-
From: scott.marlowe [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 18, 2004 2:44 PM
To: Jeremy Smith
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Jeremy Smith wrote:
> Hi,
>
> in mysql I was able to make an insert such as:
>
> INSERT INTO TABLE (integervariable) VALUES ('')
>
> and have it either insert that variable, or insert the default if it had
> been assigned. In postgresql it gives and error every time that this is
> attempted. Since I have so many queries that do this on my site already,
is
> there any way to set up a table so that it just accepts this sort of
query?
First off, the reason for this problem is that Postgresql adheres to the
SQL standard while MySQL heads off on their own, making it up as they go
along. This causes many problems for people migrating from MySQL to
almost ANY database.
Phew, now that that's out of the way, here's the standard ways of doing
it.
Use DEFAULT: If no default is it will insert a NULL, otherwise the
default will be inserted:
insert into table (integervar) values (DEFAULT);
OR
Leave it out of the list of vars to be inserted
insert into table (othervars, othervars2) values ('abc',123);
OR
Insert a NULL if that's what you want:
insert into table (integervar) values (NULL);
Note that NULL and DEFAULT are not quoted.
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Jeremy Smith wrote:
> Scott,
>
> I understand that MySQL's adherence to the standards must be lazy as I am
> running into frequent issues as I transfer my site. Unfortunately I have
> over 2500 queries, and many more of them needed to be rewritten than I ever
> would have imagined. I guess MySQL is the IE of open source DB, and
> PostgreSQL is Netscape / Mozilla, in more ways than one.
Good comparison.
> I guess in some sense, since I relied on MySQL's laziness, my code also
> became a bit lazy. There are many locations where I accept user input from
> a form, and then have a process page. And on that process page I might have
> hundreds of variables that look like:
>
> $input = $_POST['input'];
>
> and in the old days, if that was an empty value and inserted into a mysql
> query, it would just revert to the default. Now it looks like I need to:
>
> $input = $_POST['input'];
> if (!$input) {
> $input = DEFAULT;
> }
I've run into this kind of thing before. IT helps if you have an array of
all your fields like:
$fields = array("field1","field3","last_name");
and then you can foreach across the input:
foreach($fields as $f){
if (!$_POST[$f]){
$_POST[$f]='DEFAULT';
} else {
$_POST[$f] = "'".$_POST[$f]."'";
}
}
> over and over and over and over :) I guess I am just looking for a
> shortcut since the site conversion has already taken a week and counting,
> when I originally was misguided enough to think it would take hours.
Well, you might find yourself rewriting fair portions of your site, but
usually you wind up with better code and better checking, so it's a bit of
a trade off.
> Anyway, the help on this list is much appreciated..
>
> Jeremy
>
> -Original Message-
> From: scott.marlowe [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, February 18, 2004 2:44 PM
> To: Jeremy Smith
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] Inserting NULL into Integer column
>
>
> On Wed, 18 Feb 2004, Jeremy Smith wrote:
>
> > Hi,
> >
> > in mysql I was able to make an insert such as:
> >
> > INSERT INTO TABLE (integervariable) VALUES ('')
> >
> > and have it either insert that variable, or insert the default if it had
> > been assigned. In postgresql it gives and error every time that this is
> > attempted. Since I have so many queries that do this on my site already,
> is
> > there any way to set up a table so that it just accepts this sort of
> query?
>
> First off, the reason for this problem is that Postgresql adheres to the
> SQL standard while MySQL heads off on their own, making it up as they go
> along. This causes many problems for people migrating from MySQL to
> almost ANY database.
>
> Phew, now that that's out of the way, here's the standard ways of doing
> it.
>
> Use DEFAULT: If no default is it will insert a NULL, otherwise the
> default will be inserted:
> insert into table (integervar) values (DEFAULT);
>
> OR
>
> Leave it out of the list of vars to be inserted
> insert into table (othervars, othervars2) values ('abc',123);
>
> OR
>
> Insert a NULL if that's what you want:
>
> insert into table (integervar) values (NULL);
>
> Note that NULL and DEFAULT are not quoted.
>
>
>
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Inserting NULL into Integer column
I've run into this kind of thing before. IT helps if you have an array of
all your fields like:
$fields = array("field1","field3","last_name");
and then you can foreach across the input:
foreach($fields as $f){
if (!$_POST[$f]){
$_POST[$f]='DEFAULT';
} else {
$_POST[$f] = "'".$_POST[$f]."'";
}
}
Wow, great idea. I will definitely do this, thanks alot.
Well, you might find yourself rewriting fair portions of your site, but
usually you wind up with better code and better checking, so it's a bit of
a trade off.
No doubt that this is true. Of course even without the better code and
error checking, the extra features like stored procedures and automatic row
locking was more than enough to make the switch worth it.
Thanks again!
Jeremy
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Jeremy Smith wrote:
>
>
> I've run into this kind of thing before. IT helps if you have an array of
> all your fields like:
>
> $fields = array("field1","field3","last_name");
>
> and then you can foreach across the input:
>
> foreach($fields as $f){
> if (!$_POST[$f]){
> $_POST[$f]='DEFAULT';
> } else {
> $_POST[$f] = "'".$_POST[$f]."'";
> }
> }
>
> Wow, great idea. I will definitely do this, thanks alot.
>
>
>
> Well, you might find yourself rewriting fair portions of your site, but
> usually you wind up with better code and better checking, so it's a bit of
> a trade off.
>
>
> No doubt that this is true. Of course even without the better code and
> error checking, the extra features like stored procedures and automatic row
> locking was more than enough to make the switch worth it.
>
> Thanks again!
You're welcome! Enjoy getting to know Postgresql and all the great folks
on the lists, I know I have.
---(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] Inserting NULL into Integer column
> and then you can foreach across the input:
>
> foreach($fields as $f){
> if (!$_POST[$f]){
> $_POST[$f]='DEFAULT';
> } else {
> $_POST[$f] = "'".$_POST[$f]."'";
> }
> }
Default in quotes isn't going to work, and please tell me you escape
those things with pg_escape_string() at some point.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Rod Taylor wrote:
> > and then you can foreach across the input:
> >
> > foreach($fields as $f){
> > if (!$_POST[$f]){
> > $_POST[$f]='DEFAULT';
> > } else {
> > $_POST[$f] = "'".$_POST[$f]."'";
> > }
> > }
>
> Default in quotes isn't going to work, and please tell me you escape
> those things with pg_escape_string() at some point.
Note that the ' marks aren't part of the string, they are the delimiter of
the string, and I always run every server with magic_quotes_gpc on.
anything else? :-)
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Inserting NULL into Integer column
> Note that the ' marks aren't part of the string, they are the delimiter of > the string, and I always run every server with magic_quotes_gpc on. > > anything else? :-) Good point. I looked at the single quotes of the second line and somehow the DEFAULT got quoted as well ;) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Inserting NULL into Integer column
On Wed, 18 Feb 2004, Rod Taylor wrote: > > Note that the ' marks aren't part of the string, they are the delimiter of > > the string, and I always run every server with magic_quotes_gpc on. > > > > anything else? :-) > > Good point. I looked at the single quotes of the second line and somehow > the DEFAULT got quoted as well ;) Oh, and I'm stuck using add_slashes (or the magic_quotes_gpc thingie) 'cause I'm on a server that's being eoled in favor of .net, and it's running PHP 4.0.6... ugh. We really gotta get it upgraded soon. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Need some help with crafting a query to do major update
To all,
This is part of a data warehouse. Made the mistake of using a natural
key in one of the fact tables. :-( The f_test_pageviews is a simple
testing table while I work this out. The real table has an identical
schema.
I have built a mapping table, d_user, to allow the replacement of the
text based (32 characters wide) subscriber_key in f_test_pageviews with
an int4 mapping key. I need to replace all of the
f_test_pageviews.subscriber_key values with the d_user.id value putting
it in f_test_pageviews.sub_key column.
I have tried this sql:
update f_test_pageviews set sub_key = t2.id from f_test_pageviews t1,
d_user t2 where t1.subscriber_key = t2.user_id;
but it is taking forever to complete. I would appreciate it if anyone
could tell me a faster way to do this. I have to update 250 million
plus rows over 4 tables. (We break the page view tables into calendar
months)
Thanks.
--sean
Table "public.d_user"
Column | Type | Modifiers
-+-+
id | integer | not null default nextval('public.d_user_id_seq'::text)
user_id | text| not null
Indexes:
"d_user_pkey" primary key, btree (id)
"d_user_user_id_key" unique, btree (user_id)
Table "public.f_test_pageviews"
Column | Type | Modifiers
+-+---
id | integer |
date_key | integer |
time_key | integer |
content_key| integer |
location_key | integer |
session_key| integer |
subscriber_key | text|
persistent_cookie_key | integer |
ip_key | integer |
referral_key | integer |
servlet_key| integer |
tracking_key | integer |
provider_key | text|
marketing_campaign_key | integer |
orig_airport | text|
dest_airport | text|
commerce_page | boolean |
job_control_number | integer |
sequenceid | integer |
url_key| integer |
useragent_key | integer |
web_server_name| text|
cpc| integer |
referring_servlet_key | integer |
first_page_key | integer |
newsletterid_key | text|
sub_key| integer |
Indexes:
"idx_temp_pageviews_id" unique, btree (id)
---(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] Need some help with crafting a query to do major update
On Wed, 18 Feb 2004, Sean Shanny wrote: > To all, > > This is part of a data warehouse. Made the mistake of using a natural > key in one of the fact tables. :-( The f_test_pageviews is a simple > testing table while I work this out. The real table has an identical > schema. > > I have built a mapping table, d_user, to allow the replacement of the > text based (32 characters wide) subscriber_key in f_test_pageviews with > an int4 mapping key. I need to replace all of the > f_test_pageviews.subscriber_key values with the d_user.id value putting > it in f_test_pageviews.sub_key column. > > I have tried this sql: > > update f_test_pageviews set sub_key = t2.id from f_test_pageviews t1, > d_user t2 where t1.subscriber_key = t2.user_id; I don't think the above does what you want because I don't think you meant to be joining f_test_pageviews in twice (once as the table to be updated and once as t1) or at least not without limiting which rows you want to update. I think you probably just want: update f_test_pageviews set sub_key=t2.id from d_user t2 where f_test_pageviews.subscriber_key=t2.user_id; ---(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
Re: [SQL] Indexes and statistics
> The computed cost of using the index was a factor of 10 higher which I presume is why the query planner wasn't > using the index, but it ran in half the time Have you tried playing with the random_page_cost parameter? The default is 4. Try: set random_page_cost = 1; in psql to alter it for the current session (you can change this in postgresql.conf too). This will make index usage more attractive by reducing the computed cost. This is the simple way of looking at it anyway. On my system I tested a 'typical' query exercising some joins on large tables which didn't use an index, but I thought maybe it would perform better if it did. I determined that a random_page_cost of 1.8 would cause indexes to be used, but in this case the *actual* performance didn't improve very much. I took this to mean that a random_page_cost of around 1.8/1.9 represents a rough balance point on my development server (one slow IDE disk, and a big database). As well as the other things mentioned by Tom, perhaps you should be looking for the "correct" setting of random_page_cost for your system. It may be appropriate to alter it globally using postgresql.conf, and for specific situations such as you mentioned. HTH Iain ---(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] Distributed Transactions
Hi all, i am using PostgreSQL 7.3.2. How can i do distributed transactions in PostgreSQL. is there a transaction coordinator available for Postgres.. thanks in advance regards jinujose Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want.
Re: [SQL] Distributed Transactions
Jinujose, > i am using PostgreSQL 7.3.2. How can i do distributed transactions in PostgreSQL. > is there a transaction coordinator available for Postgres.. Distributed transactions? Transaction coodinator? I'm not quite sure what these are. If you mean Two Phase Commit, for committing a transaction across multiple servers/databases, it's under development and may be released with version 7.5. Or later. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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] CHAR(n) always trims trailing spaces in 7.4
elein <[EMAIL PROTECTED]> writes: > So exactly what is the order of casts that produces > different results with: > 'x' || ' ' || 'x' and 'x' || ' '::char15 || 'x' > Are operators being invoked both (text,text)? The only relevant operator is "text || text" (there are also some || operators for arrays, bytea, and BIT, but these will all be discarded as not the most plausible match). Therefore, in your first example the unspecified literals will all be presumed to be text, so the space does not get trimmed. One of the things we could think about as a way to tweak the behavior is creating "||" variants that are declared to accept char(n) on one or both sides. These could actually use the same C implementation function (textcat) of course. But declaring them that way would suppress the invocation of rtrim() as char-to-text conversion. However, if we did that then "||" would behave differently from other operators on character strings, so it doesn't seem like a very attractive option to me. regards, tom lane ---(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
