Re: [GENERAL] JOIN exclusion problem
Thank you both for your replies. Unfortunately, the AND solution doesn't work since individuals who have a value for ap.kunden_nr are not likely to have one for ap.agentur_nr -- they are two fairly distinct groups. When I insert AND, the results are blank. Any other suggestions? At 16:23 01.07.99 +1000, you wrote: >Try using AND instead of OR. > >> SELECT apartner_nr, ap.anrede, ap.vorname, ap.nachname, ap.telefon, ap.fax, >> ap.email, ap.kunden_nr, k.name1, ap.agentur_nr, ag.name1 >> FROM ansprechpartner ap, kunden k, agenturen ag >> WHERE ap.kunden_nr = k.kunden_nr OR ap.agentur_nr = ag.agentur_nr >> ORDER BY nachname" > SELECT apartner_nr, ap.anrede, ap.vorname, ap.nachname, ap.telefon, ap.fax, > ap.email, ap.kunden_nr, k.name1, ap.agentur_nr, ag.name1 > FROM ansprechpartner ap, kunden k, agenturen ag > WHERE ap.kunden_nr = k.kunden_nr OR ap.agentur_nr = ag.agentur_nr ^^ You might wanna try an AND there. > ORDER BY nachname"
Re: [GENERAL] JOIN exclusion problem
I'm not sure that I understand exactly what you are trying to do. I'm guessing (and I mean guessing) that the tables are something like: ansprechpartner: private owner kunden: client agenturen: estate agent So are you trying to get is get details on all properties both privately rented and rented via an agency? I think a \d of all the tables involved would be useful to make sense of it all. In the interim though, if you only what unique combinations of ag.name1 and k.name1 you could try: SELECT DISTINCT k.name1, ag.name1 FROM ansprechpartner ap, kunden k, agenturen ag WHERE ap.kunden_nr = k.kunden_nr OR ap.agentur_nr = ag.agentur_nr; But somethings tells me that's not what you actually wanted! >Hello, > >I'm trying to join three tables, where I need only one piece of data from >two of the tables. The code I wrote is: > >SELECT apartner_nr, ap.anrede, ap.vorname, ap.nachname, ap.telefon, ap.fax, >ap.email, ap.kunden_nr, k.name1, ap.agentur_nr, ag.name1 >FROM ansprechpartner ap, kunden k, agenturen ag >WHERE ap.kunden_nr = k.kunden_nr OR ap.agentur_nr = ag.agentur_nr >ORDER BY nachname" > >The problem is that each row from each table is combined with each row of >the other tables. I know the code is wrong, but I don't know how to write >it so that the results show only the actual value of ag.name1 and k.name1, >and not all of the possible combinations. > >Is there a way of limiting how the rows are built up? Pardon for novice >nature of this question... > >Thanks in advance, > >Anja +-+--+ | Stuart Rison| Ludwig Institute for Cancer Research | +-+ 91 Riding House Street | | Tel. (0171) 878 4041| London, W1P 8BT, UNITED KINGDOM. | | Fax. (0171) 878 4040| [EMAIL PROTECTED] | +-+--+
[GENERAL] limits of float8
Dear all, What are the limits of float8? The user guide says variable-precision, 14 decimal places range but I'm not sure how that translates to a minimum value. I'm asking because I'm storing probability scores (range 0 to 1) with some scores very small indeed. The very small scores are all entered in scientific notation. >From experimentation, the smallest value I seem to be able to enter is around 2.225e-308. Is this 'correct'? This is PG 6.4 compiled with cc under Irix 5.3. Cheers, Stuart. +-+--+ | Stuart Rison| Ludwig Institute for Cancer Research | +-+ 91 Riding House Street | | Tel. (0171) 878 4041| London, W1P 8BT, UNITED KINGDOM. | | Fax. (0171) 878 4040| [EMAIL PROTECTED] | +-+--+
[GENERAL] alter table add column is broken in 6.5
I posted this yesterday and got no response, so I will try again. Alter table add column does not set the default value for new rows added to a table. I can except that it does not copy the new default value for existing rows. That is easy to work around. My problem is that the database does not reflect the default value for new rows added after the alter statement. I could work around this is someone could tell me how to modify the system tables to specify a default value. This does not seem to be much documentation for the layout of the system tables. Bryan White ArcaMax Inc. Yorktown VA www.arcamax.com
RE: [GENERAL] 6.4.2 and 6.5 date usertype difference
Cheers everyone. Therefore : - At runtime level : test=> show DATESTYLE; test=> SET DATESTYLE TO 'ISO'; - At boot up (/etc/rc.d/init.d/postgres) : Add : export PGDATESTYLE=ISO just before executing postmaster - At database level : $ postgres - option -e = European see $ man postgres - At user level : Add in to shell startup (Eg .bash_profile) : export PGDATESTYLE=ISO Phew! > > On Tue, 29 Jun 1999, Colin Price (EML) wrote: > > > Date: Tue, 29 Jun 1999 21:53:24 +0200 > > From: "Colin Price (EML)" <[EMAIL PROTECTED]> > > To: [EMAIL PROTECTED] > > Subject: [ADMIN] 6.4.2 and 6.5 date usertype difference > > > > > > I have just replicated a db in 6.5 from 6.4.2. > > > > The table selects the date differently from the identical table. > > I obviously set a parameter in 6.4.2 which I now cannot find (from > > mail list archive and doc as well). > > > > Please aid someone who is tearing his hair out. > > Thank you in advance, > > Colin. > > > > The table & contents : > > CREATE TABLE JUNK_QWERTY ( > > id int, > > mydate date > > ); > > insert into JUNK_QWERTY values (1, '1999-02-01'); > > insert into JUNK_QWERTY values (2, '1999-03-24'); > > insert into JUNK_QWERTY values (3, '1999-11-24'); > > > > 6.4.2 output : > > timesheet=> select * from JUNK_QWERTY; > > id|mydate > > --+-- > > 1|1999-02-01 > > 2|1999-03-24 > > 3|1999-11-24 > > (3 rows) > > > > 6.5 output : > > timesheet=> select * from JUNK_QWERTY; > > id|mydate > > --+-- > > 1|02-01-1999 > > 2|03-24-1999 > > 3|11-24-1999 > > (3 rows) > > > > == > > == > > 6.4.2 version : > > timesheet=> select version(); > > version > > > > - > > PostgreSQL 6.4.2 on i586-pc-linux-gnu, compiled by gcc > > 2.7.2. > > > > 6.5 version : > > timesheet=> select version(); > > version > > > > -- > > PostgreSQL 6.5.0 on i686-pc-linux-gnu, compiled by gcc > > 2.7.2.3 > > (1 row) > > > > > > >
Re: [GENERAL] JOIN exclusion problem
At 11:24 01.07.99 +0100, you wrote: >I'm not sure that I understand exactly what you are trying to do. I'm >guessing (and I mean guessing) that the tables are something like: > >ansprechpartner: private owner >kunden: client >agenturen: estate agent > The tables represent: ansprechpartner = contact person kunden = clients agenturen = agencies What I'm trying to do is get a list of all contact people, whether they're associated with a specific client or a specific agency, so that the name of the client or agency shows up in the results. --- > >In the interim though, if you only what unique combinations of ag.name1 and >k.name1 you could try: > >SELECT DISTINCT k.name1, ag.name1 >FROM ansprechpartner ap, kunden k, agenturen ag >WHERE ap.kunden_nr = k.kunden_nr OR ap.agentur_nr = ag.agentur_nr; > >But somethings tells me that's not what you actually wanted! You're right! I need pretty much all of the data from ap, so that doesn't work. I'm trying some of the other suggestions that just came in next to see if they work. Thanks for your help, Anja
Re: [GENERAL] alter table add column is broken in 6.5
It's not too practical to insert a new row into the system table that
handles the defaults, but here it goes. The table you are looking for is
pg_attrdef and contains the following columns.
adrelid -- The relation id for the table of the default value
adnum -- The column number of the default column
adbin -- The source (what returns the default value when a row is inserted)
adsrc -- The actual default value that you give postgres (ie nextval
('sequence'))
If you have another default that is exactly the same then I think it is
possible. You should figure out the relid of the table you are working on
and the column number is not difficult.
To obtain the table relid run this query:
select relname, oid from pg_class where relname = ''
The oid is the value for which you are looking. Then the column (adnum) id
is just the in order column number when you do a \d . Then be
sure to copy the adbin and adsrc from an identical default.
Hopefully that helped.
I have never tried this and so if any of you know that this won't work or if
you know that I have made some mistakes in my judgement, then please let me
know. I am a fairly new beginner at postgres (about 2 months).
-Dan Wilson
Bryan White wrote:
> I posted this yesterday and got no response, so I will try again.
>
> Alter table add column does not set the default value for new rows added
to
> a table. I can except that it does not copy the new default value for
> existing rows. That is easy to work around. My problem is that the
database
> does not reflect the default value for new rows added after the alter
> statement.
>
> I could work around this is someone could tell me how to modify the system
> tables to specify a default value. This does not seem to be much
> documentation for the layout of the system tables.
>
> Bryan White
> ArcaMax Inc.
> Yorktown VA
> www.arcamax.com
Re: [GENERAL] JOIN exclusion problem
On Thu, Jul 01, 1999 at 03:43:27PM +0200, Anja Speerforck wrote: > At 11:24 01.07.99 +0100, you wrote: > >I'm not sure that I understand exactly what you are trying to do. I'm > >guessing (and I mean guessing) that the tables are something like: > > > >ansprechpartner: private owner > >kunden: client > >agenturen: estate agent > > > The tables represent: > > ansprechpartner = contact person > kunden = clients > agenturen = agencies > > What I'm trying to do is get a list of all contact people, whether they're > associated with a specific client or a specific agency, so that the name of > the client or agency shows up in the results. > --- Ah, now it's clear. You're looking for the UNION operator, and two selects. Something like: SELECT apartner_nr, ap.anrede, ap.vorname, ap.nachname, ap.telefon, ap.fax, ap.email, ap.kunden_nr, k.name1 FROM ansprechpartner ap, kunden k WHERE ap.kunden_nr = k.kunden_nr UNION SELECT apartner_nr, ap.anrede, ap.vorname, ap.nachname, ap.telefon, ap.fax, ap.email, ap.agentur_nr, ag.name1 FROM ansprechpartner ap, agenturen ag WHERE ap.agentur_nr = ag.agentur_nr ORDER BY nachname Hope this helps, Ross
[GENERAL] LinuxPPC
Hi all - I scoured the archives and found information regarding changing the template settings for linux_ppc when configuring postgresql 6.5 for LinuxPPC R5. This is my situation 1) Installed R5 and downloaded postgresql 6.5. Configured, compiled and installed postgresql. created a db, did the select date command, then got the errors when trying to do a "destroydb". 2) Deleted the pgsql directory and the source directory and tried again. This time, I untarred postgresql and tried again but now when I configured and ran make and installed, the /data directory was not created at all. 3) Scoured the archives and found out about changing the template to turn off egcs optimization and did that. Again, I removed the installation and source directories and tried again. This I tried over and over 3 times and each time the /data directory was not created. I've installed 6.4.2 several times and I've been following the INSTALL document very carefully using only the defaults (except for the template issues). Any ideas?? Also does turning off the optimization have much of an effect on performance on PowerPC machines? I'd like to use my PowerPC as it has an UltraSCSI PCI card installed so I was hoping that would be a better performing machine then an IDE PC machine. Thanks for any help. Mike
Re: [GENERAL] LinuxPPC
Thanks, but on the "About" page, it says they are using posgresql 6.4.2. I'm trying to figure out how to get 6.5 running correctly. Mike -- >From: Sascha Schumann <[EMAIL PROTECTED]> >To: "Mike Engelhart" <[EMAIL PROTECTED]> >Cc: [EMAIL PROTECTED] >Subject: Re: [GENERAL] LinuxPPC >Date: Thu, Jul 1, 1999, 10:18 AM > > www.geocrawler.com
