Re: [SQL] Subquery error. Help please!!
What version of postgresql are you using?
On 27 Jun 2001 17:09:14 -0400, kakerjak wrote:
> Hey all..
>
> Here's the table definition.
> CREATE TABLE "laboratory" (
> "id" "int4" NOT NULL,
> "subid" "int2" NOT NULL,
> "name" varchar(30) NOT NULL,
> CONSTRAINT "laboratory_pkey" PRIMARY KEY ("id", "subid"))
>
> The way this table works is that each lab has one ID. If any information is
> changed(there are actually more fields then what i showed, but they don't
> affect the problem) then the ID remains the same, but the SUBID gets
> incremented by 1. Thus, other tables linking to it just need to know the ID
> and then read the ID with the biggest SUBID to get the most recent record.
>
> Now, what I want to do is this. Create a list of all the laboratories using
> only the most recent record for each (the biggest SUBID for every unique
> ID).
>
> Here's my select statement.
>
> SELECT b.id, b.subid, b.name
>FROM (SELECT c.id, MAX(c.subid) AS subid FROM laboratory AS c GROUP BY
> id) AS a
>INNER JOIN
> laboratory AS b
>USING id, subid
>
> The subquery works on it's own and returns the desired ID, SUBID
> combinations.
> But when put into the other query I get parser errors.
> If the subquery is placed before the JOIN, like it is above, then the error
> i get says 'parse error at or near "select"'
> If i flip the subquery around with the laboratory table then i get 'parse
> error at or near "("'
>
> According to the documention online, it seems as if this statement should
> work.
> ( http://www.postgresql.org/idocs/index.php?queries.html#QUERIES-FROM )
>
> Any help would be appreciated. TIA
> kakerjak
>
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>
--
Wei Weng
Network Software Engineer
KenCast Inc.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[SQL]
Title: Hi, I need to know if in PostgerSQL DB has a utility like in Oracle Release 8.1.6 contains a new package called STATSPACK that improves on the UTLBSTAT/UTLESTAT process (or like the UTLBSTAT/UTLESTAT). This information is very important since we are testing PostgerSQL database and I do not know how to monitor/examine the database. Thanks, Ilan ___ ILAN FAIT Tel: 972-9-9519133 Ex.247 iWeb Technologies Fax: 972-9-9519134 91 Medinat Ha'Yehudim St. Herzliya 46120 IL mailto:[EMAIL PROTECTED] www.iweb.com
Re: [SQL] Link many attributes in one table to one attribute inanother??
A easy way to handle this: create table marble ( id int4 primary key, color1 int4, color2 int4, color3 int4 ); create table color ( id int4, desc text ); color1, color2, and color3 point to the id in color table. This way you can expand your color table indefinitely. On 27 Jun 2001 23:44:34 -0500, Christian Anton wrote: > Hello all, > > I am fairly new at db design, I have built a few simple in the past and have > never dealt with this type of problem before, any help would be appreciated. > > I have three attributes in one table that should reference one, and only > one, attribute in another table. Here's an analogy of the problem: > > I have a table with a list of marbles, each marble has three colors in it > (color1, color2, color3) and a serial number. In another table I have eight > colors to choose from but the list of colors grows regularly. > How do I associate a marble with three colors from the color table (a marble > may have three red sides or red-yellow-blue)? > > Thanks, > Christian > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Subquery error. Help please!!
"kakerjak" <[EMAIL PROTECTED]> writes:
> If the subquery is placed before the JOIN, like it is above, then the error
> i get says 'parse error at or near "select"'
> If i flip the subquery around with the laboratory table then i get 'parse
> error at or near "("'
Are you using 7.1?
regards, tom lane
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[SQL] examine the database
Title: examine the database Hi, I need to know if in PostgerSQL DB has a utility like in Oracle Release 8.1.6 contains a new package called STATSPACK that improves on the UTLBSTAT/UTLESTAT process (or like the UTLBSTAT/UTLESTAT). This information is very important since we are testing PostgerSQL database and I do not know how to monitor/examine the database. Thanks, Ilan _ ILAN FAIT Tel: 972-9-9519133 Ex.247 iWeb Technologies Fax: 972-9-9519134 91 Medinat Ha'Yehudim St. Herzliya 46120 IL mailto:[EMAIL PROTECTED] www.iweb.com
Re: [SQL] Problems using a rule with the WHERE clause
Luis Sousa <[EMAIL PROTECTED]> writes: >> that shouldn't happen, in any case. If this is the current release >> (7.1.2) > The version that I'm using is 7.1release-3.potato.1 I don't see any problem here when I run your script. I suspect you are seeing another consequence of the rewriter bug I fixed on 12-June. Please update to 7.1.2 and apply the patch I posted then. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Subquery error. Help please!!
I did something similar, but pu the subselect into a view, in the WHERE clause.
SELECT * FROM modules m WHERE (m.module_ident = (SELECT
max(modules.module_ident) AS max FROM modules WHERE (m.moduleid =
modules.moduleid) GROUP BY modules.moduleid));
The equivalent for you would be something like:
SELECT * FROM laboratory l where l.subid = (select max(laboratory.subid)
from laboratory WHERE (l.subid = laboratory.subid) GROUP BY
laboratory.id);
And make sure you have indices on both id and subid.
Ross
On Wed, Jun 27, 2001 at 05:09:14PM -0400, kakerjak wrote:
> Hey all..
>
> Here's the table definition.
> CREATE TABLE "laboratory" (
> "id" "int4" NOT NULL,
> "subid" "int2" NOT NULL,
> "name" varchar(30) NOT NULL,
> CONSTRAINT "laboratory_pkey" PRIMARY KEY ("id", "subid"))
>
> The way this table works is that each lab has one ID. If any information is
> changed(there are actually more fields then what i showed, but they don't
> affect the problem) then the ID remains the same, but the SUBID gets
> incremented by 1. Thus, other tables linking to it just need to know the ID
> and then read the ID with the biggest SUBID to get the most recent record.
>
> Now, what I want to do is this. Create a list of all the laboratories using
> only the most recent record for each (the biggest SUBID for every unique
> ID).
>
> Here's my select statement.
>
> SELECT b.id, b.subid, b.name
>FROM (SELECT c.id, MAX(c.subid) AS subid FROM laboratory AS c GROUP BY
> id) AS a
>INNER JOIN
> laboratory AS b
>USING id, subid
>
> The subquery works on it's own and returns the desired ID, SUBID
> combinations.
> But when put into the other query I get parser errors.
> If the subquery is placed before the JOIN, like it is above, then the error
> i get says 'parse error at or near "select"'
> If i flip the subquery around with the laboratory table then i get 'parse
> error at or near "("'
>
> According to the documention online, it seems as if this statement should
> work.
> ( http://www.postgresql.org/idocs/index.php?queries.html#QUERIES-FROM )
>
> Any help would be appreciated. TIA
> kakerjak
>
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] restoring a dump
Hello, I have been trying to restore my dump and it seems like it is not working. I put the dump in a tar file and when I untarred it, there are several .dat files, a toc.dat file, and a restore.sql file. I used psql to restore these files into a database. I first started restoring the restore.sql file, then the .dat files, and lastly, the toc.dat. When I did the toc.dat file, I received an error message. These are the pg_dump -C -D -F t > test.tar tar -xvf test.tar psql restore.sql psql test < 19.dat psql test < 20.dat .. and so on psql test < toc.dat This is the error message: ERROR: parser: parse error at or near "pgdmp" CHANGE ERROR: non-existent group "test" ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ ERROR: parser: parse error at or near """ I also tried using the pg_restore, but I don't know how that would work on these separate files. Is there another way I can restore the dump? Maybe an easier way than what I have been using? And do I need to restore what is in the toc.dat? Thank you for your help. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Still stuck on a left join
I've been stuck now on a left join for about two weeks. DB is MSaccess2K, language is ASP. I'm learning php and PgSQL now, and will be migrating everything soon. In the mean time. If anyone knows where I am going wrong, please let me know. Here is my current SQL query: SELECT T.ID, T.employee, T.task, T.description, T.hours_used, T.f_date, P.project_name, C.name FROM track AS T LEFT JOIN project AS P ON (T.project=P.project_id) LEFT JOIN clients AS C ON (T.client=C.ID) WHERE T.client LIKE '%MMColParam%' Here is the error I get:[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(T.project=P.project_id) LEFT JOIN clients AS C ON (T.client=C.ID)' My DB structure: I have 5 tables: clients,project,tasks,track,users rows are as follows: clients.ID,clients.name project.project_id,client_id,project_name tasks.TaskID,tasks.Task_Name track.ID,track.employee,track.client,track.project,track.task,track.descript ion,track.hours_used,trck.f_date users.c_username,users.c_password Anyone know where I am going wrong? Thanks, Ari ~ Ari Nepon MRB Communications 4520 Wilde Street, Ste. 2 Philadelphia, PA 19127 p: 215.508.4920 f: 215.508.4590 http://www.mrbcomm.com --- Sign up for our email list and receive free information about topics of interest to nonprofit communications, marketing, and community building professionals. Free resources, articles, tips. Go to http://www.mrbcomm.com and use the Mailing List form. --- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] restoring a dump
Peter Eisentraut wrote: > > Phuong Ma writes: > > > pg_dump -C -D -F t > test.tar > > tar -xvf test.tar > > psql restore.sql > > psql test < 19.dat > > psql test < 20.dat .. and so on > > psql test < toc.dat > > I believe you're supposed to use pg_restore on the tar file itself. > > -- > Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter I tried using pg_restore, but it did not restore the dump successfully. Maybe it is the syntax I'm using, but I remember reading that you cannot use the pg_restore to restore a dump that uses INSERT. Does anyone know of a way I could undump my db? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] restoring a dump
Do not understand your question. On 28 Jun 2001 16:32:47 -0700, Phuong Ma wrote: > I tried using pg_restore, but it did not restore the dump successfully. > Maybe it is the syntax I'm using, but I remember reading that you cannot > use the pg_restore to restore a dump that uses INSERT. Does anyone know What do you mean by "dump that uses INSERT"?a > of a way I could undump my db? > -- Wei Weng Network Software Engineer KenCast Inc. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
