Re: [SQL] Help With complex join
[EMAIL PROTECTED] wrote: Hi all, got a question as how to approach a somewhat complicated join query. The deal is I have three tables called attorney, lawOffice, and law_office_employment. The attorney and lawOffice tables hold attorney and lawOffice information respectively (obviously). The law_office_employment table is meant to show historical periods of time for which the attorney's worked for the different lawOffices. But it doesn't. Looking below, it shows the date they started in each law office, not the period they worked there. In fact, you can't capture a period of unemployment/sabbatical using just this table. > Here is the create table statement for law_office_employment: /*==*/ /* Table: LAW_OFFICE_EMPLOYMENT */ /*==*/ create table LAW_OFFICE_EMPLOYMENT ( ATTORNEYID IDENTIFIER not null, LAWOFFICEID IDENTIFIER not null, STARTDATEDATE not null, constraint PK_LAW_OFFICE_EMPLOYMENT primary key (ATTORNEYID, LAWOFFICEID, STARTDATE) ); Make your life easier and have start and end-dates. Oh, you might want a "finished-here" flag too to indicate the end-date can be checked. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] difference between all RDBMSs
Dhanashree wrote: hello sir, i m an engineering student and i m looking out for differrences between oracle v/s sybase v/s sql v/s plsql v/s mysql with respect to the following points Well, "sql" is a query language, and "plsql" is a procedural language, so I'm guessing you mean MS-Sql Server and PostgreSQL. A full comparison of the various databases would be a long report, and is the sort of thing you pay thousands of pounds for. Also, this being a PostgreSQL group, you'd expect more knowledge of that platform and even some bias towards it. You also don't mention what platform/application types/budget you are looking at. 1. rdbms They all are, for some definition of "RDBMS" - see http://www.dbdebunk.com for why some people think the term is horribly abused. 2. data independence In what sense? 3. support of plsql This is an Oracle language. PostgreSQL has something similar plpgsql. 4. integrity Apart from some of "MySQL's gotchas" (google for the term), they should all be equivalent, assuming you mean what I think you mean. 5. security Depends what you mean. 6. use and application Not sure what you mean here. 7. support to thirdparty products Depends what products you are interested in. 8. functionability Not sure what you mean here. 9. operatability Not sure what you mean here. 10. reliability You'd hope all would be reliable on good hardware. Sorry if this isn't as much help as you'd like, but you'll need to give more detail if you want a more detailed answer. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] How to obtain the list of data table name only
[EMAIL PROTECTED] wrote: Hi, I am wondering if anyone can tell me how I can obtain only the list of data table in postgresql without function and other ancillary tables. I hope that I can add a tag that can filter only data table. I am using the following SQL Statement: "SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES" Something like this perhaps? SELECT * FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE'; Of course, you'll probably want to allow for multiple schemas. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] difference between all RDBMSs
On Friday 15 Jul 2005 2:18 pm, Richard Huxton wrote: > Sorry if this isn't as much help as you'd like, but you'll need to > give more detail if you want a more detailed answer. looks like he needs the details for a project/report -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Yearly Archival System Using PostgreSQL
On Thu, Jul 14, 2005 at 02:24:17AM -0700, Vivek wrote: > I am developing a database system using PostgreSQL in which I should be > able to delete redundant records once a year (Time specified by the > user). THe redundant records should be inserted into the archive table > which is part of the same database. > > Is this possible in pgsql? Can I program a function in SQL or a trigger > to fire when the computer switches on say Jan 1 of every year? This is asked and answered from time to time; search the archives for past discussion. The usual answer is to use your operating system's mechanism for scheduling period jobs, e.g., cron on Unix. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] How to obtain the list of data table name only
On Thu, 2005-07-14 at 15:24 -0700, [EMAIL PROTECTED] wrote: > Hi, > > I am wondering if anyone can tell me how I can obtain only the list of > data table in postgresql without function and other ancillary tables. I > hope that I can add a tag that can filter only data table. > > I am using the following SQL Statement: > > "SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES" > > I would appreciate if anyone can enligten me on how this can be > achieve. > > Sunny I am not sure what you mean but one way to find out what tables there are is to use the 'pg_tables' view. select tablename from pg_tables; Regards Neil. ---(end of broadcast)--- TIP 1: 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
Re: [SQL] problem (bug?) with "in (subquery)"
On Thu, Jul 14, 2005 at 01:34:21AM -0600, Luca Pireddu wrote: > I have the following query that isn't behaving like I would expect: > > select * from strains s where s.id in (select strain_id from pathway_strains); Any reason the subquery isn't doing "SELECT DISTINCT strain_id"? > I would expect each strain record to appear only once. Instead I get output > like this, where the same strain id appears many times: > > id | name | organism > ---+--+-- > 83 | common | 82 > 83 | common | 82 > 83 | common | 82 What happens when you try each of the following? Do they give the expected results? I did some tests and I'm wondering if the planner's hash join is responsible for the duplicate rows. SELECT * FROM strains WHERE id IN ( SELECT strain_id FROM pathway_strains ORDER BY strain_id ); CREATE TEMPORARY TABLE foo AS SELECT strain_id FROM pathway_strains; SELECT * FROM strains WHERE id IN (SELECT strain_id FROM foo); SET enable_hashjoin TO off; SELECT * FROM strains WHERE id IN (SELECT strain_id FROM pathway_strains); -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: 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
Re: [SQL] problem (bug?) with "in (subquery)"
Luca Pireddu <[EMAIL PROTECTED]> writes: > So, am I wrong in expecting each strain record to appear only once in the > result set? Or is there something wrong with PostgreSQL? Could we see a self-contained example (table definitions and sample data as a SQL script)? I don't really have time to reverse-engineer a test case from your description ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] problem (bug?) with "in (subquery)"
On Fri, Jul 15, 2005 at 09:59:27AM -0400, Tom Lane wrote: > Luca Pireddu <[EMAIL PROTECTED]> writes: > > So, am I wrong in expecting each strain record to appear only once in the > > result set? Or is there something wrong with PostgreSQL? > > Could we see a self-contained example (table definitions and sample data > as a SQL script)? I don't really have time to reverse-engineer a test > case from your description ... I've been reverse-engineering and simplifying this. Here's something that I think is close: CREATE TABLE foo (id integer); CREATE TABLE bar (id1 integer, id2 integer); INSERT INTO foo VALUES (1); INSERT INTO bar VALUES (1, 1); INSERT INTO bar VALUES (2, 2); INSERT INTO bar VALUES (3, 1); SELECT * FROM foo WHERE id IN (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s); id 1 1 (2 rows) SELECT * FROM foo WHERE id IN (SELECT id2 FROM (SELECT id1, id2 FROM bar) AS s); id 1 (1 row) 8.0.3 and HEAD behave as shown. 7.4.8, 7.3.10, and 7.2.8 return a single row for both queries. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Postgres for Fedora Core 2 OS ****************
From where can I download? “Postgres 8.x” + required packages and “installation instruction” of Postgres for Fedora Core 2 OS. Thanks Dinesh Pandey
Re: [SQL] problem (bug?) with "in (subquery)"
Michael Fuhr <[EMAIL PROTECTED]> writes: > I've been reverse-engineering and simplifying this. Here's something > that I think is close: > CREATE TABLE foo (id integer); > CREATE TABLE bar (id1 integer, id2 integer); > INSERT INTO foo VALUES (1); > INSERT INTO bar VALUES (1, 1); > INSERT INTO bar VALUES (2, 2); > INSERT INTO bar VALUES (3, 1); > SELECT * > FROM foo > WHERE id IN (SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s); > id > > 1 > 1 > (2 rows) Ah-hah: this one is the fault of create_unique_path, which quoth /* * If the input is a subquery whose output must be unique already, we * don't need to do anything. */ Of course, that needs to read "... unique already, *and we are using all of its output columns in our DISTINCT list*, we don't need to do anything." regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] problem (bug?) with "in (subquery)"
On July 15, 2005 07:34, Michael Fuhr wrote: > On Thu, Jul 14, 2005 at 01:34:21AM -0600, Luca Pireddu wrote: > > I have the following query that isn't behaving like I would expect: Thanks for creating the reduced test case Michael. My apologies for not doing it myself. > > > > select * from strains s where s.id in (select strain_id from pathway_strains); > > Any reason the subquery isn't doing "SELECT DISTINCT strain_id"? because I don't need to according to the specification of "in". However, it does generate the correct output. So does select distinct * from strains s where s.id in (select strain_id from pathway_strains); > > > I would expect each strain record to appear only once. Instead I get output > > like this, where the same strain id appears many times: > > > > id | name | organism > > ---+--+-- > > 83 | common | 82 > > 83 | common | 82 > > 83 | common | 82 > > What happens when you try each of the following? Do they give the > expected results? I did some tests and I'm wondering if the planner's > hash join is responsible for the duplicate rows. > > SELECT * FROM strains WHERE id IN ( > SELECT strain_id FROM pathway_strains ORDER BY strain_id > ); With the "order by" it works as it should, not generating duplicate rows. > > CREATE TEMPORARY TABLE foo AS SELECT strain_id FROM pathway_strains; > SELECT * FROM strains WHERE id IN (SELECT strain_id FROM foo); This one's interesting. It only returns the unique rows. > > SET enable_hashjoin TO off; > SELECT * FROM strains WHERE id IN (SELECT strain_id FROM pathway_strains); With hashjoin off the query returns the correct output. On July 15, 2005 08:58, Tom Lane wrote: > Ah-hah: this one is the fault of create_unique_path, which quoth > > /* > * If the input is a subquery whose output must be unique already, we > * don't need to do anything. > */ > > Of course, that needs to read "... unique already, *and we are using all > of its output columns in our DISTINCT list*, we don't need to do > anything." > > regards, tom lane In any case, it looks like Tom has already found the problem :-) Thanks guys! Luca ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] problem (bug?) with "in (subquery)"
Luca Pireddu <[EMAIL PROTECTED]> writes:
> On July 15, 2005 08:58, Tom Lane wrote:
>> Ah-hah: this one is the fault of create_unique_path, which quoth
> In any case, it looks like Tom has already found the problem :-) Thanks guys!
On closer analysis, the test in create_unique_path is almost but not
quite completely wrong :-(. Here is the patch against 8.0 branch,
if you need it right away.
regards, tom lane
Index: src/backend/optimizer/util/pathnode.c
===
RCS file: /cvsroot/pgsql/src/backend/optimizer/util/pathnode.c,v
retrieving revision 1.111
diff -c -r1.111 pathnode.c
*** src/backend/optimizer/util/pathnode.c 31 Dec 2004 22:00:23 -
1.111
--- src/backend/optimizer/util/pathnode.c 15 Jul 2005 17:03:06 -
***
*** 34,40
#include "utils/syscache.h"
! static bool is_distinct_query(Query *query);
static bool hash_safe_tlist(List *tlist);
--- 34,41
#include "utils/syscache.h"
! static List *translate_sub_tlist(List *tlist, int relid);
! static bool query_is_distinct_for(Query *query, List *colnos);
static bool hash_safe_tlist(List *tlist);
***
*** 642,655
pathnode->subpath = subpath;
/*
!* If the input is a subquery whose output must be unique already, we
!* don't need to do anything.
*/
! if (rel->rtekind == RTE_SUBQUERY)
{
RangeTblEntry *rte = rt_fetch(rel->relid, root->rtable);
! if (is_distinct_query(rte->subquery))
{
pathnode->umethod = UNIQUE_PATH_NOOP;
pathnode->rows = rel->rows;
--- 643,683
pathnode->subpath = subpath;
/*
!* Try to identify the targetlist that will actually be unique-ified.
!* In current usage, this routine is only used for sub-selects of IN
!* clauses, so we should be able to find the tlist in in_info_list.
!*/
! sub_targetlist = NIL;
! foreach(l, root->in_info_list)
! {
! InClauseInfo *ininfo = (InClauseInfo *) lfirst(l);
!
! if (bms_equal(ininfo->righthand, rel->relids))
! {
! sub_targetlist = ininfo->sub_targetlist;
! break;
! }
! }
!
! /*
!* If the input is a subquery whose output must be unique already,
!* then we don't need to do anything. The test for uniqueness has
!* to consider exactly which columns we are extracting; for example
!* "SELECT DISTINCT x,y" doesn't guarantee that x alone is distinct.
!* So we cannot check for this optimization unless we found our own
!* targetlist above, and it consists only of simple Vars referencing
!* subquery outputs. (Possibly we could do something with expressions
!* in the subquery outputs, too, but for now keep it simple.)
*/
! if (sub_targetlist && rel->rtekind == RTE_SUBQUERY)
{
RangeTblEntry *rte = rt_fetch(rel->relid, root->rtable);
+ List *sub_tlist_colnos;
! sub_tlist_colnos = translate_sub_tlist(sub_targetlist,
rel->relid);
!
! if (sub_tlist_colnos &&
! query_is_distinct_for(rte->subquery, sub_tlist_colnos))
{
pathnode->umethod = UNIQUE_PATH_NOOP;
pathnode->rows = rel->rows;
***
*** 664,686
}
/*
-* Try to identify the targetlist that will actually be unique-ified.
-* In current usage, this routine is only used for sub-selects of IN
-* clauses, so we should be able to find the tlist in in_info_list.
-*/
- sub_targetlist = NIL;
- foreach(l, root->in_info_list)
- {
- InClauseInfo *ininfo = (InClauseInfo *) lfirst(l);
-
- if (bms_equal(ininfo->righthand, rel->relids))
- {
- sub_targetlist = ininfo->sub_targetlist;
- break;
- }
- }
-
- /*
* If we know the targetlist, try to estimate number of result rows;
* otherwise punt.
*/
--- 692,697
***
*** 755,804
}
/*
! * is_distinct_query - does query never return duplicate rows?
*/
! static bool
! is_distinct_query(Query *query)
{
! /* DISTINCT (but not DISTINCT ON) guarantees uniqueness */
! if (has_distinct_clause(query))
! return true;
! /* UNION, INTERSECT, EXCEPT guarantee uniqueness, except with ALL */
! if (query->setOperations)
{
! SetOperationStmt *topop = (SetOperationStmt *)
query->setOperations;
! Assert(IsA(topop, SetOperationStmt));
! Assert(topop->op != SETOP_NONE);
Re: [SQL] Error on dynamic code.
On Jul 14, 2005, at 3:46 AM, Mark J Camilleri wrote: The funny thing is that the documentation I read about SELECT INTO and RECORD types give the following example, amongst others: See the section below that on EXECUTE: The results from SELECT commands are discarded by EXECUTE, and SELECT INTO is not currently supported within EXECUTE. So there is no way to extract a result from a dynamically-created SELECT using the plain EXECUTE command. There are two other ways to do it, however: one is to use the FOR-IN-EXECUTE loop form described in Section 35.7.4, and the other is to use a cursor with OPEN-FOR- EXECUTE, as described in Section 35.8.2. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
