[SQL] primary key and indexing
I m sorry If anyone has already asked this Q earlier I wanted to know Q1. Whether I can have another key on which the table is indexed even though i have a primary key..Then how would the data be accessed according to primary key or the indexed key of my choice or i can index as per my choice ( primary / index key)? Q2. If possible can anybody tell me how to get the script given in the e-book of Postresql be interpreted in Perl i.e perl gives an error that Pg is not connected to Dbase
[SQL] Indices
Maybe a wrong mailinglist to ask this, but... Are there any plans on writing a chapter in the documentation about the implementation of indices. I know from M$ SQL Server that it is no picnic determining which indices are useful, and thick books can be written on how to make the right indices for the right query. I have no idea if postgres has similar strange ways of using indices, but even so, that should be written in the documentation. However I find it highly probable that I am not the only one having trouble designing indices. Often I see questions on this mailinglist sounding like: "Why is my query not using the index". These would perhaps be less frequent in the future if such a chapter was written. Have a nice week-end Regards, Nikolaj
[SQL] SELECT FROM SELECT
Hi, All !! Does Postrges provide "Select * from Select * from t1" construction ? I use Postrges v.7.0.3 and get "ERROR: parser: parse error at or near "select" response for such request. Thanks, Leksey
[SQL] Trouble with subqueries
I have the following two tables:
create table movies (
name varchar(80),
info varchar(80),
length int,
primary key (name)
);
create table ratings (
name varchar(80),
userid varchar(10),
rating char(1),
foreign key (name) references movies,
primary key(name, userid)
);
The tables contain movies and users' ratings of the movies.
I would like to get a listing of all the movies along with how many
users have given the movie some particular rating. The first solution
that I came up with was this:
SELECT name, length, fives
FROM movies,
(SELECT name as rname,
count(*) as fives
FROM ratings
WHERE rating='5'
GROUP BY name)
WHERE name=rname;
but in PostgreSQL 7 it just gives me this error message:
ERROR: parser: parse error at or near "("
I have previously used similar queries in Oracle where they have worked,
so it would seem to me that PostgreSQL doesn't support subselects after
all despite all the claims.
Am I doing something wrong or/and is there some another way of making
this query that would work in PostgreSQL?
Re: [SQL] Trouble with subqueries
Jussi Vainionpää <[EMAIL PROTECTED]> writes:
> SELECT name, length, fives
> FROM movies,
>(SELECT name as rname,
>count(*) as fives
> FROM ratings
> WHERE rating='5'
> GROUP BY name)
> WHERE name=rname;
>
> but in PostgreSQL 7 it just gives me this error message:
> ERROR: parser: parse error at or near "("
> I have previously used similar queries in Oracle where they have worked,
> so it would seem to me that PostgreSQL doesn't support subselects after
> all despite all the claims.
It allows subselects in the WHERE clause, but not in the FROM clause.
> Am I doing something wrong or/and is there some another way of making
> this query that would work in PostgreSQL?
What you can do, is create a view with your subselect, which you can
then use in the FROM clause.
Tomas
Re: [SQL] Trouble with subqueries
Hello Jussi,
Once, Friday, January 19, 2001, 12:34:50 PM, you wrote:
JV> I have the following two tables:
JV> create table movies (
JV> name varchar(80),
JV> info varchar(80),
JV> length int,
JV> primary key (name)
JV> );
JV> create table ratings (
JV> name varchar(80),
JV> userid varchar(10),
JV> rating char(1),
JV> foreign key (name) references movies,
JV> primary key(name, userid)
JV> );
JV> The tables contain movies and users' ratings of the movies.
JV> I would like to get a listing of all the movies along with how many
JV> users have given the movie some particular rating. The first solution
JV> that I came up with was this:
JV> SELECT name, length, fives
JV> FROM movies,
JV>(SELECT name as rname,
JV>count(*) as fives
JV> FROM ratings
JV> WHERE rating='5'
JV> GROUP BY name)
JV> WHERE name=rname;
JV> but in PostgreSQL 7 it just gives me this error message:
JV> ERROR: parser: parse error at or near "("
JV> I have previously used similar queries in Oracle where they have worked,
JV> so it would seem to me that PostgreSQL doesn't support subselects after
JV> all despite all the claims.
JV> Am I doing something wrong or/and is there some another way of making
JV> this query that would work in PostgreSQL?
If I understand correctly it must looks like this:
SELECT name, length,
(SELECT count(*)
FROM ratings
WHERE rating='5'
and rating.name=movies.name) as fives
FROM movies
WHERE name=rname;
--
Best regards,
Yury
Re: [SQL] primary key and indexing
Sorry for lack of proper quoting, you might find it useful to From: "Sharmad Naik" <[EMAIL PROTECTED]> I m sorry If anyone has already asked this Q earlier I wanted to know Q1. Whether I can have another key on which the table is indexed even though i have a primary key..Then how would the data be accessed according to primary key or the indexed key of my choice or i can index as per my choice ( primary / index key)? You can create as many indices as you like. Type "\h create index" in psql for details, but basically: CREATE INDEX indexname ON table (columnlist) PostgreSQL will use whichever index it thinks is the most useful. Remember to vacuum analyze to update table statistics. Q2. If possible can anybody tell me how to get the script given in the e-book of Postresql be interpreted in Perl i.e perl gives an error that Pg is not connected to Dbase Don't know the script you're talking about. Check the settings in the connect command. Try "perldoc Pg" for information on how the Pg module works. - Richard Huxton
Re: [SQL] notice on transaction abort?
On Thu, 18 Jan 2001, Stephan Szabo wrote: > Well, you should have gotten an error message from the statement that was > in error in any case, but maybe a message on the commit/end that says > that the transaction was aborted due to errors would be nice. Or both. Zoltan
[SQL] Where can i get Pgaccess
Hello everyone, Please can anyone tell me, where i can get Pgaccess query tool Regards, -- Ramesh HR Trainee Engineer EASi Technologies 213, 3rd Main, 4th Cross Chamrajpet, Bangalore - 560 018 India Ph.: 660 1086 / 660 2365 / 667 2984 Extn.: 155 Facsimile: 667 5274 www.easi.soft.net
Re: [SQL] Distributed database ?
Thus spake guard
> how to run "select from databaseA:tabl1 ,databaseB:table2 "
You can't. You have to have two databases open and do it yourself. I use
Python and often do things like this.
import pg
adb = pg.DB('dbase_a')
bdb = pg.DB('dbase_b')
row = adb.get('table_a', 1)# second argument references primary key
# of table_a in database_a
bdb.get('table_b', row)# one of the fields in row is the primary
# key of table_b in database_b
This assumes that the field names are consistent between databases otherwise
you have to add an extra assignment in but that's the basic idea. Similar
ways exist in other interfaces.
--
D'Arcy J.M. Cain| Democracy is three wolves
http://www.druid.net/darcy/| and a sheep voting on
+1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner.
Re: [SQL] Where can i get Pgaccess
Ramesh H R wrote: >Hello everyone, >Please can anyone tell me, where i can get Pgaccess query tool It is in the PostgreSQL source at src/bin/pgaccess/ See also http://www.flex.ro/pgaccess -- 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 "The LORD is my strength and song, and he is become my salvation; he is my God, and I will prepare him an habitation; my father's God, and I will exalt him." Exodus 15:2
Re: [SQL] SELECT FROM SELECT
"Aleksey V. Kurinov" <[EMAIL PROTECTED]> writes: > Does Postrges provide "Select * from Select * from t1" construction ? In 7.1. But you have to spell it per the SQL spec: select * from (select * from t1) as foo; The parentheses and alias name are not optional. regards, tom lane
[SQL] (No Subject)
Hello, I have visited your web site and found very interesting and informative. I would like to know: What is a BLOB Field and can you give a example of one? I hope to hear from you soon Get your small business started at Lycos Small Business at http://www.lycos.com/business/mail.html
[SQL] Correct Syntax for alter table ..add constraint
Hi All, What is the correct syntax for adding a foreign key constraint from the command line. I am using v7.1 beta3. I am doing the follwoing: alter table users add constraint age_fk foreign key(age) references age_list(id); And I get the following error: flipr=# alter table users flipr-# add constraint age_fk foreign key(age) references age_list(id); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: referential integrity violation - key referenced from users not found in age_list I have define attribute id as primary key of the table... Thanks for your help. Regards,Najm.
Re: [SQL] Correct Syntax for alter table ..add constraint
Najm, > references age_list(id); > And I get the following error: > flipr=# alter table users > flipr-# add constraint age_fk foreign key(age) references > age_list(id); > NOTICE: ALTER TABLE ... ADD CONSTRAINT will create > implicit trigger(s) > for FOREIGN KEY check(s) > ERROR: referential integrity violation - key > referenced from > users not found in age_list Simple ... you have values in the AGE column that are not in the age_list table. Thus you're in violation of the foriegn key you're trying to establish. -Josh Berkus
Re: [SQL] Correct Syntax for alter table ..add constraint
Josh Berkus wrote: > Najm, > > > references age_list(id); > > And I get the following error: > > flipr=# alter table users > > flipr-# add constraint age_fk foreign key(age) references > > age_list(id); > > NOTICE: ALTER TABLE ... ADD CONSTRAINT will create > > implicit trigger(s) > > for FOREIGN KEY check(s) > > ERROR: referential integrity violation - key > > referenced from > > users not found in age_list > > Simple ... you have values in the AGE column that are not in > the age_list table. Thus you're in violation of the foriegn > key you're trying to establish. > > -Josh Berkus HI Josh, All values in age column of are null. I have no value there.. and I have specified to accetp nulls i.e. I dont have constraint not null on this particular column... Najm
Re: [SQL] Trouble with subqueries
Yury Don wrote: > If I understand correctly it must looks like this: > SELECT name, length, > (SELECT count(*) >FROM ratings >WHERE rating='5' >and rating.name=movies.name) as fives > FROM movies > WHERE name=rname; This results in several rows for each movie, which can be fixed by using select distint, but I don't quite understand why that happens. Any ideas? The method suggested by Tomas Berndtsson involving an intermediate view works nicely too. But this was not quite what I was looking for, as I would like to have all the movies in the list, also the ones with no ratings. The fives column should just be zero for those. I though about creating a view of the union of the movies table and these results and then doing select max(fives) group by name; from that view, but it seems that views with unions are not allowed. But I did find a solution: SELECT movies.name, movies.length, COUNT(CASE WHEN ratings.name=movies.name AND rating='5' THEN true END) AS fives FROM ratings, movies GROUP BY movies.name, movies.length; But I don't quite understand why I need to have movies.length in the GROUP BY -clause?
Re: [SQL] Trouble with subqueries
Stuff like this is possible in 7.1: SELECT m.name, m.length, count(r.rating) AS fives FROM movies m LEFT JOIN (SELECT * FROM ratings WHERE rating = 5) AS r ON m.name = r.name GROUP BY m.name, m.length; I think that would work. You'd want to try different queries with EXPLAIN to see what looks best. > But I did find a solution: > SELECT movies.name, movies.length, >COUNT(CASE WHEN ratings.name=movies.name >AND rating='5' THEN true END) AS fives > FROM ratings, movies GROUP BY movies.name, movies.length; > > But I don't quite understand why I need to have movies.length in the GROUP > BY -clause? When doing a GROUP BY, you can only select grouped columns. You cannot select other columns (except in group aggregates) since there can be more than one possible value for them if the group has more than one row. The database can't know which row in the group from which to get the length field. If length is grouped, there is only one possible value for it in the whole group, so I knows what value to get (the only one). Group aggregates are allowed on the ungrouped columns (and the grouped columns too) since it is not ambiguous - not single value to trying to be selected. When you do a GROUP BY, your table is partitioned into blocks of rows where the GROUPed BY columns are the same for all rows in the group. Only one row can result from each group of a grouped table. Aggregate functions used in returning a group row from a grouped table are aggregates on the group returned by that row, not the whole (ungrouped) table. Hope that makes sense. -- Robert B. Easter [EMAIL PROTECTED] - -- CompTechNews Message Board http://www.comptechnews.com/ -- -- CompTechServ Tech Services http://www.comptechserv.com/ -- -- http://www.comptechnews.com/~reaster/
[SQL] unreferenced primary keys: garbage collection
I have a database in which five separate tables may (or may not) reference any given row in a table of postal addresses. I am using the primary / foreign key support in postgres 7 to represent these references. My problem is that, any time a reference is removed (either by deleting or updating a row in one of the five referencing tables), no garbage collection is being performed on the address table. That is, when the last reference to an address record goes away, the record is not removed from the address table. Over time, my database will fill up with abandoned address records. I suppose I could write procedural code in my client application, to check for abandonment when a reference is removed, but that would require examining each of the five referencing tables. I consider this a messy option, and I expect it would be rather inefficient. I thought of attempting to delete the address record any time a reference to it is removed, and relying on foreign key constraints to prevent the deletion if it is referenced elsewhere. However, I believe postgres will force the entire transaction block to be rolled back in such cases, thus nullifying all the other work done in the transaction. This is clearly undesirable. Isn't there some way to tell postgres *not* to roll back my transaction if a particular DELETE fails due to referential integrity? Are there any other options that might help me? Regards, Forest Wilkinson
