[SQL] renaming columns... danger?
hi.
I just discovered that doing an alter table ... alter
column (to rename a column) does not do a complete
rename throughout the database.
for example, say you have table a, with columns b and
c. b is your primary key.
now rename b to new_b. if you do a dump of the schema
after you rename, you'll find that you can't reload
that schema because at the bottom of the definition of
table a you have PRIMARY KEY ("b").
shouldn't rename update any index and key definitions?
also, and this may actually the source of the problem,
while scanning my full (schema and data) dump, I
noticed that the contents of table pga_layout also had
the old values of columns that I have renamed.
I'm very frightened right now, because I'm rather
dependent upon my database right now. I don't like
the thought that my database is corrupt at the schema
level.
michael
__
Do You Yahoo!?
Yahoo! Messenger - Talk while you surf! It's FREE.
http://im.yahoo.com/
[SQL] "average" time
Hello out there, an ignorant's question: can I/how can I create an aggregate that performs this operation on a group of timestamp values: select some_column, min(timest)+(max(timest)-min(timest))/2 from mytable group by some_column; that is, an aggregate which calculates the point half way between the minimum and the maximum of the time series? I would like to use it like this (substitute something appropriate for "median", I don't have an idea how to call it): select some_column, median(timest) from mytable group by some_column; Any suggestions appreciated! Regards, Ulf -- === Ulf Mehlig<[EMAIL PROTECTED]> Center for Tropical Marine Ecology/ZMT, Bremen, Germany ---
[SQL] Nested Aggregate fonction & gouping attributes
In my SELECT statement I fetch data from a view, and I have the 2 following errors: 1.When I use Aggregate function AVG, it returns "Aggregate function calls may not be nested" 2. When I use my PL/PGSQL function "HO()" it returns "Attribute req_preminter.datedemande must be GROUPed or used in an aggregate function" It must be a commun mistake I'm sure,but I'm not very good at big queries (I thought I was once for all succesfull by creating views...) ; I've read that subselect fct shoud be used instead, but how, and what is a grouped attribute ? Can somebody help me ? -- Here is my queries, if interested -- CREATE VIEW Req_PremInter AS SELECT Demande.demandeID, to_char(datedemande, 'MM') AS Mois, Demande.Datedemande AS Datedemande, Min(Intervention.DateInter) AS DateInter FROM Demande INNER JOIN Intervention ON Demande.demandeID = Intervention.Demande GROUP BY Demande.demandeID, to_char(datedemande, 'MM'), Demande.Datedemande; QUERY with AVG: -- SELECT Req_PremInter.Mois AS Mois, Avg(to_number(dateinter,99)) AS Delai FROM Req_PremInter GROUP BY Req_PremInter.Mois ORDER BY Req_PremInter.Mois; QUERY with HO (PL/PGSQL function) - SELECT Req_PremInter.Mois AS Mois, HO(datedemande, dateinter, '12/12/2000', '12/12/2000')/24 AS Delai FROM Req_PremInter GROUP BY Req_PremInter.Mois ORDER BY Req_PremInter.Mois; Final QUERY (what I really what to run) SELECT Req_PremInter.Mois AS Mois, Avg(HO(datedemande,'12/12/2000',dateinter,datedemande)/24) AS Delai FROM Req_PremInter GROUP BY Req_PremInter.Mois ORDER BY Req_PremInter.Mois;
[SQL] HELP! ... pg_locale ???
Hello folks,
I got a headache now, cause our admin played with postgres settings,
something about pg_locale, as he says. Perhaps the reason is
elsewhere, i dont know.
As result, now all queries, written inside the php code on multiply lines,
returns the following:
ERROR: parser: parse error at or near " "
The same query, pasted to psql, works well.
The same query written on single line in php code works well.
But i see that my older scripts with multiply line queries works well.
Whats up?! Why?
There is no errors in this code:
--
$result = @pg_exec($db,"
SET DATESTYLE = 'ISO';
SELECT DISTINCT
meznieciba,
date_part('day',date(datums)) AS dd,
date_part('month',date(datums)) AS mm,
date_part('year',date(datums)) AS ,
galvene,
kajene
FROM izsoles
WHERE datums = '$datums'") or die(pg_errormessage());
--
it works fine when written on the single line
but now i see the above stupid error message..
it so funny to have to rewrite queries to single line?..
--:)--
Best regards,
Sandis
Re: [SQL] Alternate Database Locations
Is that a small d or a capital D? try this: postmaster -D $PGDATA2 -i -p 5431 & \Indraneel On Thu, 26 Oct 2000, Brian C. Doyle wrote: > Okay I am still doing something wrong here > > I set PGDATA2=/home/user1/database > export PGDATA2 > > then I start postmaster > postmaster -d PGDATA2 -i -p 5431 & > and i get > > Can't create pid file: /usr/local/pgsql/data/postmaster.pid > Is another postmaster (pid: 10686) running? > > What am I missing... I know it must be simple!!! > > Thanks for all of your help!
Re: [SQL] Alternate Database Locations
Brian C. Doyle writes: > Okay I am still doing something wrong here > > I set PGDATA2=/home/user1/database > export PGDATA2 > > then I start postmaster > postmaster -d PGDATA2 -i -p 5431 & postmaster -D $PGDATA -i -p 5431 >logfile 2>&1 and i get > > Can't create pid file: /usr/local/pgsql/data/postmaster.pid > Is another postmaster (pid: 10686) running? -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] Query Problem
Michael, > SELECT Data > FROM Table A > WHERE NOT EXISTS ( > SELECT * FROM Table_B, Table_C > WHERE Table_B.GroupID = TableC.GroupID > AND TableC.AccountID = 11 > ) > > I think that the not exists is a bit quicker than the NOT IN. Give it > a whirl. A *lot* faster. Like, 7x as fast. I'd forgotten about EXISTS, since I so seldom have a use for it ... but this is shy it was created, I guess. Thanks so much for your help! -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Alternate Database Locations
Okay... I can now run as many postmasters as I could imagine... KICK BUTT I am still running into a problem with the "alternate database locations" and that is I seem to be able to only set up 1 other alternate location. After getting the first alt location setup Iset PGDATA2= /home/user1/database export it initdb -D /home/user1/database started postmaster -D /home/user1/database initlocation 'PGDATA2' createdb user1 -D 'PGDATA2' it creates but it is putting it in /home/user/database the first alt location i set up!!! I know I am being a pain in the ass but I truly do appreciate all the help At 10:02 AM 10/26/00 -0400, Tom Lane wrote: >"Brian C. Doyle" <[EMAIL PROTECTED]> writes: > > Okay I am still doing something wrong here > > I set PGDATA2=/home/user1/database > > export PGDATA2 > > > then I start postmaster > > postmaster -d PGDATA2 -i -p 5431 & > >I think you are confusing the "alternate database directory" stuff with >having a separate installation (primary directory). To set up a second >postmaster, you first have to initdb its installation directory: > > initdb -D /home/user1/database > >then start the postmaster like so: > > postmaster -D /home/user1/database -p whatever ... > >Instead of -D (note upper case), you can alternatively set env variable >PGDATA for these two programs. > >The stuff in the manual about alternate database directories is to allow >a *single* postmaster to manage databases located somewhere other than >underneath its installation directory. It's got nothing at all to do >with starting additional postmasters. > > regards, tom lane
Re: [SQL] Query Problem
Josh Berkus <[EMAIL PROTECTED]> writes: > Thus, I need to select: > SELECT Data FROM Table A > WHERE CaseID NOT IN ( > SELECT CaseID FROM Table_B, Table_C > WHERE Table_B.GroupID = TableC.GroupID > AND TableC.AccountID = 11) > The problem is, since Table_B and Table_C are large (10,000 records +) > this exclusion query takes several *minutes* to run. I don't think there is any good way to make this fast in current sources. A partial workaround is to use a temp table: SELECT CaseID INTO TEMP TABLE mycaseids FROM Table_B, Table_C WHERE Table_B.GroupID = TableC.GroupID AND TableC.AccountID = 11; CREATE INDEX mycaseids_idx ON mycaseids(caseid); -- critical! SELECT Data FROM TableA upper WHERE NOT EXISTS (select 1 from mycaseids where caseid = upper.caseid); You'd need to check with EXPLAIN, but the EXISTS subplan should make use of the index to probe the temp table, so you get one index lookup per outer tuple. Better than a complete scan of the subselect outputs, which is what you'll get with the NOT IN style. In 7.1 it'll be possible to do this with an outer join, which should be a lot quicker: SELECT Data FROM TableA LEFT JOIN (SELECT CaseID FROM Table_B, Table_C WHERE Table_B.GroupID = TableC.GroupID AND TableC.AccountID = 11) subselect ON (tablea.caseid = subselect.caseid) WHERE subselect.caseid IS NULL; ie, do the outer join and then discard the successfully-matched rows. Further down the pike, we have plans to make the system smart enough to transform IN and NOT IN constructs into join-like queries automatically. Right now, though, they're best rewritten into something else when performance is important. regards, tom lane
Re: [SQL] Alternate Database Locations
"Brian C. Doyle" <[EMAIL PROTECTED]> writes: > Okay I am still doing something wrong here > I set PGDATA2=/home/user1/database > export PGDATA2 > then I start postmaster > postmaster -d PGDATA2 -i -p 5431 & I think you are confusing the "alternate database directory" stuff with having a separate installation (primary directory). To set up a second postmaster, you first have to initdb its installation directory: initdb -D /home/user1/database then start the postmaster like so: postmaster -D /home/user1/database -p whatever ... Instead of -D (note upper case), you can alternatively set env variable PGDATA for these two programs. The stuff in the manual about alternate database directories is to allow a *single* postmaster to manage databases located somewhere other than underneath its installation directory. It's got nothing at all to do with starting additional postmasters. regards, tom lane
Re: [SQL] plperl
Jie Liang writes: > "../../../src/Makefile.global", line 304: Need an operator > make: fatal errors encountered -- cannot continue > su-2.04# > > what I need to do? Use GNU make. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [SQL] pg_atoi: error in "template1": can't parse "template1"
Dronamraju Rajesh <[EMAIL PROTECTED]> writes: >I have installed postgres 6.5.1 on my RedHat Linux > box. The installation went smooth without any > problems. Now when i run initdb I got errors. > Adding template1 database to pg_database... > ERROR: pg_atoi: error in "template1": can't parse > "template1" Hm. I think the most likely cause is that you are using library files (global.bki etc) that are not for the same version of Postgres as your executable is. The error message looks like there is some confusion about which column is which in pg_database, which is quite possible if the executable and .bki files don't match. In any case, it's a little bit silly to be installing 6.5.1 today. I'd suggest removing all trace of Postgres from your machine and then making a fresh installation of Postgres 7.0.2 (or wait a couple days for 7.0.3). regards, tom lane
Re: [SQL] Alternate Database Locations
"Brian C. Doyle" <[EMAIL PROTECTED]> writes: > createdb user1 -D 'PGDATA2' > it creates but it is putting it in /home/user/database > the first alt location i set up!!! Um, are you sure createdb is connecting to the right postmaster? When you use multiple postmasters you have to keep a close eye on clients' PGPORT settings ... If it is the right postmaster but wrong interpretation of PGDATA2, the odds are that you started that postmaster with the wrong value of PGDATA2. Remember that it's the postmaster's environment, not the client's, in which alternate-DB environment variables are expanded. (initlocation is an exception because it doesn't contact the postmaster) regards, tom lane
Re: [SQL] Query Problem
What is the explain output for the queries you've tried? Stephan Szabo [EMAIL PROTECTED] On Wed, 25 Oct 2000, Josh Berkus wrote: > > Folks: > > Here's the problem, in abstract: I need to select every record in table > A that does not have a link in table B Join Table C where Table > C.account = 11 > > The relevant fields: > > Table_A > CaseID > Data > > Table_B > GroupID > CaseID > Amount > > Table_C > GroupID > AccountID > > Thus, I need to select: > > SELECT Data FROM Table A > WHERE CaseID NOT IN ( > SELECT CaseID FROM Table_B, Table_C > WHERE Table_B.GroupID = TableC.GroupID > AND TableC.AccountID = 11) > > The problem is, since Table_B and Table_C are large (10,000 records +) > this exclusion query takes several *minutes* to run. > > I've fooled around with drectional joins, views, and temporary tables, > but I can seem to find anything that works faster. Suggestions? > > -Josh Berkus > > -- > __AGLIO DATABASE SOLUTIONS___ > Josh Berkus >Complete information technology [EMAIL PROTECTED] > and data management solutions (415) 436-9166 >for law firms, small businesses fax 436-0137 > and non-profit organizations. pager 338-4078 > San Francisco >
Re: [SQL] Alternate Database Locations
Okay I am still doing something wrong here I set PGDATA2=/home/user1/database export PGDATA2 then I start postmaster postmaster -d PGDATA2 -i -p 5431 & and i get Can't create pid file: /usr/local/pgsql/data/postmaster.pid Is another postmaster (pid: 10686) running? What am I missing... I know it must be simple!!! Thanks for all of your help! At 10:43 PM 10/25/00 -0400, Tom Lane wrote: >"Brian C. Doyle" <[EMAIL PROTECTED]> writes: > > I am still trying to find out how to get multiple postmasters running on > > different ports at the same time. Does anyone have any clue how to do > that? > >Uh, you just do it. Start each postmaster in a different data directory >and with a different port number (-D and -P switches) and you're set. > >Unless you run out of shared memory or some such, in which case some >tweaking of kernel parameters is called for... > > regards, tom lane
Re: [SQL] Add Constraint
On Wed, 25 Oct 2000, Sivagami . wrote: > Hi all, > > I am a newbie to Postgresql, but I am familiar with SQL. I am trying to add a >constraint to my table using the ALTER TABLE command. The command goes like this : > > ALTER TABLE USER_SIGNUP ADD CONSTRAINT > P_USER_SIGNUP_USER_ID PRIMARY KEY(user_id); > > But it is returning the error > ERROR: ALTER TABLE / ADD CONSTRAINT is not implemented > > Can anyone guide me in the right direction??? We don't have full add constraint support yet. 7.0 allows you to add the subset of foreign keys supported and current sources should allow check constraints as well. To get the unique constraint portion, you can add a unique index on user_id (it's what the system would have done anyway). To get the non-null part is a little harder, you need to find the row in pg_attribute and set the attnotnull to true, and then make sure there aren't any null values already in the data set.
[SQL] pg_atoi: error in "template1": can't parse "template1"
Dear all, I have installed postgres 6.5.1 on my RedHat Linux box. The installation went smooth without any problems. Now when i run initdb I got errors. I have seen the Postgres FAQ also and have ensured that all the directories i,e /usr/local/pgsql, /home/postgres etc are owned by postgres super user. I also have set the following environmental variables: - PATH=/usr/bin:/usr/local/bin:/usr/local/pgsql/bin HOME=/home/postgres INPUTRC=/etc/inputrc LC_COLLATE=C SHELL=/bin/bash PGLIB=/usr/local/pgsql/lib USER=postgres PGDATA=/usr/local/pgsql/data MANPATH=:/usr/local/pgsql/man LC_CTYPE=C LANG=en_US OSTYPE=Linux SHLVL=1 LC_COLLATE=C But even then I got errors when I run initdb like Adding template1 database to pg_database... ERROR: pg_atoi: error in "template1": can't parse "template1" ERROR: pg_atoi: error in "template1": can't parse "template1" syntax error 1 : parse errorinitdb: could not log template database initdb: cleaning up. PLEASE help me in this regard. Thanks & Regards Rajesh. __ Do You Yahoo!? Yahoo! Messenger - Talk while you surf! It's FREE. http://im.yahoo.com/
Re: [SQL] Query Problem
Josh Berkus <[EMAIL PROTECTED]> writes: > Folks: > > Here's the problem, in abstract: I need to select every record in table > A that does not have a link in table B Join Table C where Table > C.account = 11 > > The relevant fields: > > Table_A > CaseID > Data > > Table_B > GroupID > CaseID > Amount > > Table_C > GroupID > AccountID > > Thus, I need to select: > > SELECT Data FROM Table A > WHERE CaseID NOT IN ( > SELECT CaseID FROM Table_B, Table_C > WHERE Table_B.GroupID = TableC.GroupID > AND TableC.AccountID = 11) > > The problem is, since Table_B and Table_C are large (10,000 records +) > this exclusion query takes several *minutes* to run. > > I've fooled around with drectional joins, views, and temporary tables, > but I can seem to find anything that works faster. Suggestions? Without having tried, something like this might be faster: SELECT Data FROM Table A WHERE CaseID NOT IN ( SELECT CaseID FROM Table_B WHERE Table_B.GroupID IN ( SELECT GroupID FROM Table_C WHERE TableC.AccountID = 11)) Tomas
Re: [SQL] How to call a shell command in rule
Hi, but perl cannot be used in trigger yet :-( Clayton Cottingham wrote: > On Wed, 25 Oct 2000 10:44:48 -0700, Jie Liang said: > > > Hi, > > > > I want send a e-mail when the rows of mytable reaches 100,000, how? > > > > > > > > one way is to make a function using perl > and use say mail::sender as the module to send the info > > use the code snippet > in perldoc Mail::Sender > > another would be to build a cron perl job that would run every so often and > check out > how many rows then send email > > > -- > > Jie LIANG > > > > Internet Products Inc. > > > > 10350 Science Center Drive > > Suite 100, San Diego, CA 92121 > > Office:(858)320-4873 > > > > [EMAIL PROTECTED] > > www.ipinc.com > > > > > > > > > > -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] plperl
Jie Liang <[EMAIL PROTECTED]> writes: > su-2.04# make > "../../../src/Makefile.global", line 135: Need an operator > "../../../src/Makefile.global", line 139: Missing dependency operator Hmm, is "make" on your machine GNU make? If not try "gmake". regards, tom lane
Re: [SQL] Alternate Database Locations
AFAIK the port number is compiled in, so you need to recompile. It's there in the docs somewhere. you have to use a different value for --with-pgport option in ./configure . On Wed, 25 Oct 2000, Brian C. Doyle wrote: > > I am still trying to find out how to get multiple postmasters running on > different ports at the same time. Does anyone have any clue how to do that? >
Re: [SQL] Surprising sequence scan when function call used
see functional index On Wed, 25 Oct 2000 13:26:51 -0400 "Will Fitzgerald" <[EMAIL PROTECTED]> wrote: > I have a table, login, which has a field by the same name; there's an index > on that field. I was surprised to discover that a SELECT which compares the > login field to a constant uses an Index scan, but if it is compared to a > function call--for example, lower()--a sequence scan is forced. > > Any idea why? -- * Hosokawa Tetsuichi * [EMAIL PROTECTED]
[SQL] Query Problem
Folks: Here's the problem, in abstract: I need to select every record in table A that does not have a link in table B Join Table C where Table C.account = 11 The relevant fields: Table_A CaseID Data Table_B GroupID CaseID Amount Table_C GroupID AccountID Thus, I need to select: SELECT Data FROM Table A WHERE CaseID NOT IN ( SELECT CaseID FROM Table_B, Table_C WHERE Table_B.GroupID = TableC.GroupID AND TableC.AccountID = 11) The problem is, since Table_B and Table_C are large (10,000 records +) this exclusion query takes several *minutes* to run. I've fooled around with drectional joins, views, and temporary tables, but I can seem to find anything that works faster. Suggestions? -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Re: [SQL] Problem whith Stored queries
> Hi, anybody know how to call shell command in postgres rule or trigger, urgent!! -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
