RE: [GENERAL] Strange reference problem
OK, I see. The REFERENCES clause added to a column definition is a short cut if you only have one column as the foreign key, similar to the PRIMARY KEY clause, which you can add to a single column, but if you want a multi-column primary key, then you have to do it at the end. Thanks, Tom. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 18 January 2001 02:21 To: Michael Ansley Cc: '[EMAIL PROTECTED] ' Subject: Re: [GENERAL] Strange reference problem Michael Ansley [EMAIL PROTECTED] writes: CREATE TABLE "swt" ( "id" varchar(10) not null, "org" varchar(7) not null references "organisation"("id"), "description" varchar not null, primary key("id", "org") ); DROP TABLE "poi"; CREATE TABLE "poi" ( "id" varchar(15) not null, "org" varchar(7) not null references "organisation"("id"), "name" varchar not null, primary key("id", "org") ); DROP TABLE "tug"; CREATE TABLE "tug" ( "id" varchar(21) not null primary key, "description" varchar not null, "id_swt" varchar(10) not null references "swt"("id"), "swt_org" varchar(7) not null references "swt"("org"), "id_poi" varchar(15) not null references "poi"("id"), "poi_org" varchar(7) not null references "poi"("org") ); Those primary key declarations say that the combination of ID and ORG together will be unique for each row of swt (ditto poi). They do not promise that either ID or ORG will be unique by itself --- but that's what the references clauses require to be valid. You probably want to declare the references using a FOREIGN KEY clause that says that the two-column pair id_swt, swt_org references the two-column pair swt(id,org), and likewise for poi. AFAIK that's the only way to define a multi-column reference key. regards, tom lane ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ** application/ms-tnef
[GENERAL] Re: GET DIAGNOSTICS SELECT PROCESSED INTO int4_variable
On Thu, Jan 18, 2001 at 22:30:48 +1300, Dan Langille wrote: When will 7.1 leave beta (roughly)? From what I've been reading on the lists, "soon". I suspect within the next month or two. How stable is it? FWIW, I'll be using it for development, not production I've not tested it extensively myself, but it passed my lithmus test (import a database of a couple of million records in one of its tables, with numerous constraints and indexes, drop some data based on columns which aren't indexed, then vacuum it [*]). HTH, Ray [*] I've had infinite loops VACUUMing this database under 7.0.3. -- "The problem with the global village is all the global village idiots." Paul Ginsparg
[GENERAL] Re: Errors compiling 7.0.3 on Solaris 7
El Mi 17 Ene 2001 15:49, Gareth Cantrell escribi: Hi: I've been trying in vain for over 5 hours now to compile PostgreSQL 7.0.3 on Solaris 7 (2.7). I keep getting the following error: ld -G -o libpq++.so.3.1 pgconnection.o pgdatabase.o pgtransdb.o pgcursordb.o pglobject.o -L../../interfaces/libpq -lpq -ldl -lsocket -lresolv -lnsl -lm -lc pgconnection.o: could not read symbols: Bad value make[2]: *** [libpq++.so.3.1] Error 1 make[2]: Leaving directory `/usr/share/src/postgresql-7.0.3/src/interfaces/libpq++' make[1]: *** [all] Error 2 make[1]: Leaving directory `/usr/share/src/postgresql-7.0.3/src/interfaces' make: *** [all] Error 2 I have: gcc 2.95.2 gmake 3.78.1 readline 4.1 Flex 2.5.4 Bison 1.28 I'm not sure if this is the correct answer, but, at this moment I'm installing gcc (compiling with shared), postgres-7.1-beta1, apache, php, etc. and for what my last 2 days of experience say, use the Solaris make and binutils. They should be in /usr/ccs, if they are installed. If you have them, just change your environment variables PATH and LD_LIBRARY_PATH. I have also set LD_RUN_PATH=/usr/local/pgsql/lib I have to have this running within 10 hours... Someone PLEASE, PLEASE HELP Saludos... :-) -- System Administration: It's a dirty job, but someone told I had to do it. - Martn Marqus email: [EMAIL PROTECTED] Santa Fe - Argentinahttp://math.unl.edu.ar/~martin/ Administrador de sistemas en math.unl.edu.ar -
Re: [GENERAL] Re: JDBC Performance
Bruce Momjian [EMAIL PROTECTED] writes: Can you confirm that it is OK now? Your email from October 18th below seems to indicate it is not correct. Can I get a diff against the current CVS source that I can apply? Thanks. Sorry for the confusion. It wasn't okay before I left to Kenya on 10 december. I notified Peter about this before I went down there and I just came back two days ago, so I'm not yet up to speed on the modifications. ... cvs update ... The cvs update confirms that it is still _not_ okay. It is the first buggy version of the code that is in there still. I will supply a new patch based on the current CVS. Hopefully I will get some time tonight and on Sunday to complete the patch. regards, Gunnar
Re: [GENERAL] Re: postgresql.conf ignored
On Wed, Jan 17, 2001 at 11:46:11PM -0500, Lamar Owen wrote: Have you added the required lines to /etc/syslog.conf? (Yes, I know I haven't thoroughly documented that. Working on it.). It _is_ documented in the archives -- do a search on '/etc/syslog.conf'. Ah, no I hadn't, thanks. I'd tried searching the archives for "logging" and "RPM" (and various other combinations of stuff which might have got me the info), but no dice. I'll wait for this to be searchable in the archive, and if it doesn't come up easily, I'll try submitting something under a Subject: that will get the hits. Thanks, A -- Andrew Sullivan | McMaster University Research Computing Support, CIS | 1280 Main Street West [EMAIL PROTECTED]| Hamilton, Ontario +1 905 525 9140 x 27601 | L8S 4L8
[GENERAL] RE: Why is there so much MySQL bashing???
Agree 100% -Original Message- From: Philip Hallstrom [mailto:[EMAIL PROTECTED]] Sent: 17 January 2001 10:05 To: PostgreSQL General Subject: Why is there so much MySQL bashing??? What are my impressions going to be of PostgreSQL (and now Great Bridge since their name will pop up as well) if I search for "mysql vs postgresql" and see a lot of mysql bashing posts in thse lists? I'm going to think you all are a bunch of whining losers who can't fathom the possibility that there might be good (or even better) alternatives out there. That can't be good for PostgreSQL, can it? -philip
[GENERAL] nested table
Hi I'm sure it has been already discused milion times but i can't find it from list archives. Whats the syntax for inserting and quering tables with nested tables. For example: CREATE TABLEfoo (attr1 int2, attr2 int2); CREATE TABLE foo1 (idint4, name text,attr1 foo); Now, how can i insert values into foo1? Thanks Peeter
[GENERAL] referential integrity
Title: referential integrity Please could someone help me my database does not have referential integrity. I want to be able to delete a primary key in one table and for it to delete anything relating to it below ( same with update). i.e table product primary key product_name table release primary key release_id product_name field references product_name in product table I get a message to screen that says NOTICE: CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented thanks in advance for any help whatsoever. Michelle Anderson
[GENERAL] View table relationship
Hi. I have a big problem with postgres because I need to know how I can see the relations among the table like foreign-key. It' s possible use some commands or graphic tool from wich I can see that relations? Do you know some sites where i can found more information about this! Thank you very much, and exuse me for my bad English! Fabio
[GENERAL] permissions on user-defined functions
I am new to PostgreSQL... I've created a table (table1) and database user (dbuser). dbuser has only SELECT permission on table1. Next I created a function insert_table1 that inserts a new row into table1. I want to give dbuser the ability to invoke the insert_table1 function without granting dbuser INSERT permission on table1. Does PostgreSQL make this possible? Can someone kindly point out how to do it? Thanks! Kevin T. Manley [EMAIL PROTECTED]
[GENERAL] Query question
How can I make query SELECT * FROM my_table WHERE my_field = 'A*'; Meaning that I want to get all rows from my_table where my_field value begins with alphabet "A"
RE: [GENERAL] Query question
SELECT * FROM my_table WHERE my_field LIKE 'A%'; Should do it! -Original Message- From: Jorch [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 16, 2001 5:40 PM To: [EMAIL PROTECTED] Subject: [GENERAL] Query question How can I make query SELECT * FROM my_table WHERE my_field = 'A*'; Meaning that I want to get all rows from my_table where my_field value begins with alphabet "A"
[GENERAL] Re: Query question
SELECT * FROM my_table WHERE my_field LIKE 'A%'; Greg - Original Message - From: "Jorch" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 16, 2001 12:39 PM Subject: Query question How can I make query SELECT * FROM my_table WHERE my_field = 'A*'; Meaning that I want to get all rows from my_table where my_field value begins with alphabet "A"
[GENERAL] RE: Why is there so much MySQL bashing???
That can't be good for PostgreSQL, can it? Neither can not being able to do rpm -Uvh and have it work first time... Nor not finding a Dreamweaver Ultradev database connection for Postgresql in the live data menu when there is a Mysql one... Nor not being able to find somewhere to get a Postgresql / JSP application hosted when there are hundreds of ISPs hosting Mysql / JSP... All these things must change. Just being better is not good enough - the Mac is a better desktop computer than a Windows PC. Cheers Tony Grant -- It's just some computers connected together...
[GENERAL] Tuning queries and distinct behaviour
Is there a way to tune queries? I'm doing queries that join around 5-6 tables. All join fields are indexed either in hash (where tables are small enough and join is done on "="), or btree (big tables, not joined on "="). The tables have between several hundred and several tens of millions of records. The problem is that this tends to take a _VERY_ long time. I know that I'm asking for a bit much on such a huge task, but if I break the queries down manually into 10 simper ones that I could run manually with temporary tables, each of those would take a few seconds at most. The optimizer occasionally decides to do sequential scans, and this is probably what is killing the performance. Is there any way to give the optimizer hints? I suspect that it would help in a vast majority of cases if it looked ad the where clauses in views and selects on views and tried cutting down the working set through that, and then pruning down the rest as it went along. It just seems a bit strange that doing subqueries with temporary tables should be so much faster. Doing VACUUM ANALYZE often helps, but not always. Is there any way to give the optimizer hints on how to speed up selects on views that do big joins, both inner and outer? Another question - I have to do a join on the "" operation. Something like: SELECT * FROM Table1, Table2 WHERE Table1.Field1 Table2.Field2 ORDER BY Table1.Field1 DESC, Table2.Field2 DESC; This will give me a very large set of records. However, I only really need a few of those records. I only want the highest Field1, Field2 combination records for some other field in Table1. Effectively, something like: CREATE VIEW SomeView AS SELECT * FROM Table1, Table2 WHERE Table1.Field1 Table2.Field2 ORDER BY Table1.Field1 DESC, Table2.Field2 DESC; and then doing: SELECT DISTINCT ON (Table1.Field3) * FROM SomeView. I would hope that DISTINCT would pick the first record returned for each of the different occurences in SomeView. Unfortunately it doesn't. By having a quick scan at the returned records, it doesn't seem to pick them in any particular order. Is this the case? And is there an SQL standard that says which records should DISTINCT return (first, last, or arbitrary)? And is there a way to enforce this behaviour, just in case the default behaviour changes in the future? Alternatively, can anyone think of a solution to this problem? Thanks. Gordan
Re: [GENERAL] RE: Why is there so much MySQL bashing???
Tony Grant wrote: That can't be good for PostgreSQL, can it? Neither can not being able to do rpm -Uvh and have it work first time... H... When was the last time you tried? Thanks to the 'Do No Harm' principle, it would be foolhardy to do what has to be done to upgrade between major versions in a fully automatic fashion. So the semiautomatic way it is now done is the current best compromise. Of course, the ideal would be for a new version of PostgreSQL to be able to at least read and convert existing tables on the fly (as in when postmaster is started, or when a backend is first brought up on the table in question, or even a standalone migration utility that doesn't require an old version of the backend to read the old version files), but I wouldn't hold your breath. Yes, the existing scheme is a little baroque -- but it's better than it used to be. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
Re: [GENERAL] RE: Why is there so much MySQL bashing???
On 2001.01.18 17:31:29 +0100 Lamar Owen wrote: Tony Grant wrote: That can't be good for PostgreSQL, can it? Neither can not being able to do rpm -Uvh and have it work first time... H... When was the last time you tried? Yesterday... The RedHat 6.2 rpms are broken if they find the tiniest trace of a preceding instalation. The machine target for 7.0.3 had a copy of 6.5.x installed when the ReHat server was installed. The database system was initiallised but never used. I removed all traces of the 6.5.x install and did a 7.0.3 install. initdb fails of course because there must be something somewhere that makes it choke. I have installed previous versions from source and spent hours getting permissions right so that initdb would run. Now I just want to set up a new machine and rpm is a convenient way of doing that. When it doesn't work as advertized it is a little "annoying". Any pointers greatly appreciated. Cheers Tony Grant -- It's just some computers connected together...
Re: [GENERAL] nested table
"Peeter Smitt" wrote: I'm sure it has been already discused milion times but i can't find it from= list archives. Whats the syntax for inserting and quering tables with nested tables. For example: CREATE TABLE foo (attr1 int2, attr2 int2); CREATE TABLE foo1 (id int4, name text, attr1 foo); Now, how can i insert values into foo1? I asked this same question a couple of weeks back. Apparently the facility to do this is no longer present in PostgreSQL. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "For the eyes of the LORD run to and fro throughout the whole earth, to show himself strong in the behalf of them whose heart is perfect toward him..." II Chronicles 16:9
Re: [GENERAL] referential integrity
"Michelle Anderson" wrote: I get a message to screen that says NOTICE: CREATE TABLE/FOREIGN KEY clause ignored; not yet implemented You need to upgrade to PostgreSQL 7.0.3; referential inegrity is not implemented in 6.5.3 and earlier. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "For the eyes of the LORD run to and fro throughout the whole earth, to show himself strong in the behalf of them whose heart is perfect toward him..." II Chronicles 16:9
[GENERAL] Another optimizer question
I am not sure if this is a bug, an oversight or something else entirely, but it would appear that if there are two tables, Table1 and Table2, which are joined using INNER JOIN, specifying WHERE = one of the join fields doesn't automatically get equalised to the other field. For example: SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 = Table2.Field1) WHERE Table1.Field1 = 'SomeValue'; takes a very long time (several minutes), and explain says that sequential scans are used on both tables. However, changing the above to: SELECT * FROM Table1 INNER JOIN Table2 ON (Table1.Field1 = Table2.Field1) WHERE Table1.Field1 = 'SomeValue' AND Table2.Field1 = 'SomeValue'; yields the correct answer in a fraction of a second. Explain says that indices are being used. However, here's a REALLY strange thing. If I do: SET ENABLE_SEQSCAN = OFF; and run the first query, explain says that indices are used, but it STILL takes forever. The first, slow query executes a merge join, while the second only executes two index scans in a nested loop. Why? This seems like a fairly basic thing, but it seems to break something in the way the query is executed... Regards. Gordan
Re: [GENERAL] Tuning queries and distinct behaviour
On Thu, 18 Jan 2001, Gordan Bobic wrote: Is there a way to tune queries? I'm doing queries that join around 5-6 tables. All join fields are indexed either in hash (where tables are small enough and join is done on "="), or btree (big tables, not joined on "="). The tables have between several hundred and several tens of millions of records. The problem is that this tends to take a _VERY_ long time. I know that I'm asking for a bit much on such a huge task, but if I break the queries down manually into 10 simper ones that I could run manually with temporary tables, each of those would take a few seconds at most. The optimizer occasionally decides to do sequential scans, and this is probably what is killing the performance. Is there any way to give the optimizer hints? I suspect that it would help in a vast majority of cases if it looked ad the where clauses in views and selects on views and tried cutting down the working set through that, and then pruning down the rest as it went along. It just seems a bit strange that doing subqueries with temporary tables should be so much faster. Is it deciding that a lot of rows will match when it does the sequential scans? I guess a copy of the schema and query would help. Doing VACUUM ANALYZE often helps, but not always. Is there any way to give the optimizer hints on how to speed up selects on views that do big joins, both inner and outer? Another question - I have to do a join on the "" operation. Something like: SELECT * FROM Table1, Table2 WHERE Table1.Field1 Table2.Field2 ORDER BY Table1.Field1 DESC, Table2.Field2 DESC; This will give me a very large set of records. However, I only really need a few of those records. I only want the highest Field1, Field2 combination records for some other field in Table1. Effectively, something like: CREATE VIEW SomeView AS SELECT * FROM Table1, Table2 WHERE Table1.Field1 Table2.Field2 ORDER BY Table1.Field1 DESC, Table2.Field2 DESC; and then doing: SELECT DISTINCT ON (Table1.Field3) * FROM SomeView. I would hope that DISTINCT would pick the first record returned for each of the different occurences in SomeView. Unfortunately it doesn't. By having a quick scan at the returned records, it doesn't seem to pick them in any particular order. Is this the case? And is there an SQL standard that says which records should DISTINCT return (first, last, or arbitrary)? And is there a way to enforce this behaviour, just in case the default behaviour changes in the future? DISTINCT ON is a non-standard "feature" and yes, without additional help, it will not pick a particular row (well, it's probably the first one it comes across). I believe if you use order by you can get the first/last by the metric you ordered by. So if you did Field3, Field1 DESC, Field2 DESC in the order by it should give you the highest Field1 valued row, and then higest Field2 for ties.
Re: [GENERAL] Re: JDBC Performance
Thanks you so much. I am sorry for the mixup. We will get it in there ASAP when it arrives. Bruce Momjian [EMAIL PROTECTED] writes: Can you confirm that it is OK now? Your email from October 18th below seems to indicate it is not correct. Can I get a diff against the current CVS source that I can apply? Thanks. Sorry for the confusion. It wasn't okay before I left to Kenya on 10 december. I notified Peter about this before I went down there and I just came back two days ago, so I'm not yet up to speed on the modifications. ... cvs update ... The cvs update confirms that it is still _not_ okay. It is the first buggy version of the code that is in there still. I will supply a new patch based on the current CVS. Hopefully I will get some time tonight and on Sunday to complete the patch. regards, Gunnar -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] Re: View tables relationship
Have you tried PGAdmin? You can use this graphical tool from windows. It's very good and it's also free! "riccardo" [EMAIL PROTECTED] wrote in message news:941d3q$fqm$[EMAIL PROTECTED]... Hi. I have a big problem with postgres because I need to know how I can see the relations among the table like foreign-key. It' s possible use some commands or graphic tool from wich I can see that relations? Do you know some sites where i can found more information about this! Thank you very much, and exuse me for my bad English! Fabio
[GENERAL] consistency check ?
Hi to all, I am considering to use PostgreSQL. But I need some more information about some features: Is there a consistency check? Online or Offline? Thanks Toni
[GENERAL] Troubles with performances
Hi, I use PostGreSQL with a Web server which receive 200 HTTP simultaneous queries. For each HTTP query, I have about 5 SELECT queries and 3 UPDATE ones. Queries have been optimized, I have INDEX on my tables... The biggest table has more than 500 000 records. And I have big troubles, PostGreSQL doesn't seem to be able to handle so many queries at the same time. The server is a NetFinity bi-proc 400 MHz with 1Gb RAM... What can I do ?? Thanx in advance Guillaume
[GENERAL] Re: Query question
select * from my_table where my_field like 'A%'; "Jorch" [EMAIL PROTECTED] wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... How can I make query SELECT * FROM my_table WHERE my_field = 'A*'; Meaning that I want to get all rows from my_table where my_field value begins with alphabet "A"
[GENERAL] initdb failing
Hello, I am going throught the pains of initdb failing again. I know that usually this is a permissions problem but can't remember how I fixed it 18 months ago (yes we should all have a notebook to document such things...) I had a working 6.5.x on RedHat 6.2 and (stupidly) decided to upgrade to 7.0.3. rpm -Uvh postgres* installs all the bits that interest me, but "initdb -D /var/lib/pgsql" and "/etc/rc.d/init.d/postgres start" both fail. postgres]$ initdb -D /home/postgres This database system will be initialized with username "postgres". This user will own all the data files and must also own the server process. Fixing permissions on pre-existing data directory /home/postgres Creating database system directory /home/postgres/base Creating database XLOG directory /home/postgres/pg_xlog Creating template database in /home/postgres/base/template1 Creating global relations in /home/postgres/base Adding template1 database to pg_database /usr/bin/initdb: /tmp/initdb.29795: Permission denied /usr/bin/initdb: /tmp/initdb.29795: Permission denied /usr/bin/initdb: /tmp/initdb.29795: Permission denied /usr/bin/initdb: /tmp/initdb.29795: No such file or directory initdb failed. Removing /home/postgres. rm: cannot remove directory `/home/postgres': Permission denied Failed. Removing temp file /tmp/initdb.29795. HELP =;-` Cheers Tony Grant -- It's just some computers connected together...
[GENERAL] ltsWriteBlock: failed to write block xxx of temporary file
I have a database and a table with 7 records (which i dont think it's too much) I join this table with another of about the same size and put a record restriction (WHERE) and everything works out fine, but when I put two SUMs over a couple of fields and add a GROUP BY I keep getting this error, ltsWriteBlock: failed to write block 27231 of temporary file Perhaps out of disk space? I checked and I have over 1GB free so I figure this must have something to do with configuration parameters. I'd appreciate any help. Ligia
Re: [GENERAL] Re: MySQL file system
On Wed, Jan 17, 2001 at 02:21:01PM +0100, Fausto Guzzetti wrote: Hello, I have what I think is a trivial problem. But I wasn't able to find a solution in the FAQ or in the docs I have. It has to do with case sensitivity. I am working with an application of PostrreSQL (7.02) and PHP4. My table name is (for example) avi_f_amm and contains a field named Number (capital N) The query select * from avi_f_amm works fine. The query select * from avi_f_amm where Number gt 1 does not because number (small cap n) is to a field in the table. In other words Postgresql does not recognize "Number" and thinks it is "number" Any idea of what is going on or where should I look in the documentation? Try: select * from avi_f_amm where "Number" 1; Cheers, Patrick
Re: [GENERAL] character sets
hi all, Does postgres support other character sets? We've been thinking in porting an application to chinesse, but we don't know if it's possible to store chinesse characters in postgres I don't know what kind of Chinese are talking about, but PostgreSQL does support both tradional Chinese and simplified Chinese. tradional Chinese: EUC-CN simplified Chinese: EUC-TW (you could use Big5 for clients only. PostgreSQL will do automatic conversion between Big5 and EUC-TW, in this case) ALso, you could use UNICODE(UTF-8). For upcomming 7.1, PostgreSQL will provide automatic conversion between: UTF-8 -- EUC-CN UTF-8 -- EUC-TW UTF-8 -- Big5 -- Tatsuo Ishii
Re: [GENERAL] initdb doesn not create template1 database
"mike" [EMAIL PROTECTED] writes: Tom thanks for the heads up on the bug earlier, thngs worked much better ui= ng the currnet snapshot, though fomr some reason initdb seems to do its job= though its not creating database template1. That's an interesting definition of "doing its job" :-(. How about you show us the exact output you're getting, not an interpretation? regards, tom lane
[GENERAL] Re: Query question
SELECT * FROM my_table WHERE my_field LIKE 'A%'; --rob - Original Message - From: "Jorch" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, January 16, 2001 12:39 PM Subject: Query question How can I make query SELECT * FROM my_table WHERE my_field = 'A*'; Meaning that I want to get all rows from my_table where my_field value begins with alphabet "A"
Re: [GENERAL] Performance of a single (big) select and Multiprocessor Machines
Konstantinos Agouros [EMAIL PROTECTED] writes: Is there a way in postgres to make use of the extra cpu(s) the machine has for the single tasks of importing the data and doing the somewhat intensive selects that result from the sheer amount of data. Maybe I'm missing something, but it seems like all you need to do is run the data import and the selects in different processes (multiple backends). There isn't any way to apply multiple CPUs in a single SELECT, if that's what you were hoping for. Perhaps you could break down the data reduction task into independent subqueries, but that will take some thought :-( regards, tom lane
Re: [GENERAL] Problems building 7.1beta3 on WinNT under cygwin
Barry Lind [EMAIL PROTECTED] writes: dllwrap -o plpgsql.dll --dllname plpgsql.dll --def plpgsql.def pl_parse.o pl_handler.o pl_comp.o pl_exec.o pl_funcs.o ../../../../src/utils/dllinit.o -L/usr/local/lib -L/usr/local/lib -L../../../../src/backend -lpostgres -lcygipc -lcygwin -lcrypt -lkernel32 pl_exec.o(.text+0x3465):pl_exec.c: undefined reference to `TransactionCommandContext' I think that's been resolved since beta3. Try the nightly snapshot. Also read the recent mail list traffic concerning the NT port ... mostly in pgsql-ports, IIRC, but check -patches and -hackers too. regards, tom lane
Re: [GENERAL] child table doesn't inherit PRIMARY KEY?
Does this bug still exist? [ Charset ISO-8859-1 unsupported, converting... ] Louis-David Mitterrand writes: When creating a child (through CREATE TABLE ... INHERIT (parent)) it seems the child gets all of the parent's contraints _except_ its PRIMARY KEY. Is this normal? It's kind of a bug. -- Peter Eisentraut Sernanders v?g 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
[GENERAL] Re: Help with query. (*)
Read the docs on "exists" that should be what you are looking for. --rob - Original Message - From: "Diehl, Jeffrey" [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: "Diehl, Jeffrey" [EMAIL PROTECTED] Sent: Tuesday, January 16, 2001 3:42 PM Subject: Help with query. (*) I'm having difficulty writing a query which I really can't live without... I need to get a list of records from table A for which there are corresponding records in table B. I've tried to use the intersect clause, but it doesn't seem to work, or it runs far too long. For example: select * from A where 1=1 intersect select * from A where B.x=A.x and A.y=B.y and A.z=B.z limit 100 I need the most efficient method possible; my A tables have upward of 5 Million records. The B table, btw, only has about 100 records. Any help will be most appreciated.