RE: [SQL] primary key question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 El 20-Jul-2000 Carolyn Lu Wong escribio: > create table aaa( > field1 not null, > field2 , > , > primary key (field1, field2) > ); > > Based on the above table definition, field2 allows null values. But > after the table created based on the above script, field2 becomes not > null. The only conclusion I come up with is setting the field as part of > the primary key, PostgreSQL automatically sets the field to not null. Or > is it something else? > > Is this a feature or bug? It's a SQL feature, because primary key must no contain null values. Pablo Niklas [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: PGPfreeware 5.0i for non-commercial use Charset: noconv iQA/AwUBOXg9Mozs62hO+t8PEQIsGgCdFO5HlMTy087WsBFc3tPSGFBKGOUAni6C 2NnOSwcsnlNFrkNsPoV1N3u7 =Ex7i -END PGP SIGNATURE-
[SQL] command in C++
am i use "PQexec()" or "conn.query()" for inserting informations in my BD ? thanks. Jerome.
Re: [SQL] command in C
> am i use "PQexec()" or "conn.query()" for inserting informations > in my BD ? I wrote a class to encapsulate all the ugliness of it. Inside my class I'm calling PQexec(). -Michael
[SQL] pg_dump
hai when dump the database it throw "getTables(): relation 'ordermaster': cannot find function with oid 1655 for trigger RI_ConstraintTrigger_2017354 " any clues thank advance p. ashok kumar __ Do You Yahoo!? Get Yahoo! Mail Free email you can access from anywhere! http://mail.yahoo.com/
Re: [SQL] pg_dump
sathya priya <[EMAIL PROTECTED]> writes: > when dump the database it throw > "getTables(): relation 'ordermaster': cannot find > function with oid 1655 for trigger > RI_ConstraintTrigger_2017354 " I think this must be another variant of the 6.5-pg_dump-with-7.0-database problem. There's no error message of that spelling in 7.0 pg_dump, but there is in 6.5 ... Check your PATH, or invoke pg_dump using a full path instead of letting it default. regards, tom lane
[SQL] Change attribute of a column
Hi, I have a field in text[] and I want to change it to a datetime[]. Is it possible? how? Thanks very much. - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
[SQL] Index selection on a large table
Hi. I believe this to be a bug, but I am submitting it to the SQL list as well in the case I overlooked something. I'm running Postgres 7.0.2 on FreeBSD 4.0-STABLE/Intel compiled with [gcc version 2.95.2 19991024 (release)] I've got this interesting problem where the query plan is not what I expect. Every month we log details of users logging into their webmail accounts. Every week before the database is vacuumed the oldest week's entries are removed. The table can be quite large. Here is the relevant parts of its structure: Table "logins" Attribute |Type | Modifier ---+-+-- loginid | varchar(16) | ip| varchar(15) | [...] Indices: logins_ip_idx, logins_loginid_idx The indexes are as follows: Index "logins_ip_idx" Attribute |Type ---+- ip| varchar(15) btree Index "logins_loginid_idx" Attribute |Type ---+- loginid | varchar(16) btree Size of the table: fastmail=> select count(*) from logins; count - 1082564 (1 row) Now here is a query plan from a selection using loginid: explain select * from logins where loginid='michael'; NOTICE: QUERY PLAN: Index Scan using logins_loginid_idx on logins (cost=0.00..500.57 rows=130 width=48) As expected it uses the logins_loginid_idx to select the rows that match loginid='michael'; Now I should note that I store the IP's as type varchar(15). The following query yeilds the questionable query plan: explain select * from logins where ip='38.30.141.44'; NOTICE: QUERY PLAN: Seq Scan on logins (cost=0.00..25248.51 rows=11602 width=48) This one decides to ignore the fact that IP is indexed (and the table was vacuumed) and it does a slow-assed sequential scan through a million or so rows to pick out (in this case 3 matching rows). Just to be sure, I re-vacuumed the table and tried the questionable query again with the same results. Here is the vacuum output in case it helps: NOTICE: --Relation logins-- NOTICE: Pages 11717: Changed 1, reaped 0, Empty 0, New 0; Tup 1082580: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 64, MaxLen 92; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 2.28s/15.38u sec. NOTICE: Index logins_ip_idx: Pages 4550; Tuples 1082580. CPU 0.78s/2.65u sec. NOTICE: Index logins_loginid_idx: Pages 3881; Tuples 1082580. CPU 0.62s/2.67u sec. VACUUM -Michael
Re: [SQL] Index selection on a large table
"Michael Richards" <[EMAIL PROTECTED]> writes: > The following query yeilds the questionable query plan: > explain select * from logins where ip='38.30.141.44'; > NOTICE: QUERY PLAN: > Seq Scan on logins (cost=0.00..25248.51 rows=11602 width=48) If there really were 11602 matching rows, the seq scan likely would be the way to go. So the issue here is poor selectivity estimation. You said you did a VACUUM, but you didn't mention if you'd ever done a VACUUM ANALYZE. That looks to me like it's using the default 1% selectivity estimate, which is what you'd get if you hadn't ever done an ANALYZE. If you have done one, what do you get from select attname,attdisbursion,s.* from pg_statistic s, pg_attribute a, pg_class c where starelid = c.oid and attrelid = c.oid and staattnum = attnum and relname = 'logins'; ? regards, tom lane
