Re: [SQL] Index of a table is not used (in any case)
Hello all! Thank you for the answers I got. I would like to mention first, that I will use the [SQL] list for my answers, regarding the notice of Josh Berkus. Q: "did you use VACUUM ANALYZE"? A: This table was a test bed, just using INSERTS without ANY deletes or updates (See: vacuum verbose analyze wetter; NOTICE: --Relation wetter-- NOTICE: Pages 149752: Changed 0, reaped 194, Empty 0, New 0; Tup 2034: Vac 26169, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 52, MaxLen 52; Re-using: Free/Avail. Space 1467792/1467792; EndEmpty/Avail. Pages 0/194. CPU 6.10s/1.78u sec. ) Q: You should upgrade to 7.1.3? A: Can you tell me the specific the reason? Am afraid, that the real answer is not mentioned: Why is the index used in the SELECT: select * from wetter order by epoche desc; select * from wetter where epoche between '1970-01-01' and '1980-01-01' order by epoche asc; ? Any ideas? -- Mit freundlichen Gruessen / With best regards Reiner Dassing ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query On Case structure
> > Hi All, > Can we create a query to find different values based on different criteria = > from a table using case structure. For example > Select Into :DBComment Case When localComment Is Null Then Comment When localComment = '123' Then 'Numeric Comment' Else 'String Comment' End from PDBUser Where UserIndex = 23; > > Using such type of structure can we evaluate multiple values. e.g > Select DBComment , DBName, Case When localComment Is Null Then Comment When localComment = '123' Then 'Numeric Comment' Else 'String Comment' End, Case When localName Is Null Then UserName When localName = 'SuperUser' Then 'Supervisor' Else 'NormalUser' End from PDBUser Where UserIndex = 23; > > > Is this possible. If yes then How is it. > Any help/suggestion may be benificial. > > Regards > Dinesh Parikh > NSTL New Delhi > Looks fine to me. What do you want by asking "If yes then How is it." Run the query and have a look on the result. Regards, Christoph ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Diferent databases on same query...
Andre, The only reason to have data for one query in different databases is if the databases are on different machines. If you're running on PC-type hardware, you might have to do that some times for performance reasons. But if at that point, you should ask yourself if you shouldn't invest in some faster hardware ;-). Best regards (Tschüs), Chris On Friday, October 19, 2001, at 02:05 , Andre Schnabel wrote: > > "Douglas Rafael da Silva" <[EMAIL PROTECTED]> schrieb im Newsbeitrag > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... >> Hi, >> >> I'd like to do a query where can be possible I access tables from >> diferent databases on the same query. >> On MySQL, I do: >> >> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM >> Database1.People1, Database2.Result1 WHERE ... >> >> I think on ORACLE works like as: >> >> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM >> People1@Database1, Result1@Database2 WHERE ... >> >> But Who I can to do this on Postgresql ? >> > > You CANNOT do that with PostgreSQL. > But why do you want to do that? IMHO it's a rather bas design to hold data > in different places, if you need to select them in one query. > Is there a real reason to hold the tables in different databases? > > Andre > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > -- Chris Ruprecht Network grunt and bit pusher extraordinaíre ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Auto Increment
Mayuresh, First, beginner's questions belong on the PGSQL-NOVICE list. > could anyone tell me how to make a primary key to AUTO INCREMENT. The > document is not exactly very explainatory about it :) No, it is quite clear. See: http://www.postgresql.org/idocs/index.php?datatype.html section 3.1.1 I can't imagine how it would be more clear. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] can't update 'c:\windows'
Guard, First, this kind of beginner question is more appropriate for the NOVICE list. > IF update table set field='c:\windows' > but > > c:\windows -> c:windows That's because "\" is the Unix escape character. If I were to want to save "O'Reilly", for example, I could: UPDATE table SET name = 'O\'Reilly'; Since you want to save an actual backslash, do this: UPDATE table SET field = 'C:\\windows'; In your interface code, you may which to add a function that doubles your backslashes. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Index of a table is not used (in any case)
Reiner Dassing <[EMAIL PROTECTED]> writes: > explain select * from wetter order by epoche desc; > NOTICE: QUERY PLAN: > Index Scan Backward using wetter_epoche_idx on wetter > (cost=0.00..3216018.59 rows=2034 width=16) > explain select * from wetter where epoche between '1970-01-01' and > '1980-01-01' order by epoche asc; > NOTICE: QUERY PLAN: > Sort (cost=480705.74..480705.74 rows=203400 width=16) > -> Seq Scan on wetter (cost=0.00..454852.00 rows=203400 width=16) It's hard to believe that you've done a VACUUM ANALYZE on this table, since you are getting a selectivity estimate of exactly 0.01, which just happens to be the default selectivity estimate for range queries. How many rows are there really in this date range? Anyway, the reason the planner is picking a seqscan+sort is that it thinks that will be faster than an indexscan. It's not necessarily wrong. Have you compared the explain output and actual timings both ways? (Use "set enable_seqscan to off" to force it to pick an indexscan for testing purposes.) 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] Error codes as numbers or in other languages etc.
I have seen several people asking about getting error numbers or similar things out of
postgres. The way I have done this is as follows:
CREATE TABLE errors (
errorstring TEXT PRIMARY KEY,
message TEXT
);
INSERT INTO errors VALUES ('^FATAL 1: Database "([^\"]+)" does not exist in the
system catalog.$', 'FATALE 1: Le database "$1" n''existe pas dans le catalogue
systeme');
Now in the app if we use perl and DBI we set RaiseError => 1 and catch the error via
$dbh->errstr or something
pass it into a query like:
prepare('SELECT * FROM errors WHERE ? ~ errorstring');
#Isn't postgres great? reverse regular expression searches :)
my $s = execute($dbh->errstr);
then we exploit perl's ability:
$dbh->errstr =~ /$s->{'errorstring'}/"$s->{'message'}"/e;
Hopes someone else find it usefull,
Getting out numbers is offcourse even easier.
Aasmund Midttun Godal
[EMAIL PROTECTED] - http://www.godal.com/
+47 40 45 20 46
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [HACKERS] Index of a table is not used (in any case)
> Hello PostgreSQl Users! > > PostSQL V 7.1.1: You should upgrade to 7.1.3 at some point... > I have defined a table and the necessary indices. > But the index is not used in every SELECT. (Therefore, the selects are > *very* slow, due to seq scan on > 20 million entries, which is a test setup up to now) > > The definitions can be seen in the annex. > > Does some body know the reason and how to circumvent the seq scan? Yes. You probably have not run 'VACUUM ANALYZE' on your large table. > Is the order of index creation relevant? I.e., should I create the > indices before inserting > entries or the other way around? If you are inserting a great many entries, insert the data first and then create the indices - it will be much faster this way. > Should a hashing index be used? (I tried this, but I got the known error > "Out of overflow pages") Just do the default CREATE INDEX - btree should be fine... (probably) > The table entry 'epoche' is used in two different indices. Should that > be avoided? It's not a problem, but just check your EXPLAIN output after the VACUUM to check that you have them right. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] PL/pgSQL triggers ON INSERT OR UPDATE
Hello, I'm trying to set up a trigger on insert or update but when using the predefined variable ``OLD'' I get a NOTICE from the trigger function about OLD not being defined yet. Of course OLD is not defined when the function is triggered on INSERT event, and I did not mention it if not inside a conditional block checking for the TG_OP variable being 'UPDATE'. For better understanding here is some code: BEGIN IF TG_OP = 'UPDATE' THEN IF OLD.id <> NEW.id THEN -- do the work END IF END IF; END; Even when TG_OP != 'UPDATE' (INSERT event) I still get an error message from the pl/pgsql compiler (the first time the trigger is fired). What should I do then ? Is it still possible to use the same function for UPDATE OR INSERT events ? TIA --san; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [HACKERS] Index of a table is not used (in any case)
Reiner Dassing <[EMAIL PROTECTED]> writes: > Hello PostgreSQl Users! > > PostSQL V 7.1.1: > > I have defined a table and the necessary indices. > But the index is not used in every SELECT. (Therefore, the selects are > *very* slow, due to seq scan on > 20 million entries, which is a test setup up to now) Perennial first question: did you VACUUM ANALYZE? -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] pgsql embedded again!
Hi Esteban,
Your problem looks familiar to me,
and it seems you did not follow my advice.
Anyway, here is a C sample program which works.
It has nothing to do with wrong types, but with a
missing connection, but see for yourself.
/*
/opt/pgsql/bin/ecpg -o sampleprog01.c sampleprog01.sql
the compile command is for HP-UX - you have to adapt it for your machine
cc -Aa +w1 -g -I/opt/pgsql/include/ -L/opt/pgsql/lib/ sampleprog01.c -Wl,-a,archive
-lecpg -lpq -o sampleprog01
*/
#include
#include
#include
#include "libpq-fe.h"
exec sql include sqlca;
void main()
{
EXEC SQL BEGIN DECLARE SECTION;
char *dbName;
VARCHAR tabla[50];
EXEC SQL END DECLARE SECTION;
int nrows;
dbName = "template1";
EXEC SQL CONNECT TO :dbName ;
printf("sqlca.sqlcode = %d \n", sqlca.sqlcode) ;
EXEC SQL BEGIN ;
EXEC SQL DECLARE T99 CURSOR FOR select relname from pg_class ;
EXEC SQL OPEN T99;
for(nrows = 0;; nrows++) {
EXEC SQL FETCH IN T99 INTO :tabla;
if ( sqlca.sqlcode != 0 )
break;
printf("%.*s \n", tabla.len, tabla.arr );
}
printf("nrows = %d ; sqlca.sqlcode = %d \n", nrows, sqlca.sqlcode) ;
EXEC SQL CLOSE T99;
EXEC SQL END ;
EXEC SQL DISCONNECT ;
}
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Diferent databases on same query...
"Douglas Rafael da Silva" <[EMAIL PROTECTED]> schrieb im Newsbeitrag [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > Hi, Andre ! > > Maybe yes, maybe not. I have 6 companies of the same group, little but > they are like as, and they share the main database. But there are data > that are specific of each one. The design of tables is the same for all > them. So, on mysql I had on diferent databases. Thus, on the same query, > I select the name of employee, the truck and invoice of the specific > company. Two companies have server separated. How can I do to do > distribute database server with postgres ? You have a suggestion ? Hi Douglas, ok, that's a good point to have different databases in one query. Unfortunately there is no way to do this with postgresql. At least no way with the standard distribution. Maybe it's worth to try replication. You should be able to replicate the tables of your main db between the others. But I never tried that. AFAIK, he most advanced replication tooll is PostgreSQL Replicator (http://pgreplicator.sourceforge.net/). Maybe it's the better way to go back(?) to MySQL or have a look at Interbase. Andre ---(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] dropping foreign key
I have to drop a froeign key from one of my tables. The problem is that I have another table that has a foreign key on the first one, so I can't do the select to temp-table thing and move it back. Is there any way I can remove it by mucking with pg's internal tables? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.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] can't update 'c:\windows'
On Mon, Oct 22, 2001 at 05:14:32PM +0800, guard wrote: > dear all > > IF update table set field='c:\windows' > but > > c:\windows -> c:windows Errr. Some context please? My crystal ball is still getting fixed :) -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Windows is not the problem. Ignorance is the problem. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Auto Increment
Try using SERIAL:
CREATE TABLE mytable (
MYID SERIAL,
name TEXT
);
It will auto create the sequences for you.
Cory Wright
[EMAIL PROTECTED]
http://www.southcarrollton.net/
On Mon, 22 Oct 2001, Arian Prins wrote:
> On Mon, 22 Oct 2001 12:06:21 +0530, "Mayuresh Kadu"
> <[EMAIL PROTECTED]> wrote:
>
> >hi all,
> >
> >could anyone tell me how to make a primary key to AUTO INCREMENT. The
> >document is not exactly very explainatory about it :)
> >
> >Thankx in advance ...
> >
> >
> >
> >Mayuresh
> >
> >
>
> Try first creating a sequence and then using the sequence as a default
> value in your table:
>
>
> -- STEP 1: create sequence
> create sequence mytableid_seq;
>
> -- STEP 2: create table
> create table mytable (
> id int default nextval ('mytableid_seq'),
> anyvalue varchar
> primary key (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
>
---(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] Diferent databases on same query...
Just my two cents on the issue: A good reason would be having several databases which each use the same set of tables which contain some information which takes a lot of storage, such as dictionaries or map information. You wouldn't want to maintain several copies of 500 Mb tables, especially if they get updated frequently. The workaround, for now, is to have a table just for the foreign keys for the different systems, and to query the shared database separately from within your programming environment. Troy > > > "Douglas Rafael da Silva" <[EMAIL PROTECTED]> schrieb im Newsbeitrag > [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > > Hi, > > > > I'd like to do a query where can be possible I access tables from > > diferent databases on the same query. > > On MySQL, I do: > > > > But Who I can to do this on Postgresql ? > > You CANNOT do that with PostgreSQL. > But why do you want to do that? IMHO it's a rather bas design to hold data > in different places, if you need to select them in one query. > Is there a real reason to hold the tables in different databases? > > Andre > > > > ---(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 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] Deleting obsolete values
Delete from partitur where userid NOT IN (SELECT DISTINCT ON (userid)
userid, val, ts FROM partitur)
"Haller Christoph" <[EMAIL PROTECTED]> wrote in message
200110161445.QAA11833@rodos">news:200110161445.QAA11833@rodos...
> This may look familiar to you - it was on the list last month.
> Consider the following table
> create table partitur
> (userid text, val integer, ts timestamp DEFAULT NOW() );
> Do some inserts
> insert into partitur values('Bart', 1440);
> insert into partitur values('Lisa', 1024);
> insert into partitur values('Bart', 7616);
> insert into partitur values('Lisa', 3760);
> insert into partitur values('Bart', 3760);
> insert into partitur values('Lisa', 7616);
> To retrieve the latest values (meaning the last ones inserted)
> Tom Lane wrote
> >This is what SELECT DISTINCT ON was invented for. I don't know any
> >comparably easy way to do it in standard SQL, but with DISTINCT ON
> >it's not hard:
> >SELECT DISTINCT ON (userid) userid, val, ts FROM partitur
> >ORDER BY userid, ts DESC;
>
> My question now is
> Is there a way to delete all rows the select statement did not
> bring up?
> After that *unknown* delete statement
> select userid, val, ts from partitur ;
> should show exactly the same as the SELECT DISTINCT ON (userid) ...
> did before.
>
> 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 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] system maintained keys
Thank you very much! Thanks to all who answered! An I will never again ask silly questions here :-) "Stefan Lindner" <[EMAIL PROTECTED]> schrieb im Newsbeitrag 9qf06v$20bh$[EMAIL PROTECTED]">news:9qf06v$20bh$[EMAIL PROTECTED]... > Is there any way to get system maintained keys from postgres? e.g. to > have a table with a primary key column (varchar or int) and let postgres > chose the next unique value for this column? > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Auto Increment
hi all, could anyone tell me how to make a primary key to AUTO INCREMENT. The document is not exactly very explainatory about it :) Thankx in advance ... Mayuresh ---(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] Diferent databases on same query...
>> Hi, >> >> I'd like to do a query where can be possible I access tables from >> diferent databases on the same query. >> On MySQL, I do: >> >> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM >> Database1.People1, Database2.Result1 WHERE ... >> >> I think on ORACLE works like as: >> >> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM >> People1@Database1, Result1@Database2 WHERE ... >> >> But Who I can to do this on Postgresql ? >> > >You CANNOT do that with PostgreSQL. >But why do you want to do that? IMHO it's a rather bas design to hold data >in different places, if you need to select them in one query. >Is there a real reason to hold the tables in different databases? > >Andre Hi, Andre ! Maybe yes, maybe not. I have 6 companies of the same group, little but they are like as, and they share the main database. But there are data that are specific of each one. The design of tables is the same for all them. So, on mysql I had on diferent databases. Thus, on the same query, I select the name of employee, the truck and invoice of the specific company. Two companies have server separated. How can I do to do distribute database server with postgres ? You have a suggestion ? Thank you, Douglas. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Granting database level permissions...
have a look at pg_hba.conf in your data dir. it's all in there. Steve "Thomas Swan" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > Is it possible to grant database level access to a user in PostgreSQL? > > I have created a user and database, and I want the user to have full > control over that database. I have been through the online docs and > news groups but have not found a statement or answer to the question. > > Thomas > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Auto Increment
On Mon, 22 Oct 2001 12:06:21 +0530, "Mayuresh Kadu"
<[EMAIL PROTECTED]> wrote:
>hi all,
>
>could anyone tell me how to make a primary key to AUTO INCREMENT. The
>document is not exactly very explainatory about it :)
>
>Thankx in advance ...
>
>
>
>Mayuresh
>
>
Try first creating a sequence and then using the sequence as a default
value in your table:
-- STEP 1: create sequence
create sequence mytableid_seq;
-- STEP 2: create table
create table mytable (
id int default nextval ('mytableid_seq'),
anyvalue varchar
primary key (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] oid's in views.
No, many of the tables have primary keys already, serial, single key or several keys. I don't want to 'serialize' every table, because it does not make sense. Oid's however are very usefull as I can use them as a primary key on any table. Regards, Aasmund. On Fri, 19 Oct 2001 15:38:16 -0700, "Josh Berkus" <[EMAIL PROTECTED]> wrote: > Aasmund, > > > You'd still be better off defining your own SERIAL columns and/or > primary keys and using those. Some Trigger types require referencing > the OID, but that's about it. > > -Josh > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Index of a table is not used (in any case)
In article <[EMAIL PROTECTED]>, Josh Berkus wrote: > Reinier, > > For future notice, [SQL] is the correct list for this kind of inquiry. > Please do not post it to [HACKERS]. And please don't cross-post ... it > results in a lot of needless duplication of effort. > >> I have defined a table and the necessary indices. > >> Is the order of index creation relevant? I.e., should I create the >> indices before inserting >> entries or the other way around? > > Ummm ... not to be obvious, or anything, but did you VACCUUM ANALYZE > after populating your table? > > There's also some special steps to take if you are regularly deleting > large numbers of records. Could you tell me what those steps are or where to find them? I have a db that I delete about 1 million records a day from in a batch job. The only special thing I do is every few days I reindex the table involved to reclame the space burned by the indexes not reclaiming space on deletion of rows. What other good and useful things could I do? Thanks marc > > -Josh > > __AGLIO DATABASE SOLUTIONS___ >Josh Berkus > Complete information technology [EMAIL PROTECTED] >and data management solutions (415) 565-7293 > for law firms, small businessesfax 621-2533 > and non-profit organizations. San Francisco > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] can't update 'c:\windows'
dear all IF update table set field='c:\windows' but c:\windows -> c:windows -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PL/pgSQL triggers ON INSERT OR UPDATE
First, I may be wrong but I do think they would prefer if you did not cross-post (especially to hackers). Second I think it probably make more sense to make two different triggers here. If you really wanted to do it that way you might want to try executing that part. Regards, Aasmund. On Mon, 22 Oct 2001 23:25:44 +0200, [EMAIL PROTECTED] wrote: > Hello, I'm trying to set up a trigger on insert or update > but when using the predefined variable ``OLD'' I get a > NOTICE from the trigger function about OLD not being defined yet. > > Of course OLD is not defined when the function is triggered on INSERT > event, and I did not mention it if not inside a conditional block > checking for the TG_OP variable being 'UPDATE'. > > For better understanding here is some code: > > BEGIN > IF TG_OP = 'UPDATE' THEN > IF OLD.id <> NEW.id THEN > -- do the work > END IF > END IF; > END; > > Even when TG_OP != 'UPDATE' (INSERT event) I still get an error > message from the pl/pgsql compiler (the first time the trigger is fired). > > What should I do then ? Is it still possible to use the same function > for UPDATE OR INSERT events ? > > TIA > > --san; > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster Aasmund Midttun Godal [EMAIL PROTECTED] - http://www.godal.com/ +47 40 45 20 46 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Index of a table is not used (in any case)
Reiner Dassing <[EMAIL PROTECTED]> writes: > I would like to mention first, that I will use the [SQL] list for my > answers, > regarding the notice of Josh Berkus. > > Q: "did you use VACUUM ANALYZE"? > A: This table was a test bed, just using INSERTS without ANY deletes or > updates You still need to run VACUUM ANALYZE. The ANALYZE part measures the statistics of your data, which the planner needs in order to make decision. > Am afraid, that the real answer is not mentioned: > Why is the index used in the SELECT: > select * from wetter order by epoche desc; > > > select * from wetter where epoche between '1970-01-01' and '1980-01-01' > order by epoche asc; If you EXPLAIN output for these queries, someone can probably help you. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(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] Index of a table is not used (in any case)
Doug McNaught wrote: > Reiner Dassing <[EMAIL PROTECTED]> writes: > > > Hello PostgreSQl Users! > > > > PostSQL V 7.1.1: > > > > I have defined a table and the necessary indices. > > But the index is not used in every SELECT. (Therefore, the selects are > > *very* slow, due to seq scan on > > 20 million entries, which is a test setup up to now) > > Perennial first question: did you VACUUM ANALYZE? Can there, or could there, be a notion of "rule based" optimization of queries in PostgreSQL? The "not using index" problem is probably the most common and most misunderstood problem. ---(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] oid's in views.
Josh Berkus wrote: >Aasmund, > > Thank you for the clarification. Now that I know what you are doing, I >went through exactly the same thing about a year ago ... which is how we >discovered some additional problems with using OIDs in database design. >I was trying to spare you the same dead end. > >>>If your problem is that you want to update VIEWs and aren't sure >>> >>what the >> >>>PK for the view is, could you follow a standard like this: >>> >>>CREATE TABLE person (social_security CHAR(9), full_name TEXT); >>> >>>CREATE VIEW pers_view AS select social_security AS primkey, >>>social_security, >>>full_name); >>> >>>and know that you can always find the "primkey" field in the view as one >>> >>>to use in where clauses for updates? >>> > >This is more-or-less a correct approach. As it does not address the >issue of different data types, let me tell you what I did: > >Each significant data table contains one column, the first column, >called "usq", for "universal sequence". This usq field may or may not >be the primary key for the table, but does have a unique index. The usq >is populated by a single sequence "universal_sq" which is shared between >tables, thus allowing all tables usq uniqueness between them. > >This strategy has allowed me to write a number of functions which are >table-agnostic, needing only the usq to do their job (such as a function >that creates modification hisotry). > >-Josh Berkus > Hi Josh! Once you have your usq, how do you get more info about that row? How do you know which table it came from? Thanks -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 216-2053 [EMAIL PROTECTED] San Francisco, CA. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
