[SQL]
Hello, I have following problem with PostgreSQL 6.5.3, I haven't possiblity to check it on 7.0 and I want to know is it possible to run such query: SELECTk.pic, id_g, id_k, count(*) FROM kart k, pictues p WHERE k.pic = p.pic GROUP BY k.pic PICTURES(pic,id_g,id_k) KART (pic,email,mess,date) I've got answer from Postgres "Illegal attributes or non-group column" Is it error in query or in parser ? Yours Jan Bajerski
[SQL] Re: SQL
(Aplolgies to Mr. McCoy, to whom I mailed this awnser in error) Mr. Bajerski, > > I've got answer from Postgres > > "Illegal attributes or non-group column" > > > > Is it error in query or in parser ? It's your query. In a GROUP BY query, all named columns must either contain and aggregate function (e.g. SUM) or be named in the GROUP BY clause. O'Reilly has just come out with a SQL in a Nutshell book. I'd suggest picking one up. -Josh Berkus -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
[SQL] help on creating table
Hi All, I'm planning to have data in a tree structure when fetched. e.g. NODE1 + --- NODE1_1 + --- NODE1_2 |+ --- NODE1_2_1 + --- NODE1_3 Is this possible? How? I would really appreciate any help. Sherwin
Re: [SQL]
[EMAIL PROTECTED] writes: > Hello, > I have following problem with PostgreSQL 6.5.3, I haven't possiblity > to check it on 7.0 and I want to know is it possible to run such query: > > SELECTk.pic, id_g, id_k, count(*) > FROM kart k, pictues p > WHERE k.pic = p.pic > GROUP BY k.pic > > PICTURES(pic,id_g,id_k) > KART (pic,email,mess,date) > > I've got answer from Postgres > "Illegal attributes or non-group column" > > Is it error in query or in parser ? You need to have all non-aggragated columns in the GROUP BY: SELECTk.pic, id_g, id_k, count(*) FROM kart k, pictues p WHERE k.pic = p.pic GROUP BY k.pic, id_g, id_k Tomas
Re: [SQL]
On Mon, 23 Oct 2000 [EMAIL PROTECTED] wrote: > Hello, > I have following problem with PostgreSQL 6.5.3, I haven't possiblity > to check it on 7.0 and I want to know is it possible to run such query: > > SELECTk.pic, id_g, id_k, count(*) > FROM kart k, pictues p > WHERE k.pic = p.pic > GROUP BY k.pic > > PICTURES(pic,id_g,id_k) > KART (pic,email,mess,date) > > I've got answer from Postgres > "Illegal attributes or non-group column" > > Is it error in query or in parser ? AFAICS, the above construct isn't a legal GROUP BY query. All columns in the select list must either be grouped columns or in some sort set value function. Assuming that pictures.pic is unique, you can add p.id_g and p.id_k to the group by clause. Otherwise, you need to decide which id_g and id_k you want (min or max is often useful).
Re: [SQL]
On Mon, 23 Oct 2000 [EMAIL PROTECTED] wrote:
> I have following problem with PostgreSQL 6.5.3, I haven't possiblity
> to check it on 7.0 and I want to know is it possible to run such query:
>
> SELECTk.pic, id_g, id_k, count(*)
What table do these come from?
> FROM kart k, pictues p
This looks like a typo
> WHERE k.pic = p.pic
> GROUP BY k.pic
>
> PICTURES(pic,id_g,id_k)
> KART (pic,email,mess,date)
>
> I've got answer from Postgres
> "Illegal attributes or non-group column"
>
> Is it error in query or in parser ?
Probably the query.
Brett W. McCoy
http://www.chapelperilous.net
---
The goys have proven the following theorem...
-- Physicist John von Neumann, at the start of a classroom
lecture.
[SQL] Large Objects
Hi, Could someone please provide a demo of creating the type "Lo". Regards, Craig May Enth Dimension http://www.enthdimension.com.au
[SQL] Need info: pl/pgsql performance
Hi, I would to know if there is any articles or books that talk about pl/pgsql performance especially versus C. Thanking you in advance for your help. Regards. Najm
[SQL] Help: Using a regular expression match as a value
I am just getting started with pgsql and have read available docs I can find. I know I can match a row in a where clause using a regular expression. How can I use what was regexp matched (e.g. perl $1,$2, etc...) as a column assignment ? I'm looking for something like this? select ... ... $1 of firstname, /^R.*d/ as name_starts_with_r_and_ends_with_d ... TIA Richard DeVenezia
[SQL] Postgresql Site Search
Considering postgresql performs so well for me here, why is the postgres site search so fucking slow??
Re: [SQL] Postgresql Site Search
On Tue, 24 Oct 2000, Craig May wrote: > > Considering postgresql performs so well for me here, why is the > postgres site search so fucking slow?? The search engine used (udmsearch) isn't written to be the most efficient as far as making use of SQL features are concerned (ie. it was designed with MySQL limitations in mind) ... I've sent in several suggestions that provide speed improvements (use of subselects) that haven't been implemented as it would break MySQL support ... One of these days, I'll sit down and do the mods and submit it in such a way that for PostgreSQL, it uses subselects, for MySQL, it doesn't ... unless someone else feels like diving into some code? The PostgreSQL backend for the site is running on a dedicated PIII-400 with ~512Meg of RAM ... its not like the resources aren't there :(
[SQL] Like Query on BLOB's
Hi, Does anyone know if it's possible to do a like query on an oid (blob)? Regards, Craig May Enth Dimension http://www.enthdimension.com.au
Re: [SQL] Large Objects
FROM test.pl of DBD-Pg-0.93.tar ...
# test large objects
# create large object from binary file
my ($ascii, $pgin);
foreach $ascii (0..255) {
$pgin .= chr($ascii);
};
my $PGIN = '/tmp/pgin';
open(PGIN, ">$PGIN") or die "can not open $PGIN";
print PGIN $pgin;
close PGIN;
# begin transaction
$dbh->{AutoCommit} = 0;
my $lobjId;
( $lobjId = $dbh->func($PGIN, 'lo_import') )
and print "\$dbh->func(lo_import) .. ok\n"
or print "\$dbh->func(lo_import) .. not ok\n";
# end transaction
$dbh->{AutoCommit} = 1;
unlink $PGIN;
# blob_read
# begin transaction
$dbh->{AutoCommit} = 0;
$sth = $dbh->prepare( "" ) or die $DBI::errstr;
my $blob;
( $blob = $sth->blob_read($lobjId, 0, 0) )
and print "\$sth->blob_read ok\n"
or print "\$sth->blob_read not ok\n";
$sth->finish or die $DBI::errstr;
# end transaction
$dbh->{AutoCommit} = 1;
# read large object using lo-functions
# begin transaction
$dbh->{AutoCommit} = 0;
my $lobj_fd; # may be 0
( defined($lobj_fd = $dbh->func($lobjId, $dbh->{pg_INV_READ}, 'lo_open')) )
and print "\$dbh->func(lo_open) ok\n"
or print "\$dbh->func(lo_open) not ok\n";
( 0 == $dbh->func($lobj_fd, 0, 0, 'lo_lseek') )
and print "\$dbh->func(lo_lseek) ... ok\n"
or print "\$dbh->func(lo_lseek) ... not ok\n";
my $buf = '';
( 256 == $dbh->func($lobj_fd, $buf, 256, 'lo_read') )
and print "\$dbh->func(lo_read) ok\n"
or print "\$dbh->func(lo_read) not ok\n";
( 256 == $dbh->func($lobj_fd, 'lo_tell') )
and print "\$dbh->func(lo_tell) ok\n"
or print "\$dbh->func(lo_tell) not ok\n";
( $dbh->func($lobj_fd, 'lo_close') )
and print "\$dbh->func(lo_close) ... ok\n"
or print "\$dbh->func(lo_close) ... not ok\n";
( $dbh->func($lobjId, 'lo_unlink') )
and print "\$dbh->func(lo_unlink) .. ok\n"
or print "\$dbh->func(lo_unlink) .. not ok\n";
# end transaction
$dbh->{AutoCommit} = 1;
# compare large objects
( $pgin cmp $buf and $pgin cmp $blob )
and print "compare blobs .. not ok\n"
or print "compare blobs .. ok\n";
#
[EMAIL PROTECTED] writes:
>Hi,
>
>Could someone please provide a demo of creating the type "Lo".
>
>Regards,
>Craig May
>
>Enth Dimension
>http://www.enthdimension.com.au
[SQL] Re: [HACKERS] foreign key introduces unnecessary locking ?
Mikheev, Vadim wrote: > Try this for both FK tables: > > create table tmp2(idx2 int4, col2 int4, constraint > tmpcon2 foreign key(col2) references tmp1(idx) INITIALLY DEFERRED); > > This will defer constraint checks till transaction commit... > though constraint triggers should use SnapshotDirty instead of > SELECT FOR UPDATE anyway. > > Did you consider this, Jan? > > Vadim Whenever the checks are done, the transaction inserting a new reference to the key must ensure that this key cannot get deleted until it is done and it's newly inserted reference is visible to others.Otherwise a referential action, preventing referenced key deletion (or other action) wouldn't see those and it would be possible to violate the constraint. I don't see any other way doing it than obtaining a lock. Using SnapshotDirty would mean, that one transaction could DELETE a reference, then another transaction removes the primary key (because using Dirty the DELETE is already visible), but now the first transaction rolls back. Voila, constraint violated. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
[SQL] Re: what is the best way to set-up keywords in tables and Queries ?
Do you want to have a field name called "SELECT"? If so, you can use brackets in SQL 7 or higher. [SELECT] You can also use quoted identifiers (and the double quote) "SELECT" Keith "lesstif" <[EMAIL PROTECTED]> wrote in message [EMAIL PROTECTED]">news:[EMAIL PROTECTED]... > what is the best way to set-up keywords in tables and Queries? please post > examples! > I am worried about a field for each keyword > > less > >
