Re: [SQL] constrains questios ..
On Tuesday 11 February 2003 18:41, Matthew Nuzum wrote: > Can you please demonstrate the syntax you would use to create the > constraints? I'm not real clear on this and my attempts haven't > produced what I wanted. Checked the docs? They are really good at this point. Anyways. The following creates Tables A, B and AB. A and B have an id and a text field, AB connects them (N:M relation). create table A ( id integer not null primary key, something varchar(16) ); create table B ( id integer not null primary key, otherthing varchar(16) ); create table AB ( id_a integer not null, id_b integer not null, constraint pk_AB primary key(id_a, id_b) ); create unique index u_A_something on A (something); create unique index u_A_otherthing on B (otherthing); alter table AB add constraint fk_AB_A_exists foreign key(id_a) references A(id); alter table AB add constraint fk_AB_B_exists foreign key(id_b) references B(id); HTH Johannes Lochmann -- Disclaimer - These opiini^H^H damn! ^H^H ^Q ^[ .. :w :q :wq :wq! ^d X^? exit X Q ^C ^c ^? :quitbye CtrlAltDel ~~q :~q logout save/quit :!QUIT ^[zz ^[ZZ ^H man vi ^@ ^L ^[c ^# ^E ^X ^I ^T ? help helpquit ^D ^d man help ^C exit ?Quit ?q CtrlShftDel "Hey, what does this button d..." ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] anal about my syntax
Hello All I was looking at this plpgsql function: FOR rec IN EXECUTE''select count(person_id) as total from person where person_email like '' || $1 || ''% and person_id IN (select cp.person_id from cluster_person cp, cluster c where cp.cluster_id = c.cluster_id and c.c_id = '' || $2 || ''group by cp.person_id) ''LOOPtot = rec.total;END LOOP; It works fine - I was just wondering if you can: 1. execute this sql without a loop being used as its not required. 2. could this be a straight SQL function instead( I dont think you can append strings together in SQL functions )
Re: [SQL] What is wrong with this identification configuration?
> The format of the hba.conf file changed between 7.1 and 7.2. It looks like > you are using an old one. After the database field, there is now a user > field. To get the same effect as before, use 'all' for the user. I installed PostgreSQL rpm on a fresh installed Redhat 7.3. There is no PostgreSQL 7.1 previously installed so I don't think the pg_hba.conf I was editing is a left-over copy. Specifically, which part of the configuration: local all trust hostall 127.0.0.1 255.255.255.255 trust is wrong and should be fixed accordingly? Thanks Wei ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Lock timeout detection in postgres 7.3.1
Tomasz Myrta wrote: > Christoph Haller wrote: > > > Yupp, I agree. > > But from former DBMS I was dealing with, > > I know this SET TIMEOUT called feature, which if properly set > > terminated processes like that hanging on T2. > > Is there something comparable within Postgres? > > PostgreSQL 7.3 Documentation > 3.4. Run-time Configuration > STATEMENT_TIMEOUT (integer) > Aborts any statement that takes over the specified number of milliseconds. A value >of zero turns off the timer. > DEADLOCK_TIMEOUT (integer) > This is the amount of time, in milliseconds, to wait on a lock before checking to >see if there is a deadlock condition > > > In this case I suppose 2 things: > - table has a lot of records and you should just wait to finish operation. > - another query locked the table and it is realy a deadlock One of the uses of STATEMENT_TIMEOUT is to allow a LOCK or query to fail if it doesn't complete in a short time. We don't have a special timer to say if we are waiting on a lock for a specified time --- just a query-level timer. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] adding not null constraints on columns
Title: Message Is there a way to add not null constraints on a column after the column has been created? Any help would be appreciated. Thanks. The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. Nathaniel Borenstein (1957 - ) [EMAIL PROTECTED] <><><>
Re: [SQL] Working with very large datasets
On Tue, 11 Feb 2003, Wilkinson Charlie E wrote: > Greetings, > Can anyone enlighten me or point me at resources concerning use of pgsql > with > very large datasets? > > My specific problem is this: > > I have two tables, one with about 100 million rows and one with about 22,000 > rows. My plan was to inner join the two tables on an integer key and output > the 4 significant columns, excluding the keys. (Those with a better > understanding > of pgsql internals, feel free to laugh.) The result was a big angry psql > that > grew to 800+MB before I had to kill it. Was it psql that grew to 800Mb or a backend? If the former, how many rows do you expect that to return? You probably want to look into using cursors rather than returning the entire result set at once. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
