Re: [SQL] 'no wait' option for locks
Hi, Why not the program create a lock file on start and delete it when finished. regds mallah. > Hi... > > anyone knows a workaround for Oracles "no wait" option in "SELECT ... > FOR UPDATE"? > > I wrote a programm which runs twice. Both poll the pgsql database all 5 > seconds and make an "SELECT ... FOR UPDATE" for the first row found. > After some work and commit the next row is processed. The second task > waits until the first hast commited, but it should detect the lock and > continue work with next row. > > How can i detect this lock? Oracle returns with "no wait" option an sql > error code. > > Any hints? > > best regards > Daniel Stuewe > > > ---(end of > broadcast)--- TIP 6: Have you searched our list > archives? > > http://archives.postgresql.org - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(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] STATISTICS?
do you need http://www.postgresql.org/idocs/index.php?monitoring-stats.html ? Folks, > > Can anyone point me to documentation on the new STATISTICS features of 7.2? I can't >seem to > find anything in the online docs, except the barest mention of ALTER TABLE SET >STATISTICS > (which doesn't explain what to do with the info). > > -Josh Berkus > > ---(end of broadcast)--- TIP 6: Have >you > searched our list archives? > > http://archives.postgresql.org - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] getting ILIKE or ~* to use indexes....
Thanks Josh
I normally use tsearch for full text search i will probably use that
or may be this what u suggested.
regds
mallah.
> Rajesh,
>
>> 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%' ;
>
> Well, for an *anchored* case-insensitive search, you can create an index on
>lower(field_name)
> to use an index.
>
> CREATE INDEX idx_uni_co_name ON unifed_data(lower(co_name));
> SELECT co_name FROM unified_data where
> lower(co_name) LIKE (lower('rajesh') || '%') ;
>
> And that will use the index.
>
> However, what you are doing is an *unanchored* text search, meaning that you are
>searching for
> 'rajesh' anywhere in the field. No standard index can help you with that.
>
> Instead, you should look into Full Text Search tools. There's a simple one in
>/contrib in the
> Postgresql source, and an more robust one
> available from the OpenFTS project.
>
> -Josh Berkus
-
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] tree structures in sql - my point of view (with request
Yep! ltree is Fst , and i use it at the moment. But will it work in INGRES,DB2 or ORACLE ? what if tommorow my boss ask me to use ORACLE? I have similar issues in using intarray & arrays in PGSQL though reasons of shifting to others dbs are diminishing with every major release of PG ;-) regds mallah. > While I don't have a time to comment your message I want to point to contrib/ltree >package > which is extremely fast :-) > > http://www.sai.msu.su/~megera/postgres/gist/ltree > > Oleg > On Tue, 3 Sep 2002, Hubert depesz Lubaczewski wrote: > >> hi >> i recently spent some time on tree-structures in sql. >> i started with simple id/parent_id approach, used by nearly everyone, then i >stopped at joe >> celko's nested sets, but i found it not very usable. >> then i found my own (maybe someone wrote it before, but i haven't read it, so idea >is mine) >> way. >> in my way we have two tables: >> create table data (id serial, name text); >> create table structure (parent_id int8, child_id int8, depth int8); >> >> structure table represents all paths in tree. >> for example for this tree: >> >>sql >> / \ >> postgresql oracle-__ >> |/|\ >> linux scolinux windows >> / \ >>glibc1 glibc2 >> >> (sorry for used data - it is just template, and personally i don't like oracle). >> so, for this tree we would populate the tables this way: >> data: >> id | name >> + >> 1 | sql >> 2 | postgresql >> 3 | oracle >> 4 | linux >> 5 | sco >> 6 | linux >> 7 | windows >> 8 | glibc1 >> 9 | glibc2 >> >> structure: >> parent_id | child_id | depth >> ---+--+--- >> 1 |1 | 0 >> 2 |2 | 0 >> 3 |3 | 0 >> 4 |4 | 0 >> 5 |5 | 0 >> 6 |6 | 0 >> 7 |7 | 0 >> 8 |8 | 0 >> 9 |9 | 0 >> 1 |2 | 1 >> 1 |3 | 1 >> 1 |4 | 2 >> 2 |4 | 1 >> 1 |5 | 1 >> 1 |6 | 1 >> 1 |7 | 1 >> 3 |5 | 2 >> 3 |6 | 2 >> 3 |7 | 2 >> 1 |8 | 3 >> 1 |9 | 3 >> 3 |8 | 2 >> 3 |9 | 2 >> 6 |8 | 1 >> 6 |9 | 1 >> >> (records with depth 0 are technologically not necessary, but they simplify and >speedup some >> queries). >> >> with this data layout (easily indexable) you can fetch any data with just one >select statement >> (no recursion needed in any case): >> - fetching parent >> - fetching childs >> - fetching "from id up" >> - fetching "from id down" >> also when you need to get indirect parents/childs or when you need only some of the >data (from >> me, up, but not more then to my >> grand-grand-grand-father). >> >> i'd like to get some comments on this - how do you see my idea, is it worth it, do >you know >> any better way to store trees in sql? >> >> best regards >> >> depesz >> >> > > 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 2: you >can get off > all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] new calculated column
HI. simple (tedious) solution would be do fire 20 sqls. update tabel set f1= where f2='some varchar1'; update tabel set f1= where f2='some varchar2'; update tabel set f1= where f2='some varchar3'; incase there is some the integer is a function of varchar u may translate to the corresponding SQL. regds mallah. > Hi; > > I've got a table with two fields and about 3000 rows, the second one is a character >field, what > can have about twenty different values; of course these values are repeated a lot of >times in > the table. I need to create a new column of type integer, whose value depens on the >character > fields. The values of the new column are not important, the important thing is who >can I create > this column and assign a different integer to a different char value in the other >column. > Thanks > -- > Javier > > --- > > ---(end of broadcast)--- TIP 4: >Don't 'kill -9' > the postmaster - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.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] Displaying current queries on the database?
its possible, select * from pg_stat_activity after AS connect as postgres. THOUGH NEEd to configure postgresql.conf accordigly regds mallaH > I was wondering if there is a way to display all of the current queries that are >being > submitted to the database. Thanks in advance. > > -Sloan Bowman > > > ---(end of broadcast)--- TIP 4: >Don't 'kill -9' > the postmaster - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(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] Drop NOT NULL constraint !!!
do a \d tablename for the name of the contraint. say its $1 the do psql> alter table drop contstraint "$1" RESTRICT; > Does anybody could tell me how to drop a constraint on a column where no name was >provided to > the constraint? How does Pg name constraints? > > Thanks > -- > Renê Salomão > Ibiz Tecnologia -- www.ibiz.com.br > > > ---(end of broadcast)--- TIP 6: Have >you > searched our list archives? > > http://archives.postgresql.org - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] How to recover Data
Sridhar such questions shud be posted to pgsql-admin list. generally in case of power failure pgsql is unable to remove its pid file., if u are using RPM based installation the file shud be in /var/lib/pgsql/ in the name of postmaster.pid If such a file exists postmaster will refuse to start. in such case first remove that file. then start postmaster using # /etc/rc.d/init.d/postgresql start or better # su - postgres $ pg_ctl -l logfile -D /var/lib/pgsql/data observe the recovery messages in logfile using $tail -f logfile in general such recoveries mostly succeeds and in no case shud be inturrupted to avoid further (grave) complications. Good Luck with your data, regds mallah. PS: its not easy to loose data with pgsql ;-) > I had a m/c with Postgres 7.2 and huge amount of data. on Power > failure and restart of the m/c pgsql refused connect to any of the database. Being >an invoice i > took a tar of the data dir. > I tried reinstalling PGSQL and copied the data dir to the same dir where the new >data is being > stored. ie > cp /backup/pgsql/data /var/lib/pgsql/data Only data directory is not sufficeint u need the pg_xlog directory as well. In any case such backups in prociple are not ok unless postmaster is shut down during the cp. > > When i connect to the old database i am able to connect but when i do a \d to list >the tables i > get no relations found. Also when i do > select * from a table i am able to get the structure but it has no records in it. > WHen i check the same info in pg_database to check if the old database entry is >present i dont > find it. > Can someone help me as to how to recover my data??? > Basically what i feel is update the pg_tables. > Thanks and rgds > Padmanab > > ---(end of broadcast)--- TIP 6: Have >you > searched our list archives? > > http://archives.postgresql.org - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] escape single quote in INSERT command
> Hi Group -
>
> I have a perl application for a registration form.
Same Here,
Why dont' you use prepare and execute in case you are using DBI
same program is like this.
$dbh = DBI -> connect ( "..");
$sth = $dbh -> prepare("insert into tab (a,b) values (?,?)");
$sth -> execute($a , $b );
$sth -> finish();
$dbh -> commit();
$dbh -> disconnect();
regds
mallah.
I'd like to put escape characters in my
> insert command to accommodate for '
> (i.e. O'Brien, O'Malley, etc). I've tired double quotes, single
> quotes, back tick, forward ticks, curly bracket, round brackets - no success.
>
>
> Thanks, dave
>
> ---(end of broadcast)--- TIP 6: Have
>you
> searched our list archives?
>
> http://archives.postgresql.org
-
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to rename and drop a column in pg7.3?
Hmmm... both are supported in 7.3.x ALTER TABLE rtfm RENAME f1 to f2 ; ALTER TABLE rtfm DROP COLUMN f3; hope it helps. regds mallah. > > I need to rename and remove columns of a table. Is any new way to accomplish the two >tasks in > 7.3? I have searched the online document and don't see any related information. > > Thanks, > > Vernon > > > > ---(end of broadcast)--- TIP 2: you >can get off > all lists at once with the unregister command >(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] returning table from a function
Hmm.. do you mean functions returning sets then http://techdocs.postgresql.org/guides/SetReturningFunctions is for you. regds mallah. - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Help with a query for charting
you could keep a table with list of all possible days in a month.
left join that to the results you get from query below this will return NULL
for days where there is no data. NULL could then easily converted to 0 using CASE
or COALESCE.
regds
mallah.
> I'm trying to do a query to count the number of tickets opened on each day of a
>month. It'll
> always be from the 1st to the end of the month. This does it:
>
> SELECT TO_CHAR(DATE_TRUNC('day',date_opened),'DD') AS day, COUNT(*)
>FROM ticket GROUP BY DATE_TRUNC('day', date_opened)
>WHERE ;
>
> But it doesn't give me a zero for the days when no tickets were opened - which I
>really need
> because I want to graph the result.
>
> I could do this in the application code but that doesn't seem right.
>
> Help would be great.
>
> Andrew
>
>
> ---(end of broadcast)--- TIP 4:
>Don't 'kill -9'
> the postmaster
-
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] cannot EXPLAIN query...
Hi ,
This is to confirm that the EXPLAIN problem
does not occur anymore after successfully upgrading
to 7.3.1 from 7.3.0
Thanks everyone.
Regards
Mallah.
explain select * from shippers1 where city='DELHI';
QUERY
PLAN---
Nested Loop (cost=0.00..26461.22 rows=1 width=339)
-> Subquery Scan b (cost=0.00..26448.53 rows=477 width=307)
Filter: (city = 'DELHI'::character varying)
-> Append (cost=0.00..26448.53 rows=477 width=307)
-> Subquery Scan "*SELECT* 1" (cost=0.00..6739.42 rows=249 width=307)
-> Index Scan using eyp_listing_category_id,
eyp_listing_category_id,
eyp_listing_category_id, eyp_listing_category_id,
eyp_listing_category_id on
eyp_listing (cost=0.00..6739.42 rows=249 width=307)
Index Cond: ((category_id = 1142) OR (category_id = 1143) OR
(category_id = 1145) OR (category_id = 1259) OR
(category_id = 1161)) Filter: ((amount > 0) AND (status =
'a'::character varying) AND (email
IS NOT NULL)) -> Subquery Scan "*SELECT* 2"
(cost=0.00..9288.33 rows=77 width=286)
-> Seq Scan on iid_listing (cost=0.00..9288.33 rows=77
width=286)
Filter: (((category_id = 1142) OR (category_id = 1143) OR
(category_id
= 1145) OR (category_id = 1259) OR (category_id = 1161))
AND (amount >
0) AND (status = 'a'::character varying) AND (email IS NOT
NULL)) -> Subquery Scan "*SELECT* 3" (cost=10413.26..10420.79 rows=151
width=248)
-> Unique (cost=10413.26..10420.79 rows=151 width=248)
-> Sort (cost=10413.26..10417.02 rows=1506 width=248)
Sort Key: branding_master.company_id
-> Hash Join (cost=339.35..10333.74 rows=1506
width=248)
Hash Cond: ("outer".company_id =
"inner".company_id)
-> Seq Scan on branding_master
(cost=0.00..7171.80
rows=32985 width=242)
Filter: ((old_company_id = 0) AND (status =
'a'::character varying) AND (email IS NOT
NULL) AND
(eyp_paid IS NULL) AND (iid_paid IS
NULL)) -> Hash (cost=331.00..331.00 rows=3339
width=6)
-> Seq Scan on branding_sp_category
(cost=0.00..331.00 rows=3339 width=6)
Filter: ((category_id = 1142) OR
(category_id =
1143) OR (category_id = 1145) OR
(category_id =
1259) OR (category_id = 1161)) ->
Index Scan using approved_profiles_id_key on approved_profiles a (cost=0.00..5.30
rows=1
width=15) Index Cond: ((a.id = "outer".id) AND (a.source = "outer".source))
(24 rows)
tradein_clients=#
>
>
> 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
> m
[SQL] simulating partial fkeys..
hi , Is there any way to enforce fkeys only on subset of the table something on the lines of unique partial indexes or any work around ? (on update or insert trigger is the only thing i can think of) regds mallah. - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Getting rid of accents..
Is there any easy way for converting accented text to closest text without accents in postgresql ? eg: BÂLÂ MORGHÂB to BALA MORGHAB Regds Mallah. - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.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] Getting rid of accents..
Thanks Ian ,
Indeed its the simplest/easiest solution to this problem i feel.
i did tried to_ascii function but was not specifying 'LATIN1'
do i was getting error
tradein_clients=# SELECT to_ascii('BÂLÂ MORGHÂB' );
ERROR: pg_to_ascii(): unsupported encoding from SQL_ASCII
would u like to explain it ?
Then i created a new database in latin1 encoding loaded the data
used to_ascii to convert , copied the data to new file and reloaded
back to original database ;-) .oO (tiring )
Lucas Thanks for that perl stuff too i will use it in some program
that stuffs arbitary text into database.
Regds
Mallah.
> On Tuesday 27 May 2003 22:55, [EMAIL PROTECTED] wrote:
>> Is there any easy way for converting accented text to
>> closest text without accents in postgresql ?
>>
>> eg:
>>
>> BÂLÂ MORGHÂB to BALA MORGHAB
>
> Have you looked at to_ascii()? Something along the lines of
>
> select to_ascii('Â', 'LATIN1')
>
>
> Ian Barwick
> [EMAIL PROTECTED]
>
>
> ---(end of broadcast)--- TIP 2: you
> can get off
> all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
-
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.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] disabling triggers
> Mallah, > >> Is setting reltriggers=0 on pg_class an accepatble way of >> disabling triggers on a table temporarily? Ok , but someone on list was scary few months back. > > Yes. pg_dump does this. > >> secondly is it worth having commands like >> alter trigger inactive; >> alter trigger active ; >> i feel such commands exists on Oracle. > > No, since DROP TRIGGER ... CREATE TRIGGER does the same thing in Postgres. Agreed , but there is no easy way to view current trigger defination of existing triggers before dropping from psql . One has to go the long way of pg_dump -s -t tabname to view a list. regds mallah. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] disabling triggers
> Mallah, > >> >> Is setting reltriggers=0 on pg_class an accepatble way of >> >> disabling triggers on a table temporarily? >> >> Ok , but someone on list was scary few months back. > > I've done it many times without a problem. The trick is re-setting the triggers to > the > correct number when you're done. See the scripts that pg_dump -a creates for a > good example. > >> Agreed , but there is no easy way to view current trigger defination of existing >> triggers >> before dropping from psql . > > So? Create a perl script. It can: > 1) query the system tables to find out the trigger definintion > (you'll need pg_trigger, pg_proc, and pg_type) > 2) generate a script to restore all the triggers to be used later; 3) drop all the > triggers > > Of course, setting reltriggers=0 is probably a lot easier. in past we tried creating a plpgsql function that takes relname and schema and try setting reltriggers=0 on disabling and = (select count(*) from pg_trigger for that relname and schema) on enabling . we got stuck somewhere and abandoned it , do u think above approach would work in principle ? i will retry it tommorw though and post the results. regds mallah. > > -- > -Josh Berkus > Aglio Database Solutions > San Francisco > > > ---(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 - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.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] max length of sql select statement ?
Depending on ur original problem EXISTS or IN may be usable EXISTS is efficient and IN can be used efficiently in 7.4 version of postgresql regds mallah. > Hi All! > > I was searching the archive and was wondering why nobody asked this > strange(!) question (or I've not found it?): > > "What is the max allowed length of a sql statement or query?" > I want to combine hundrets or thousands 'OR' within a select statement. > Possible or not (e.g: SELECT * FROM table WHERE col='a' OR col='b' OR > col='c' OR .. ) > > This would be a very "dirty" or some would say, a "horrible" solution; > > but how are you searching through your table with sql, when your SELECT > is against a collection of different elements (e.g. Array of Strings, > which should fit on one column and return all records which fit) > > Hope for help > cheers Markus - sql beginner ;-) > > > > > ---(end of > broadcast)--- TIP 1: subscribe and unsubscribe > commands go to [EMAIL PROTECTED] - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] max length of sql select statement ?
> On Mon, 2003-07-07 at 17:57, [EMAIL PROTECTED] wrote:
>> Depending on ur original problem EXISTS or IN may be usable
>> EXISTS is efficient and IN can be used efficiently in 7.4 version of
>> postgresql
>
> Could be a solution?!
> The question is - how long could the IN be?
>
> I mean, if I write something like:
> SELECT * FROM table WHERE columnX IN ('a', 'b', 'c', 'd', 'e', ... );
> How long can the collection (list) within IN be? Also thousands of
> elements?
Well i DO NOT know the exact limit.
May be someone else can answer it accurately.
But you could produce the list within IN using a subselect
that again depends on the exact problem.
regds
Mallah.
>
> And what means efficient? Goes the DB only once through the table?
>
> Cheers, Markus
>
>
>
>
> ---(end of
> broadcast)--- TIP 2: you can get off all lists
> at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
-
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] disabling triggers
Hi, Is setting reltriggers=0 on pg_class an accepatble way of disabling triggers on a table temporarily? secondly is it worth having commands like alter trigger inactive; alter trigger active ; i feel such commands exists on Oracle. sometime we need to bulk update tables from backdoor and we feel the trigger execution to be unneccessary. regds mallah. - Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Query becoming slower on adding a primary key
Hi ,
After adding a primary key in one of the participant tables
the query never finishes. The live table has a primary key
so it cannot be removed. I made a copy of the live table
using create table t_a as select * from tab. the query works
fine . when i ad the pkey like i have in the live table it does
not work. Can anyone please help me with this problem?
below are the details.
thanks in advance.
Regds
Mallah.
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 ;
QUERY PLAN
---
Hash Join (cost=134818.15..221449.12 rows=306921 width=40) (actual
time=9457.000..17700.227 rows=283763 loops=1)
Hash Cond: ("outer".email_id = "inner".email_id)
-> Seq Scan on email_subscriptions h (cost=0.00..70323.77 rows=746257
width=4) (actual time=0.054..3434.639 rows=746721 loops=1)
Filter: (sub_id = 3)
-> Hash (cost=131485.92..131485.92 rows=308491 width=44) (actual
time=9456.757..9456.757 rows=0 loops=1)
-> Hash Join (cost=26878.00..131485.92 rows=308491 width=44)
(actual time=2293.378..8978.407 rows=299873 loops=1)
Hash Cond: ("outer".email_id = "inner".email_id)
-> Seq Scan on email_source f (cost=0.00..26119.84
rows=308491 width=4) (actual time=0.123..1094.661
rows=317504 loops=1)
Filter: (source_id = 1)
-> Hash (cost=18626.80..18626.80 rows=800080 width=40)
(actual time=2275.979..2275.979 rows=0 loops=1)
-> Seq Scan on t_a a (cost=0.00..18626.80
rows=800080 width=40) (actual time=0.009..1297.728
rows=800080 loops=1)
Total runtime: 17856.763 ms
(12 rows)
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: 6322.116 ms
tradein_clients=# VACUUM analyze t_a;
VACUUM
Time: 809.840 ms
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.
ROW COUNTS:
t_a : 8,00,080
email_source: 15,45,056
email_subscriptions: 40,41,133
Structures:
tradein_clients=# \d email_source
Table "public.email_source"
Column | Type | Modifiers
---+-+---
email_id | integer | not null
source_id | integer | not null
Indexes:
"email_source_pkey" primary key, btree (email_id, source_id)
"email_source_sid" btree (source_id)
Foreign-key constraints:
"$1" FOREIGN KEY (source_id) REFERENCES sources(source_id) ON UPDATE
CASCADE ON DELETE CASCADE
\d t_a
Table "public.t_a"
Column | Type | Modifiers
---+--+---
email_id | integer | not null
userid| integer |
email | character varying(100) |
country | character varying(100) |
city | character varying(50)|
contact | character varying(100) |
last_sent | timestamp with time zone |
pref | character varying(1) |
website | character varying(255) |
address | text |
\d email_subscriptions
Table "public.email_subscriptions"
Column | Type | Modifiers
--+-+---
email_id | integer | not null
sub_id | integer | not null
Indexes:
"email_subscriptions_pkey" primary key, btree (email_id, sub_id)
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[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?
this kind of joining has been termed "explicit JOIN syntax" by pgsql 7.3 docs. I personally feel it makes ur SQL look uglier and complicated. i feel the WHERE caluse shud contain the genuine filters of result set not the ones which could be a part of JOIN syntax itself. (its personal view though) you may refer to the DOCs below on postgresqls' website. http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=explicit-joins.html . excerpt from the docs. When a query only involves two or three tables, there aren't many join orders to worry about. But the number of possible join orders grows exponentially as the number of tables expands. Beyond ten or so input tables it's no longer practical to do an exhaustive search of all the possibilities, and even for six or seven tables planning may take an annoyingly long time. When there are too many input tables, the PostgreSQL planner will switch from exhaustive search to a genetic probabilistic search through a limited number of possibilities. (The switch-over threshold is set by the GEQO_THRESHOLD run-time parameter described in the PostgreSQL 7.3 Administrator's Guide.) The genetic search takes less time, but it won't necessarily find the best possible plan. regds mallah. On Friday 21 February 2003 07:00 pm, Rafal Kedziorski wrote: > hi, > > I have 8 tables and this query: > > select u.users_id, m.name as mandant_name, u.login_name, u.password, > u.first_name, u.last_name, u.creation_date, g.name as groups_name, > ae.acl_entry_id, a.name as acl_name, p.name as permission_name > from mandant m, users_2_groups u2g, groups g, users u, permission p, > acl a, acl_entry ae, groups_2_acl_entry g2ae > where m.mandant_id = u.mandant_id and > u2g.groups_id = g.groups_id and > u2g.users_id = u.users_id and > g2ae.groups_id = g.groups_id and > g2ae.acl_entry_id = ae.acl_entry_id and > ae.acl_id = a.acl_id and > ae.permission_id = p.permission_id > > I'm not using JOIN for get this information. would be JOIN a better sql > programming style? faster? > > > Best Regards, > Rafal > > > sql script: > > DROP TABLE groups_2_acl_entry; > DROP TABLE users_2_groups; > DROP TABLE groups; > DROP TABLE users; > DROP TABLE acl_entry; > DROP TABLE permission; > DROP TABLE acl; > DROP TABLE language; > DROP TABLE mandant; > DROP TABLE license; > DROP TABLE firm; > > > > CREATE TABLE firm ( > firm_id numeric(20, 0) NOT NULL, > name varchar(40) NOT NULL, > CONSTRAINT firm_pkey PRIMARY KEY (firm_id) > ) WITH OIDS; > > INSERT INTO firm VALUES (1, 'polonium'); > > > > CREATE TABLE license ( > license_id numeric(20, 0) NOT NULL, > key varchar(100) NOT NULL, > creation_date timestamp NOT NULL, > valid_from timestamp NOT NULL, > expired timestamp, > CONSTRAINT license_pkey PRIMARY KEY (license_id) > ) WITH OIDS; > > INSERT INTO license VALUES (1, 'NOT AT THIS TIME - SHOULD BE GENERATED', > now(), now(), NULL); > > > > CREATE TABLE mandant ( > mandant_id numeric(20, 0) NOT NULL, > firm_id numeric(20, 0) NOT NULL, > license_id numeric(20, 0) NOT NULL, > parent_id numeric(20, 0), > name varchar(20) NOT NULL, > creation_date timestamp NOT NULL, > CONSTRAINT mandant_pkey PRIMARY KEY (mandant_id), > CONSTRAINT fk_firm FOREIGN KEY (firm_id) REFERENCES firm (firm_id), > CONSTRAINT fk_license FOREIGN KEY (license_id) REFERENCES license > (license_id), > CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES mandant > (mandant_id) > ) WITH OIDS; > > INSERT INTO mandant VALUES (1, 1, 1, NULL, 'polonium', now()); > > > > CREATE TABLE language ( > language_id int2 NOT NULL, > lang_short char(2) NOT NULL, > lang_long varchar(20) NOT NULL, > CONSTRAINT language_pkey PRIMARY KEY (language_id) > ) WITH OIDS; > > CREATE UNIQUE INDEX language_lang_short_idx ON language (lang_short); > CREATE UNIQUE INDEX language_lang_idx ON language (lang_short, lang_long); > > INSERT INTO language VALUES (1, 'de', 'deutsch'); > INSERT INTO language VALUES (2, 'en', 'english'); > > > > CREATE TABLE acl ( > acl_id int2 NOT NULL, > name varchar(20) NOT NULL, > description varchar(200), > CONSTRAINT acl_pkey PRIMARY KEY (acl_id) > ) WITH OIDS; > > CREATE UNIQUE INDEX acl_name_idx ON acl (name); > > INSERT INTO acl VALUES (1, 'mmcms.access', 'acl for login module'); > INSERT INTO acl VALUES (2, 'mmcms.system', 'acl for system module'); > INSERT INTO acl VALUES (3
[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.
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions may be gotten over by wrapping the first query result in a subselect. not sure though if its proper. regds mallah. test=# SELECT * from ( SELECT distinct on (a.id) b.id ,courseid,name,submission from course a join history b on (a.id=b.courseid) ) as results order by results.submission desc; ++--+---++ | id | courseid | name| submission | ++--+---++ | 3 | 104 | Maths | 2002-04-30 | | 2 | 102 | Chemistry | 2002-02-17 | | 1 | 101 | Physics | 2002-01-20 | ++--+---++ (3 rows) On Monday 24 February 2003 10:48 am, Eddie Cheung wrote: > Hi, > > I was very glad to see the replies from you guys this > morning. The two suggested SQL queries did not return > the expected results, but will help me to explain the > problem I am facing further. > > 1) Josh suggested the following query. (I have made > minor changes by adding the table name to each field) > >SELECT history.id, history.courseId, course.name, > MAX(history.submission) AS submission >FROM history JOIN course ON history.courseId = > course.Id >GROUP BY history.id, history.courseId, course.name >ORDER BY course.name; > > The results returned are: > id | courseid | name| submission > +--+---+ > 2 | 102 | Chemistry | 2002-02-17 > 4 | 102 | Chemistry | 2002-02-22 > 3 | 104 | Maths | 2002-04-30 > 5 | 104 | Maths | 2002-03-15 > 6 | 104 | Maths | 2002-01-21 > 1 | 101 | Physics | 2002-01-20 > > There are duplicate courses because the history.id > column has different values. The history.id cannot be > use in the GROUP BY clause. But it is one of the > displaying field, so I could not remove it from the > GROUP BY clause either. > > 2) Bruno suggested the following query: > > 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; > > I have not used NATURAL JOIN before, but from what I > know, it joins the columns with the same name. Since > the joining columns of History and Course have > different names, I have replace JOIN clause. Please > let me know if I have made a mistake. > > The modified query is: >SELECT DISTINCT ON (course.id) course.id, > history.id, course.name, history.submission >FROM history JOIN course ON history.courseId = > course.id >ORDER BY course.id, history.submission desc; > > The results returned are : > id | id | name| submission > -++---+ > 101 | 1 | Physics | 2002-01-20 > 102 | 4 | Chemistry | 2002-02-22 > 104 | 3 | Maths | 2002-04-30 > > The problem here is that the results are not ordered > by the submission date. If I sort by > "history.submission" first, I get >ERROR: SELECT DISTINCT ON expressions must match > initial ORDER BY expressions. > Please note that I cannot select distinct on the > course.name either because it is NOT unique. The > original tables are much larger, and the only unique > column is the id. > > I have included the queries to create the tables here. > > > > CREATE TABLE course ( > id integer, > name varchar(32), >Constraint course_pkey Primary Key (id) > ); > > CREATE TABLE history ( > id integer NOT NULL, > courseid integer REFERENCES course(id), > submission date, > Constraint history_pkey Primary Key (id) > ); > > INSERT INTO course (id,name) VALUES (101,'Physics'); > INSERT INTO course (id,name) VALUES (102,'Chemistry'); > INSERT INTO course (id,name) VALUES (103,'Biology'); > INSERT INTO course (id,name) VALUES (104,'Maths'); > INSERT INTO course (id,name) VALUES (105,'English'); > > INSERT INTO history (id,courseid,submission) VALUES > (1,101,'2002-01-20'); > INSERT INTO history (id,courseid,submission) VALUES > (2,102,'2002-02-17'); > INSERT INTO history (id,courseid,submission) VALUES > (3,104,'2002-04-30'); > INSERT INTO history (id,courseid,submission) VALUES > (4,102,'2002-02-22'); > INSERT INTO history (id,courseid,submission) VALUES > (5,104,'2002-03-15'); > INSERT INTO history (id,courseid,submission) VALUES > (6,104,'2002-01-21'); > > > > > Thanks for all your help. > > > Regards, > Eddie > &
[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
