Re: [SQL] Copy from a SELECT
That's exactly what I don't want to do. I've got *very* limited HDD space for the amount of information I need to copy out. The place to where the CSV file is would be output is NTFS and from the Postgres documentation, it states that I should not put the table oid files there and link in order to free up space in the default drive. I've tried using \o to stream the output to a file on the NTFS area, but the machine ran out of memory (memory = 2G). I was looking for a work around to copy out the section of data I wanted to a temporary table and then copy that table out . -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Christoph Haller Sent: 27 February 2003 12:45 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Copy from a SELECT > > I know that the COPY command requires that you work with plain > tables. > > Is there any other way at all of export only part of a table > What about CREATE [ possibly TEMPORARY ]TABLE table_name AS and then COPY ... Regards, Christoph ---(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]
Re: [SQL] pgsql problem
On Wed, 26 Feb 2003, Grignon Etienne wrote:
>
> test=# SELECT createuser('toto', true, true, true);
> NOTICE: Begin Of Update Permissions
> NOTICE: End Of Update Permissions
> NOTICE: TRIGGER
> createuser
>
> 0
> (1 row)
> test=#
>
>
> My problem is that the trigger for the insert is executed at the end of the
> procedure, so I can't do the update of the user's permissions.
>
> For me and my co-workers, the NOTICE TRIGGER should be before Begin Of
> Update Permissions.
>
> Could you explain to me how is it working ?
Right now AFAIR after triggers run at the end of the containing statement
(in this case the select of createuser) which is what causes the behavior
you're seeing. I'm not sure of a good work around, off hand, for your
case though.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Analyse article
Hi folks, I've just finished writing up a bit on analyze. http://www.freebsddiary.org/postgresql-analyze.php It's not so much an explanation of the command as it is a series of examples which show a query evolving from a 4 second monster to a 14 ms speed daemon. Corrections, comments, etc appreciated. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] GiST, Not Using Index
> Ah. Your problem is you need to mark sig_in() as IMMUTABLE, or at
> least [...]
That was it! Thanks!
For your enjoyment:
test=# select count(*) from x;
count
20
(1 row)
test=# explain analyze select * from x where y ~>= sig_in('{1,2,3,4,5}'::int4[]);
QUERY PLAN
--
Index Scan using xx on x (cost=0.00..4.82 rows=1 width=8) (actual time=5.07..93.15
rows=29 loops=1)
Index Cond: (y ~>=
'.*..'::sig)
Total runtime: 93.31 msec
(3 rows)
test=# select * from x where y ~>= sig_in('{1,2,3,4,5}'::int4[]);
y
--
.*.*..*..**.*.*...*.*.*.**...*..
.***.*..**.*.*.***...**.**..*..*.*...**..****...
.*..*...**..*.*.**..*...*...*..*
.*..*...**.**.*..*..***...**...*
***.**.*...**.*...*.
.*.*.**.*...***.*...*.**.*..*..**...*...
.**.*.*...*..*..*.*..**..***.*...***.*..*....**.
***..*.*.*.**.*..**..*..*...*...
***..**..***.*..*...*...*.*.***.*.*...**...***...*..
.*..*.*..*.*.*...**...***..**...
.**..*.*.**.*.*.*..*.***.**..**.
**.*.*.*...*..*...*.**...****..*.*.*
.***...****.*.*..*.*.*..*..*..*...*.
*.*..*.*...*...*.*.*...*.**...*.
.*..*.**...*...**..*
.***.**.*..***.*.*.*..*.*.*..***...**..*...*
.***..*..*.****...*.**.*
.***...*.**.***..*..*..**..**.*.**.**.*..***
**.*...**..*..*...*...*..*.*..**.*.*..*.**..*...
.*.**...**..*...***..***
***.**...*..**..*.******...**.*.
**...*...*.*.**...**...*...**.*...*.**...*..
.*..***.**.*...**...**.*...***..*..**...
**..***..**.**.*...*.**..*.*.**.
.**.*.*...*.**..*..*...*..**.**.*...*...**..
.*..*...******...*..
.*..**.**..*...*..*.*.*..*..***.*..***.*..*.
.*...*...*...*..
**...*...*..*.*...**...*
(29 rows)
test=# drop index xx;
DROP INDEX
test=# explain analyze select * from x where y ~>= sig_in('{1,2,3,4,5}'::int4[]);
QUERY PLAN
---
Seq Scan on x (cost=0.00..22.50 rows=1 width=8) (actual time=35.16..2231.98 rows=29
loops=1)
Filter: (y ~>=
'.*..'::sig)
Total runtime: 2232.18 msec
(3 rows)
--
Itai Zukerman
Re: [SQL] GiST, Not Using Index
Itai Zukerman <[EMAIL PROTECTED]> writes: >> Never leave out the "unimportant" stuff --- that's usually where you >> went wrong ;-) > OK, I've attached below the SQL I use to generate stuff and an > interactive session with the "explain" etc. Ah. Your problem is you need to mark sig_in() as IMMUTABLE, or at least STABLE, for an expression involving sig_in() to be considered safe to index. If it's in C, more than likely it'd best be marked STRICT too (else you'd better have explicit NULL checks in the function code). Don't forget to look at your other custom functions too, to see if they're correctly marked. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
