[SQL] "Display of specified number of records."
Hi, I have got following simple SQL. Select TestID from test where testname = ' ' order by testdate. Suppose for argument sake there are 100 records and testID's are 1 to 100. Is it possible to modify this SQL so that it will display records from 10 to 50 and not any other records. Rajesh. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] shmget fails for min buffers of postmaster also on solaris 7 .....
Dear All, I have installed postgres 7.0.2 on solaris 7 on an intel based machine. But, when I am trying to run the postmaster( the database startup) with minimum buffers also it gives the following error IpcMemoryCreate: shmget failed (Invalid argument) key=5432001, size=1104896, per mission=600 This type of error is usually caused by an improper shared memory or System V IPC semaphore configuration. For more information, see the FAQ and platform-specific FAQ's in the source directory pgsql/doc or on our web site at http://www.postgresql.org. FATAL 1: ShmemCreate: cannot create region Then I have read the POSTGRES on SOLARIS and other FAQ's and then edited the /etc/system file and added the following entries to increase the shared memory size to 128MB. set shmsys:shminfo_shmmax=268435456 set shmsys:shminfo_shmmin=200 set shmsys:shminfo_shmmni=200 set shmsys:shminfo_shmseg=200 set semsys:seminfo_semmap=250 set semsys:seminfo_semmni=500 set semsys:seminfo_semmns=500 set semsys:seminfo_semmsl=500 set semsys:seminfo_semmnu=500 set semsys:seminfo_semume=100 Inspite of this I am unable to start the postmaster. Can anybody Please suggest me a solution for this problem. It would be of great help if someone can suggest me what all changes in the OS or postgres are to be made for starting postmaster with 512 buffers. Thanks for all suggestions. Thanks & Regards Rajesh. __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
[SQL] what utilities do i need for postgres on solaris 7 ??
Hello Everyone, I have an Intel system with solaris 7 installed on it. Now I have d/l'ed postgres 6.5.1 to install it on my system. 1.) What all the utilities are needed before I start compiling and installing the postgres s/w?. I mean to say utilities like gmake. But I could not get gmake from anywhere. 2.) I have read the INSTALL file but could not get how to compile and INSTALL the s/w on Solaris 7. Thanks for all the suggestions. Rajesh. __ Do You Yahoo!? Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/
[SQL] how can I mirror a postgres database
Hi all, I am having postgres6.5 installed on a remote linux server. Now i have installed the same scenario in a local machine. Now i want to update my local machine with any transaction or changes made in remote machine. something like a oracle parallel server. where if one machine breaks down due to some reason the other machine can take over as it has all the data with it. I want to move my complete database with critical data on to postgres. can anybody please suggest how do i do it as the server is a mission critical server. any suggestions are appreciated and thanks for all of the suggestions in advance. Thanks & Regards Rajesh. __ Do You Yahoo!? Yahoo! Photos - 35mm Quality Prints, Now Get 15 Free! http://photos.yahoo.com/
[SQL] pg_atoi: error in "template1": can't parse "template1"
Dear all, I have installed postgres 6.5.1 on my RedHat Linux box. The installation went smooth without any problems. Now when i run initdb I got errors. I have seen the Postgres FAQ also and have ensured that all the directories i,e /usr/local/pgsql, /home/postgres etc are owned by postgres super user. I also have set the following environmental variables: - PATH=/usr/bin:/usr/local/bin:/usr/local/pgsql/bin HOME=/home/postgres INPUTRC=/etc/inputrc LC_COLLATE=C SHELL=/bin/bash PGLIB=/usr/local/pgsql/lib USER=postgres PGDATA=/usr/local/pgsql/data MANPATH=:/usr/local/pgsql/man LC_CTYPE=C LANG=en_US OSTYPE=Linux SHLVL=1 LC_COLLATE=C But even then I got errors when I run initdb like Adding template1 database to pg_database... ERROR: pg_atoi: error in "template1": can't parse "template1" ERROR: pg_atoi: error in "template1": can't parse "template1" syntax error 1 : parse errorinitdb: could not log template database initdb: cleaning up. PLEASE help me in this regard. Thanks & Regards Rajesh. __ Do You Yahoo!? Yahoo! Messenger - Talk while you surf! It's FREE. http://im.yahoo.com/
[SQL] Significance of Database Encoding
Hi , I would want to know what is the difference between databases that are created using UNICODE encoding and SQL_ASCII encoding. I have an existing database that has SQL_ASCII encoding but still i am able to store multibyte characters that are not in ASCII character set. for example: tradein_clients=# \l List of databases +-+--+---+ | Name | Owner | Encoding | +-+--+---+ | template0 | postgres | SQL_ASCII | | template1 | postgres | SQL_ASCII | | tradein_clients | tradein | SQL_ASCII | +-+--+---+ tradein_clients=# SELECT * from t_A; +--+ |a | +--+ | 私はガラス | +--+ Above is some japanese character. I have seen some posting regarding migrating databases from SQL_ASCII to UNICODE, given the above observation what significance does a migration have. Regards Rajesh Kumar Mallah. __ Yahoo! Mail Mobile Take Yahoo! Mail with you! Check email on your mobile phone. http://mobile.yahoo.com/learn/mail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Significance of Database Encoding [ update ]
I am not sure why the characters did not display properly in the mailling list archives. http://archives.postgresql.org/pgsql-sql/2005-05/msg00102.php but when i do the select in my screen (xterm -u8) i do see the japanese glyphs properly. Regds Mallah. --- Rajesh Mallah <[EMAIL PROTECTED]> wrote: > Hi , > > I would want to know what is the difference between databases > that are created using UNICODE encoding and SQL_ASCII encoding. > > I have an existing database that has SQL_ASCII encoding but > still i am able to store multibyte characters that are not > in ASCII character set. for example: > > tradein_clients=# \l > > List of databases > +-+--+---+ > | Name | Owner | Encoding | > +-+--+---+ > | template0 | postgres | SQL_ASCII | > | template1 | postgres | SQL_ASCII | > | tradein_clients | tradein | SQL_ASCII | > +-+--+---+ > > tradein_clients=# SELECT * from t_A; > +--+ > |a > | > +--+ > | 私はガラス > > > | > +--+ > > Above is some japanese character. > > I have seen some posting regarding migrating databases from > SQL_ASCII to UNICODE, given the above observation what > significance does a migration have. > > Regards > > Rajesh Kumar Mallah. > > > > > > > > __ > Yahoo! Mail Mobile > Take Yahoo! Mail with you! Check email on your mobile phone. > http://mobile.yahoo.com/learn/mail > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > Discover Yahoo! Find restaurants, movies, travel and more fun for the weekend. Check it out! http://discover.yahoo.com/weekend.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Significance of Database Encoding
--- PFC <[EMAIL PROTECTED]> wrote: > > > +--+ > > | 私はガラス > > +--+ > > You say it displays correctly in xterm (ie. you didn't see these in > your > xterm). > There are HTML/XML unicode character entities, probably generated by > your > mailer from your Unicode cut'n'paste. That is correct. Now the question is how to convert from SQL_ASCII to UNICODE. Mailing lists suggests to run recode or iconv on the dump file and restore. The problem is on running iconv with -f US-ASCII the program aborted: $ iconv -f US-ASCII -t UTF-8 < test.sql > out.sql iconv: illegal input sequence at position 114500 Any ideas how the job can be accomplised reliably. Also my database may contain data in multiple encodings like WINDOWS-1251 and WINDOWS-1256 in various places as data has been inserted by different peoples using different sources and client software. Regds Rajesh Kumar Mallah. > Using SQL ASCII to store UTF8 encoded data will work, but postgres > won't > know that it's manipulating multibyte characters, so for instance the > length of a string will be its Byte length instead of correctly counting > the characters, collation rules will be funky, etc. And substring() may > well cut in the middle of an UTF8 multibyte char which will then screw > your application side processing... > Apart from that, it'll work ;) > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Significance of Database Encoding
--- PFC <[EMAIL PROTECTED]> wrote: > > > $ iconv -f US-ASCII -t UTF-8 < test.sql > out.sql > > iconv: illegal input sequence at position 114500 > > > > Any ideas how the job can be accomplised reliably. > > > > Also my database may contain data in multiple encodings > > like WINDOWS-1251 and WINDOWS-1256 in various places > > as data has been inserted by different peoples using > > different sources and client software. > > You could use a simple program like that (in Python): > > output = open( "unidump", "w" ) > for line in open( "your dump" ): > for encoding in "utf-8", "iso-8859-15", "whatever": > try: > output.write( unicode( line, encoding ).encode( "utf-8" > )) > break > except UnicodeError: > pass > else: > print "No suitable encoding for line..." This may not work . Becuase ,conversion to utf-8 can be successfull (no runtime error) even for an incorrect guess of the original encoding but the result will be an incorrect utf8. Regds Rajesh Kumar Mallah > > I'd say this might work, if UTF-8 cannot absorb an apostrophe inside a > multibit character. Can it ? > > Or you could do that to all your table using SELECTs but it's going to > be > painful... > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > __ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] sequence chages after firing update
dear subha, Use explicit ORDER BY if u want to order the records by some column. otherwise the order of output from a select stmt is undefined. bu generally it is found the the last updated record comes last. On Wednesday 26 June 2002 17:17, Subhashini Karthikeyan wrote: > hi all > > > In postgresql 7.1.3 > > i am updateing a row. it is a 4th record. > after updation if i am firing a select query it is > coming as a last record ..what shall i do to avoid > that.. > any help appriciated > > thankz in advance > > regards > subha > > > __ > Do You Yahoo!? > Yahoo! - Official partner of 2002 FIFA World Cup > http://fifaworldcup.yahoo.com > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Efficient Query For Mapping IP Addresses To Country Code.
Hi folks, the problem is to update one table by querying another. i have a table where i store apache access logs where one of the fields is the host ip address. i need to find corresponding country for all the ip addrresses. for this i have another table that contains apnic,arin and ripe databases in the form of: Table "ip_country_map" Column | Type | Modifiers --+--+--- start_ip | inet | end_ip | inet | country | character(2) | Indexes: end_ip_idx, start_ip_idx I need to update the accees log's country field by searching the ip in ip_country_map for country. i have follwing three alternatives , all seems to be slow. 1 st. (based on implicit join) - explain UPDATE access_log_2002_06_25 set country=ip_country_map.country where host_ip between ip_country_map.start_ip and ip_country_map.end_ip; Nested Loop (cost=0.00..1711037.55 rows=5428333 width=563) -> Seq Scan on ip_country_map (cost=0.00..1112.55 rows=48855 width=70) -> Seq Scan on access_log_2002_06_25 (cost=0.00..20.00 rows=1000 width=493) --- 2nd (based on subselect) --- explain UPDATE access_log_2002_06_25 set country=(select country from ip_country_map where access_log_2002_06_25.host_ip between start_ip and end_ip); NOTICE: QUERY PLAN: Seq Scan on access_log_2002_06_25 (cost=0.00..20.00 rows=1000 width=493) SubPlan -> Seq Scan on ip_country_map (cost=0.00..1356.83 rows=5428 width=6) EXPLAIN 3 rd (do not update country field at all just join both the table) explain SELECT host_ip,ip_country_map.country from access_log_2002_06_25 join ip_country_map on ( host_ip between start_ip and end_ip) ; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..1711037.55 rows=5428333 width=102) -> Seq Scan on ip_country_map (cost=0.00..1112.55 rows=48855 width=70) -> Seq Scan on access_log_2002_06_25 (cost=0.00..20.00 rows=1000 width=32) EXPLAIN Yet Another option -- while loading access_log from file into database i do a select on ip_country_map. also even a simple query like do not use indexes. access_log=# explain SELECT country from ip_country_map where start_ip <= '203.196.129.1' and end_ip >= '203.196.129.1'; NOTICE: QUERY PLAN: Seq Scan on ip_country_map (cost=0.00..1356.83 rows=5428 width=6) EXPLAIN access_log=# explain SELECT country from ip_country_map where '203.196.129.1' between start_ip and end_ip; NOTICE: QUERY PLAN: Seq Scan on ip_country_map (cost=0.00..1356.83 rows=5428 width=6) EXPLAIN access_log=# IS THERE ANYTHING woring with my database schema? how shud i be storing the the data of ipranges and country for efficient utilization in this problem. regds Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] how to write procedures
Hi , Stored procedures are supported in pgsql for quite a long time consult postgresql docs on website http://www.postgresql.org/idocs/index.php?xplang.html or your local installations. regds malz. On Thursday 04 July 2002 16:15, srikanth wrote: > Hi, I am using postgre sql server on linux server but for my database I am > using storedprocedures which i need to create , but there are no commands > to create procedures it says it does not support is there any way to work > with stored procedures in postgre sql server. > thanks, > srikanth. > > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Can this be done with sql?
Hi , you can use GROUP BY , at the expense of adding one more column of SERIAL data type, say, select * from t_a limit 10; access_log=# SELECT * from t_a limit 15; sno | value -+--- 1 | 4533 2 | 2740 3 | 9970 4 | 6445 5 | 2220 6 | 2301 7 | 6847 8 | 5739 9 | 5286 10 | 5556 11 | 9309 12 | 9552 13 | 8589 14 | 5935 15 | 2382 (15 rows) if you want avg for every third item you can use: access_log=# SELECT avg(value) from t_a group by (1+(sno-1)/3) limit 5; avg - 5747.67 3655.33 5957.33 8139.00 5635.33 (5 rows) you can replace 3 in the SQL with any number for grouping that many records. if you need MEAN , STDDEV , MAX, MIN etc you can use approprite AGGREGATE that PGSQL supports for numbers eg for MAX access_log=# SELECT MAX(value) from t_a group by (1+(sno-1)/3) limit 5; max -- 9970 6445 6847 9552 8589 (5 rows) Regds MAlz. On Thursday 04 July 2002 00:02, teknokrat wrote: > In my database i have values recorded in one minute intervals. I would > like a query that can get me results for other time intervals. For > example - return maximum value in each 3 minute interval. Any ideas > how i can do this with sql? I tried writing a procedure in plsql but i > am told it does not support tuples as output. I can get the all the > one minute intervals and process them to get me three minute intervals > in my application but i would rather not do the expensive call for the > one minute intervals in the first place due to the large number of > data. any ideas? > > thanks > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Is Dropping a column "CHECK" constraint possible?
Hi Folks ,
can anyone please help?
i have a to drop a check contstraint from a column. eg
tradein_clients=# \d t_a
Table "t_a"
Column | Type | Modifiers
+-+---
company_id | integer |
exp| text|
imp| text|
Check constraints: "$1" (length(imp) > 1)
"aq" (length(imp) > 1)
Can i remove to contraints thru ALTER TABLE or similar commands.
I have read the docs for ALTER TABLE but the command described is
not working for me.
===
ALTER TABLE [ ONLY ] table
DROP CONSTRAINT constraint { RESTRICT | CASCADE }
========
Regards
Mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Is Dropping a column "CHECK" constraint possible?
Yup it did!. Thanks a ton, it was there in the ALTER TABLE documentation i overlooked :-( regds mallah. On Saturday 06 July 2002 10:05, Christopher Kings-Lynne wrote: > > can anyone please help? > > i have a to drop a check contstraint from a column. eg > > > > tradein_clients=# \d t_a > >Table "t_a" > >Column | Type | Modifiers > > +-+--- > > company_id | integer | > > exp| text| > > imp| text| > > Check constraints: "$1" (length(imp) > 1) > >"aq" (length(imp) > 1) > > > > Can i remove to contraints thru ALTER TABLE or similar commands. > > I have read the docs for ALTER TABLE but the command described is > > not working for me. > > This should work, so long as you're using postgres 7.2+ > > ALTER TABLE "t_a" DROP CONSTRAINT "aq" RESTRICT; > > Chris -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Can this be done with sql?
Hi, You cannot easily return datasets from stored procedures. there has been lots of discussion on it. regds mallah. > yes, thank you, that may help but unfortunately there are are few more > problems to face. > > 1. I will need to select groups from anywhere in the table so i cannot > assume that 1 will be the start number. They will be contigous however so i > can use another query top get the start number but is it possible to do it > with just one select? > > 2. I need to display not just aggregates but the first and last value in > the group for two of the fields. I mean by this that i need > opening_value(field1) and closing_value(field2). > > 3. If this needs to be done via stored procedure how do i get it to return > a result set. I've tried setof record but it doesn't work. > > thanks > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Is Dropping a column "CHECK" constraint possible?
Hi , i want to enforce that a coulumn 'imp' shud either contain valid data or no data at all. by valid data i mean data having at least one non whitespace character. will this constraint be approprote for accomplishing it? tradein_client=> ALTER TABLE t_a ADD CHECK ( length( btrim(imp) ) > 1 or imp is NULL ); regards mallah. On Saturday 06 July 2002 10:05, Christopher Kings-Lynne wrote: > > can anyone please help? > > i have a to drop a check contstraint from a column. eg > > > > tradein_clients=# \d t_a > >Table "t_a" > >Column | Type | Modifiers > > +-+--- > > company_id | integer | > > exp| text| > > imp| text| > > Check constraints: "$1" (length(imp) > 1) > >"aq" (length(imp) > 1) > > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] INSERT only under certain conditions (SELECT)
create an uniq index on the columns, # create unique index index_name on table (col1,col2,col3,...) hope it helps. regds mallah On Monday 08 July 2002 06:48, Joachim Trinkwitz wrote: > Hi, > > I want to insert a row only under condition that there isn't already > another row with similar values -- something like a INSERT INTO > ... WHERE NOT EXISTS (SELECT ...)? > > Hoping for help, > joachim > > > > ---(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 -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Cascading deletions does not seem to work inside PL/PGSQL functions.
Hi folks, This problem has been troubling me for quite sometime and I would be very thankful for your help. I have included the complete commented script to recreate the problem in question. The problem is inside a plpgsql function i do not see the records in the slave tables getting deleted when i delete the corresponing referenced record from the master table. But things as expected inside a Transaction at the PSQL prompt. any help is very much appreciated. regds mallah. stuff in test_case.sql --- -- W A R N I N G --- scripts will delete tables t_master and t_slave and a function t_test() --- DROP TABLE t_master; --clean up stuff first. DROP TABLE t_slave; CREATE TABLE t_master (id integer primary key); CREATE TABLE t_slave (id integer references t_master on delete cascade on update cascade unique ); INSERT INTO t_master values (1); INSERT INTO t_slave values (1); -- Demonstrate that record in salve table do get -- deleted when the master record is deleted. BEGIN work; delete from t_master where id=1; select id from t_slave where id=1; -- <-- this selects returns no record. ROLLBACK; -- Same stuff tried inside a PL/PGSQL function... DROP FUNCTION t_test(); CREATE OR REPLACE FUNCTION t_test () RETURNS integer AS ' DECLARE rec RECORD; BEGIN DELETE FROM t_master where id=1; SELECT INTO rec id from t_slave where id=1; -- <-- this selects returns record. RAISE NOTICE ''id in slave table is %'' , rec.id ; RETURN 1; END; ' LANGUAGE 'plpgsql' ; select t_test(); --- the end --- -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. -- W A R N I N G --- scripts will delete tables t_master and t_slave and a function t_test() --- DROP TABLE t_master; --clean up stuff first. DROP TABLE t_slave; CREATE TABLE t_master (id integer primary key); CREATE TABLE t_slave (id integer references t_master on delete cascade on update cascade unique ); INSERT INTO t_master values (1); INSERT INTO t_slave values (1); -- Demonstrate that record in salve table do get -- deleted when the master record is deleted. BEGIN work; delete from t_master where id=1; select id from t_slave where id=1; -- <-- this selects returns no record. ROLLBACK; -- Same stuff tried inside a PL/PGSQL function... DROP FUNCTION t_test(); CREATE OR REPLACE FUNCTION t_test () RETURNS integer AS ' DECLARE rec RECORD; BEGIN DELETE FROM t_master where id=1; SELECT INTO rec id from t_slave where id=1; -- <-- this selects returns record. RAISE NOTICE ''id in slave table is %'' , rec.id ; RETURN 1; END; ' LANGUAGE 'plpgsql' ; select t_test(); ---(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] How do I concatenate row-wise instead of column-wise?
Hi Marcus, It is simple , you need to write a function and define an aggregate using that function. in case you have already searched for the solution and not found here it is from this mailing list only: === Date: Tue, 14 May 2002 18:13:09 +0200 From: Mathieu Arnold <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [SQL] aggregate... Message-ID: <[EMAIL PROTECTED]> X-Mailer: Mulberry/2.2.1 (Win32) X-wazaaa: True, true MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Precedence: bulk Sender: [EMAIL PROTECTED] Status: RO X-Status: O Hi I have this : CREATE FUNCTION "comma_cat" (text,text) RETURNS text AS ' SELECT CASE WHEN $2 IS NULL OR $2 = '''' THEN $1 WHEN $1 IS NULL OR $1 = '''' THEN $2 ELSE $1 || '', '' || $2 END ' LANGUAGE 'sql'; CREATE AGGREGATE list ( BASETYPE = text, SFUNC = comma_cat, STYPE = text, INITCOND = '' ); I can use it as : select user, list(email) from user join email using (id_user); user | list ---+- mat| [EMAIL PROTECTED], [EMAIL PROTECTED] isa| [EMAIL PROTECTED] === regds On Tuesday 16 July 2002 13:39, you wrote: > I have a table like this: > SELECT * FROM old_tab; > id|descr > --- > 1|aaa > 1|aaa > 1|bbb > 2|ccc > 2|bbb > 3|ddd > 3|ddd > 3|eee > 3|fff > 4|bbb > etc... > > And I want a new table where the descr is concatenated row-wise like this: > SELECT * FROM new_tab; > id|descr > -- > 1|aaa;bbb > 2|ccc;bbb > 3|ddd;eee;fff > 4|bbb > etc... > > This is the closest I get: > UPDATE new_tab SET descr = old_tab.descr||' ; '||old_tab.descr from > old_tab where old_tab.id=new_tab.id; > UPDATE 4 > SELECT * FROM new_tab ; > id | descr > +--- > 1 | aaa ; aaa > 2 | ccc ; ccc > 3 | ddd ; ddd > 4 | bbb ; bbb > etc... > > Thus, the concatenating operator never works on other rows than the > present. How can I get around that and still stick to the postgresql > syntax? > > Regards > Marcus > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Cascading deletions does not seem to work inside PL/PGSQL functions.
On Tuesday 16 July 2002 21:41, you wrote: > On Tue, 16 Jul 2002, Rajesh Kumar Mallah. wrote: > > Hi folks, > > > > This problem has been troubling me for quite sometime and > > I would be very thankful for your help. > > > > I have included the complete commented script to recreate the problem in > > question. > > > > The problem is inside a plpgsql function i do not see the records in the > > slave tables getting deleted when i delete the corresponing referenced > > record from the master table. > > > > But things as expected inside a Transaction at the PSQL prompt. > > It should get deleted, but it won't be deleted until the end of the > user's sql statement (ie, not until after the function has finished). Hi thanks for the reply, Is it a bug? or is it expected , what if i do not want to fire sperate delete SQLs for the slave tables ? regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] How to find out if an index is unique?
perhaps This is more elegant has Tom has indicated : select pg_get_indexdef(oid) from pg_class where relname = 'email_bank_email'; pg_get_indexdef --- CREATE UNIQUE INDEX email_bank_email ON email_bank USING btree (lower(email)) (1 row) substitue 'email_bank_email' with name of your index. regds mallah. On Wednesday 17 July 2002 15:36, Dirk Lutzebaeck wrote: > Hello, > > is there a way to ask the system tables if a given index was created > with the unique qualification? I don't want to insert data to try. > > Greetings, > > Dirk > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Cascading deletions does not seem to work inside PL/PGSQL functions.
> what if i do not want to fire sperate delete SQLs for the slave > > > tables ? Hi , what is mean is that I a have many tables(x,y,z...) which reference the master table (M). I want that the records from the tables (x,y,z ect) automatically get deleted in the function when i delete the recored in the master (M). so that i do not have to explicitly delete from each of the tables x,y,z, etc. actually i want to reinsert the records in the table x,y,z as well as M with some modification. sorry for my english. regds mallah. > I'm not sure what you mean by this. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Cascading deletions does not seem to work inside PL/PGSQL functions.
Thanks for your reply. Stephan. On Thursday 18 July 2002 12:01, you wrote: > On Thu, 18 Jul 2002, Rajesh Kumar Mallah. wrote: > > > what if i do not want to fire sperate delete SQLs for the slave > > > > > > > tables ? > > > > Hi , > > > > what is mean is that I a have many tables(x,y,z...) which reference the > > master table (M). > > > > I want that the records from the tables (x,y,z ect) automatically get > > deleted in the function when i delete the recored in the master (M). so > > that i do not have to explicitly delete from each of the tables x,y,z, > > etc. > > > > actually i want to reinsert the records in the table x,y,z as well as M > > with some modification. > > So you want to do something like: > delete from M > ... > Insert into M,x,y,... > > inside the function with the x,y, etc rows > going away between those two? > > Hmm, yeah, that's a case that I don't think you can > do currently using the on delete cascade and would > require separate deletes. It'd make sense that you should > be able to do that, however... It doesn't really work with > how they're implemented currently, so it'd certainly be > a while before it'd change in any case. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Is login_cnt is reserved attribute name?
Hi i did nothing but changed attribute name from 'login_cnt' to 'cnt_login' and my update stmt started working? I am using DBD::Pg + postgresql 7.2.1 i will try to produce a test case. regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] possible bug in \df+
Hi there , " SELECT prosrc from pg_proc where proname=''; " and \df+ are reporting two different versions for FUNCTION body. eg: \df+ category_path shows: DECLARE v_category_id ALIAS FOR $1; DECLARE tmp_record RECORD; DECLARE tmp_id VARCHAR; DECLARE tmp_code VARCHAR; BEGIN tmp_code := '' ; SELECT INTO tmp_record b.name , a.category_id , a.parent_category_id from category_tree_new a natural inner join categories_new b where category_id=v_category_id and a.link is not true ; IF NOT FOUND THEN RETURN 'Home'; END IF; IF tmp_record.category_id = 0 THEN RETURN tmp_record.name; END IF; tmp_id := category_path_text(tmp_record.parent_category_id) ; IF tmp_record.category_id <> 0 THEN tmp_code := tmp_id || ' > ' || tmp_record.name ; END IF; RETURN tmp_code; END; and select from pg_proc gives DECLARE v_category_id ALIAS FOR $1; DECLARE tmp_record RECORD; DECLARE tmp_id VARCHAR; DECLARE tmp_code VARCHAR; BEGIN tmp_code := '' ; SELECT INTO tmp_record b.name , a.category_id , a.parent_category_id from category_tree_new a natural inner join categories_new b where category_id=v_category_id and a.link is not true ; IF NOT FOUND THEN RETURN '0' || ':' || 'ROOT'; END IF; IF tmp_record.category_id = 0 THEN RETURN tmp_record.category_id || ':' || tmp_record.name ; END IF; tmp_id := category_path(tmp_record.parent_category_id) ; IF tmp_record.category_id <> 0 THEN tmp_code := tmp_id || '#' || tmp_record.category_id || ':' || tmp_record.name ; END IF; RETURN tmp_code; END; regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Ltree usage..
Hi Oleg, I am trying to use contrib/ltree for one of my applications. the query below works fine for me. Qry1: SELECT path from unified_data where path ~ '*.180.*' and path ~ '*.1.*'; is there any way of compacting it for example Qry2: SELECT path from unified_data where path ~ '*.180.*' or path ~ '*.1.*'; is better written as Qry3: SELECT path from unified_data where path ~ '*.180|1.*' ; also is qry3 better to Qry2 in terms of performance? regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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
Re: [SQL] Ltree usage..
Hi Oleg,
It does not yeild the correct result for me.
I am providing more details this time.
path is ltree [] for me not ltree,
Column | Type |Modifiers
++-
profile_id | integer| not null default
nextval('"unified_data_profile_id_seq"'::text)
co_name| character varying(255) |
city | character varying(100) |
path | ltree[]|
Indexes: unified_data_path
Unique keys: unified_data_co_name_key,
unified_data_profile_id_key
eg if my sample data set is.
profile_id | path
+--
25477 | {0.180.830,0.180.848}
26130 | {0.180.848}
2928 | {0.64.65,0.64.67,0.180.830,0.180.848,0.180.849}
26129 | {0.180.848}
26126 | {0.180.848}
26127 | {0.180.848}
26128 | {0.180.848}
24963 | {0.180.830,0.180.848}
26125 | {0.180.848}
7239 | {0.246.256,0.246.282,0.180.848,0.246.857,0.76.1255.1161}
(10 rows)
what query shud i use to extract profiles where path contains *.64.* and *.180.*
eg this query
SELECT profile_id,pathfrom unified_data where path ~ '*.180.*' and path ~
'*.64.*' limit 10;
profile_id | path
+-
2928 | {0.64.65,0.64.67,0.180.830,0.180.848,0.180.849}
3238 | {0.64.68,0.180.830,0.395.904}
6255 | {0.180.227,0.64.814}
6153 | {0.180.227,0.505.518,0.64.814}
6268 | {0.180.227,0.64.814}
6267 | {0.180.227,0.64.814}
6120 | {0.180.227,0.64.814}
6121 | {0.180.227,0.64.814}
6084 | {0.180.227,0.64.814}
6066 | {0.180.227,0.64.810}
(10 rows)
gives me the correct result but i am not sure if its the most efficient.
I will be using it for medium sized dataset approx 100,000 that there will be such
search on upto four such indexed columns.
regds
mallah.
On Friday 02 August 2002 22:30, Oleg Bartunov wrote:
> On Fri, 2 Aug 2002, Rajesh Kumar Mallah. wrote:
> > Hi Oleg,
> >
> > I am trying to use contrib/ltree for one of my applications.
> >
> > the query below works fine for me.
> >
> > Qry1: SELECT path from unified_data where path ~ '*.180.*' and path
> > ~ '*.1.*';
> >
> > is there any way of compacting it for example
> >
> > Qry2: SELECT path from unified_data where path ~ '*.180.*' or path
> > ~ '*.1.*'; is better written as
> > Qry3: SELECT path from unified_data where path ~ '*.180|1.*' ;
>
> Qry2 and Qry3 are equvalent and Qry3 is faster but not much.
> But Qry1 is not the same as Qry2 !!!
>
> Qry1 could be rewritten as:
>
> SELECT path from unified_data where path @ '180 & 1';
>
> > also is qry3 better to Qry2 in terms of performance?
> >
> > regds
> > mallah.
>
> Regards,
> Oleg
> _____
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] getting ILIKE or ~* to use indexes....
Hi folks, can anyone tell me or point me to the right thread. I want my query to use indexes for company name searches but its not happening unless is use '=' which does not server the purpose. eg tradein_clients=# explain SELECT co_name FROM unified_data where co_name ilike '%rajesh%' ; NOTICE: QUERY PLAN: Seq Scan on unified_data (cost=0.00..19293.00 rows=1 width=25) EXPLAIN tradein_clients=# explain SELECT co_name FROM unified_data where co_name = 'rajesh' ; NOTICE: QUERY PLAN: Index Scan using unified_data_co_name_key on unified_data (cost=0.00..6.26 rows=1 width=25) EXPLAIN tradein_clients=# Regards mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Difference between is true and = 't' in boolean feild. & bitmap indexes
Any can anyone explain me why in a query of a boolean feild "is ture" does not indexes where as = 't' does? is "is true" not a more standard SQL than " = 't' ". Also is there any working implementation of BITMAP INDEXES in postgresql as found in ORACLE? regds mallah. tradein_clients=> explain analyze select c.email,date(a.generated),c.expired from eyp_rfi a join users b on (a.receiver_uid = b.userid) join grace_mytradeindia c on (b.email = c.email) where generated_date = '2002-08-17' and c.expired is true; NOTICE: QUERY PLAN: Hash Join (cost=1948.01..2214.60 rows=14 width=67) (actual time=9.32..40.03 rows=12 loops=1) -> Seq Scan on grace_mytradeindia c (cost=0.00..246.17 rows=4051 width=26) (actual time=0.65..27.76 rows=5372 loops=1) -> Hash (cost=1947.46..1947.46 rows=218 width=41) (actual time=2.66..2.66 rows=0 loops=1) -> Nested Loop (cost=0.00..1947.46 rows=218 width=41) (actual time=0.16..2.57 rows=31 loops=1) -> Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..677.81 rows=218 width=12) (actual time=0.09..0.57 rows=50 loops=1) -> Index Scan using users_pkey on users b (cost=0.00..5.80 rows=1 width=29) (actual time=0.03..0.03 rows=1 loops=50) Total runtime: 40.26 msec EXPLAIN tradein_clients=> explain analyze select c.email,date(a.generated),c.expired from eyp_rfi a join users b on (a.receiver_uid = b.userid) join grace_mytradeindia c on (b.email = c.email) where generated_date = '2002-08-17' and c.expired = 't'; NOTICE: QUERY PLAN: Hash Join (cost=1948.01..2144.70 rows=14 width=67) (actual time=6.26..49.92 rows=12 loops=1) -> Index Scan using grace_mytradeindia_exp on grace_mytradeindia c (cost=0.00..176.27 rows=4051 width=26) (actual time=0.03..37.53 rows=5372 loops=1) -> Hash (cost=1947.46..1947.46 rows=218 width=41) (actual time=2.15..2.15 rows=0 loops=1) -> Nested Loop (cost=0.00..1947.46 rows=218 width=41) (actual time=0.15..2.06 rows=31 loops=1) -> Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..677.81 rows=218 width=12) (actual time=0.09..0.48 rows=50 loops=1) -> Index Scan using users_pkey on users b (cost=0.00..5.80 rows=1 width=29) (actual time=0.02..0.03 rows=1 loops=50) Total runtime: 50.16 msec EXPLAIN tradein_clients=> -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] BITMAP INDEXES...
Hi, I have learnt from certain texts that bitmap indexes are very useful for large DSS (decesion support systems). bitmap indexes are currently not available in postgresql. My question is in what way is a normal btree index in pgsql inferior to bitmap indexes (found in oracle for example). is it just in terms of space requirements for performance too? regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Index usage on date feild , differences in '>' and '>=' and between
Hi , I am trying to improve a query to use existing indexes but facing diffculty. Looks like 'between' amd '>=' are not using indexes althoug > and < does. all my application code uses between and i am sure it use to work fine at one point of time. regds mallah. SQL TRANSCRIPT: == tradein_clients=> explain select list_id from eyp_rfi a where generated > '2002-08-13' and generated < '2002-09-19' ; NOTICE: QUERY PLAN: Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..15796.97 rows=4150 width=4) EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated >= '2002-08-13' and generated < '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated >= '2002-08-13' and generated <= '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-08-13' and '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) EXPLAIN tradein_clients=> == -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Index usage on date feild , differences in '>' and '>=' and between
Thanks very much for the response. set enable_seqscan=off; Definitely helps. and for wide date ranges it usees indexes. But with default value of enable_sequence changing date range seems to have effect. can you explain me a bit more or point me to right documents for understanding the languae of "EXPLAIN". EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-09-11' and '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=12924 width=4) EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-09-12' and '2002-09-19' ; NOTICE: QUERY PLAN: Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..17369.05 rows=12220 width=4) EXPLAIN tradein_clients=> == the distribution of values are as follows: > select generated ,count(generated) from eyp_rfi a where generated between >'2002-09-10' and '2002-09-19' group by generated; generated | count +--- 2002-09-10 | 442 2002-09-11 | 1060 2002-09-12 | 641 2002-09-13 | 607 2002-09-14 | 1320 2002-09-15 | 521 2002-09-16 | 1474 2002-09-17 | 940 2002-09-18 | 1005 2002-09-19 | 178 (10 rows) Last Question , Shud i do "enable_seqscan=off" in Application Level? I use Perl-DBI Thanks and Regards Rajesh Mallah. Tsday 19 September 2002 12:07, Stephan Szabo wrote: > On Thu, 19 Sep 2002, Rajesh Kumar Mallah. wrote: > > I am trying to improve a query to use existing indexes but facing > > diffculty. > > > > > > Looks like 'between' amd '>=' are not using indexes althoug > and < does. > > all my application code uses between and i am sure it use to work fine > > at one point of time. > > > > > > regds > > mallah. > > > > SQL TRANSCRIPT: > > = > >= > > > > tradein_clients=> explain select list_id from eyp_rfi a where > > generated > '2002-08-13' and generated < '2002-09-19' ; NOTICE: > > QUERY PLAN: > > > > Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..15796.97 > > rows=4150 width=4) > > > > EXPLAIN > > tradein_clients=> explain select list_id from eyp_rfi a where > > generated >= '2002-08-13' and generated < '2002-09-19' ; NOTICE: > > QUERY PLAN: > > > > Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) > > Given how close the two results are (and the fact that they differ by 900 > rows), have you tried using set enable_seqscan=off and seeing what > explain gives you for the second query? My guess is that it'll have > an estimated cost greater than the 17923.81 it's estimating from the > sequence scan. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Appending to an array[] feild...[ ltree ]
Hi ,
We are undergoing a data consolidation process wherein we are making a common
repository of business profiles from various sources.
I require to store label paths like 1.1.1 , 1.1.2,1.1.3 etc in a feild
and i use ltree[] for fast searching.
The problem is in the ltree[] feild in need to store uniq paths and need
want to know if some utility functions exists.
For example if {1.1.1,1.1.2,1.1.3} is contained in a ltree[] record and i encounter
a path say 1.1.4 i need to update it to {1.1.1,1.1.2,1.1.3,1.1.4} in other words
i need to insert to the ltree[] feild. Does there exists any generic function
(or ltree[] specific function) to add an item in the array? (first question)
My another question is is there any way to matain uniqueness in a ltree[] feild
for example , suppose i now encounter {1.1.1} again i do not want to
update the record to {1.1.1,1.1.2,1.1.3,1.1.4,1.1.1} to want it to remain the same
ie,{1.1.1,1.1.2,1.1.3,1.1.4} becoz 1.1.1 is already present in the [] , does there
exists any function to probe an ltree[] feild for existance ?
Regards
Mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(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] check source of trigger
Hi, In case you doing all this to replicate tables conside contrib/dbmirror it does it fairly elegantly. regds mallah. On Friday 20 September 2002 13:55, wit wrote: > Hello, > > I have a question about trigger. I have tables with the following > structure: > > create table A ( >e_codeA char(5) default '' not null, >n_codeA varchar(20) default '' not null, >constraint A_pkey primary key ( e_codeA ) > ); > > create table B ( >e_codeB char(5) default '' not null, >e_codeA char(5) default '' not null > constraint e_codeA_ref references A( e_codeA ) > on delete cascade on update cascade, >n_codeB varchar(20) default '' not null, >constraint B_pkey primary key ( e_tranB, e_codeA ) > ); > > I have trigger and procedure on table B to capture any change and insert > into table logB: > create trigger trigger_b before insert or update or delete on B for > each row execute procedure log_change(); > > When I update e_codeA in table A, the constrain trigger will update e_codeA > in B. My trigger, trigger_b, also was trigged and procedure will record > change into table logB too. > How to write a code in my db procedure to check whether the procedure was > called by normal SQL or was called by cascade trigger. > > Regards, > wit > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Preventing DELETEs
Hi , I have a created a database and a table in it, I want to prevent "DELETES" on the table in this database by everyone except superuser postgres. even by me (the creator of this database and table) I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) but i always end up with having the permission can any one tell me how the prevention can be accomplished? thanks in advance. regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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
Re: [SQL] Preventing DELETEs
Hi Dima, I currently have only one user in the system its me and superuser postgres. every thing belongs to me currently. and the programs connect as me. if make transfer the database ownership to postgres will all the tables also get transfered to him? it that case all programs will stop working. can i transefer database ownership to postgres and allow myself ALL the PREVILEGES and selectively REVOKE the DELETE permission from myself on the concerned table? If that is possible could you kindly tell me the commands Current state is: tradein_clients=> \l List of databases Name | Owner -+-- template0 | postgres template1 | postgres tradein_clients | tradein (this is me) (3 rows) tradein_clients=> regds Mallah. On Friday 27 September 2002 00:30, dima wrote: > > I have a created a database and a table in it, > > > > I want to prevent "DELETES" on the table in this > > database by everyone except superuser postgres. > > even by me (the creator of this database and table) > > make superuser the database owner & grant the rights needed to the users -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Preventing DELETEs
Hi Robert, I will be obliged to receive the "real" code , if its feasible for you. I am not used RULEs before. regds mallah. On Friday 27 September 2002 00:39, Robert Treat wrote: > In psuedo-code : create rule on mytable on delete return null > > Robert Treat > > On Thu, 2002-09-26 at 15:00, Rajesh Kumar Mallah. wrote: > > Hi , > > > > I have a created a database and a table in it, > > > > I want to prevent "DELETES" on the table in this > > database by everyone except superuser postgres. > > even by me (the creator of this database and table) > > > > > > I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) > > > > but i always end up with having the permission > > > > > > can any one tell me how the prevention can be accomplished? > > > > thanks in advance. > > > > regds > > mallah. > > > > > > > > -- > > Rajesh Kumar Mallah, > > Project Manager (Development) > > Infocom Network Limited, New Delhi > > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > > > Visit http://www.trade-india.com , > > India's Leading B2B eMarketplace. > > > > > > > > ---(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 -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Preventing DELETEs
Hi Dmitry, Got it working i made a small change. On Friday 27 September 2002 00:47, you wrote: > I think this should work: > > alter table mytable owner to postgres; > grant all on my table to public; instead of > revoke delete on my table from public; i did : revoke delete on my table from tradein (which is me) ; tradein_clients=> BEGIN WORK; delete from users where userid=34866; BEGIN ERROR: users: Permission denied. tradein_clients=> ROLLBACK ; ROLLBACK tradein_clients=> UPDATE users set password='mallah' where userid=34866; UPDATE 1 tradein_clients=> does public not include me?? regds mallah. > > I hope, it helps... > > Dima > > Rajesh Kumar Mallah. wrote: > > Hi , > > > > I have a created a database and a table in it, > > > > I want to prevent "DELETES" on the table in this > > database by everyone except superuser postgres. > > even by me (the creator of this database and table) > > > > > > I have tried in many ways (ALTER TABLE REVOKE DELETE .. etc etc) > > > > but i always end up with having the permission > > > > > > can any one tell me how the prevention can be accomplished? > > > > thanks in advance. > > > > regds > > mallah. > > > > > > > > --=20 > > Rajesh Kumar Mallah, > > Project Manager (Development) > > Infocom Network Limited, New Delhi > > phone: +91(11)6152172 (221) (L) ,9811255597 (M) > > > > Visit http://www.trade-india.com , > > India's Leading B2B eMarketplace. > > > > > > > > ---(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 -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] enforcing with unique indexes..
Hi , can anyone tell me how can i enforce below in a table. I want that no more that one distinct userid exists for a given group_id in the table. ie i want 1 to 1 mapping between group_id and userid so that , there shud not be a single group_id having more that one kind of userid. SELECT group_id from eyp_listing group by group_id having count(distinct userid) > 1 ; always returns empty. can it be done with some sort of UNIQUE INDEX? Regds MAllah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] enforcing with unique indexes..
Sorry Bhuvan it wont work, COMPOSITE UNIQUE INDEX will prevent entry of rows like (group_id,user_id) 1 1 1 1 what i want to prevent is this: 1 1 1 2 did you notice the distinct inside the count? regds mallah. On Saturday 05 October 2002 12:36, Bhuvan A wrote: > > SELECT group_id from eyp_listing group by group_id having > > count(distinct userid) > 1 ; > > > > always returns empty. > > > > can it be done with some sort of UNIQUE INDEX? > > Yes it can be done using UNIQUE INDEX. Create a composite unique index on > these 2 fields and it will do the rest. For more details, try > > $ \h CREATE INDEX > > regards, > bhuvaneswaran -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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
Re: [SQL] Output of function into a file
in 7.3 you will be able to return multiple rows with multiple columns. 7.3 stable is going to be out soon as it in beta 5 stage currently. regds mallah. On Saturday 09 November 2002 11:00, karthick v wrote: > Hi, > > I am writing a small function to manipulate records in multiple > tables. Since the function cannot return more than one value, I would > like to get all the outputs of the queries and append them into a text > file. > > Is there any way to do it inside the function. I came across many > postings which tell me how to use it in pgsql but not inside the > function. > > I would be very glad if someone could help me > > Thanks in advance > > karthick v > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] why the difference?
Hi folk, i am finding something mysterious in SQL can anyone explain? consider the SQL: tradein_clients=# select distinct on (amount,co_name,city) category_id,amount,co_name,city from eyp_listing where keywordidx ## 'vegetable' and category_id=781 ; category_id | amount | co_name |city -++---+ 781 | 0 | ANURADHA EXPORTS | CHENNAI 781 | 0 | R.K.INTERNATIONAL | CHENNAI 781 | 0 | SAI IMPEX | MUMBAI 781 | 0 | TRIMA ENTERPRISES | CHENNAI 781 | 0 | UNIQUE DEHYDRATES LIMITED | AHMEDABAD 781 | 5000 | RSV EXPORT| COIMBATORE (6 rows) lets remove the contraint "category_id=781" and store the output in a table "t_a". tradein_clients=# CREATE TABLE t_a AS select distinct on (amount,co_name,city) category_id,amount,co_name,city from eyp_listing where keywordidx ## 'vegetable' ; then when i select from t_a with category_id=781 i have less secords tradein_clients=# SELECT * from t_a where category_id=781; category_id | amount | co_name |city -++---+ 781 | 0 | R.K.INTERNATIONAL | CHENNAI 781 | 0 | SAI IMPEX | MUMBAI 781 | 0 | UNIQUE DEHYDRATES LIMITED | AHMEDABAD 781 | 5000 | RSV EXPORT| COIMBATORE (4 rows) Can anyone please explain the difference? Regds Mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How to take advantage of PREPARED QUERIES of PGSQL 7.3 in mod_perl enviornment?
Hi
Like many others we also use postgresql as the backend DataBase server for our web
applications.
I am curious how can i take advantage of this new feature.
Typically each mod_perl (perl enabled apache) httpd process creates a persistant
connection with the DB server (this acheived by connection pooling thru Apache::DBI ),
My question is we already have a "prepare" facility in the DBI layer of perl which
which in effect does something similar to "PREPARE" in the DB server. Is prepare
at DB server level better that prepare at perl level ? or am i misunderstanding
something?
Secondly if i were to use DB level prepare in an existing mod_perl scenerio what
could be a typical usage/implementation pattern? any suggestions?
thirdly , Say in my startup.pl , thru a DBI->do(" STATEMENT " ) call i create all
my PREPARED queries , will it be scalable?
i have 20-30 kinds of SQL statements that my webapps
frequently execute, how many prepared queries can be stored in a backend
without scalability issues. will it be an acceptable usage pattern of
this new feature in a mod_perl environment?
Sorry if my mail is sounding too mod/perl/DBI ish ;-)
Regds
Mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Moving tables accross schemas in PostgreSQL 7.3
Hi folks, what would be the best way of moving my tables out of public schema to a schema "foo"? I have few a 100s of tables to move? Regds Mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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
Re: [SQL] function replace doesnt exist
Are you looking for this ?
available on
http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23
regds
mallah.
View One Recipe
Home -> Postgres -> CookBook Home -> View One Recipe Submitted on: 03-16-2001
Description:
mimic oracle's replace function. versions in pltcl and plpgsql.
Code:
-- by Jonathan Ellis ([EMAIL PROTECTED])
-- licensed under the GPL
-- emailing me improvements is appreciated but not required
-- args: string substring replacement_substring
create function replace (varchar, varchar, varchar) returns varchar as '
-- escape out characters that regsub would treat as special
regsub -all {&} "$3" {\\\&} 3
regsub -all {\\[0-9]} "$3" {\\\0} 3
eval "regsub -all \{$2\} \{$1\} \{$3\} rval"
return $rval
' language 'pltcl';
-- plpgsql version so we don't have to jump through hoops to call it from other
functions
create function replace (varchar, varchar, varchar) returns varchar as '
declare
string alias for $1;
sub alias for $2;
replacement alias for $3;
-- xxx[MATCH]
-- | end_before
-- | start_after
match integer;
end_before integer;
start_after integer;
string_replaced varchar;
string_remainder varchar;
begin
string_remainder := string;
string_replaced := '''';
match := position(sub in string_remainder);
while match > 0 loop
end_before := match - 1;
start_after := match + length(sub);
string_replaced := string_replaced || substr(string_remainder, 1, end_b
efore) || replacement;
string_remainder := substr(string_remainder, start_after);
match := position(sub in string_remainder);
end loop;
string_replaced := string_replaced || string_remainder;
return string_replaced;
end;
' LANGUAGE 'plpgsql';
On Thursday 12 December 2002 10:04 pm, Andy Morrow wrote:
> Hi
>
> im trying to execute an update command on a postgresql DB table using
> pgAdmin II
>
> im using the following statement
>
> UPDATE commandlist SET command = REPLACE (command,'A','B')
>
>
> commandlist is the table name
> command is the column
> and i want to change the value A to B
>
>
> but it's giving me the following error message
>
>
> an error has occured in pgAdmin II:frmSQLInput.cmdExecute_Click:
>
> Number: -2147467259
> Description: Error while executing the query;
> ERROR: Function'replace(varchar, unknown, unknown)' does not exist
> Unable to identify a function that satisfies the given argument types
> You may need to add explicit typecasts
>
>
> ---(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
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Stored Procedure Problem
In 7.3 you can , in follwoing steps, 1. do a CREATE TYPE (i would recommend to use a sperate schema for storing user defined types) 2. in plpgsql declare the RECORD of that type . 3. populate the record varible according to your business logic and return the RECORD using RETURN statements. hope it will help , if not please revert back. regds mallah. On Thursday 12 December 2002 08:21 pm, Tomasz Myrta wrote: > Atul wrote: > > CREATE FUNCTION b_function() RETURNS varchar AS ' > > > > DECLARE > > > > an_integer int4; > > > > an_namevarchar; > > > > BEGIN > > > > select into an_integer emp_id,an_name emp_name from employee; > > > > return an_integer,an_name; > > > > END; > > > > ' > > First: select into an_integer,an_name emp_id,emp_name... > Second: you can't return 2 variables from plpgsql function. > > Tomasz Myrta > > > -------(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] COPY command problems
http://www.memtest86.com/ may be useful. regds mallah. On Tuesday 24 December 2002 09:25 pm, Nikola Ivacic wrote: > It must be internal error: > two reasons: > 1.) the original file is OK (I checked with grep + there is no network > envolved) > 2.) Error has strange patern: it substitutes 0x31 with 0x21 (1 with !) also > 0x34 with 0x24 (4 with $) > and 0x39 with 0x29 (9 with ) ) > > so I guess you are right. > > can you suggest some tools for FreeBSD > to test RAM, because I think the hard disk is ok. > > p.s. right now I am testing it with splited file > > thanks > > Nikola > > - Original Message - > From: "Tom Lane" <[EMAIL PROTECTED]> > To: "Nikola Ivacic" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Tuesday, December 24, 2002 4:24 PM > Subject: Re: [SQL] COPY command problems > > > "Nikola Ivacic" <[EMAIL PROTECTED]> writes: > > > Has somebody experienced difficulties using COPY command on large > > > files. I have a large (250MB) file and each time I insert records I've > > > got one > > or = > > > > more (< 30 of cca 1079000) corrupted > > > records. The number of corrupted records is not constant (i.e. 1, 30, > > > 7, > > 23= > > > > etc..) > > > > I'd bet on flaky hardware --- have you run memory and disk tests? If > > the COPY data is passing across a network, then network problems are > > also worthy of suspicion. > > > > 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]) -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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
Re: [SQL] Search and Replace
REPLACE is the right function for you avaliable as an addon. install it in postgresql installation using the source at: http://www.brasileiro.net:8080/postgres/cookbook/view-one-recipe.adp?recipe_id=23 if you face problem please lemme know once this function is installed you could update like: -- UPDATE publications SET url = replace( 'www.srs.fs.fed.us' , 'www.srs.fs.usda.gov' , url ) WHERE url ilike '%www.srs.fs.fed.us%'; -- regds mallah. On Wednesday 08 January 2003 07:32 pm, Randy D. McCracken wrote: > I apologize for having to resort to sending what is most likely a simple > tech support question regarding PostgreSQL to this list but I have not > been able to find the answer in the documentation. > > I am responsible for managing a database containing over 6,000 records of > US Forest Service Research publications > (http://www.srs.fs.usda.gov/pubs/search.jsp) and I need to do a simple > search and replace in one of the columns. In these records we have a > field for URLs of the location the research publications and I need to > change the base URL from www.srs.fs.fed.us to www.srs.fs.usda.gov. It > seems like this "search and replace" would be a simple thing to do with an > UPDATE command but I am having great difficulty making this work. > > The table definition I am trying to update is: url. At first I thought > about using a simple UPDATE command like this: > > UPDATE publications SET url = 'www.srs.fs.usda.gov' WHERE url = > 'www.srs.fs.fed.us'; > > Of course that would work fine but "www.srs.fs.fed.us" is only part of a > complete URL such as: http://www.srs.fs.fed.us/pubs/rp/rp_se263.pdf. My > problem (among other things!) is that I don't know how to pass along > wildcards so that I do not change the other parts of the complete URL. I > have tried substituting "like" for "=" and trying to use the wildcard of > "%" but to no avail. I am really just guessing here. > > Any help would be greatly appreciated! > > Best, > > --rdm > > > === > Randy D. McCracken (0> > Web Guy //\ > Communications GroupV_/_ > > USDA/FS - Southern Research Station > > E-Mail: [EMAIL PROTECTED] > Voice:(828) 259-0518 > Fax: (828) 257-4840 > Web: http://www.srs.fs.fed.us/ > === > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Search and Replace
any anyone explain whats wrong with the replace based solution to this problem which i posted earlier? did i misunderstood anything? regds mallah. On Thursday 09 January 2003 01:48 am, Randy D. McCracken wrote: > Just to close off another thread and to give a tad more information... > > I was not clear enough in my initial question to the list because not all > of the URLs I am trying to change begin with http://www.srs.fs.fed.us/ so > what I was really looking for was the syntax for replacing > "www.srs.fs.fed.us" with "www.srs.fs.usda.gov" and not touching any > records do not contain "www.srs.fs.fed.us" > > Ross Reedstrom was kind enough to give me some additional help that worked > perfectly and after doing a few tests I am happy to share his SQL > statement with the list. > > update pubs set > url=substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||s >ubstr(url,strpos(url,'www.srs.fs.fed.us')+17) where url ~ > 'www.srs.fs.fed.us' > > Thanks Ross! > > --rdm > > On Wed, 8 Jan 2003, Ross J. Reedstrom wrote: > > > > > > As you've discovered, standard SQL text processing functions are a bit > > primitive - usually you break out to the application language for that > > sort of thing. However, if you know for sure that there's only one > > instance of the replace string, and it's a fixed length string, you > > can get away with something like this: > > > > > > test=# select * from pubs; > > id | url > > + > > 1 | http://www.srs.fs.fed.us/pub/1 > > 2 | http://www.srs.fs.fed.us/pub/2 > > 3 | http://www.srs.fs.fed.us/pub/3 > > (3 rows) > > > > test=# update pubs set url= > > > > substr(url,0,strpos(url,'www.srs.fs.fed.us'))||'www.srs.fs.usda.gov'||sub > >str(url,strpos(url,'www.srs.fs.fed.us')+17); > > > > UPDATE 3 > > > > test=# select * from pubs; > > id | url > > +-- > > 1 | http://www.srs.fs.usda.gov/pub/1 > > 2 | http://www.srs.fs.usda.gov/pub/2 > > 3 | http://www.srs.fs.usda.gov/pub/3 > > (3 rows) > > > > You can figure out how it works by playing with SELECTing different > > substr() ans strpos() directly, like this excerpt from my query history: > > > > select strpos(url,'www.srs.fs.usda.gov') from pubs; > > select substr(url,strpos(url,'www.srs.fs.usda.gov')) from pubs; > > select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs; > > select substr(url,1,strpos(url,'www.srs.fs.usda.gov')) from pubs; > > select substr(url,-1,strpos(url,'www.srs.fs.usda.gov')) from pubs; > > select substr(url,0,strpos(url,'www.srs.fs.usda.gov')) from pubs; > > select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs; > > select substr(url,strpos(url,'www.srs.fs.usda.gov')+18) from pubs; > > select substr(url,strpos(url,'www.srs.fs.usda.gov')+19) from pubs; > > > > Hope this helps, > > > > Ross > > -- > > Ross Reedstrom, Ph.D. [EMAIL PROTECTED] > > Research Scientist phone: 713-348-6166 > > The Connexions Project http://cnx./rice.edu fax: 713-348-6182 > > Rice University MS-39 > > Houston, TX 77005 > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] sort by relevance
Yet another customer for relevence ranking ;-) malz. On Wednesday 15 January 2003 11:49 pm, Oleg Bartunov wrote: > On Wed, 15 Jan 2003 [EMAIL PROTECTED] wrote: > > HI, PPL! > > > > How am I able to sort query results by relevance? > > I use contrib/tsearch to search using fill text index! > > Use OpenFTS (openfts.sourceforge.net) for relevance ranking. > we might add relevance feature to tsearch though. > > > > > Regards, > Oleg > _ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Implementing automatic updating of primary keys...
Hi we are working on re-structuring our database schemas and intend to implement the functionality below at database level. consider a master table with following data. Table: profile_master id | username | password ---|--|-- 1 | u1 | p1 2 | u2 | p2 id--> primary key not null. table t1 --- id | service_id ---| 1 | 1 2 | 10 table t2 -- id | rfi_id ---|--- 1 | 1001 2 | 23 there can be many such tables that have foreign key id which is referencing the master table test_master column "id". what we want is when some ids become redundant we have to merge two ids , we want that thru out the system the occurance of the old primary key shud be removed and replaced with the new id. so if id 2 were to be mergered to id 1 then the tables shud look like: Table: profile_master id | username | password ---|--|-- 1 | u1 | p1 id--> primary key not null. table t1 --- id | service_id ---| 1 | 1 1 | 10 table t2 -- id | rfi_id ---|--- 1 | 1001 1 | 23 can some thing be done in the database level it self so that we do not have to keep modifying the mantainence programs as the number of tables referencing master table grows? regds mallah. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Implementing automatic updating of primary keys...
yes you got my problem rightly. If i use "on update cascade" approach still there is problem. If i attempt to update the key in master table it wont be allowed becoz of temporary violation of PRIMARY KEY CONSTRAINT. becoz 1 is also existing in the master table. update profile_master set id=1 where id=2 will not be accepted. regds mallah. On Wednesday 01 January 2003 06:11 pm, Tomasz Myrta wrote: > Rajesh Kumar Mallah. wrote: > >Hi we are working on re-structuring our database schemas and > >intend to implement the functionality below at database level. > > > >consider a master table with following data. > > > > > >Table: profile_master > > > > > >id | username | password > >---|--|-- > >1 | u1 | p1 > >2 | u2 | p2 > > > >id--> primary key not null. > > > >can some thing be done in the database level it self so that we do not > > have to keep modifying the mantainence programs as the number of tables > > referencing master table grows? > > > >regds > >mallah. > > If I understood well you want to change id in all tables from some value > into another one and no matter, how many these tables exist? > > First - if your tables are created with "on update cascade", you can just > change value on master table. > > If you didn't create tables with this option and referencing key has the > same name in all tables, it isn't still too difficult. > > Everything you need is a function which finds all tables with field "id" > and for each table performs: update set id=newvalue where > id=oldvalue. > > In plpgsql it will look something like: > create or replace function... > declare > oldvalue alias for $1; > newvalue alias for $2; > tablename varchar; > begin > for tablename in SELECT relname from pg_attribute join pg_class on > (attrelid=oid) where attname=''id'' and relkind='r'; > loop > perform ''update '' || tablename '' set id='' || newvalue || '' where > id='' || oldvalue; end loop; > end; > > Many interesting find about database special tables you will find in > Chapter 3. System Catalogs inside Postgresql documentation. > > Regards, > Tomasz Myrta > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] converting microsoft sql server 2000 sql-code for postgresql 7.2.1
Only 1 small change makes it acceptable to pgsql. change datetime to timestamp . regds mallah. On Tuesday 28 January 2003 03:38 pm, william windels wrote: > Hello all, > > I a m a new member of the list and at the moment , I am studiing > informatica: sql. > > At the workplace, we use microsoft sql server 2000. > At home, I use postgresql 7.2.1 and now I would import the data of the > database at the workplace into the postgresql environment at home. > > I have paste a little part of the sql-code to create a table in a database > called "tennisclub". > > To execute the code bellow with pgsql, I do the following steps: > > pgsql tennisclub > \e file_with_sql_code.sql > > The contens of the file file_with_sql_code.sql is as follows: > > CREATE TABLE SPELERS > > (SPELERSNR SMALLINT NOT NULL, > > NAAM CHAR(15) NOT NULL, > > VOORLETTERS CHAR(3) NOT NULL, > > VOORVOEGSELS CHAR(7) , > > GEB_DATUM datetime , > > GESLACHT CHAR(1) NOT NULL, > > JAARTOE SMALLINT NOT NULL, > > STRAAT CHAR(15) NOT NULL, > > HUISNR CHAR(4) , > > POSTCODE CHAR(6) , > > PLAATS CHAR(10) NOT NULL, > > TELEFOON CHAR(10) , > > BONDSNR CHAR(4) , > > PRIMARY KEY (SPELERSNR) ); > > INSERT INTO SPELERS VALUES ( > > 6, 'Permentier', 'R', NULL, '1964-06-25', 'M', 1977, 'Hazensteinln', > > '80', '1234KK', 'Den Haag', '070-476537', '8467' > > ); > > INSERT INTO SPELERS VALUES ( > > 44, 'Bakker', 'E', 'de', '1963-01-09', 'M', 1980, 'Lawaaistraat', > > '23', 'LJ', 'Rijswijk', '070-368753', '1124' > > ); > > INSERT INTO SPELERS VALUES ( > > 83, 'Hofland', 'PK', NULL, '1956-11-11', 'M', 1982, 'Mariakade', > > '16a', '1812UP', 'Den Haag', '070-353548', '1608' > > ); > > INSERT INTO SPELERS VALUES ( > > 2, 'Elfring', 'R', NULL, '1948-09-01', 'M', 1975, 'Steden', > > '43', '3575NH', 'Den Haag', '070-237893', '2411' > > ); > > INSERT INTO SPELERS VALUES ( > > 27, 'Cools', 'DD', NULL, '1964-12-28', 'V', 1983, 'Liespad', > > '804', '8457DK', 'Zoetermeer', '079-234857', '2513' > > ); > > INSERT INTO SPELERS VALUES ( > > 104, 'Moerman', 'D', NULL, '1970-05-10', 'V', 1984, 'Stoutlaan', > > '65', '9437AO', 'Zoetermeer', '079-987571', '7060' > > ); > > INSERT INTO SPELERS VALUES ( > > 7, 'Wijers', 'GWS', NULL, '1963-05-11', 'M', 1981, 'Erasmusweg', > > '39', '9758VB', 'Den Haag', '070-347689', NULL > > ); > > INSERT INTO SPELERS VALUES ( > > 57, 'Bohemen', 'M', 'van', '1971-08-17', 'M', 1985, 'Erasmusweg', > > '16', '4377CB', 'Den Haag', '070-473458', '6409' > > ); > > INSERT INTO SPELERS VALUES ( > > 39, 'Bischoff', 'D', NULL, '1956-10-29', 'M', 1980, 'Ericaplein', > > '78', '9629CD', 'Den Haag', '070-393435', NULL > > ); > > INSERT INTO SPELERS VALUES ( > > 112, 'Baalen', 'IP', 'van', '1963-10-01', 'V', 1984, 'Vosseweg', > > '8', '6392LK', 'Rotterdam', '010-548745', '1319' > > ); > > INSERT INTO SPELERS VALUES ( > > 8, 'Niewenburg', 'B', NULL, '1962-07-08', 'V', 1980, 'Spoorlaan', > > '4', '6584WO', 'Rijswijk', '070-458458', '2983' > > ); > > INSERT INTO SPELERS VALUES ( > > 100, 'Permentier', 'P', NULL, '1963-02-28', 'M', 1979, 'Hazensteinln', > > '80', '6494SG', 'Den Haag', '070-494593', '6524' > > ); > > INSERT INTO SPELERS VALUES ( > > 28, 'Cools', 'C', NULL, '1963-06-22', 'V', 1983, 'Oudegracht', > > '10', '1294QK', 'Leiden', '010-659599', NULL > > ); > > INSERT INTO SPELERS VALUES ( > > 95, 'Meuleman', 'P', NULL , '1963-05-14', 'M', 1972, 'Hoofdweg', > > '33a', '5746OP', 'Voorburg', '070-867564', NULL > > ); > > > > This code doesn't work. > > > > Can someone tell me how I can adjust the syntax of the code and in global: > how can I convert sql-code , for microsoft sql server 2000, to sql-code for > postgresql? > > > > Thanks in advance > > > > best regards > > William Windels > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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
Re: [SQL] Which version is this?
your question doesnt' seem to be very clear.
But the following appeared in release note of version 7.2
hope it helps
regds
mallah.
`
A.5. Release 7.2
Release date: 2002-02-04
A.5.1. Overview
This release improves PostgreSQL for use in high-volume applications.
Major changes in this release:
VACUUM
Vacuuming no longer locks tables, thus allowing normal user access during the vacuum.
A new VACUUM FULL command does
old-style vacuum by locking the table and shrinking the on-disk copy of the table.
~~
On Friday 31 January 2003 10:13 pm, Wei Weng wrote:
> Since which version PostgreSQL is able to do Vacuum Analyze even in the
> middle of a transaction, namely, insert, delete, update?
>
>
> Thanks
>
>
> Wei
--
Regds Mallah
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(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] cannot EXPLAIN query...
Hi, the query is running fine but i cannot EXPLAIN or (ANALYZE) it. I am seeing this message for the first time: tradein_clients=# SELECT count(*) from shippers1 where city='DELHI'; +---+ | count | +---+ | 2 | +---+ (1 row) tradein_clients=# tradein_clients=# explain SELECT count(*) from shippers1 where city='DELHI'; ERROR: get_names_for_var: bogus varno 5 tradein_clients=# i can paste the nasty view definations if nothing is obvious till now. regds MAllah. Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cannot EXPLAIN query...
| Modifiers
+--+-
userid | integer |
category | character varying(50)|
category_id| integer | not null
branch | character varying|
sno| integer | default -1
size | character varying(20)|
co_name| character varying(100) | not null
address1 | character varying(200) |
address2 | character varying(200) | default ' '
address3 | character varying(200) | default ' '
city | character varying(100) |
pin_code | character varying(30)|
phone | character varying(100) |
fax| character varying(100) |
email | character varying(100) |
contact| character varying(100) |
website| character varying(100) |
keywords | text |
show_ad| character varying(25)| default 'f'
status | character varying(200) | default 'a'
amount | integer |
group_id | integer | default
nextval('"eyp_listing_group_id_seq"'::text)
list_id| integer | not null default
nextval('"eyp_listing_list_id_seq"'::text)
catalog_id | integer | default 0
generated | date | default date('now'::text)
edition| smallint |
wrong_last_updated | date |
last_updated | timestamp with time zone | default now()
user_keywords | text |
counter| smallint | default 0
sent_on| timestamp with time zone | default now()
max_emails | smallint |
emails_sent| smallint | default 0
total_emails | smallint | default 0
notification | boolean |
branding_id| integer |
keywordidx | txtidx |
company_id | integer |
website_working| boolean | default 'f'
hide_email | boolean | default 'f'
co_name_index | txtidx |
bankers| character varying(200) |
estd | integer |
staff | integer |
annual_turn_value | numeric |
mobile | character varying(50)|
reminder_cnt | smallint | default 0
expires_on | date |
Indexes: eyp_listing_pkey primary key btree (list_id),
eyp_listing_br_cid_cat_id unique btree (company_id, category_id) WHERE (size
= 'BRANDING'::character varying),
a_gist_key gist (keywordidx),
eyp_listing_amt btree (amount),
eyp_listing_branch btree (branch) WHERE (amount > 0),
eyp_listing_category_id btree (category_id),
eyp_listing_co_name btree (co_name),
eyp_listing_co_name_index gist (co_name_index),
eyp_listing_company_id btree (company_id),
eyp_listing_email btree (email),
eyp_listing_group_id btree (group_id),
eyp_listing_size btree (size),
eyp_listing_sno_branch btree (branch, sno),
eyp_listing_userid btree (userid)
Check constraints: "branding_check" CASE WHEN (size = 'BRANDING'::character varying)
THEN ((company_id IS NOT NULL) AND (company_id > 0)) ELSE (company_id IS NULL) END
Foreign Key constraints: referrer_branches_master FOREIGN KEY (branch) REFERENCES
tradeindia_branches(branch) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: RI_ConstraintTrigger_29292778,
RI_ConstraintTrigger_29292779,
co_name_index_update,
last_updated,
set_category,
set_max_emails
tradein_clients=#
On Monday 03 February 2003 08:16 pm, Tom Lane wrote:
> "Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes:
> > tradein_clients=# explain SELECT count(*) from shippers1 where
> > city='DELHI'; ERROR: get_names_for_var: bogus varno 5
>
> What version is this? ISTR having fixed some bugs that might cause that.
>
> > i can paste the nasty view definations if nothing is obvious till
> > now.
>
> If it's a current release, we need to see *all* the schema definitions
> referenced by the query --- views and tables.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
--
Regds Mallah
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cannot EXPLAIN query...
Thank you . But i have a problem , I think if i do that i will hve to immediately upgrade all the 7.3.0 clients in other machines to 7.3.1 rite? regds Mallah. On Monday 03 February 2003 09:10 pm, Tom Lane wrote: > "Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes: > > It is PostgreSQL 7.3.0 on Linux. > > Try 7.3.1 then. I think this is this problem: > > 2002-12-06 14:28 tgl > > * src/backend/commands/explain.c (REL7_3_STABLE): Explain's code > for showing quals of SubqueryScan nodes has been broken all along; > not noticed till now. It's a scan not an upper qual ... > > 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] cannot EXPLAIN query...
Thanks , if that is so i am upgrading it right away and posting
you the results. Its my live DB server :-)
Regds
mallah.
On Monday 03 February 2003 09:15 pm, Tom Lane wrote:
> "Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes:
> > I think if i do that i will hve to immediately upgrade
> > all the 7.3.0 clients in other machines to 7.3.1 rite?
>
> No.
>
> regards, tom lane
--
Regds Mallah
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)26152172 (221) (L) 9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] cannot EXPLAIN query...
On Monday 03 February 2003 09:20 pm, Christoph Haller wrote: > > Sorry Postgresql has really made my VIEWS ugly. > > It wasnt' so when i fed them. > > > > I wish pgsql stores the create view defination some day , > > just like it does for indexes (pg_get_indexdef) > > Did you ever try > > SELECT * FROM pg_views ; i thing when you do a \d it uses that only. in any case i have verified that the content in them are equally messed up. regds mallah. > > It definitely has all view definitions. > > Regards, Christoph -- -------- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] cannot EXPLAIN query...
Hmmm... upgrade to 7.3.1 was not that smooth.. after upgrade i could not run a single query.. tradein_clients=> SELECT * from hogs; ERROR: current transaction is aborted, queries ignored until end of transaction block tradein_clients=> any other query seems to be giving the same ERROR. check the message below on psql start (7.3.1) with a 7.3.1 server. PS: i applied the heir patch though ... ;-) will try again without that. [postgres@ns3 postgres]$ psql -Upostgres -h66.234.10.12 tradein_clients >> ERROR: nodeRead: did not find '}' at end of plan node Welcome to psql 7.3.1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit tradein_clients=> regds mallah. On Monday 03 February 2003 09:15 pm, Tom Lane wrote: > "Rajesh Kumar Mallah." <[EMAIL PROTECTED]> writes: > > I think if i do that i will hve to immediately upgrade > > all the 7.3.0 clients in other machines to 7.3.1 rite? > > No. > > regards, tom lane -- ---- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)26152172 (221) (L) 9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] " Adding missing FROM-clause entry for table .... " problem.
Hmmm i forgot to follow up. Thanks for pointing out the relevent Docs. Regds Mallah. On Tuesday 18 February 2003 04:04 pm, Christoph Haller wrote: > > We find that if we alias a tablename and refer to that tablename in > > where cluase instead of reffering > > > to the alias it produces wrond results. > > > > EG: > > select to_char(a.generated, 'DD/Mon/' ) ,userid,email,descr from > > membership_invoice a join payment_classes using( > payment_class) > > > join users using(userid) join membership_status using(userid) where > > membership_invoice.status='a' and granted is fa > lse and > > > membership_invoice.last_reminder is null and current_date - > > date(a.generated) > 4 limit 10 ; > > > NOTICE: Adding missing FROM-clause entry for table > > "membership_invoice" > > > Where as merely rewriting the quer to use defined aliases gives the > > correct results. > > > select to_char(a.generated, 'DD/Mon/' ) ,userid,email,descr from > > membership_invoice a join payment_classes > > > using(payment_class) join users using(userid) join membership_status > > using(userid) where a.status='a' and granted i > s > > > false and a.last_reminder is null and current_date - date(a.generated) > > 4 ; > > > > Can Anyone please explain if its a BUG or problem in my understanding > > I think it's a problem in understanding. The documentation (7.2.1) > states (as the NOTICE: does) > > 2.2.1.3. Table and Column Aliases > > A temporary name can be given to tables and complex table references to > be used for references to the derived table in further > processing. This is called a table alias. > > FROM table_reference AS alias > > Here, alias can be any regular identifier. The alias becomes the new > name of the table reference for the current query -- it is no > longer possible to refer to the table by the original name. Thus > > SELECT * FROM my_table AS m WHERE my_table.a > 5; > > is not valid SQL syntax. What will actually happen (this is a PostgreSQL > extension to the standard) is that an implicit table > reference is added to the FROM clause, so the query is processed as if > it were written as > > SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5; > > Regards, Christoph > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] function defination help ..
Thank you i will look into its source code. and try to find some solution for myself. regds mallah. On Saturday 22 February 2003 07:40 am, Peter Eisentraut wrote: > Rajesh Kumar Mallah writes: > > is it possible to get the function creation defination as produced by > > pg_dump by some SQL queries on system catalogs? > > > > pg_func stores procsrc but i am trying to get RETURNS and the arg part > > also. > > You will need to reconstruct what pg_dump does. -- Regds Mallah ---- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] good style?
, 'mmcms.admin', 'acl for admin module'); > INSERT INTO acl VALUES (4, 'mmcms.category', 'acl for category module'); > INSERT INTO acl VALUES (5, 'mmcms.context', 'acl for context module'); > > > > CREATE TABLE permission ( > permission_id int2 NOT NULL, > name varchar(20) NOT NULL, > description varchar(200), > CONSTRAINT permission_pkey PRIMARY KEY (permission_id) > ) WITH OIDS; > > CREATE UNIQUE INDEX permission_name_idx ON permission (name); > > INSERT INTO permission VALUES (1, 'access', 'access permission'); > INSERT INTO permission VALUES (2, 'read', 'read permission'); > INSERT INTO permission VALUES (3, 'write', 'write permission'); > INSERT INTO permission VALUES (4, 'execute', 'execute permission'); > INSERT INTO permission VALUES (5, 'modify', 'modify permission'); > INSERT INTO permission VALUES (6, 'list', 'list permission'); > > > > CREATE TABLE acl_entry ( > acl_entry_id int2 NOT NULL, > acl_id int2 NOT NULL, > permission_id int2 NOT NULL, > CONSTRAINT acl_entry_pkey PRIMARY KEY (acl_entry_id), > CONSTRAINT fk_acl FOREIGN KEY (acl_id) REFERENCES acl (acl_id), > CONSTRAINT fk_permission FOREIGN KEY (permission_id) REFERENCES > permission (permission_id) > ) WITH OIDS; > > -- acl_entry for mmcms.access acl with access permission > > -- acl 'mmcms.access' has 'access' permission > INSERT INTO acl_entry VALUES (1, 1, 1); > -- acl 'mmcms.system' has 'read' permission > INSERT INTO acl_entry VALUES (2, 2, 2); > -- acl 'mmcms.system' has 'write' permission > INSERT INTO acl_entry VALUES (3, 2, 3); > -- acl 'mmcms.admin' has 'read' permission > INSERT INTO acl_entry VALUES (4, 3, 2); > -- acl 'mmcms.admin' has 'write' permission > INSERT INTO acl_entry VALUES (5, 3, 3); > > > > CREATE TABLE users ( > users_id numeric(20, 0) NOT NULL, > mandant_id numeric(20, 0) NOT NULL, > language_id int2 NOT NULL, > login_name varchar(50) NOT NULL, > password varchar(15) NOT NULL, > first_name varchar(20) NOT NULL, > last_name varchar(20) NOT NULL, > creation_date timestamp NOT NULL, > last_login_date timestamp, > status int2 NOT NULL, > CONSTRAINT users_pkey PRIMARY KEY (users_id), > CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant > (mandant_id), > CONSTRAINT fk_language FOREIGN KEY (language_id) REFERENCES language > (language_id) > ) WITH OIDS; > > CREATE UNIQUE INDEX users_login_name_idx ON users (login_name); > > INSERT INTO users VALUES (1, 1, 1, '[EMAIL PROTECTED]', 'test', > 'Rafal', 'Kedziorski', now(), NULL, 0); > > > > CREATE TABLE groups ( > groups_id numeric(20, 0) NOT NULL, > mandant_id numeric(20, 0) NOT NULL, > name varchar(20) NOT NULL, > description varchar(200) NOT NULL, > creation_date timestamp NOT NULL, > CONSTRAINT groups_pkey PRIMARY KEY (groups_id), > CONSTRAINT fk_mandant FOREIGN KEY (mandant_id) REFERENCES mandant > (mandant_id) > ) WITH OIDS; > > CREATE UNIQUE INDEX groups_name_idx ON groups (mandant_id, name); > > -- every mandant should have own access group > INSERT INTO groups VALUES (1, 1, 'access', 'access group', now()); > INSERT INTO groups VALUES (2, 1, 'system', 'system group', now()); > INSERT INTO groups VALUES (3, 1, 'admin', 'admin group', now()); > > > > CREATE TABLE users_2_groups ( > users_2_groups_id numeric(20, 0) NOT NULL, > users_id numeric(20, 0) NOT NULL, > groups_id numeric(20, 0) NOT NULL, > valid_from timestamp NOT NULL, > expired timestamp, > CONSTRAINT users_2_groups_pkey PRIMARY KEY (users_2_groups_id), > CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups > (groups_id), > CONSTRAINT fk_users FOREIGN KEY (users_id) REFERENCES users (users_id) > ) WITH OIDS; > > CREATE UNIQUE INDEX users_2_groups_usersgroups__idx ON users_2_groups > (users_id, groups_id); > > INSERT INTO users_2_groups VALUES (1, 1, 1, now(), NULL); > INSERT INTO users_2_groups VALUES (2, 1, 2, now(), NULL); > INSERT INTO users_2_groups VALUES (3, 1, 3, now(), NULL); > > > > CREATE TABLE groups_2_acl_entry ( > groups_2_acl_entry_id numeric(20, 0) NOT NULL, > groups_id numeric(20, 0) NOT NULL, > acl_entry_id int2 NOT NULL, > CONSTRAINT groups_2_acl_entry_pkey PRIMARY KEY (groups_2_acl_entry_id), > CONSTRAINT fk_groups FOREIGN KEY (groups_id) REFERENCES groups > (groups_id), > CONSTRAINT fk_acl_entry FOREIGN KEY (acl_entry_id) REFERENCES > acl_entry (acl_entry_id) > ) WITH OIDS; > > INSERT INTO groups_2_acl_entry VALUES (1, 1, 1); > INSERT INTO groups_2_acl_entry VALUES (2, 2, 2); > INSERT INTO groups_2_acl_entry VALUES (3, 2, 3); > INSERT INTO groups_2_acl_entry VALUES (4, 3, 4); > INSERT INTO groups_2_acl_entry VALUES (5, 3, 5); > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] function defination help ..
Hi, is it possible to get the function creation defination as produced by pg_dump by some SQL queries on system catalogs? pg_func stores procsrc but i am trying to get RETURNS and the arg part also. -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Help with query involving aggregation and joining.
gt; --- Bruno Wolff III <[EMAIL PROTECTED]> wrote: > > On Sun, Feb 23, 2003 at 11:02:27 -0800, > > > > Eddie Cheung <[EMAIL PROTECTED]> wrote: > > > HISTORY > > > === > > > id | courseId | submission > > > ---+---+- > > > 1 | 101 | 2002-01-20 > > > 2 | 102 | 2002-02-17 > > > 3 | 104 | 2002-04-30 > > > 4 | 102 | 2002-02-22 > > > 5 | 104 | 2002-03-15 > > > 6 | 104 | 2002-01-21 > > > > > > > > > COURSE > > > == > > > id | name > > > --+--- > > > 101 | Physics > > > 102 | Chemistry > > > 103 | Biology > > > 104 | Maths > > > 105 | English > > > > > > > > > Basically I would like to display the latest > > > submission for each course in a table as shown > > > > below, > > > > > order by name of the courses. > > > > > > Query Results: > > > == > > > id | courseId | name| submission > > > --- > > > 4 | 102 | Chemisty | 2002-02-22 > > > 3 | 104 | Maths| 2002-04-30 > > > 1 | 101 | Physics | 2002-01-20 > > > > I think you want to do something like: > > > > select distinct on (course.courseid) > > history.id, course.courseid, course.name, > > history.submission > > from course natural join history > > order by course.courseid, history.submission desc; > > > > ---(end of > > broadcast)--- > > TIP 1: subscribe and unsubscribe commands go to > > [EMAIL PROTECTED] > > > __ > Do you Yahoo!? > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > http://mailplus.yahoo.com > > ---(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 -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] sql question regarding count(*)
When does count(*) returns "o rows" ? and when does it return 1 row value being 0. tradein_clients=# SELECT count(*) from public.eyp_listing where sno> 0 and amount> 0 group by sno,branch,edition having count(distinct userid) > 1 ; count --- (0 rows) tradein_clients=# tradein_clients=# tradein_clients=# SELECT count(*) from public.users where userid=-1; count --- 0 (1 row) tradein_clients=# -- Regds Mallah ---- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Novice needs help
shud use NOT EXISTS instead of NOT IN as others have suggested . becoz NOT IN is not very efficient at the moment except the case on there are small number of items in IN( ... ) regds mallah. On Wednesday 12 Mar 2003 2:16 am, Terry Lee Tucker wrote: > I answered my own question. Yes, it can be done like this: > > SELECT code, name, city, country, province FROM cust WHERE (country, > province) NOT IN > (SELECT country, code FROM province); > > The query returned two cust records that had bogus province codes in > them. Very cool :o) > > Terry Lee Tucker wrote: > > > I have loaded over 29,000 customer records into a database. I'm trying > > to apply a referential foreign key that validates the country code and > > the province code in another table. It won't work because somewhere in > > that 29,000 records there is a province code or country code that > > doesn't match. Is there a way to use a select statement to find out > > which customer records has the invalid data? Here is the partial table > > layout: > > > > custprovince > > --- > > country ===> country > > province===> code > > > > Thanks in advance... > > > > -- > Sparta, NC 28675 USA > 336.372.6812 > http://www.esc1.com > > > > > ---(end of broadcast)------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > -- Regds Mallah Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] explain
Dear Marian, in postgresql 7.3 explain commands retruns as set of rows. The result of EXPLAIN can just be fecthed as result of any other SELECT query and manipulated further. i hope it helps. sorry if i did not get ur problem rite. regds mallah. On Friday 21 Mar 2003 11:51 am, Popeanga Marian wrote: > > > Roberto Mello wrote: > > >On Fri, Mar 21, 2003 at 07:46:01AM +0200, Popeanga Marian wrote: > > > > > >>Hello all, > >> > >> > >> Can someone point me how can i make explain plan for a query? > >> > >> > > > >explain select blah from foo where bar = baz; > > > >\h explain > > > >Look at the PostgreSQL documentation (SQL Reference). > > > >-Roberto > > > > > > >I have look at this command. I want to know how can i make this from > other tool than pgsql. > In oracle when i issue an explain plan the explain insert values into a > table ( plan_table ). > Then i could select the values from plan_table. Is there similar > in postgres ? > > -- > Popeanga Marian > DBA Oracle > CNLO Romania > > > -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] FUNCTIONS PROBLEM
CREATE TYPE can be used as well i think in that case "as (val1 int, val2 int, val3 real, val4 char);" need not be done while selecting. regds mallah On Monday 24 Mar 2003 4:48 am, David Witham wrote: > Hi Mario, > > I have used a record type to do this: > > create myfunc() returns record as ' > > declare > return_val record; > col1int; > col2int; > col3real; > col4char; > > col1 := 5; > col2 := 10; > col3 := 2.7; > col4 := ''z''; > > select col1,col2,col3,col4 into return_val; > return return_val; > end; > ' language 'plpgsql'; > > When you call the function you need to specify the expected output: > > select * from myfunc() as (val1 int, val2 int, val3 real, val4 char); > > See the SELECT reference page in the documentation. > > There are other ways (which may be better) to do this that don't require > the output types to be specified with the query but this is the one I got > going first so I stuck with it. Hope this helps. > > Regards, > David Witham > Telephony Platforms Architect > Unidial > > -Original Message- > From: Mario Alberto Soto Cordones [mailto:[EMAIL PROTECTED] > Sent: Friday, 21 March 2003 09:26 > To: [EMAIL PROTECTED] > Subject: [SQL] FUNCTIONS PROBLEM > Importance: High > > > Hi. > > i have a function and i need to return 4 fields but not work, > > any idea , please > > thank > > mario > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Off topic : world database
Hi, can any one help me converting longitudes and latitudes to something that packages like postgis can readily accept. calle.com provides longitude and latitude info of almost all the places in world. fetching that data and inserting in database is not a problem. I am wanting to make an application similar to you which shud be able to get all the places within certaint radius of the place in question. Any hint on how i shud be proceeding? Regds mallah. On Monday 31 Mar 2003 6:02 am, Rudi Starcevic wrote: > Hi all, > > A quick email to let you know where I'm at with the 'world database'. > Thanks to all the replies and tips. > > I've recieved a couple of off list tips and questions so in order to > reach every one I'll reply via this list. > > So far I haven't got all the info together. > Countries like the US and Australia are easy - there's plenty of freely > available data. > Getting the entire globe is another question all together. > > I stll have some leads to follow up however those remaining leads will > surely required some dollars. > So far the the best list I have come across is at > http://www.calle.com/world. > This site has Countries and Cities - what's needed is Countries, States > and cities. > > I know other site's out there are using the info I'm after. > One site for example is date.com > At this site, a global dating site, no matter which county your from you > will drill down into your > state and then city. Once your logged in you can search for matches and > limit by distance. > One of the many uses for a world database. > > I hope to eventually report back with a url and web services for us all > so we can use > this info in the greatest database ever - PG. > > Cheers > Kind regards > Rudi. > > Rudi Starcevic wrote: > > Hi, > > > > I have a slightly off SQL topic question which I don't think is too > > inappropriate, > > if it is please let me know and I'll not push my luck again of my > > *favorite email list*. > > > > I'd like to build a Postgresql database of Countries,States and Cities > > of the world. > > I would like to use 'earthdistance' module so distance queries are > > possible. > > I plan to expose this data as an SOAP webservice via xmethods.com for > > others to > > access as well. > > > > I've been searching for a source for this data without joy so far and > > would like to > > ask if someone on this list could point me to or help with a source > > for this info. > > Ideally I'd like Country,States,and Cities with Longitude and Latitude > > coordinates. > > > > Thank you kindly, > > Again if this is too off topic let me know and I'll keep all my > > questions strictly to SQL. > > Regards > > Rudi. > > > > > > ---(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 > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] substr_count
On Thursday 10 Jul 2003 10:08 am, Tom Rochester wrote: > Hey all, > > I would like to achive something along the lines of: > > SELECT field FROM table WHERE field ILIKE '$searchterm' ORDER BY > substr_count(field, '$searchterm'); > Hi In case you are attempting to search text in a feild and sort it by relevence then contrib/tsearch V2 is for you. http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ regds mallah. > Of course the substr_count function does not exist. Is there anyway to > do this? > > I had a thought char_count(replace(field, !$searchterm, '')) might do > the job but replace doesn't allow for 'replace everything NOT string'; > > Any Ideas? > > Thanks in advance, -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(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] Blobs
Sir Devi, This is PostgreSQL mailing List. If you need help porting applications from Oracle9i to PostgreSQL we might help. Or if you have generic SQL question not specific to any database we can also consider. for help in Oracle specific problems there may be more appropriate lists on the net. Rajesh Mallah. On Thursday 10 Jul 2003 4:40 pm, sri devi wrote: > hi > > we have to download url files in to oracle using BLOBs how to create oracle > table stucture how to write the query,we are using oracle9i,and > javaswings,reply me to this id. [EMAIL PROTECTED] thanking you > sridevi > > SMS using the Yahoo! Messenger;Download latest version. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] trigger proceedures in sql
Yes of course! contrib/dbmirror does execute a procedure written in 'C' called recordchange() ON update , insert , delete. If you need help in getting its source lemme know. regds Mallah. On Thursday 10 Jul 2003 11:10 am, adivi wrote: > hi, > > can trigger proceedures ( procedures to be executed from within a > trigger ) not be written in sql. > > i was looking for examples and can find proceedures in 'c' only. > > regards > -adivi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Recursive request ...
Dear Bournon, There are already good implementation of Tree structures in databases ranging from using pure SQL to PostgreSQL specfic methods , less point in revinting wheel unless u really need. Some Pointers: "Tree-structure functions" http://www.brasileiro.net:8080/postgres/cookbook/ Gist Based: contrib/ltree Joe Celko's Article on "Nested Sets & Adjacency Lists" http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html http://users.starpower.net/rjhalljr/Serve/MySQL/traer.html http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=adjacency+list+%2B+tree+%2B+joe On Wednesday 09 Jul 2003 8:57 pm, Benoît Bournon wrote: > I have to make a function that returns a tree with title and link of a > table. > > Recursively, a information depends on a parent information. > > It is to organise a menu with parent dependance. > > How is it possible and faster ? in C ? pl/pgsql or other ? > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] help yourself by helping others
dear ali,
something like
select machine,date_part('day' , date) , sum(withdrawals) from
Table where date_part('month' , date)='month in question' group by
machine,date_part('day' , date) ;
will give you agrregated withdrawals by machine and day,
use the frontend language for formatting it.
note that it will not display the days for which there
has been no withdrawls. If u need to report them also 0
then create a table that holds 1 year of dates and
left or right join the output of first query with it.
exact query is not being provided , its just an idea.
regds
mallah.
On Friday 04 Apr 2003 4:40 pm, Ali Adams wrote:
> Dear All,
>
> I am new to Relational Databases and SQL and my background in ODBs is
> clouding my way to solving what seems to be a simple problem. I am sure
> many of you have met it many times.
>
> OK, I have a table as follows:
>
>
> ID Machine Date Withdrawals
> 1 1 01/01/20031101
> 2 2 01/01/20032101
> 3 3 01/01/20033101
>
> 4 1 02/01/20031102
> 5 2 02/01/20032102
>
> 6 1 03/01/20031103
> 7 3 03/01/20033103
>
> 8 2 04/01/20032104
> 9 4 04/01/20034104
>
> And i would like to create a monthly withdrawals report as follows:
>
> Machine Day1 Day2 Day3 Day4
> .. Day31 1 11
> 12 13 0 0 2
> 21 22 024
> 0 3 31 033 0
> 0 4 00 0
> 0 0
>
> Can you please help?
>
> Many thanks in advance.
>
> Ali
>
> www.geocities.com/aliadams
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] relevance
On Thursday 17 Jul 2003 8:21 am, Terence Kearns wrote: > >>select id from tablename where message like '%sql%'; > >> > >>If there any way to determine exactly how many times 'sql' is matched in > >>that search in each particular row, and then sort by the most matches, > >>or am I going to have to write a script to do the sorting for me? > > You could probably write a function in postgres (say, "matchcount()") > which returns the match count (possibly using perl and a regex). Why reinvent the wheel when tsearch already does the job perfectly and is PostgreSQL compaitable. Regds Mallah. > > SELECT matchcount(message,'sql') AS matchcount, id > FROM tablename > WHERE message LIKE '%sql%' > ORDER BY matchcount(message,'sql') DESC > > The ORDER BY will probably fail, but you can try :) ---(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] ALTER TABLE ... DROP CONSTRAINT
if the constraint are named $1 $2 etc you will need to quote them eg ALTER TABLE xyz DROP CONSTRAINT "$1" ; in some version you may require ALTER TABLE xyz DROP CONSTRAINT "$1" RESTRICT; What is ur version btw? try to post the table structure also. regds mallah. Elielson Fontanezi wrote: Hi all! Who can tell me what postgres version supports ALTER TABLE... DROP CONSTRAINT without the need of droping the table to remove a simple coinstraint. (link) >\\\!/< 55 11 5080 9283 !_"""_! Elielson Fontanezi (O) (o) PRODAM - Technical Support Analyst ---oOOO--(_)--OOOo--- Success usually comes to those who are too busy to be looking for it. 0 0 ---()--( ) \ () / \_/\_/ ---(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] Joined deletes but one table being a subquery.
Hi Folks, DELETE from eyp_listing where userid=t_a.userid and category_id=t_a.category_id; such queries work perfectly. but if t_a is a subquery how to accomplish the delete. Regds Mallah. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Joined deletes but one table being a subquery.
I apologize for the silence. t_a as been created as CREATE TABLE t_a as SELECT userid,category_id from eyp_listing where userid=21742 and size ilike '%WEBFL%' EXCEPT SELECT userid,category_id from company_export_profile where userid=21742 ; so the subquery is basically ( SELECT userid,category_id from eyp_listing where userid=21742 and size ilike '%WEBFL%' EXCEPT SELECT userid,category_id from company_export_profile where userid=21742 ) regds mallah. On Friday 22 Aug 2003 3:53 pm, Tomasz Myrta wrote: > > Hi Folks, > > > > DELETE from eyp_listing where userid=t_a.userid and > > category_id=t_a.category_id; such queries work perfectly. > > > > but if t_a is a subquery how to accomplish the delete. > > What kind of subquery it is? Exist/Not exist doesn't work? > > Regards, > Tomasz Myrta > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(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
Re: [SQL] transaction processing after error in statement
Rod Taylor wrote: be recovered either. When committing a transaction the effects of all operations that did not fail will be made permanent. This is how transaction processing is described in the literature. I would be interested in reading that (URLs please) as I didn't see anything in the spec that was interesting on this topic. 4.8.5 from Framework (part 01) An SQL-transaction (transaction) is a sequence of executions of SQL-statements that is atomic with respect to recovery. That is to say: either the execution result is completely successful, or it has no effect on any SQL-schemas or SQL-data. Although i am not aware of the roots of this discussion but would like to comment at this point . When we work with sequences an aborted transaction does have a permanent effect on the last value of sequence. Is this behaviour not a violation of above defination of transaction ? Regds Mallah. The "execution result is completely successful" could certainly be used to back up PostgreSQLs choice to force a rollback. However, it doesn't differentiate between execution of what the user requested, and execution of recovery procedures on the successful user elements. Irregardless, I wish a commit on a failed transaction would throw an error -- END is good enough for Rollback or Commit. For PostgreSQL to implement this we need Savepoints or nested transactions internally since in many cases data is physically written in order to perform things like Foreign Key constraint checks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Adding a column to a VIEW which has dependent objects.
Dear PostgreSQL gurus, How do people extend a parent view which has lot of dependent views? The parent view cannot be dropped because that will require recreating a dozen of dependent views. Is there any workaround. Also is there an easy way of dumping the definitions of all the dependent views of a given object. Does information_schema helps here. Regds mallah. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] array_lower /array_prepend doubt
Greetings!
can anyone explain why
SELECT array_lower(array_prepend(0, ARRAY[1,2,3]), 1);
returns 0 not 1
because
tradein_clients=# SELECT array_prepend(0, ARRAY[1,2,3]);
+---+
| array_prepend |
+---+
| {0,1,2,3} |
+---+
(1 row)
and
tradein_clients=# SELECT array_lower( ARRAY[0,1,2,3],1 );
+-+
| array_lower |
+-+
| 1 |
+-+
(1 row)
Time: 402.614 ms
Regds
Mallah.
---(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
Re: [ADMIN] [SQL] Database diagram
Dear Ganesan, pgautodoc (for dot output) + dot (from graphviz.org , creates postscript) + epssplit ( splits the postscript into multipage so that one can create poster size diagrams) works well for me. I have 45 tables in the database though. Regds mallah. Chris Travers wrote: There is a free Perl script which is called something like pgautodoc which creates DIA diagrams from databases. Take a look for it on Freshmeat. - Original Message - From: "Ganesan Kanavathy" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Tuesday, January 20, 2004 1:38 PM Subject: [SQL] Database diagram I have a postgres database with many tables. How do I create database diagram? Are there any free tools available to create database diagram from pgsql database? Regards, Ganesan ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Tsearch2 question: getting histogram of the vector elements
Greetings! My original problem is to de duplicate a list of around 0.3 million company names. Since a company name can be potentially (mis)spelt in numerous ways exactmatch obviously wont work. To make the searches faster i am using tsearch. For each company name i want to search other companies whose name is similar to the company in question. Since inclusion of all the vector elements of a given company reduces the chance of matching i am thinking of excluding the high frequency words from the query. Hence i need to find the high frequency elements like say 'consulting' , 'limited' , 'Private' 'Industries' that occur commonly in company names. In my table i have populated the co_name_vec feild as strip(to_tsvector(co_name)) can anyone help me analyzing the co_name_vec for the high frequency words? Also i would like to know alternate / better solution to this problem. Regds Mallah. SAMPLE DATA. +-+--+ | co_name | co_name_vec| +-+--+ | European Trade Partner & Consulting | 'trade' 'consult' 'partner' 'european' | | Gulbrandsen Chemicals Pvt. Ltd. | 'ltd' 'pvt' 'chemic' 'gulbrandsen' | | Govt. of Karnataka, Vision Group on Biotechnology | 'govt' 'group' 'vision' 'karnataka' 'biotechnolog' | | Digital Globalsoft Ltd. (A Hewlett Packard Company) | 'ltd' 'digit' 'compani' 'hewlett' 'packard' 'globalsoft' | | Shanon Construction Material Industries | 'materi' 'shanon' 'industri' 'construct' | | singpore india trade rsources company | 'india' 'trade' 'rsourc' 'compani' 'singpor' | | RGV TELECOM CONSULTANTS PVT. LTD. | 'ltd' 'pvt' 'rgv' 'consult' 'telecom'| | avid information search and documents (p) ltd. | 'p' 'ltd' 'avid' 'inform' 'search' 'document'| | Tavant Technologies India (P) Ltd. | 'p' 'ltd' 'india' 'tavant' 'technolog' | | Maschinen Fabrik (India) Pvt. Ltd | 'ltd' 'pvt' 'india' 'fabrik' 'maschinen' | | Manishri Refractories and Ceramics Pvt. Ltd.| 'ltd' 'pvt' 'ceram' 'manishri' 'refractori' | | xavier export import management institute| 'manag' 'export' 'import' 'xavier' 'institut'| | Best InformationTechnology ltd. | 'ltd' 'best' 'informationtechnolog' | | FutureCalls Technology Private Limited | 'limit' 'privat' 'futurecal' 'technolog' | | mak controls and systems pvt ltd| 'ltd' 'mak' 'pvt' 'system' 'control' | | NATIONAL RESEARCH CENTRE FOR CASHEW | 'centr' 'cashew' 'nation' 'research' | | The Madras Aluminium Company Ltd. | 'ltd' 'madra' 'compani' 'aluminium' | | Shriram Institute for Industrial Research | 'shriram' 'industri' 'institut' 'research' | | All India Carpet Trade Fair Committee | 'fair' 'india' 'trade' 'carpet' 'committe' | | Tuff Security & Allied Services | 'alli' 'tuff' 'secur' 'servic' | +-+--+ (20 rows) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Querying two databases
hi, its not possible to join cross database tables . you may keep tables in different schemas instead of databases. you may also try contrib/dblink to use tables from different database. Regds mallah. Pallav Kalva wrote: Hi, I am new to postgres and I need to do a query which joins two tables from different databases. Can you please advice me on how to achieve this in postgres. Thanks! Pallav ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Problem in SQL Trigger
Can you tell us about the postgresql versions in 7.3 and 9.0 also post the actuall error message from postgresql. regds mallah. Ramesh Patel wrote: Hi i have one problem in Trigger. this trigger alread work on Red Hat Linux 7.3 but now i shift to RHL9.0 in RHL 9.0 not working . in this problem in ROUND function. but how to i slove this i dont know. so please help me. This is Function and Trigger. /// Function Start// CREATE FUNCTION add_issue_fun() returns opaque as' BEGIN UPDATE mtrl_mst set balstk_cs = balstk_cs - NEW.issueqty_cs, balstk_mt = round( cast((balstk_mt - NEW.issueqty_mt) as numeric ),4) where mtrl_mst.mtrl_code = NEW.mtrl_code; UPDATE rcpt_detail set consqty_cs=consqty_cs + NEW.issueqty_cs WHERE rcpt_detail.batch_code = NEW.batch_code and rcpt_detail.mtrl_code = NEW.mtrl_code and rcpt_detail.loc_code = NEW.loc_code; return NULL; END;' language 'plpgsql'; /// Function End// ///Trigger Start // CREATE TRIGGER add_issue_trg AFTER INSERT ON issue_detail FOR EACH ROW EXECUTE PROCEDURE add_issue_fun(); /// Trigger End// Thanking Ramesh Patel Computer Dept. Banasdairy, Palanpur ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Logical comparison on Strings
kumar wrote: Dear Friends, Postgres 7.3.2 on Linux 7. I want to compare to columns and get the logical result as follows. C1 is 'YNYNY' . C2 is 'NNYYY'. I want to compare like AND and OR operators. C1 AND C2 should give result like NNYNY. C1 OR C2 should give result like YNYYY. Bit String Types in PostgreSQL may be what you are looking for. btw: what is linux 7? Please shed some light. Thanks Kumar ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Query becoming slower on adding a primary key
Tom Lane wrote:
[EMAIL PROTECTED] writes:
tradein_clients=# explain analyze select email_id ,email ,contact from
t_a a join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
Runs for Ever.
So what does plain explain say about it?
Oops sorry that was a valuable info i left. (sorry for delay too)
tradein_clients=# explain select email_id ,email ,contact from
t_a a join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
+-+
| QUERY
PLAN |
+-+
| Hash Join (cost=133741.48..224746.39 rows=328814
width=40) |
| Hash Cond: ("outer".email_id =
"inner".email_id) |
| -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
rows=749735 width=4) |
| Filter: (sub_id =
3) |
| -> Hash (cost=130230.99..130230.99 rows=324994
width=44) |
| -> Hash Join (cost=26878.00..130230.99 rows=324994
width=44) |
| Hash Cond: ("outer".email_id =
"inner".email_id) |
| -> Seq Scan on email_source f (cost=0.00..26159.21
rows=324994 width=4) |
| Filter: (source_id =
1) |
| -> Hash (cost=18626.80..18626.80 rows=800080
width=40) |
| -> Seq Scan on t_a a (cost=0.00..18626.80
rows=800080 width=40) |
+-+
(11 rows)
Time: 452.417 ms
tradein_clients=# ALTER TABLE t_a add primary key(email_id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"t_a_pkey" for table "t_a"
ALTER TABLE
Time: 7923.230 ms
tradein_clients=# explain select email_id ,email ,contact from t_a a
join email_source f using(email_id) join email_subscriptions
h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
+---+
| QUERY
PLAN |
+---+
| Hash Join (cost=106819.76..197824.68 rows=328814
width=40) |
| Hash Cond: ("outer".email_id =
"inner".email_id)
|
| -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
rows=749735 width=4) |
| Filter: (sub_id =
3)
|
| -> Hash (cost=103309.28..103309.28 rows=324994
width=44) |
| -> Merge Join (cost=0.00..103309.28 rows=324994
width=44) |
| Merge Cond: ("outer".email_id =
"inner".email_id) |
| -> Index Scan using t_a_pkey on t_a a
(cost=0.00..44689.59 rows=800080 width=40) |
| -> Index Scan using email_source_pkey on
email_source f (cost=0.00..52602.59 rows=324994 width=4) |
| Filter: (source_id =
1)
|
+---+
(10 rows)
Time: 2436.551 ms
tradein_clients=#
Regds
Mallah.
regards, tom lane
Re: [SQL] assistance on self join pls
Dear Darren, Your question is not very clear to me. On what columns do you want to aggregate? suppose u want to aggregate on outsite and inside ip you shud group by those columns and run a aggregate function like sum or avg etc , suppose u want the total traffic for every pair you can do this: select inside_ip,outside_ip , sum(outbound_bytes) as total_traffic from connection_data group by inside_ip,outside_ip ; Hope it helps. Regds Mallah. email lists wrote: Hi all, I have the following firewall connection data. datetime | protocol | port | inside_ip| outside_ip | outbound_count | outbound_bytes -+--+---++-- --++--- 2004-05-05 05:00:00 |6 |21 | 192.168.11.191 | 205.227.137.53 | 6 | 3881 2004-05-05 05:00:00 |6 | 22326 | 192.168.11.191 | 205.227.137.53 | 1 | 2592 2004-05-05 05:00:00 |6 | 38005 | 192.168.11.191 | 205.227.137.53 | 1 | 51286 2004-05-05 05:00:00 |6 | 51861 | 192.168.11.191 | 205.227.137.53 | 1 | 42460 2004-05-05 05:00:00 |6 | 52095 | 192.168.11.191 | 205.227.137.53 | 1 | 2558 2004-05-05 05:00:00 |6 | 59846 | 192.168.11.191 | 205.227.137.53 | 1 |118 2004-05-05 05:00:00 |6 | 60243 | 192.168.11.191 | 205.227.137.53 | 1 | 2092 2004-05-05 06:00:00 |6 |21 | 192.168.11.185 | 205.227.137.53 | 6 | 3814 2004-05-05 06:00:00 |6 | 29799 | 192.168.11.185 | 205.227.137.53 | 1 |118 2004-05-05 06:00:00 |6 | 30138 | 192.168.11.185 | 205.227.137.53 | 1 | 2092 2004-05-05 06:00:00 |6 | 30215 | 192.168.11.185 | 205.227.137.53 | 1 | 42460 2004-05-05 06:00:00 |6 | 51279 | 192.168.11.185 | 205.227.137.53 | 1 | 1332 2004-05-05 06:00:00 |6 | 52243 | 192.168.11.185 | 205.227.137.53 | 1 | 51286 2004-05-05 06:00:00 |6 | 60079 | 192.168.11.185 | 205.227.137.53 | 1 | 2558 I am wanting to aggregate / collapse each entry to something similar to: datetime | protocol | port | inside_ip| outside_ip | outbound_count | outbound_bytes -+--+---++-- --++--- 2004-05-05 05:00:00 |6 |21 | 192.168.11.191 | 205.227.137.53 | 12 | 104987 2004-05-05 06:00:00 |6 |21 | 192.168.11.185 | 205.227.137.53 | 12 | 103660 I have not had much success - any assistance greatly appreciated Darren ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Query becoming slower on adding a primary key
Even the first query used to run fine before but one fine day
it changed plans i think.
Regds
Mallah.
Rajesh Kumar Mallah wrote:
Tom Lane wrote:
[EMAIL PROTECTED] writes:
tradein_clients=# explain analyze select email_id ,email ,contact from
t_a a join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
Runs for Ever.
So what does plain explain say about it?
Oops sorry that was a valuable info i left. (sorry for delay too)
tradein_clients=# explain select email_id ,email ,contact from t_a a
join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
+-+
| QUERY
PLAN|
+-+
| Hash Join (cost=133741.48..224746.39 rows=328814
width=40) |
| Hash Cond: ("outer".email_id =
"inner".email_id) |
| -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
rows=749735 width=4) |
| Filter: (sub_id =
3)|
| -> Hash (cost=130230.99..130230.99 rows=324994
width=44)|
| -> Hash Join (cost=26878.00..130230.99 rows=324994
width=44) |
| Hash Cond: ("outer".email_id =
"inner".email_id) |
| -> Seq Scan on email_source f (cost=0.00..26159.21
rows=324994 width=4) |
| Filter: (source_id =
1) |
| -> Hash (cost=18626.80..18626.80 rows=800080
width=40) |
| -> Seq Scan on t_a a (cost=0.00..18626.80
rows=800080 width=40) |
+-+
(11 rows)
Time: 452.417 ms
tradein_clients=# ALTER TABLE t_a add primary key(email_id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"t_a_pkey" for table "t_a"
ALTER TABLE
Time: 7923.230 ms
tradein_clients=# explain select email_id ,email ,contact from t_a a
join email_source f using(email_id) join email_subscriptions
h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
+---+
|QUERY
PLAN |
+---+
| Hash Join (cost=106819.76..197824.68 rows=328814
width=40) |
| Hash Cond: ("outer".email_id =
"inner".email_id)
|
| -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
rows=749735 width=4)|
| Filter: (sub_id =
3)
|
| -> Hash (cost=103309.28..103309.28 rows=324994
width=44) |
| -> Merge Join (cost=0.00..103309.28 rows=324994
width=44) |
| Merge Cond: ("outer".email_id =
"inner".email_id) |
| -> Index Scan using t_a_pkey on t_a a
(cost=0.00..44689.59 rows=800080 width=40) |
| -> Index Scan using email_source_pkey on email_source
f (cost=0.00..52602.59 rows=324994 width=4) |
| Filter: (source_id =
1) |
+---+
(10 rows)
Time: 2436.551 ms
tradein_clients=#
Regds
Mallah.
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Query becoming slower on adding a primary key
Hi,
Is there any solution to this issue ? I am facing it every week.
Warm Regds
Mallah.
Rajesh Kumar Mallah wrote:
Tom Lane wrote:
[EMAIL PROTECTED] writes:
tradein_clients=# explain analyze select email_id ,email ,contact from
t_a a join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
Runs for Ever.
So what does plain explain say about it?
Oops sorry that was a valuable info i left. (sorry for delay too)
tradein_clients=# explain select email_id ,email ,contact from t_a a
join email_source f using(email_id) join email_subscriptions h
using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
+-+
| QUERY
PLAN|
+-+
| Hash Join (cost=133741.48..224746.39 rows=328814
width=40) |
| Hash Cond: ("outer".email_id =
"inner".email_id) |
| -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
rows=749735 width=4) |
| Filter: (sub_id =
3)|
| -> Hash (cost=130230.99..130230.99 rows=324994
width=44)|
| -> Hash Join (cost=26878.00..130230.99 rows=324994
width=44) |
| Hash Cond: ("outer".email_id =
"inner".email_id) |
| -> Seq Scan on email_source f (cost=0.00..26159.21
rows=324994 width=4) |
| Filter: (source_id =
1) |
| -> Hash (cost=18626.80..18626.80 rows=800080
width=40) |
| -> Seq Scan on t_a a (cost=0.00..18626.80
rows=800080 width=40) |
+-+
(11 rows)
Time: 452.417 ms
tradein_clients=# ALTER TABLE t_a add primary key(email_id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"t_a_pkey" for table "t_a"
ALTER TABLE
Time: 7923.230 ms
tradein_clients=# explain select email_id ,email ,contact from t_a a
join email_source f using(email_id) join email_subscriptions
h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ;
+---+
|QUERY
PLAN |
+---+
| Hash Join (cost=106819.76..197824.68 rows=328814
width=40) |
| Hash Cond: ("outer".email_id =
"inner".email_id)
|
| -> Seq Scan on email_subscriptions h (cost=0.00..70329.54
rows=749735 width=4)|
| Filter: (sub_id =
3)
|
| -> Hash (cost=103309.28..103309.28 rows=324994
width=44) |
| -> Merge Join (cost=0.00..103309.28 rows=324994
width=44) |
| Merge Cond: ("outer".email_id =
"inner".email_id) |
| -> Index Scan using t_a_pkey on t_a a
(cost=0.00..44689.59 rows=800080 width=40) |
| -> Index Scan using email_source_pkey on email_source
f (cost=0.00..52602.59 rows=324994 width=4) |
| Filter: (source_id =
1) |
+---+
(10 rows)
Time: 2436.551 ms
tradein_clients=#
Regds
Mallah.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Query becoming slower on adding a primary key [ SOLVED
HI, The problem was solved by reducing the effective_cache_size from 102400 to 10240 my total RAM is 4GB. Regds mallah. Tom Lane wrote: [EMAIL PROTECTED] writes: tradein_clients=# explain analyze select email_id ,email ,contact from t_a a join email_source f using(email_id) join email_subscriptions h using(email_id) where 1=1 and f.source_id =1 and h.sub_id = 3 ; Runs for Ever. So what does plain explain say about it? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Problem in Stored Procedures
Pradeepkumar, Pyatalo (IE10) wrote: Hi all, I m using Postgresql version 7.1.3-2. The create type and create function does work in PostgreSQL 7.4 without any modification. I guess the features you are trying to use are not supported in 7.1.x Regds Mallah. I have written a function which accepts 2 arguments and returns matching tuples from a table based on the arguments passed...but i am having problems in getting it work. This is my function - CREATE TYPE PointType AS(ParamId INTEGER,ParamName VARCHAR(5),Is_FixEnum BIT,Is_ExpandEnum BIT); CREATE FUNCTION PP_ReadParameter(integer,varchar) RETURNS setof PointType AS ' DECLARE rec PointType; BEGIN IF $1 IS NOT NULL THEN FOR rec IN SELECT ParamId, ParamName, Is_FixEnum, Is_ExpandEnum AttributeId, AttributeName,IsFixEnum,IsExpandEnum FROM Attributes WHERE AttributeId = $1 ORDER BY AttributeId LOOP RETURN NEXT rec; END LOOP; RETURN; END IF; ELSE IF $2 IS NOT NULL THEN FOR rec IN SELECT ParamId, ParamName, Is_FixEnum, Is_ExpandEnum AttributeId, AttributeName,IsFixEnum,IsExpandEnum FROM Attributes WHERE AttributeId = $2 ORDER BY AttributeId LOOP RETURN NEXT rec; END LOOP; RETURN; ELSE FOR rec IN SELECT ParamId, ParamName, Is_FixEnum, Is_ExpandEnum AttributeId, AttributeName,IsFixEnum,IsExpandEnum FROM Attributes ORDER BY AttributeId LOOP RETURN NEXT rec; END LOOP; RETURN; END IF; END IF; END; ' language 'plpgsql'; I get the error... psql:Procedures.sql:2: ERROR: parse error at or near "AS" (for CREATE TYPE command) psql:Procedures.sql:40: NOTICE: return type 'pointtype' is only a shell CREATE WHEN I EXECUTE THE FUNCTION USING SELECT (pp_readparameter(42,null)); ERROR: fmgr_info: function 0: cache lookup failed. any value inputs on why this is happening. With Best Regards Pradeep Kumar P J ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- regds Mallah. Rajesh Kumar Mallah +---+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---+ ---(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] Comparing tsearch2 vectors.
Hi,
We want to compare strings after stemming. Can anyone
tell me what is the best method. I was thinking to compare
the tsvector ,but there is no operator for that.
Regds
Mallah.
tradein_clients=# SELECT to_tsvector('handicraft exporters');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 710.315 ms
tradein_clients=#
tradein_clients=# SELECT to_tsvector('handicrafts exporter');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 400.679 ms
tradein_clients=# SELECT to_tsvector('Hi there') = to_tsvector('Hi there');
ERROR: operator does not exist: tsvector = tsvector
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
tradein_clients=#
--
regds
Mallah.
Rajesh Kumar Mallah
+---+
| Tradeindia.com (3,11,246) Registered Users |
| Indias' Leading B2B eMarketPlace |
| http://www.tradeindia.com/ |
+---+
---(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
Re: [SQL] Comparing tsearch2 vectors.
Dear Mantzios,
I have to get set of banners from database in
response to a search term. I want that the search term
be compared to the keyword corresponding to the
banners stored in database. current i am doing an
equality match but i woild like to do it after stemming
both the sides (serch term and keywords).
So that the banners for the adword say 'incense exporter' is
shown even if 'incenses exporter' or 'incense exporters' is
searched.
I hope i am able to clarify.
Regds
Mallah.
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Hi,
We want to compare strings after stemming. Can anyone
tell me what is the best method. I was thinking to compare
the tsvector ,but there is no operator for that.
I'd tokenize each string and then apply lexize() to get the
equivalent stemified
word, but what exactly are you trying to accomplish?
Regds
Mallah.
tradein_clients=# SELECT to_tsvector('handicraft exporters');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 710.315 ms
tradein_clients=#
tradein_clients=# SELECT to_tsvector('handicrafts exporter');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 400.679 ms
tradein_clients=# SELECT to_tsvector('Hi there') = to_tsvector('Hi there');
ERROR: operator does not exist: tsvector = tsvector
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
tradein_clients=#
--
regds
Mallah.
Rajesh Kumar Mallah
+---+
| Tradeindia.com (3,11,246) Registered Users |
| Indias' Leading B2B eMarketPlace |
| http://www.tradeindia.com/ |
+---+
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Comparing tsearch2 vectors.
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Dear Mantzios,
I have to get set of banners from database in
response to a search term. I want that the search term
be compared to the keyword corresponding to the
banners stored in database. current i am doing an
equality match but i woild like to do it after stemming
both the sides (serch term and keywords).
You could transform your search terms so that there is the "&"
separator between them. (& stands for "AND").
E.g. "handicrafts exporter" becomes "handicrafts&exporter"
And then
select * from where idxfti @@ to_tsquery();
But i do not want 'handicraft exporters of delhi' to pop out if i search
for 'handicrafts exporters' whereas
SELECT to_tsvector('handycrafts exporters of delhi') @@
to_tsquery('handycraft&exporting');
will be true.
Regds
Mallah.
where idxfti is your tsvector column.
E.g.
# SELECT to_tsvector('handycrafts exporters') @@ to_tsquery('handycraft&exporting');
?column?
--
t
(1 row)
So that the banners for the adword say 'incense exporter' is
shown even if 'incenses exporter' or 'incense exporters' is
searched.
I hope i am able to clarify.
Regds
Mallah.
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Hi,
We want to compare strings after stemming. Can anyone
tell me what is the best method. I was thinking to compare
the tsvector ,but there is no operator for that.
I'd tokenize each string and then apply lexize() to get the
equivalent stemified
word, but what exactly are you trying to accomplish?
Regds
Mallah.
tradein_clients=# SELECT to_tsvector('handicraft exporters');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 710.315 ms
tradein_clients=#
tradein_clients=# SELECT to_tsvector('handicrafts exporter');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 400.679 ms
tradein_clients=# SELECT to_tsvector('Hi there') = to_tsvector('Hi there');
ERROR: operator does not exist: tsvector = tsvector
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
tradein_clients=#
--
regds
Mallah.
Rajesh Kumar Mallah
+---+
| Tradeindia.com (3,11,246) Registered Users |
| Indias' Leading B2B eMarketPlace |
| http://www.tradeindia.com/ |
+---+
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Secure DB Systems - How to
Sarah Tanembaum wrote: I was wondering if it is possible to create a secure database system usingPostgreSQL/PHP combination? I have the following in mind: I wanted to store all my( and my brothers and sisters) important document information such as birth certificate, SSN, passport number, travel documents, insurance(car, home, etc) document, and other important documents imagined in the database. The data will be entered either manually and/or scanned(with OCR). I need to be able to search on all the fields in the database. We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I maintained. The data should be synchronize/replicate between those computers. Well, so far it is easy, isn't it? Here's my question: a) How can I make sure that it secure so only authorized person can modify/add/delete the information? Beside transaction logs, are there any other method to trace any transaction(kind of paper trail)? There can be multiple solutions to your problem. The security and logging may be implemented either at database level or application level. That is a call you have to take. If you consider the database to take care of security and logging you could do the following. 1. create a database user for each of your family members 2. ask the memebers to login to your application using their own id. 3. Use that id for connecting to the database using php. the security at table level can be managed by various GRANT commands. the security at row level can be done using a mechanism methods describe in the -general mailling list (search: "row level grants"). For logging changes to your tables you can create audit trail of all the tables in question by using triggers or enbale logging of sql statements (with current user display) in postgresql server. u may consider: http://gborg.postgresql.org/project/audittrail/projdisplay.php although i have not used it myself. Assuming there are 3 step process to one enter the info e.g: - One who enter the info (me) - One who verify the info(the owner of info) - One who verify and then commit the change! How can I implement such a process in PostgreSQL and/or PHP or any other web language? I think such a moderation should be implemented at application level. b) How can I make sure that no one can tap the info while we are entering the data in the computer? (our family are scattered within US and Canada) you may run yor web application using https:// rather than http:// and you may enable ssl in postgresql for securing the communication between application and database. c) Is it possible to securely synchronize/replicate between our computers using VPN? Does PostgreSQL has this functionality by default? Slony and many other replication solution exists for asyncronous replication. Hope it helps a bit. Regds Mallah. d) Other secure method that I have not yet mentioned. Anyone has good ideas on how to implement such a systems? Thanks ---(end of broadcast)--- TIP 8: explain analyze is your friend -- regds Mallah. Rajesh Kumar Mallah +---+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---+ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Comparing tsearch2 vectors.
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Dear Mantzios,
I have to get set of banners from database in
response to a search term. I want that the search term
be compared to the keyword corresponding to the
banners stored in database. current i am doing an
equality match but i woild like to do it after stemming
both the sides (serch term and keywords).
You could transform your search terms so that there is the "&"
separator between them. (& stands for "AND").
E.g. "handicrafts exporter" becomes "handicrafts&exporter"
And then
select * from where idxfti @@ to_tsquery();
But i do not want 'handicraft exporters of delhi' to pop out if i search
for 'handicrafts exporters' whereas
SELECT to_tsvector('handycrafts exporters of delhi') @@
to_tsquery('handycraft&exporting');
will be true.
Define what you want, and then read tsearch2 userguide.
I'm sure you'll find your way :)
The requirement is different than full text search.
I am not searching a word in a collection of words (text)
rather comparing two strings after all the words in those
strings are stemmed. Hope my requirement is clear now.
Regds
mallah.
Regds
Mallah.
where idxfti is your tsvector column.
E.g.
# SELECT to_tsvector('handycrafts exporters') @@ to_tsquery('handycraft&exporting');
?column?
--
t
(1 row)
So that the banners for the adword say 'incense exporter' is
shown even if 'incenses exporter' or 'incense exporters' is
searched.
I hope i am able to clarify.
Regds
Mallah.
Achilleus Mantzios wrote:
O kyrios Rajesh Kumar Mallah egrapse stis Jul 12, 2004 :
Hi,
We want to compare strings after stemming. Can anyone
tell me what is the best method. I was thinking to compare
the tsvector ,but there is no operator for that.
I'd tokenize each string and then apply lexize() to get the
equivalent stemified
word, but what exactly are you trying to accomplish?
Regds
Mallah.
tradein_clients=# SELECT to_tsvector('handicraft exporters');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 710.315 ms
tradein_clients=#
tradein_clients=# SELECT to_tsvector('handicrafts exporter');
+---+
|to_tsvector|
+---+
| 'export':2 'handicraft':1 |
+---+
(1 row)
Time: 400.679 ms
tradein_clients=# SELECT to_tsvector('Hi there') = to_tsvector('Hi there');
ERROR: operator does not exist: tsvector = tsvector
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
tradein_clients=#
--
regds
Mallah.
Rajesh Kumar Mallah
+---+
| Tradeindia.com (3,11,246) Registered Users |
| Indias' Leading B2B eMarketPlace |
| http://www.tradeindia.com/ |
+---+
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] Arbitrary precision arithmatic with pgsql
Hi, The docs says that numeric type supports numbers upto any precision 8.1.2. Arbitrary Precision Numbers The type numeric can store numbers with up to 1000 digits of precision and perform calculations exactly. It is especially recommended for storing monetary amounts and other quantities where exactness is required. However, the numeric type is very slow compared to the floating-point types described in the next section. However tradein_clients=# SELECT cast(2^100 as numeric); +-+ | numeric | +-+ | 126765060022823 | +-+ (1 row) Time: 1036.063 ms Naturally there is a loss of information here. So my question is 1. Does the specs not require pgsql to print a warning or info , will it not be considered silient truncation of data. 2. Is there any way to do such calculation using pgsql, i understand bc is a better tool for it. Warm Regards Rajesh Kumar Mallah. -- regds Mallah. Rajesh Kumar Mallah +---+ | Tradeindia.com (3,11,246) Registered Users | | Indias' Leading B2B eMarketPlace | | http://www.tradeindia.com/ | +---+ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
