Re: [SQL] Refactored queries needing validation of syntactic equivalence
Mike Adams wrote: So. The first query should pull all 'MOM' records that have one or more corresponding, and possibly orphaned, unassigned receiving records belonging to the same po_cd and item_cd. The second query should pull all unassigned, and possibly orphaned receiving records that have one or more corresponding 'MOM' records once again matching on po_cd and item_cd. Using the results of both queries to double check each other, I can figure out which (if any) open records are, in fact, orphans and do an "after the fact" assignment to the "SPLIT IN MOM" invoice to reduce our accrual. Of course, our ERMS should take care of this automagically; but, tragically, it seems "real" inventory cost flow was attached to the system using duct tape, hot glue, and a couple of thumb tacks. So, given all the administriva above, have I actually refactored them correctly? Well, clearly you could have multiple possible matches, because apart from anything else you could in theory have multiple entries with the same item-code on the same purchase-order-code. In practice it will be rare, but it could happen. However, since the purpose is to provide you with a list so you can make manual changes there's no problem with that. What I might be tempted to do is restrict the dates more - you had <= '31 Oct 2007' I'd also apply >= '1 Aug 2007' (or whatever period is reasonable). You can always run an unconstrained match once a month to catch any that slip through the net, but presumably most will fall within a 90-day period. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] Inconsistent sql result
Hi listers, As a PG administrator, I'm trying to read technical data from pg_class table to monitor tables and indexes space. We are running a quite big postgres platform, with multiple databases, multiples schemes in each database, and hundreds tables. When I run the attach python script, I get 1809 rows as result. When I run the included (from the script) select query from phpPgAdmin or pgAdmin III, I get 2010 rows as result. When I try to focus on specific table including where relname ='tablename' in both parts of the join, I also get different numbers of rows. So I can't have the full size of all indexes attached on a table. Does anyone has a clue of why the same query, on same database gives different result depending on it is included in a python script, or ran from a console? Many Thanks Patrick #!/usr/bin/python2.4 # import sys import pgdb DEBUG = True global db # # Database access # def opendb(): global db # connect to database port='5432' username='xxx' dbname='xxx' host='xx.xx.xx.xx' password='xxx' try: db = pgdb.connect(database=dbname,host=host, user=username, password=password) except Exception, detail: db.rollback() if DEBUG: print 'Error occured while connecting to database : %s' % detail sys.exit(0) # # Close Database # def closedb(): global db # Commit all changes before closing db.commit() db.close() if __name__== '__main__': # # Main # opendb() query = "SELECT relname, relnamespace, relkind, relfilenode, relpages, reltoastrelid, relname AS idx_table_name FROM pg_class UNION SELECT pg_c_i.relname, pg_c_i.relnamespace, pg_c_i.relkind, pg_c_i.relfilenode, pg_c_i.relpages, pg_c_i.reltoastrelid, pg_c_i_o.relname AS idx_table_name FROM pg_class pg_c_i, pg_index pg_i, pg_class pg_c_i_o WHERE pg_c_i.relfilenode = pg_i.indexrelid AND pg_i.indrelid = pg_c_i_o.relfilenode " cur = db.cursor() cur.execute(query) tables_details = cur.fetchall() nb_tables = len(tables_details) for table in tables_details: print table print "Tables count=",nb_tables closedb()
Re: [SQL] Inconsistent sql result
Patrick De Zlio wrote: Hi listers, As a PG administrator, I'm trying to read technical data from pg_class table to monitor tables and indexes space. We are running a quite big postgres platform, with multiple databases, multiples schemes in each database, and hundreds tables. When I run the attach python script, I get 1809 rows as result. When I run the included (from the script) select query from phpPgAdmin or pgAdmin III, I get 2010 rows as result. When I try to focus on specific table including where relname ='tablename' in both parts of the join, I also get different numbers of rows. So I can't have the full size of all indexes attached on a table. Does anyone has a clue of why the same query, on same database gives different result depending on it is included in a python script, or ran from a console? Many Thanks Patrick #!/usr/bin/python2.4 # import sys import pgdb DEBUG = True global db # # Database access # def opendb(): global db # connect to database port='5432' username='xxx' dbname='xxx' host='xx.xx.xx.xx' password='xxx' try: db = pgdb.connect(database=dbname,host=host, user=username, password=password) except Exception, detail: db.rollback() if DEBUG: print 'Error occured while connecting to database : %s' % detail sys.exit(0) # # Close Database # def closedb(): global db # Commit all changes before closing db.commit() db.close() if __name__== '__main__': # # Main # opendb() query = "SELECT relname, relnamespace, relkind, relfilenode, relpages, reltoastrelid, relname AS idx_table_name FROM pg_class UNION SELECT pg_c_i.relname, pg_c_i.relnamespace, pg_c_i.relkind, pg_c_i.relfilenode, pg_c_i.relpages, pg_c_i.reltoastrelid, pg_c_i_o.relname AS idx_table_name FROM pg_class pg_c_i, pg_index pg_i, pg_class pg_c_i_o WHERE pg_c_i.relfilenode = pg_i.indexrelid AND pg_i.indrelid = pg_c_i_o.relfilenode " cur = db.cursor() cur.execute(query) tables_details = cur.fetchall() nb_tables = len(tables_details) for table in tables_details: print table print "Tables count=",nb_tables closedb() Hi Patrick: I tried your script and have the expected behaviour (both results are identical). I didnt use pg_admin nor pgaccess, i just use the psql. I have tried using pgdb and PyGresql, having the exact (good) behaviour. So, sory but have to ask: Are you reaally shure that you are executing the query on the same database? Python postgres are basicly not much but wrappers to C functionality, thats why the "error" you post looks very strange to me. I tried it on - python 2.4 - postgres 8.1.3 - pgdb and pyGreSql libraries Can you check that? If you are really executing the exactly same query on the exacly same database, you could build 2 temporary tables with the query results, and then look for the diff and try to figure out what the diffs are, and continue watching, i dont know, if you need to add a search_path in order to search on all the schemas or something. Cheers. Gerardo ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Having difficulty writing a "best-fit" query..
Hi list, I have a many-to-many relationship between movies and genres and, in the link table I have a third field called which orders the "appropriateness" of the relations within each movie. For example: movie_id, genre_id, relevance (i've used movie/genre titles for clarity here, but in reality they're id's) beverly hills cop, action, 2 beverly hills cop, comedy, 1 the heartbreak kid, comedy, 2 the heartbreak kid, romance, 1 The above would mean, to my application: "Beverly Hills Cop is both an Action and a Comedy movie but primarily an Action movie." "The Heartbreak Kid is both a Comedy and a Romance movie but primarily a Comedy movie." First of all, if there's a better way to model this kind of ranking/ordering of many-to-many relationships, please let me know. Now, to my problem.. I'm taking a subset of all my genres, and I want to get ONE row for each movie in the subset alongside its most appropriate genre (whichever has the highest relevance). In other words, the best fit. I've had a few goes at writing this query but I can't seem to get it right.. The theory in my mind is to: 1. filter the link table down to rows that fit the subset of categories (easy) 2. filter the link table further to keep only the max(relevance) for each movie_id .. this is where i'm having trouble. If someone can shed some light on this for me, I'd really appreciate it. Thanks for your time, Jamie
Re: [SQL] Having difficulty writing a "best-fit" query..
On 10/16/07, Jamie Tufnell <[EMAIL PROTECTED]> wrote: > I'm taking a subset of all my genres, and I want to get ONE row for each > movie in the subset alongside its most appropriate genre (whichever has the > highest relevance). In other words, the best fit. You didn't provide the expected output, but try: SELECT * FROM t tt WHERE relevance = (SELECT MAX(relevance) FROM t WHERE movie_id = tt.movie_id) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Having difficulty writing a "best-fit" query..
--- Jamie Tufnell <[EMAIL PROTECTED]> wrote: > movie_id, genre_id, relevance (i've used movie/genre titles for clarity > here, but in reality they're id's) > > beverly hills cop, action, 2 > beverly hills cop, comedy, 1 > the heartbreak kid, comedy, 2 > the heartbreak kid, romance, 1 > First of all, if there's a better way to model this kind of ranking/ordering > of many-to-many relationships, please let me know. Joe Celko had an example like this in his book: 17: EMPLOYMENT AGENCY PUZZLE http://www.elsevier.com/wps/find/bookdescription.cws_home/710075/description#description the only difference was that he was modeling employees and skillsets. IIRC, the terminology for the improved model was the "full disjuctive" model. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Having difficulty writing a "best-fit" query..
On Oct 16, 2007, at 12:14 , Richard Broersma Jr wrote: the only difference was that he was modeling employees and skillsets. IIRC, the terminology for the improved model was the "full disjuctive" model. Off chance, might the full disjunction work be relevant here? http://pgfoundry.org/projects/fulldisjunction/ Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] what's wrong with my date comparison?
Hi Everybody, I have a table with a column of timestamp type. It is known to postgres like this: name| character varying | not null value | character varying | not null datecreated | timestamp without time zone | not null when I do query select name, value, datecreated from mytable where datecreated > 2007-10-02; it reports: name |value | datecreated --+--+- al_qual | 0| 2007-08-09 00:06:06.742 srehquan | 3| 2007-08-09 00:06:06.742 complete | 1| 2007-08-09 00:06:06.743 al_quan | 0.315924933 | 2007-08-09 00:06:06.742 bsa_qual | 0| 2007-08-09 00:06:06.743 bsl_qual | 2| 2007-08-09 00:06:06.743 sh_qual | 0| 2007-08-09 00:06:06.742 . .. . . .. . I don't understand why it thinks August is greater than October. Can someone please elucidate what is going on? Regards, Tena Sakai [EMAIL PROTECTED]
Re: [SQL] what's wrong with my date comparison?
Oooops! I got it. I was missing quotes. It must have evaluated 2007-10-02 and used it as a numerical constant 1995. Sorry about commotion. Tena -Original Message- From: [EMAIL PROTECTED] on behalf of Tena Sakai Sent: Tue 10/16/2007 10:57 AM To: pgsql-sql@postgresql.org Subject: [SQL] what's wrong with my date comparison? Hi Everybody, I have a table with a column of timestamp type. It is known to postgres like this: name| character varying | not null value | character varying | not null datecreated | timestamp without time zone | not null when I do query select name, value, datecreated from mytable where datecreated > 2007-10-02; it reports: name |value | datecreated --+--+- al_qual | 0| 2007-08-09 00:06:06.742 srehquan | 3| 2007-08-09 00:06:06.742 complete | 1| 2007-08-09 00:06:06.743 al_quan | 0.315924933 | 2007-08-09 00:06:06.742 bsa_qual | 0| 2007-08-09 00:06:06.743 bsl_qual | 2| 2007-08-09 00:06:06.743 sh_qual | 0| 2007-08-09 00:06:06.742 . .. . . .. . I don't understand why it thinks August is greater than October. Can someone please elucidate what is going on? Regards, Tena Sakai [EMAIL PROTECTED]
Re: [SQL] what's wrong with my date comparison?
On Tue, Oct 16, 2007 at 10:57:03AM -0700, Tena Sakai wrote: > select name, value, datecreated >from mytable > where datecreated > 2007-10-02; ^^ 2007-10-02 is an arithmetic expression equivalent to 1995. I think what you want is WHERE datecreated > '2007-10-02'; Note the quotes. A -- Andrew Sullivan | [EMAIL PROTECTED] In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] what's wrong with my date comparison?
On Oct 16, 2007, at 12:57 , Tena Sakai wrote: select name, value, datecreated from mytable where datecreated > 2007-10-02; where datecreated > '2007-10-02' 2007-10-02 = 1995. # select current_date < 2007-10-31 as arithmetic_comparison, current_date < '2007-10-31' as date_comparison; arithmetic_comparison | date_comparison ---+- f | t (1 row) Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] what's wrong with my date comparison?
"Tena Sakai" <[EMAIL PROTECTED]> writes: > I was missing quotes. > It must have evaluated 2007-10-02 and used it as a > numerical constant 1995. Actually, what you got was a *textual* comparison between '1995' and the timestamp converted to text, which makes even less sense. FWIW, as of PG 8.3 you'll get an error: regression=# select * from timestamp_tbl where d1 > 2007-10-02; ERROR: operator does not exist: timestamp without time zone > integer LINE 1: select * from timestamp_tbl where d1 > 2007-10-02; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. We've seen too many people get burnt by variants of this problem... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
