Re: [SQL] Querying a list field against another list
Try this: CREATE OR REPLACE FUNCTION csv_matches(TEXT, TEXT) RETURNS BOOLEAN AS $$ DECLARE -- $1 is the field text, $2 is the list of ints to try and match. m TEXT; f TEXT; i INTEGER := 1; j INTEGER; BEGIN IF $1 IS NULL THEN RETURN 'f'; ELSIF $2 IS NULL THEN RETURN 'f'; END IF; LOOP m := split_part($2, ',', i); IF m LIKE '' THEN RETURN 'f'; END IF; j := 1; LOOP f := split_part($1, ',', j); IF f LIKE '' THEN EXIT; END IF; IF f LIKE m THEN RETURN 't'; END IF; j := j + 1; END LOOP; i = i + 1; END LOOP; END; $$ LANGUAGE 'plpgsql'; Then you can do "select * from foo where csv_matches(da_list, '1,4');" -Mark. Aarni Ruuhimäki wrote: Hi, I tried to mail this to the novice list I believe it was rejected: The original message was received at 2004-11-26 14:55:09 +0100 from postoffice.local [10.0.0.1] - The following addresses had permanent fatal errors - <[EMAIL PROTECTED]> -Transcript of session follows - ... while talking to postoffice.local.: RCPT To:<[EMAIL PROTECTED]> <<< 550 5.1.1 unknown or illegal alias: [EMAIL PROTECTED] 550 <[EMAIL PROTECTED]>... User unknown So here's my question. Hi people, This is not quite a pg question, but any suggestions are most welcome. How can one query a list of values against a db field that contains a list of values ? Table foo foo_id | foo_name | da_list -- 1 | x | 1,2,3,4,5 2 | y | 1,4,5 3 | z | 4,5,11 4 | xyz | 14,15,33 As a result from another query I have parameter bar = '1,4' and want to find all rows from foo where da_list contains '1' or '4'. So loop over bar to loop over da_list in foo ? My humble thanks, Aarni -- This is a bugfree broadcast to you from **Kmail** on **Fedora Core 2** linux system -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Deleting obsolete values
Delete from partitur where userid NOT IN (SELECT DISTINCT ON (userid)
userid, val, ts FROM partitur)
"Haller Christoph" <[EMAIL PROTECTED]> wrote in message
200110161445.QAA11833@rodos">news:200110161445.QAA11833@rodos...
> This may look familiar to you - it was on the list last month.
> Consider the following table
> create table partitur
> (userid text, val integer, ts timestamp DEFAULT NOW() );
> Do some inserts
> insert into partitur values('Bart', 1440);
> insert into partitur values('Lisa', 1024);
> insert into partitur values('Bart', 7616);
> insert into partitur values('Lisa', 3760);
> insert into partitur values('Bart', 3760);
> insert into partitur values('Lisa', 7616);
> To retrieve the latest values (meaning the last ones inserted)
> Tom Lane wrote
> >This is what SELECT DISTINCT ON was invented for. I don't know any
> >comparably easy way to do it in standard SQL, but with DISTINCT ON
> >it's not hard:
> >SELECT DISTINCT ON (userid) userid, val, ts FROM partitur
> >ORDER BY userid, ts DESC;
>
> My question now is
> Is there a way to delete all rows the select statement did not
> bring up?
> After that *unknown* delete statement
> select userid, val, ts from partitur ;
> should show exactly the same as the SELECT DISTINCT ON (userid) ...
> did before.
>
> 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])
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Function return rows?
Can I use a function to execute a query and return a row or set of rows? If so, can you point me to some examples or perhaps give me an example of a function that would do roughly the same thing as: select * from mytable where mytable.name ~* 'aname'; I can't seem to find any examples that return rows, just single ints and bools and stuff. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Cenceptual help needed - periodic events
I'm goofing around, designing a planned maintenance system. In a couple of weeks I'll be taking on the actual scheduling of tasks. How would you experts out there approach this? I've beeen thinking I can approach this two ways. 1) When a list of tasks is requested, generate new rows and store them. 2) When a list of tasks is requested, look at the task row and calculate the tasks, display them, but do not generate rows for storage. Just calc on the fly all the time. Method #1 would mean less CPU and RAM use but then I'd have to worry about tasks changing or being added to a maintenance schedule and not being reflected in any pre-generated rows. #2 would alleviate that problem, but may be slower overall and perhaps eat large amounts of ram. If anyone has done something similar with periodic events, what did you find worked best? I'm planning on using a base task record to record the frequency and other specifics of a task. This record is the basis for generating the periodic tasks. One row: task_id =1 (serial) task_system = 1 task_equip = 12 task_text = Lubricate fan shafts with lithium grease task_interval = 1 month (using an interval type column) task_startmonth = 2 (offset so that not ALL 6 month interval tasks actuall happen in June) Another row: task_id =25 (serial) task_system = 8 task_equip = 72 task_text = task_interval = 6 month (using an interval type column) task_startmonth=3 Then I'll have to step through the calendar by month and match up all the task rows and print out a single page for each system. This is all a brand new concept to me, so any suggestions are more than welcome. I still have a few weeks work in other areas to keep me busy, but this is the toughy that I'm spending brain time on =) ---(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] trigger to maintain relationships
I am maintaining a set of hierarchical data that looks a lot like a tree. (And my SQL is very rusty. And I'm new to postgres.) Questions: - 1.) Is the following a reasonable solution? Is there a postgres-specific way to handle this better? Is there a good generic SQL way to handle this? 2.) Can I write pure "SQL" triggers to handle this? Am I getting close in my first cut (below)? 3.) Any other ideas/suggestions? I have one table with essentially the nodes of a tree: nodes -- node_id integer parent_id integer references nodes(node_id) ...and other descriptive columns... I want an easy way to find all the elements of a subtree. Not being able to think of a good declarative solution, I was thinking about cheating and maintaining an ancestors table: ancestors --- node_idinteger ancestor_id integer references nodes(node_id) I figured I could populate the ancestors table via trigger(s) on the nodes table. Then I should be able to find a whole subtree of node X with something like: select * from nodes where node_id in ( select node_id from ancestors where ancestor_id = X) Here's my best guess so far at the triggers (but, obviously, no luck so far): --insert trigger create function pr_tr_i_nodes() returns opaque as ' insert into ancestors select NEW.node_id, ancestor_id from ancestors where node_id = NEW.parent_id;' language sql; create trigger tr_i_nodes after insert on nodes for each row execute procedure pr_tr_i_nodes(); --delete trigger create function pr_tr_d_nodes() returns opaque as ' delete from ancestors where node_id = OLD.parent_id;' language sql; create trigger tr_d_nodes after insert on nodes for each row execute procedure pr_tr_d_nodes(); --update trigger create function pr_tr_u_nodes() returns opaque as ' delete from ancestors where node_id = OLD.parent_id; insert into ancestors select NEW.node_id, ancestor_id from ancestors where node_id = NEW.parent_id;' language sql; create trigger tr_u_nodes after insert on nodes for each row execute procedure pr_tr_u_nodes(); I realize the update trigger could be handled a multitude of ways and that my first guess may be pretty lousy. But I figured the insert/update triggers would be pretty straightforward. Am I missing something basic? I also tried things like (following the one example in the reference manual): --insert trigger create function pr_tr_i_nodes() returns opaque as ' insert into ancestors select NEW.node_id, ancestor_id from ancestors where node_id = NEW.parent_id; return NEW;' language 'plpgsql'; create trigger tr_i_nodes after insert on nodes for each row execute procedure pr_tr_i_nodes(); ---(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] trigger to maintain relationships
I think I figured out my join syntax error (sorry for confusing the issue with noise like that). I'd still be interested in general comments on design. FYI, join should've looked like: create function pr_tr_i_nodes() returns opaque as ' insert into ancestors select NEW.node_id, ancestor_id from NEW left outer join ancestors on (NEW.parent_id = ancestors.node_id); return NEW;' language 'plpgsql'; create trigger tr_i_nodes after insert on nodes for each row execute procedure pr_tr_i_nodes(); David M wrote: > I am maintaining a set of hierarchical data that looks a lot like a > tree. (And my SQL is very rusty. And I'm new to postgres.) > > Questions: > - > 1.) Is the following a reasonable solution? Is there a > postgres-specific way to handle this better? Is there a good generic > SQL way to handle this? > 2.) Can I write pure "SQL" triggers to handle this? Am I getting close > in my first cut (below)? > 3.) Any other ideas/suggestions? > > I have one table with essentially the nodes of a tree: > > nodes > -- > node_id integer > parent_id integer references nodes(node_id) > ...and other descriptive columns... > > I want an easy way to find all the elements of a subtree. Not being > able to think of a good declarative solution, I was thinking about > cheating and maintaining an ancestors table: > > ancestors > --- > node_idinteger > ancestor_id integer references nodes(node_id) > > I figured I could populate the ancestors table via trigger(s) on the > nodes table. Then I should be able to find a whole subtree of node X > with something like: > > select * > from nodes > where node_id in ( > select node_id > from ancestors > where ancestor_id = X) > > Here's my best guess so far at the triggers (but, obviously, no luck so > far): > > --insert trigger > create function pr_tr_i_nodes() returns opaque > as ' > insert into ancestors > select NEW.node_id, ancestor_id > from ancestors > where node_id = NEW.parent_id;' > language sql; > create trigger tr_i_nodes after insert > on nodes for each row > execute procedure pr_tr_i_nodes(); > > --delete trigger > create function pr_tr_d_nodes() returns opaque > as ' > delete from ancestors > where node_id = OLD.parent_id;' > language sql; > create trigger tr_d_nodes after insert > on nodes for each row > execute procedure pr_tr_d_nodes(); > > --update trigger > create function pr_tr_u_nodes() returns opaque > as ' > delete from ancestors > where node_id = OLD.parent_id; > > insert into ancestors > select NEW.node_id, ancestor_id > from ancestors > where node_id = NEW.parent_id;' > language sql; > create trigger tr_u_nodes after insert > on nodes for each row > execute procedure pr_tr_u_nodes(); > > I realize the update trigger could be handled a multitude of ways and > that my first guess may be pretty lousy. But I figured the > insert/update triggers would be pretty straightforward. Am I missing > something basic? I also tried things like (following the one example in > the reference manual): > > --insert trigger > create function pr_tr_i_nodes() returns opaque > as ' > insert into ancestors > select NEW.node_id, ancestor_id > from ancestors > where node_id = NEW.parent_id; > > return NEW;' > language 'plpgsql'; > create trigger tr_i_nodes after insert > on nodes for each row > execute procedure pr_tr_i_nodes(); > > ---(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 4: Don't 'kill -9' the postmaster
[SQL] Why their is a limit in Postgresql (psql) Parameters..?
Hi All, I'm using Postgresql v7.3.3. I have a small question ... Why is that there is a maximum limit of 32 input parameters to thePostgresql function? Whereas stored procedures in Oracle and SQL Servertake more than 32 input arguments. So this puts extra burden on themiddleware developer to handle this stiuation at the time of migratingexisting databases in SQL Server or Oracle to Postgresql. Any Info/Suggestions will be highly appreciated. Are you Unmarried? Register in India's No 1 Matrimony
[SQL] Postgresql Temporary table scripts..
Hi, I'm using Postgresqlv7.3.3. Actually my requirement was to create one temporary table and insert some values on it and finally return the inserted values. For this simple thing i'm struggling a lot with two errors. one is 'RELATION '' ALREADY EXISTS' -- This is happening when ever i called the function more than ones in the same connection. To avoid this, i had created a nested function, In inner function i had created the temporary table and inserted some values and finally i called the return values on outter fucntion. this time i get the error as 'RELATION 'x' DOES NOT EXIST'. I hope u all understood my problem.. if any of u send some sample example to work around this problem will be highly appreciated. Thanks in advance, Vijay Watch Hallmark. Enjoy cool movies. Win hot prizes!
[SQL] Equivalent to sql%rowcount or @@rowcount
Hi All! is it possible to get in sql number of rows affected by the sql last insert, update or delete statement?? for eg, oracle - sql%rowcount. sqlserver select @@rowcount. Any help will be highly appreciated.. Thanks Regards, VijayCool new emoticons. Lots of colour! On MSN Messenger V6.0
[SQL] problem in database backup
Hi All, I'm using Postgresql V7.3.3, on that i created one database with name "mydb". PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) While taking a backup of my postgresql database, i'm getting the below error. bash-2.05b$ pg_dump mydb >mydb.outpg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2pg_dump: aborting because of version mismatch (Use the -i option to proceed any bash-2.05b$ pg_dump -i mydb > mydb.outpg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2pg_dump: proceeding despite version mismatchpg_dump: query to obtain list of data types failed: ERROR: Attribute "typprtlen" not found== After getting these errors, i tried to check the version of pg_dump on my system by using the below command. Here is the result . [EMAIL PROTECTED] root]# find / -name pg_dump -print/root/postgresql-7.3.3/src/bin/pg_dump/root/postgresql-7.3.3/src/bin/pg_dump/pg_dump/usr/bin/pg_dump/usr/local/pgsql/bin/pg_dump Kindly guide me to solve this problem. .. Thanks in advance, With Regards, Vijay It's all happening @ F1. Feel the thrill! Race along right here!
Re: [SQL] problem in database backup
Hi Tomasz Myrta, Thanks for ur info, u r correct .. after pointing to /usr/bin/pgsql/bin/pg_dump, i'm able to take backup.. Thank u very much.. With Regards Vijay >From: Tomasz Myrta <[EMAIL PROTECTED]> >To: vijaykumar M <[EMAIL PROTECTED]> >CC: [EMAIL PROTECTED] >Subject: Re: [SQL] problem in database backup >Date: Thu, 31 Jul 2003 13:40:45 +0200 > >Dnia 2003-07-31 13:13, U¿ytkownik vijaykumar M napisa³: > >> >>Hi All, >> >>I'm using Postgresql V7.3.3, on that i created one database with >>name "mydb". >> >>* PostgreSQL 7.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) >>3.2 20020903 (Red Hat Linux 8.0 3.2-7)* >> >>While taking a backup of my postgresql database, i'm getting the >>below error. >> >>bash-2.05b$ *pg_dump mydb >mydb.out >>pg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2 >>pg_dump: aborting because of version mismatch (Use the -i option >>to proceed any >>* >> >>*bash-2.05b$ pg_dump -i mydb > mydb.out >>pg_dump: server version: SAGES/pg_dump.mo; pg_dump version: 7.2.2 >>pg_dump: proceeding despite version mismatch >>pg_dump: query to obtain list of data types failed: ERROR: >>Attribute "typprtlen" not found >>==* >> >>After getting these errors, i tried to check the version of pg_dump >>on my system by using the below command. Here is the result . >> >>[EMAIL PROTECTED] root]# find / -name pg_dump -print >>/root/postgresql-7.3.3/src/bin/pg_dump >>/root/postgresql-7.3.3/src/bin/pg_dump/pg_dump >>/usr/bin/pg_dump >>/usr/local/pgsql/bin/pg_dump >>* > >Probably you have both 7.2.2 and 7.3.3 pg_dump versions and PATH >points to 7.2.2 /usr/bin/pg_dump. Try using >/usr/local/pgsql/bin/pg_dump > >Regards, >Tomasz Myrta > > It's all happening @ F1. Feel the thrill! Race along right here!
[SQL] createlang plpgsql failing on redhatlinux7.2
Hi All, I'm getting some problem on "createlang plpgsql". with REDHATLINUX7.2 These are all my system configurations.. We have a machine with RedhatLinix 7.2, on top of this i upgraded the postgresql7.3.3 version. After creating a database, i'm trying to create a language for that database by using createlang plpgsql it throws an error " failed with some missing files". so, i copied /usr/local/pgsql/plpgsql.so from Redhatlinux8.0 machine and tried again to create a language. this time i got an error like 'Load of file /usr/lib/pgsql/plpgsql.so failed :/lib/i686/libc.so.6: version GLIB_2.3 not found (required by /usr/lib/pgsql/plpgsql.so). Kindly guide to solve this issue. all ur helps are highly appreciable.. Thanks in advance, Vijay A chance to meet Aishwarya Rai. Win lucky prizes.
[SQL] createlang problme
Hi All, i'm using RedhatLinux7.2, on top of this i upgrade the Postgres with V7.3.3. After that, i tried to create a language with a createlang command. ERROR: Load of file /usr/local/pgsql/lib/plpgsql.so failed: /usr/local/pgsql/lib/plpgsql.so: un defined symbol: xlateSqlType createlang: language installation failed Can some one help me to come out of this problem, Thanks in advance Vijay _ The hottest things. The coolest deals. http://www.msn.co.in/Shopping/ Get them online! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL]
Hi all, I have few questions to clear my self.. 1. Is that postgres supports distributed database ? _ Talk to Karthikeyan. Watch his stunning feats. http://server1.msn.co.in/sp03/tataracing/index.asp Download images. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] few questions ..?
Hi All, I have few questions, kindly suggest your comments on this.. 1. Is that postgres supports distributed database ? In oracle, by creating Dblink we can communicate the remorte database from the local one. How you could implement this in postgres database. 2. Is there any way to cache the table on memorry ? In oracle, we can use the cache statement at the table creation itself. 3. List out the main tuning parameters in postgres.conf to take care of supporing millions of records. Thanks & Regards Vijay _ Attention NRIs! Banking worries? http://server1.msn.co.in/msnspecials/nriservices/index.asp Get smart tips. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] few questions ..?
Thanks richards ..i will go through that doc.. With Regards Vijay From: Richard Huxton <[EMAIL PROTECTED]> To: "vijaykumar M" <[EMAIL PROTECTED]>, [EMAIL PROTECTED] Subject: Re: [SQL] few questions ..? Date: Thu, 25 Sep 2003 11:07:00 +0100 On Thursday 25 September 2003 09:36, vijaykumar M wrote: > Hi All, > I have few questions, kindly suggest your comments on this.. > > 1. Is that postgres supports distributed database ? > In oracle, by creating Dblink we can communicate the remorte > database from the local one. How you could implement this in postgres > database. See contrib/dblink. I think someone is looking at linking to Oracle too (but I might be wrong about that). > 2. Is there any way to cache the table on memorry ? >In oracle, we can use the cache statement at the table creation > itself. PG uses the operating-system rather than replacing it. If you're using the table regularly it should end up in cache. If you're not using it regularly, you probably didn't want it in cache anyway. > 3. List out the main tuning parameters in postgres.conf to take care > of supporing millions of records. See http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php There are two documents there describing the various config settings and the basics of performance tuning. Millions of records don't need too much tuning even on a standard PC, but the standard config settings are *very* conservative. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] _ Answer simple questions. Win a free honeymoon. http://server1.msn.co.in/sp03/shaadi/index.asp Sail into the sunset! ---(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] Temporary tables
Hi, Try the below steps .. 1. Write one procedure to create tempory table (generic purpose) ** CREATE OR REPLACE FUNCTION SP_CREATE_TEMP_TABLE () RETURNS VARCHAR AS ' DECLARE L_SchemaName name; BEGIN EXECUTE ''CREATE TEMPORARY TABLE temp_table_gen (X VARCHAR);''; SELECT schemaname INTO L_SchemaName FROM pg_stat_user_tables where relname =''temp_table_gen''; RETURN L_SchemaName; END; ' LANGUAGE 'plpgsql'; ** 2. Call the above (generic) procedure to get the temporary table schema name.. by using that schema name ..you can check whether the (real) temporary table is exists or not. ** select into L_SchemaName * from SP_CREATE_TEMP_TABLE(); -- get the schemaname execute ''drop table temp_table_gen;''; -- drop the temptable select schemaname into L_Schema from pg_stat_user_tables where relname=''temp_total_count'' and schemaname =||L_SchemaName||; if (L_Schema is null) then EXECUTE ''CREATE TEMPORARY TABLE temp_total_count (TOTAL_COUNT NUMERIC);''; ELSE EXECUTE ''DELETE FROM temp_total_count;''; END IF; ** I hope this will help u to solve these temporary table issues.. With Regards Vijay From: "George A.J" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [SQL] Temporary tables Date: Sat, 27 Sep 2003 06:31:39 -0700 (PDT) hi, I am using postgresql 7.3.2. Is there any function to determine whether a table exists in the database.Or is there any function that returns the current temp schema. I am using a pl/pgsql function that create and drop a temporary table. The procedure run correctly for the first time for each database connection. If I run the same procedure second time in the same connection it produces the error "ERROR: pg_class_aclcheck: relation 219389 not found WARNING: Error occurred while executing PL/pgSQL function testFun WARNING: line 20 at SQL statement " Here is the function - CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof int AS ' DECLARE --Aliases for parameters vSBAcNo ALIAS FOR $1; --local variables vRow RECORD; BEGIN -- create a tempory table to hold the numbers CREATE TABLE tempTable ( testNo int ) ; for vRow IN select Entryno from EntryTable LOOP return next vRow.Entryno; insert into tempTable values( vRow.Entryno); end loop; drop table tempTable; return; END;' LANGUAGE 'plpgsql'; - If i commented the "insert into tempTable values( vRow.Entryno);" line the function works correctly. The problem is the oid of tempTable is kept when the function is first executed. the next execution creates another table with different oid. So the insert fails. I want to check whether the temporary table exist. If exist do not create the temporary table in subsequent calls and do not dorp it. This will solve the problem. When i searched the pg_class i found the temp table name more than once. ie, a temporary table is created for each connection.I cannot distingush the temp tables. But the tables are in different schema. Is there a method to get the current temporary schema? How postgres distinguish this temp tables?.Is there a way to distinguish temporary tables. The entries in pg_class table is same except the schema. When i used the current_schema() function it returns public. There is a lot of functions that uses temporary tables. I think that there is an option when creating temp tables in postgres 7.4 . But no way to use 7.4 now it is a working database. can i write a function to check the existance of the temporary table... please help... jinujose - Do you Yahoo!? The New Yahoo! Shopping - with improved product search _ Keep up with the pace of change. Register for My Tech Ed. http://server1.msn.co.in/sp03/teched/index.asp Realise your potential! ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] how to read bytea contents by using pgsql scripts
Hi All, Is their any way to read the bytea contents by using pgsql script. Thanks & regards Vijay _ BharatMatrimony.com. http://www.bharatmatrimony.com/cgi-bin/bmclicks1.cgi?74 India's premium matrimonial website. ---(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 read bytea contents by using pgsql scripts
Hi Christoph, Thanks for ur reply. Let me explain my problem..., In one of my table.. we used bytea datatype for one field.. actaully i want to know the original stored contents (not in byte's) from that field by using pgsql scripts. Thanks in advance.. With regards Vijay From: Christoph Haller <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] (vijaykumar M) CC: [EMAIL PROTECTED] Subject: Re: [SQL] how to read bytea contents by using pgsql scripts Date: Mon, 24 Nov 2003 11:26:02 MET > > Hi All, > Is their any way to read the bytea contents by using pgsql script. > > Thanks & regards > Vijay > > _ > Not sure if that's what you are asking for but you can do e.g. $PGSQLD/bin/psql -d -f ./query2.txt > ./query2.res ./query2.txt has something like select * from bytea_tab ; then you'll find all non-printables in ./query2.res properly escaped as octets as shown in Data Types - Binary Strings. HTH Regards, Christoph ---(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 _ MSN Hotmail now on your Mobile phone. http://server1.msn.co.in/sp03/mobilesms/ Click here. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] how to read bytea contents by using pgsql scripts
Hi Christoph, Yes, U catched my problem, i want to know the original stored contents (not in byte form). With thanks and regards Vijay From: Christoph Haller <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] (vijaykumar M) CC: [EMAIL PROTECTED] Subject: Re: [SQL] how to read bytea contents by using pgsql scripts Date: Mon, 24 Nov 2003 16:12:56 MET > > Hi Christoph, > Thanks for ur reply. > Let me explain my problem..., In one of my table.. we used bytea > datatype for one field.. actaully i want to know the original stored > contents (not in byte's) from that field by using pgsql scripts. > > Thanks in advance.. > > With regards > Vijay > > Sorry, Vijay, but I don't get it. What do you mean by "the original stored contents (not in byte's)" ? Regards, Christoph ---(end of broadcast)--- TIP 8: explain analyze is your friend _ Express your Digital Self. Win fabulous prizes. http://www.msn.co.in/DigitalSelf/ Enter this cool contest. ---(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
[SQL] Pgaccess problem
Hi All,
I am trying to install pgaccess on a redhat 7.2 linux server with
postgres7.4. Everything seems to be alright but when I go to run pgaccess I
get an error message saying
Application initialization failed: no display name and no $DISPLAY
environment v
ariable
Error in startup script: invalid command name "image"
while executing
"image create bitmap dnarw -data {
#define down_arrow_width 15
#define down_arrow_height 15
static char down_arrow_bits[] = {
0x00,0x80,0x00,0x80,0x0..."
(file "/usr/local/pgaccess/main.tcl" line 5)
Can anyone Offer any advice on this problem?
Thanks in advance,
Vijay
_
Stand out from the crowd. Make your own MMS cards. http://msn.migasia.cn/msn
Have some mobile masti!
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Pgaccess problem on RedhatLinux9
Hi All, Previously i was used RedhatLinux7.2 & Postgres7.4, that time i'm able to use the pgaccess command to view the tables. Presently, i'm using RedhatLinux9 & Postgres7.4, here i'm not able to use the pgaccess command. It is saying "command not found." One thing, i observed was on RedhatLinux7.2,this pgaccess is available at \usr\share\pgsql\pgaccess.this is missing at redhatlinux9. Is there any way to use pgaccess on Redhatlinux9. Thanks in advance, Vijay _ Contact brides & grooms FREE! Only on www.shaadi.com. http://www.shaadi.com/ptnr.php?ptnr=hmltag Register now! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Entered data appears TWICE in table!!?
I'm JUST getting started with the online SQL tutorial at
http://sqlcourse.com. When I create a table and insert data, the data
appears TWICE. A simple example:
***Create the table:
create table rnmrgntable
(first varchar(20),
last varchar(30));
***Insert data:
insert into rnmrgntable
(first, last)
values ('Bill' , 'Smith');
***Then look at the table:
select * from rnmrgntable;
And I get:
firstlast
BillSmith
BillSmith
EVERYTHING I enter appears twice, duplicated on two rows as in this
example. What the heck's going on?
Ron M.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] Database triggers
I have a lab assignment that I have been struggling with. We are using oracle sql. Can someone please help me. See the lab below. I have done ques 1 - 3 thus far and am now stuck on triggers ques 4 - 6. THIS IS THE LAB: 1. Create a table called QUOTE. · Give the table an initial and next extent size of 8192 · Specify a pctincrease of 0 · Define the following columns using the datatypes and length listed below. All columns should be mandatory except the COMMENTS column: o ID NUMBER(4) o QUOTE_DATE DATE o SALES_REP_IDNUMBER(4) o CUST_NBRNUMBER(5) o PARTVARCHAR2(20) o QUANTITYNUMBER(4) o UNIT_COST NUMBER(8,2) o STATUS CHAR(1) o COMMENTSVARCHAR2(100) · Define the ID column as the primary key for the table. You can do this in the CREATE TABLE statement, or issue an ALTER TABLE statement afterwards. 2. Alter the table above to add some foreign key constraints. Name these constraints QUOTE_tablename_FK, where tablename is the table referred to by the foreign key. For example, a foreign key on the QUOTE table referring to the PART table should be called QUOTE_PART_FK. · A foreign key on the SALES_REP_ID column should refer to the EMPLOYEE table. · A foreign key on the CUST_NBR column should refer to the CUSTOMER table. · A foreign key on the PART column should refer to the PART table. 3. Create a composite index on the CUST_NBR, PART and QUOTE_DATE columns. · Give the index an initial and next extent of 8192 · Use pctincrease 0 · Name the index whatever you'd like 4. Create a database trigger on the QUOTE table that will fire before an INSERT, UPDATE or DELETE operation. Name the trigger QUOTE_TRG. The trigger should enforce the following rules: · If INSERTING or UPDATING o QUOTE_DATE cannot be greater that SYSDATE (the current system date and time) o UNIT_COST can't be greater than the UNIT_COST for this part in the PART table o If QUANTITY is over 100, the UNIT_COST must be at least 20% less than the UNIT_COST for this part as listed in the PART TABLE · If INSERTING, in addition to the rules listed above: o STATUS must contain a value of P (which stands for pending) · If UPDATING, in addition to the rules listed earlier: o A STATUS of P can only be changed to a STATUS of A (which stands for active) o A STATUS of A can be changed to P, W, L or C (for pending, won, lost or cancelled) o A STATUS of W, L or C can only be changed back to P · If DELETING o STATUS must be P or C If any of these rules are violated, raise one of the following exceptions which you will define in the EXCEPTION portion of your trigger. Raise an application error. Use whatever error numbers you'd like, and provide meaningful text to describe the error: · Quote date can't be a future date · Quoted price is too high · New quotes must have a status of P · Pending status (P) can only be changed to Approved (A) · Invalid status code · Won, Lost or Cancelled quotes can only be changed to Pending 5. Create a BEFORE UPDATE trigger on the PART table. The trigger should enforce the following rule: · If UNIT_COST is being updated o The new price can't be lower than any of the quoted prices in the QUOTE table for this part, if the quote status is P or A o The new price must be at least 20% more than any quoted prices in the QUOTE table for this part, if the quote is for a quantity > 100 and the quote status is P or A Define a single exception that is raised when either error occurs. The text of the application error should indicate that the cost is invalid based upon outstanding quotes on the part. 6. Write a series of statements to test your new triggers: · Try to insert a row into the quote table. For the quote date, provide a value of SYSDATE+1. This will try to insert a row with tomorrow's date for the quote date. · Try to insert a row into the quote table with a price greater than that listed for the part in the PART table · Try to insert a row into the quote table with a quantity > 100 and a price > 20% off the price in the PART table · Try to INSERT a row with a STATUS other than P · Now insert a valid row so that you can test some UPDATE statements · Issue an UPDATE to modify the price to a price higher than that in the PART table · Issue an UPDATE to modify the quote date to SYSDATE+1 · Issue an UPDATE to modify the quantity to > 100 and the price to something higher than 20% off the price listed in the PART table · Issue an update to modify the status from P to W · Now issue a valid update to change the status to A · Issue a delete to make sure you can't delete a row with status of A · Fina
Re: [SQL] Database triggers
Thank you, will look at the reference manual. This is how I've done it to this point. It runs and the trigger is created but am not quite sure if its the right thing. CREATE OR REPLACE TRIGGER QUOTE_TRG BEFORE INSERT or DELETE OR UPDATE ON QUOTE FOR EACH ROW DECLARE today_date date; part_cost number(8, 2); current_status char(1); future_date exception; high_cost exception; discount_error exception; invalid_insert exception; invalid_status exception; delete_status exception; BEGIN if inserting or updating then today_date := :new.QUOTE_DATE; if today_date > SYSDATE then raise future_date; end if; select PART.UNIT_COST into part_cost from PART where PART.PART_NBR = :NEW.PART; if part_cost < :NEW.UNIT_COST then raise high_cost; end if; if :NEW.QUANTITY > 100 then if (part_cost * .8) < :NEW.UNIT_COST then raise discount_error; end if; end if; end if; if inserting then if upper(:NEW.STATUS) != 'P' then raise invalid_insert; end if; end if; if updating then if upper(:NEW.STATUS) != 'A' then raise invalid_status; end if; end if; if deleting then select QUOTE.STATUS into current_status from QUOTE where QUOTE.ID = :NEW.ID; if current_status != 'P' and current_status != 'C' then raise delete_status; end if; end if; EXCEPTION when future_date then raise_application_error(-20110, 'Quote date cannot be a future date.'); when high_cost then raise_application_error(-20111, 'Quoted price is too high'); when discount_error then raise_application_error(-20112, 'Quoted discount price is too high'); when invalid_insert then raise_application_error(-20113, 'New quotes must have a status of P'); when invalid_status then raise_application_error(-20114, 'Pending status (P) con only be changed to Approved (A)'); when delete_status then raise_application_error(-20115, 'Status must be (P) Pending or (C) Cancelled to be deleted'); END; ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Storing properties in a logical way.
Hello everybody, In a database there is a table with items, and each item can have 0 to n properties. The objective is to store information about items' properties in a mentioned database in a logical and an efficient way. Usually it is easily done by creating a validation table with a list of possible properties and then creating a n-to-n relationship by means of a linking table (hope I use the correct terms here). But after looking closely at the list of a possible properties, i found out that some of them depend on others. For example, if item is a PDF document, it can have an index. But a document can also have an index with links. Logically, a properties like 'index with links' don't belong to the verification table - they look like a kind of a composite field - 'index with links' is not a stand-alone property, but it also implies that an item also has an 'index' property. On the other hand, it is impossible to decouple 'index' from 'with links', because the second part won't have any meaning without the first part. How can such a kind of data be modeled in a logical way? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Storing properties in a logical way.
On Sun, 05 Sep 2004 19:51:44 +0200, Pierre-Frédéric Caillaud <[EMAIL PROTECTED]> wrote: >> But after looking closely at the list of a possible properties, i found >> out that some of them depend on others. For example, if item is a >> PDF document, it can have an index. But a document can also have an >> index with links. Logically, a properties like 'index with links' >> don't belong to the verification table - they look like a kind of >> a composite field - 'index with links' is not a stand-alone property, >> but it also implies that an item also has an 'index' property. >> On the other hand, it is impossible to decouple 'index' from >> 'with links', because the second part won't have any meaning without >> the first part. > >You mean your properties would be better organized as a tree ? >Or is it even more complicated than that ? I never thought about that possibility - it is an interesting idea, and it solves the logical problem (though there is still a need to ensure that if child property is set, that the user won't be able to also set a parent property - which is probably implementable by using triggers). Though I would prefer, if it is possible, something much simpler, because there are only about 10 properties and 2 'composite' properties - it would probably be an overkill to create a tree for such a small table if a simpler solution exists. Daniel. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] HowTo change encoding type....
Hi, how do I change the encoding type in postgreSQL (8) from UTF-8 to ISO-8859-1? many thanks Andrew <> +The home of urban music + http://www.beyarecords.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Failed system call was shmget(key=1, size=1155072, 03600).
Hi, when building postgreSQL 8 I get the following error message: DETAIL: Failed system call was shmget(key=1, size=1155072, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1155072 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 10) What is the best way to resolve this? max_connections = 10? Does that figure auto increase as more users request data? regards Andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] unsubscribe pgsql-sql
unsubscribe pgsql-sql ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SQL syntax rowcount value as an extra column in the result set
Hi, Is this what you are trying to do? postgres=# select * from (select count(*) from people ) p, (select firstname from people)p2; count | firstname ---+--- 5 | Mary 5 | Mary 5 | John 5 | John 5 | Jacob (5 rows) I do not know about the performance impact of such a query (cartesian join) Regards, Jayadevan From: "Snyder, James" To: Date: 26/03/2010 03:21 Subject:[SQL] SQL syntax rowcount value as an extra column in the result set Sent by:[email protected] Hello I’m using PostgreSQL (8.4.701) and Java (jdbc, postgresql-8.4-701.jdbc4.jar) to connect to the database. My question is: what is the SQL syntax for PostgreSQL to achieve the following: I want to receive the rowcount along with the rest of a result set. For example, let’s say the following query returns select first_name from people; first_name = Mary Sue Joe and the following query returns the value select count(*)as ROWCOUNT from people; ROWCOUNT == 3 3 What I’m looking for is the output as ROWCOUNT , first_name = 3 , Mary 3 , Sue 3 , Joe so I can use JDBC (snip-it) as follows: resultSet.getInt(“ROWCOUNT”) resultSet.getString(“first_name”) On a side note, Oracle allows the following syntax to achieve the above: select count(*) over () as ROWCOUNT , first_name from people Thanks,Jim DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] SQL syntax rowcount value as an extra column in the result set
Hi, I don't think so. Oracle - SQL> select count(*) over () as ROWCOUNT , first_name from people; ROWCOUNT FIRST_NAME -- - --- 6 Mary 6 Mary 6 John 6 John 6 John 6 Jacob 6 rows selected. PostgreSQL postgres=# select count(*) over () as ROWCOUNT , first_name from people; ERROR: syntax error at or near "over" LINE 1: select count(*) over () as ROWCOUNT , first_name from people... ^ Regards, Jayadevan From: Thomas Kellerer To: [email protected] Date: 26/03/2010 03:26 Subject:Re: [SQL] SQL syntax rowcount value as an extra column in the result set Sent by:[email protected] Snyder, James wrote on 25.03.2010 22:33: > I’m using PostgreSQL (8.4.701) There is no such version. The current version is 8.4.3 > On a side note, Oracle allows the following syntax to achieve the above: > > select count(*) over () as ROWCOUNT , first_name from people > The same syntax will work on Postgres Thomas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] SQL syntax rowcount value as an extra column in the result set
Hi, >It works, but you should use a recent version: >test=*# select count(1) over (), i from foo; > count | i >---+ > 8 | 1 > 8 | 2 > 8 | 3 > 8 | 6 > 8 | 7 > 8 | 9 > 8 | 13 > 8 | 14 >(8 rows) > test=*# select version(); >version > > PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real > (Debian 4.3.2-1.1) 4.3.2, 64-bit > (1 row) Thank you for setting that right. Apologies for not checking version. Is this approach better compared to postgres=# select * from (select count(*) from people ) p, (select firstname from people)p2; count | firstname ---+--- 5 | Mary 5 | Mary 5 | John 5 | John 5 | Jacob (5 rows) This gives me postgres=# explain select * from (select count(*) from people )as p, (select firstname from people)p2; QUERY PLAN - Nested Loop (cost=14.00..30.42 rows=320 width=226) -> Aggregate (cost=14.00..14.01 rows=1 width=0) -> Seq Scan on people (cost=0.00..13.20 rows=320 width=0) -> Seq Scan on people (cost=0.00..13.20 rows=320 width=218) Since I don't have 8.4, I am not in a position to do explain on that version. My guess - over () will be better. My query does sequential scans/nested loop...(if there are no indexes) Regards, Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] LEFT OUTER JOIN issue
Hi, > SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as ct > LEFT OUTER JOIN h_part as hp > ON ct.dat = hp.datmesure > AND ct.heur = hp.heuremesure > WHERE > hp.poste_idposte = 275 > ORDER BY ct.dat, ct.heur > dat heur datmesure heuremesure t > --- > 15/03/2008 0:00 15/03/2008 0:008,3 > 15/03/2008 3:00 15/03/2008 3:0012 > 15/03/2008 6:00 15/03/2008 6:0015 > 15/03/2008 9:00 15/03/2008 9:0018 > 15/03/2008 12:00 nullnull null > 15/03/2008 15:00 nullnull null Would this work? SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as ct LEFT OUTER JOIN h_part as hp ON ct.dat = hp.datmesure AND ct.heur = hp.heuremesure WHERE coalesce(hp.poste_idposte,275) = 275 ORDER BY ct.dat, ct.heur dat | heur | datmesure | heuremesure | t +--++-+-- 2008-03-15 | 00:00:00 | 2008-03-15 | 00:00:00| 8.3 2008-03-15 | 03:00:00 | 2008-03-15 | 03:00:00| 12.0 2008-03-15 | 06:00:00 | 2008-03-15 | 06:00:00| 15.0 2008-03-15 | 09:00:00 | 2008-03-15 | 09:00:00| 18.0 2008-03-15 | 12:00:00 || | 2008-03-15 | 15:00:00 || | (6 rows) Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] [GENERAL] How to Get Column Names from the Table
> Use: > \d tablename And what I really like about it is the way you can make a guess about the table name and use * . postgres-# \d mt* Table "public.mt1" Column | Type | Modifiers +-+--- id | integer | Table "public.mt2" Column | Type | Modifiers +-+--- id | integer | Table "public.mt3" Column | Type | Modifiers +-+--- id | integer | Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SUM the result of a subquery.
> SELECT SUM ( > (SELECT i.id_item, i.price, SUM (o.quantity), ROUND (SUM > (o.quantity) * i.price, 2) AS cost > FROM orders o > JOIN items i ON i.id_item = o.id_item > WHERE o.date_order BETWEEN '2010-01-01' AND '2010-01-31' > GROUP BY i.id_item, i.price) > ); > > No luck. Obviously SUM expects an expression, not a set of rows. Is > there a way to perform a sum of the resulting rows? > I don't have a PostgreSQL server to try this right now. But you are looking for something like SELECT SUM (cost) from ( (SELECT i.id_item, i.price, SUM (o.quantity), ROUND (SUM (o.quantity) * i.price, 2) AS cost FROM orders o JOIN items i ON i.id_item = o.id_item WHERE o.date_order BETWEEN '2010-01-01' AND '2010-01-31' GROUP BY i.id_item, i.price) ) as x Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] find and replace the string within a column
Hello, > the below one help's me to find the data within the two brackets. > > SELECT name,(REGEXP_MATCHES(name, E'\\(.+?\\)'))[1] from person; > regexp_matches > > (S/o Sebastin ) > - > Trying to work with your code - update table set name = substr( name,1,strpos(name, (REGEXP_MATCHES(name, E'\\(.+?\\)')) )-1 ) || substr( name,strpos(name, (REGEXP_MATCHES(name, E'\\(.+?\\)')) ) + 1 ,char_length(name)) I am trying to find what is there before the pattern and after the pattern and concatenating them . Please see documentation for proper use of substr,strpos,cahr_length etc. Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Calculate next event date based on instance of the day of week
Hello, > I have a table which stores an event date. The event is stored as a > standard mm/dd/yy entry. > > I then need to calculate based on the dd value, WHICH day of the > week that is (e.g. Wednesday) and which occurrence of that day of > the week, within the month, it is (e.g. the THIRD Wednesday). > Here is an example to reach this far postgres=# create table mt(myd date); postgres=# insert into mt select current_date+se from (select generate_series(1,1) as se ) as x; postgres=# select * from mt order by postgres-# myd limit 10; myd 2010-12-04 2010-12-05 2010-12-06 2010-12-07 2010-12-08 2010-12-09 2010-12-10 2010-12-11 2010-12-12 2010-12-13 (10 rows) This is the query to get the data in the format you want... select myd, d ,w from ( select myd, to_char(myd,'Day') as d , to_char(myd,'W') as w ,rank() over (partition by to_char(myd,'W') order by myd ) as x from mt order by myd ) as t order by myd ; myd | d | w +---+--- 2010-12-04 | Saturday | 1 2010-12-05 | Sunday| 1 2010-12-06 | Monday| 1 2010-12-07 | Tuesday | 1 2010-12-08 | Wednesday | 2 2010-12-09 | Thursday | 2 2010-12-10 | Friday| 2 2010-12-11 | Saturday | 2 2010-12-12 | Sunday| 2 2010-12-13 | Monday| 2 2010-12-14 | Tuesday | 2 2010-12-15 | Wednesday | 3 2010-12-16 | Thursday | 3 2010-12-17 | Friday| 3 2010-12-18 | Saturday | 3 2010-12-19 | Sunday| 3 2010-12-20 | Monday| 3 2010-12-21 | Tuesday | 3 2010-12-22 | Wednesday | 4 2010-12-23 | Thursday | 4 2010-12-24 | Friday| 4 2010-12-25 | Saturday | 4 2010-12-26 | Sunday| 4 2010-12-27 | Monday| 4 2010-12-28 | Tuesday | 4 2010-12-29 | Wednesday | 5 2010-12-30 | Thursday | 5 2010-12-31 | Friday| 5 Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get the max viewd product_id for user_id
Hello, > I went this way, but for a large number of user_id's, it's quite slow: > > CREATE VIEW v_views AS >SELECT user_id, product_id, count(*) as views >FROM viewlog >GROUP BY user_id, product_id > > SELECT >DISTINCT user_id, >(SELECT product_id FROM v_views inn WHERE inn.user_id = out.user_id > ORDER BY views DESC LIMIT 1) as product_id, >(SELECT views FROM v_views inn WHERE inn.user_id = out.user_id ORDER BY > views DESC LIMIT 1) as views > FROM >v_views out > Does this work faster? select x.user_id,y.product_id,x.count from (select user_id, max(count ) as count from (select user_id,product_id, count(*) as count from viewlog group by user_id,product_id) as x group by user_id ) as x inner join (select user_id,product_id, count(*) as count1 from viewlog group by user_id,product_id ) as y on x.user_id=y.user_id and x.count=y.count1 Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get the max viewd product_id for user_id
> > The issue in both approaches is that if I have two product_ids that are > > viewed same number of times and share the first place as most viewed > > products by that user, I'll get only one of them (LIMIT 1 OR MAX() can > > only return one row :). > > > > And then, to jump again into my own mouth - your approach, Jayadevan, > correctly gives me both product_id's if they're viewed the same number > of times. > Good. It should, since we are joining on count and user_id. I was surprised to see your mail which said it wouldn't :). Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] group by with sum and sum till max date
This is a little hard to explain, and I'm not sure if it's possible, but here goes. This is my query: select year, month, (select number from account where account.account_id = view_account_change.account_id) as number, (select name from account where account.account_id = view_account_change.account_id) as account, sum(amount) as amount from view_account_change where view_account_change.change_date >= '2010-01-01' group by year,month, number, account order by year,month, number, account I want to make an exception for the sum so that if the account number is less than 4000, I want a sum of all transactions until the last date of the group by. the query for that would be: Select sum(amount) from view_account_change where change_date > "max date in the group" Is this possible? Thanks, Mark -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] parsing audit table
Hi everyone, I'm a bit lazy, or actually in a bit of a crunch. I added an audit recording a few months ago, but never really used it much, but today I'm seeing a bunch of suspicious activity by one user. Does someone have any function to quickly parse this data? I followed this: http://wiki.postgresql.org/wiki/Audit_trigger - so I'm hoping someone else has something to parse that. original data: "(B04TaEsAAIG5bEEX5xBVPQ,2,jakew,mag,"",2011-07-05,2011-07-05,,,7,528284,"",2011-07-05,13:20:59,2011-07-05,"",A04CQUUABSxYfxftPQqJlg,,JUfhSzwADKqAFSN1Cbv+mg,BUiZqlIABburW7jqdY9JJQ,HUZPx0gACfCxy1Y34QSTQw,,,0,N,Y,Y,919.9100,0.,0.,2011-07-05,2011-07-05)" new data: "(B04TaEsAAIG5bEEX5xBVPQ,3,jakew,mag,"",2011-07-05,2011-07-05,,,7,528284,"",2011-07-05,13:20:59,2011-07-05,"",A04CQUUABSxYfxftPQqJlg,,JUfhSzwADKqAFSN1Cbv+mg,BUiZqlIABburW7jqdY9JJQ,HUZPx0gACfCxy1Y34QSTQw,,,0,N,N,Y,919.9100,919.9100,0.,,2011-08-04)" those 22 alphanumeric columns are IDs. Thanks for any help, Mark -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] running totals with end of month line
Hi everyone, I would like to create a query that does a running total for each account, but I also want to show a 'phantom' row that gives the end of month with the last day of the month as the transaction date. Here's a sample query: SELECT n.customer_id, n.order_id, n.order_total, COALESCE(SUM(o.order_total),0) As past_order_total FROM orders AS n LEFT JOIN orders AS o ON (o.customer_id = n.customer_id AND n.order_datetime > o.order_datetime) GROUP BY n.customer_id, n.order_datetime, n.order_id, n.order_total ORDER BY n.customer_id, n.order_datetime, n.order_id; --- taken from http://bit.ly/speZzs Is there a way to have that 'phantom' row for each account? I want to result to be ordered by customer_id, account_type. More details: In my situation, I have Customers and Grain types. I want to generate a result that will show Customer, Grain Type, Daily Avg Bal, Charge Rate, discount, Charge. Maybe it's not really possible. I see it a bit hard group it properly, showing only single row per customer per grain. Many thanks, Mark
[SQL] help on a function with exception
Hi, I want to do a check on a column if other columns meet certain conditions. The program I'm working with allows to create additional columns on every 'object' - called extra data, but I have no control over the program. I want to enforce the values on this one extra data to be of type date. My idea was to do a Trigger function and cast to a date and if there's an exception, raise an error. Below is what I've tried, but it just keeps on Raising Exception. Could someone please help me? The date I enter is: 2012-10-10 which works fine if I do a: select '2012-10-10'::date Thanks --Postgres 9.0 CREATE OR REPLACE FUNCTION fnc_check_PO_extra_date() RETURNS trigger AS $BODY$ DECLARE tmp_date date; BEGIN IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN IF (NEW.data_value IS NOT NULL or new.data_value = '') and NEW.extra_id = (select extra_id from extra_data where data_type = 9 and (data_name = 'ETA' or data_name = 'Adjusted ETA')) THEN tmp_date := new.data_value::date; END IF; END IF; EXCEPTION WHEN others THEN RAISE EXCEPTION 'Invalid date on Extra Data!'; return NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE CREATE TRIGGER trg_check_PO_extra_date BEFORE INSERT OR UPDATE ON extra_values FOR EACH ROW EXECUTE PROCEDURE fnc_check_PO_extra_date(); -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Postgresql function which compares values from both tables
Hi, > To: [email protected] > Subject: [SQL] Postgresql function which compares values from both tables > > hi , > how to write a function which should read data from 2 tables having > same number of columns and should show the common values from those tables. If you want to compare and ensure that the values are same for all columns, please have a look at INTERSECT http://www.postgresql.org/docs/9.1/static/sql-select.html Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] How to limit access only to certain records?
HI, > > is there a way to limit access for some users only to certain records? > > e.g. there is a customer table and there are account-managers. > Could I limit account-manager #1 so that he only can access customers > only acording to a flag? > > Say I create a relation cu_am ( customer_id, account_manager_id ). > Could I let the database control that account-manager #1 can only see > customers who are assigned to him in the cu_am-relation? > > For now I do this in the front-end but this is easily circumvented for > anyone who has a clue and uses some other client like psql. Using a VIEW? Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
[SQL] Aggregate over a linked list
Hello! I trade futures contracts and I have a PostgreSQL 9.2 database that keeps track of all of my trading activity. The table includes columns for the futures contract, the entry and exit dates and the profit for that particular trade. Now, futures contracts expire, so within a trade being indicated by my rules, I may need to "roll" contracts to the next contract to avoid contract expiration. Therefore I can end up with multiple actual trades in my table that are linked by the dates. e.g. If the contract is SH12 (S = soybeans and H12 = March 2012 expiration) contract entry_date exit_date profit --- SH12 2012-1-102012-2-27 500.00 SM12 2012-2-272012-3-30 200.00 While these are the actual exchange executed trades, I'd like to reduce this into a single row like (linked by the "S" and then exit_date = entry_date): contract entry_date exit_date profit --- S 2012-1-102012-3-30 700.00 I've gone round and round in my head, google, SQL Cookbook, etc. trying to figure out how to do this. Can anyone provide any pointers on how to do this? Thanks and best regards! Mark
Re: [SQL] DB link from postgres to Oracle; how to query Dbname.tablename?
In PostgreSQL, you always connect to a 'database', then query tables. So if
you are connecting to the 'wrong' database, you will get the error you
mentioned. You can troubleshoot this in many ways -
one way would be to enable logging on PostgreSQL side and check the log and
see which database you are connecting to. Another way will be to execute
the PostgreSQL function current_database()
http://www.postgresql.org/docs/9.3/static/functions-info.html
instead of the query you are using right now and verify if you are
connecting to the correct database.
On Wed, Oct 2, 2013 at 5:11 AM, Bhanu Murthy wrote:
> Hi all, greetings!
>
> Using Oracle Heterogeneous Services (Oracle HS) I have configured/created
> a DB link from Postgres 9.3 database into Oracle 11gR3 database (with
> postgres DB user credentials).
>
> SQL> create public database link pg_link connect to "postgres"
> identified by "blahblah" using 'postgresql';
> Since Postgres does not support public synonyms across databases in a
> cluster, how do I connect to a specific database and query a specific table
> in this Postgres cluster using the HS DB link?
>
> Let's say, if I have 2 Postgres databases named pgdb01 and pgdb02 in the
> Postgres cluster, using this DB link that I have created in Oracle, how can
> I query a specific table called table01 from pgdb01 database?
>
> Even though the table user_account exists in pgdb01 database, I cannot
> select from it using the DB link.
>
> SQL> select count(*) from
> mailto:%22user_account%22@pg_link<%22user_account%22@pg_link>
> ;
> *select count(*) from **"user_account"@pg_link*<%22user_account%22@pg_link>
> *; *
> ERROR at line 1:
> ORA-28500: connection from ORACLE to a non-Oracle system returned this
> message:
> ERROR: relation "user_account" does not exist at character 21;
> No query has been executed with that handle {HY000,NativeErr = 1}
> ORA-02063: preceding 3 lines from PG_LINK;*
>
> I tried dbname.tablename syntax, but it didn't work! BTW, all my tables
> belong to public schema.
>
> Does anyone with DB link expertise try to answer my question?
>
> Thanks,
> Bhanu M. Gandikota
> Mobile: (415) 420-7740
>
>*From:* Alejandro Brust
> *To:* [email protected]
> *Sent:* Tuesday, October 1, 2013 12:30 PM
> *Subject:* Re: [ADMIN] PostgreSQL 9.2 - pg_dump out of memory when
> backuping a database with 3 large objects
>
> Did U perform any vacuumdb / reindexdb before the Pg_dump?
>
>
> El 01/10/2013 09:49, Magnus Hagander escribió:
> > On Tue, Oct 1, 2013 at 11:07 AM, Sergey Klochkov
> wrote:
> >> Hello All,
> >>
> >> While trying to backup a database of relatively modest size (160 Gb) I
> ran
> >> into the following issue:
> >>
> >> When I run
> >> $ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb
> >>
> >> File /path/to/mydb.dmp does not appear (yes, I've checked permissions
> and so
> >> on). pg_dump just begins to consume memory until it eats up all
> avaliable
> >> RAM (96 Gb total on server, >64 Gb available) and is killed by the oom
> >> killer.
> >>
> >> According to pg_stat_activity, pg_dump runs the following query
> >>
> >> SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
> lomowner)
> >> AS rolname, lomacl FROM pg_largeobject_metadata
> >>
> >> until it is killed.
> >>
> >> strace shows that pg_dump is constantly reading a large amount of data
> from
> >> a UNIX socket. I suspect that it is the result of the above query.
> >>
> >> There are >3 large objects in the database. Please don't ask me
> why.
> >>
> >> I tried googling on this, and found mentions of pg_dump being killed by
> oom
> >> killer, but I failed to find anything related to the huge large objects
> >> number.
> >>
> >> Is there any method of working around this issue?
> > I think this problem comes from the fact that pg_dump treats each
> > large object as it's own item. See getBlobs() which allocates a
> > BlobInfo struct for each LO (and a DumpableObject if there are any,
> > but that's just one).
> >
> > I assume the query (from that file):
> > SELECT oid, lomacl FROM pg_largeobject_metadata
> >
> > returns 3 rows, which are then looped over?
> >
> > I ran into a similar issue a few years ago with a client using a
> > 32-bit version of pg_dump, and got it worked around by moving to
> > 64-bit. Did unfortunately not have time to look at the underlying
> > issue.
> >
> >
>
>
>
> --
> Sent via pgsql-admin mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
[SQL] pg_dump
I've install postgres in linux. but pg_dump its not working at all. if i execute the pg_dump to the file like this pg_dump -Upostgres -dpostgres > file.dmp the file file.dmp is empty and if i execute the psql with list database option, the result is error -bash-3.00$ psql -l ERROR: relation "pg_catalog.pg_user" does not exist can any one help me Thanx tosa === PT. BANK ARTHA GRAHA INTERNASIONAL TBK. DISCLAIMER: This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. If you are not the intended recipient you are notified that disclosing, copying, distributing or taking any action in reliance on the contents of this information is strictly prohibited. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] AUTO INCREMENT
On Sat, 10 Jun 2000, GANESH KUMAR wrote: > i am searching for > autoincrement in table > > in table employee > i want give empno as autoincrement create table xyz (id serial primary key); check documentation what it really is:). Marcin Mazurek -- administrator MULTINET SA o/Poznan http://www.multinet.pl/
[SQL] ltsWriteBlock: failed to write block XX of temporary file. Perharps out of disk space...
I have a database and a table with 7 records (which i dont think it's too much) I join this table with another of about the same size and put a record restriction (WHERE) and everything works out fine, but when I put two SUMs over a couple of fields and add a GROUP BY I keep getting this error, ltsWriteBlock: failed to write block 27231 of temporary file Perhaps out of disk space? I checked and I have over 1GB free so I figure this must have something to do with configuration parameters. I'd appreciate any help. Ligia
[SQL] Re: ltsWriteBlock: failed to write block XX of temporary file. Perharps out of disk space...
I fixed it!, it just added an index! Thanks anyway... "Ligia M. Pimentel" <[EMAIL PROTECTED]> wrote in message 944kbb$114p$[EMAIL PROTECTED]">news:944kbb$114p$[EMAIL PROTECTED]... > I have a database and a table with 7 records (which i dont think it's > too much) > I join this table with another of about the same size and put a record > restriction (WHERE) and everything works out fine, but when I put two SUMs > over a couple of fields and add a GROUP BY I keep getting this error, > > ltsWriteBlock: failed to write block 27231 of temporary file > Perhaps out of disk space? > > I checked and I have over 1GB free so I figure this must have something to > do with configuration parameters. > > I'd appreciate any help. > > > Ligia > >
[SQL] Query optimizing - paradox behave
NOTICE: QUERY PLAN: Sort (cost=2194791.19..2194791.19 rows=2555204 width=284) -> Merge Join (cost=8978.44..9453.57 rows=2555204 width=284) -> Sort (cost=990.43..990.43 rows=8725 width=72) -> Seq Scan on patient (cost=0.00..212.25 rows=8725 width=72) -> Sort (cost=7988.00..7988.00 rows=29286 width=212) -> Seq Scan on study (cost=0.00..1236.86 rows=29286 width=212) Restructured tables i.e. PAtient-study relationship is 1:n realized with column patientoid in table study. Table "patient" Attribute | Type | Modifier --++-- chilioid | character varying(80) | name | text | id | character varying(256) | birthdate| date | birthtime| time | sex | character(1) | medicalrecordlocator | character varying(128) | Table "study" Attribute| Type | Modifier ++-- chilioid | character varying(80) | instanceuid| character varying(64) | id | character varying(64) | studydate | date | studytime | time | modality | character varying(2) | manufacturer | character varying(128) | referingphysician | text | description| character varying(128) | manufacturersmodelname | character varying(128) | importtime | double precision | chilisenderid | character varying(80) | accessionnumber| character varying(64) | institutionname| character varying(128) | workflowstate | character varying(8) | flags | character varying(8) | performingphysician| character varying(128) | reportingphysician | character varying(128) | patientoid | character varying(80) | The times of the processes are escape-eliminated by statistical methods. I determined that the "compare" database is 8% times faster than the new restructured "pacs" database. How can I understand this? Whats my mistake? Anybody who can make some sugestions on the above will receive my enthusiastic gratitude David M. Richter begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Query optimizing - paradox behave
ient.name using <" > 2tableni NOTICE: QUERY PLAN: Sort (cost=2194791.19..2194791.19 rows=2555204 width=284) -> Merge Join (cost=8978.44..9453.57 rows=2555204 width=284) -> Sort (cost=990.43..990.43 rows=8725 width=72) -> Seq Scan on patient (cost=0.00..212.25 rows=8725 width=72) -> Sort (cost=7988.00..7988.00 rows=29286 width=212) -> Seq Scan on study (cost=0.00..1236.86 rows=29286 width=212) Restructured tables i.e. PAtient-study relationship is 1:n realized with column patientoid in table study. Table "patient" Attribute | Type | Modifier --++-- chilioid | character varying(80) | name | text | id | character varying(256) | birthdate| date | birthtime| time | sex | character(1) | medicalrecordlocator | character varying(128) | Table "study" Attribute| Type | Modifier ++-- chilioid | character varying(80) | instanceuid| character varying(64) | id | character varying(64) | studydate | date | studytime | time | modality | character varying(2) | manufacturer | character varying(128) | referingphysician | text | description| character varying(128) | manufacturersmodelname | character varying(128) | importtime | double precision | chilisenderid | character varying(80) | accessionnumber| character varying(64) | institutionname| character varying(128) | workflowstate | character varying(8) | flags | character varying(8) | performingphysician| character varying(128) | reportingphysician | character varying(128) | patientoid | character varying(80) | The times of the processes are escape-eliminated by statistical methods. I determined that the "compare" database is 8% times faster than the new restructured "pacs" database. How can I understand this? Whats my mistake? Anybody who can make some sugestions on the above will receive my enthusiastic gratitude David M. Richter begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Re: Query optimizing - paradox behave
Hallo! Thanks a lot to You Tom. I stared only at the user and the system time. I didn't found any C-function, with wich I could measure the whole time, so I used getrusage(). I did not recognize, that the whole time is reduced, because the User time is increased. Could that anybody explain to me? Why is the usertime increased and the whole time is decreased? # Anyway .. Thanks all a lot for Your effort. I will now tune my radiology-database further... Thankful Greetings David "David M. Richter" <[EMAIL PROTECTED]> writes: > The query with the 3 tables is faster than the query with 2 tables. How you figure that? > time psql -d compare -c "SELECT patient.*,study.* FROM > patient,study,relpatient_study000 r0 WHERE > (patient.chiliOID=r0.parentOID AND study.chiliOID=r0.childOID) order by > patient.name using <" > 3tableni > 1.671u 0.130s 0:11.14 16.4% 0+0k 0+0io 208pf+0w > time psql -d pacs -c "SELECT patient.*,study.* FROM patient,study WHERE > (patient.chiliOID=study.patientOID ) order by patient.name using <" > > 2tableni > 1.730u 0.120s 0:09.44 19.5% 0+0k 0+0io 208pf+0w 9.44 vs 11.14 seconds looks like a clear advantage for the second query to me... begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: Query optimizing - paradox behave
Hallo Stephan! Are there several versions of dbPG95GetIndex existing, or did you mention postgres version 7.1.2? With a little help I have killed the Problem!! Yeah, But now I trying to improve the C-code. Do You have any experience with optimizing C-Code. Are there some new miracle-like function, wich improves the speed dramatically? Do You know some database options wich I could change for better performance? Thanks a lot for Your suggestions!! David >What version are you using? (dbPG95GetIndex?) begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-15296 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] View consistency
Hello! Im using several views for the Usermanagment of a database. My question is: How does postgres keep the views consistent to the according tables( if the original table has been changed)? Is there a Rule? and how is the Rule invoked. With ON DELETE UPDATE INSERT of the original table? I didnt found any Rule in pg_rules. There should be a rule, because I cannot imagine another way wich tells the view that the table has been changed. The problem is: If updating the view takes too much time after every little manipulation, I couldnt use views for usermanagement because of performance problems. Any hints and facts? Thanks in advance David begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum/German Cancer Research Center;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-17024 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] table restruction
Hello! I want to restructure a table called study. this table has the following structure: Table "study" Attribute| Type | Modifier ++-- chilioid | character varying(80) | instanceuid| character varying(64) | id | character varying(64) | studydate | date | studytime | time | modality | character varying(2) | manufacturer | character varying(128) | referingphysician | character varying(128) | description| character varying(128) | manufacturersmodelname | character varying(128) | importtime | double precision | chilisenderid | character varying(80) | accessionnumber| character varying(64) | institutionname| character varying(128) | workflowstate | character varying(8) | flags | character varying(8) | performingphysician| character varying(128) | reportingphysician | character varying(128) | parentoid | character varying(80) | So , what I have to do is to move the column parentoid between chilioid and instanceoid. After that operation parentoid is the second column in the table study. Can I do any restructuring in the running database or should I dump the database and create a new changed schema and then put the data from the dump back? So this would spent a lot of time, otherwise it works sure. Is there any oppertunity to restructure the existing database without using a dump? Thanks in advance David begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum/German Cancer Research Center;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-17024 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] table restruct...
Hi! Thanks, to You! Yes I have to do . Now I solved that problem with rename the original table study to _study then create the new right structured table study , Insert into study (chilioid,...,...) SELECT * FROM _study; Ok not elegant but it works. Another questions: Can I change the physical order of the rows in a database? Is the order of a database under all circumstances the same? (in pg) That is essential for my further restruction of the database... Thanks a lot David begin:vcard url;quoted-printable:http://mbi.DKFZ-Heidelberg.de/=0D=0A n:M. Richter;David x-mozilla-html:FALSE org:Deutsches Krebsforschungszentrum/German Cancer Research Center;Division Medizinische und Biologische Informatik version:2.1 email;internet:[EMAIL PROTECTED] adr;quoted-printable:;;Im Neuenheimer Feld 280 url: http://mbi.DKFZ-Heidelberg.de/=0D=0AD-69120 Heidelberg, Germany ;Heidelberg;Germany;; x-mozilla-cpt:;-17024 fn:David M. Richter end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Conversion
On Tue, Aug 13, 2002 at 11:32:25AM +0800, Christopher Kings-Lynne wrote: > Is the int4 a UNIX epoch? ie. seconds since 1970? > > If so, then this will generally work: > > SELECT CAST(int4field AS abstime); > > or > > SELECT int4field::abstime; http://www.postgresql.org/idocs/index.php?datatype-datetime.html says: The types abstime and reltime are lower precision types which are used internally. You are discouraged from using any of these types in new applications and are encouraged to move any old ones over when appropriate. Any or all of these internal types might disappear in a future release. Don't they? -- Fduch M. Pravking ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Conversion
On Tue, Aug 13, 2002 at 03:14:38PM +0800, Christopher Kings-Lynne wrote: > > http://www.postgresql.org/idocs/index.php?datatype-datetime.html says: > > > > The types abstime and reltime are lower precision types which are used > > internally. You are discouraged from using any of these types in new > > applications and are encouraged to move any old ones over when > > appropriate. Any or all of these internal types might disappear in a > > future release. > > Yes, but in absence of: > > SELECT EXTRACT(TIMESTAMP FROM EPOCH '12341234234'); Sounds nice :) > (Hint Hint Thomas!!!) > > It's all he can do. I suggest using the syntax above to convert his integer > column to a timestamp column. Sure. I use the same. But I don't like it because of that caution :( The other way is SELECT 'epoch'::timestamp + (int4field::text || 's')::interval, but it's much much slower... And it seems not to handle timestamps after 2038-01-19. -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Results from EXECUTE
How can I obtain results from an EXECUTE statement within a pl/PgSQL function? E.g., something like value := EXECUTE ''SELECT '' || quote_ident(field_name) || '' FROM ...''; Thanks in advice. -- Fduch M. Pravking ---(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] Different size in the DATA directory
On Fri, Nov 01, 2002 at 10:48:00PM -0500, Bruce Momjian wrote: > Tim, I guess your problem is dead index pages that can't be reclaimed, > and it isn't fixed in 7.3. Only REINDEX fixes it, and we have a > /contrib/reindexdb script in 7.3. As I see, contrib/reindexdb requires perl for commandline procesing. I don't think it's a good idea, since e.g. FreeBSD 5.0-CURRENT have no perl in standard distribution. Thomas, why not to use sed? -- Fduch M. Pravking ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Different size in the DATA directory
On Sat, Nov 02, 2002 at 08:20:04PM -0500, Bruce Momjian wrote: > Alexander M. Pravking wrote: > > As I see, contrib/reindexdb requires perl for commandline > > procesing. I don't think it's a good idea, since > > e.g. FreeBSD 5.0-CURRENT have no perl in standard distribution. > > > > Thomas, why not to use sed? > > No perl? I am no perl guy, but I assumed everyone had that already. > > I just looked at the code, and yes, it should use sed rather than perl, > especially since it is using it just for processing command line args. > > Seems it is a problem/bug for you. Patch applied to use sed rather than > perl. Well, I DO use perl, so it's not a problem for me :) But I see many people on these lists that do not. Thanks, Bruce. -- Fduch M. Pravking ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Problem with looping on a table function result
I've met the following problem.
I had successfully written a function divide_into_days(timestamp, timestamp)
which returns setof (timestamp, timestamp) pairs - a list of days the
given interval is divided into.
What I want is to use each record from resultset to pass to another
function, something like:
SELECT days.*, summary_stats(days.day_start, days.day_end)
FROMdivide_into_days('2003-06-01', '2003-07-01') days;
The problem is that summary_stats function returns a record, so I have
to use SELECT * FROM summary_stats(...). I can't use the following too:
SELECT *
FROMsummary_stats(days.day_start, days.day_end) stats,
divide_into_days('2003-06-01', '2003-07-01') days;
(there was a discussion a few days ago about using subselects,
but here's a slightly different case).
I wonder if where's a way to do the trick without writing one more
PL/PgSQL table function doing FOR row IN SELECT ... LOOP or using
client-side loop?
Thanks for your help.
--
Fduch M. Pravking
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Expression transformation curiosity
On Sun, Aug 17, 2003 at 11:32:58AM +0100, Richard Huxton wrote: > I just had a look at EXPLAIN ANALYSE output for an SQL function I'm trying to > write. The WHERE clause wasn't too complex, but the explain output took up a > dozen lines. > > Boiling the problem down, I've looked at a clause of the form: > a OR (b AND c) > which PG converts to: > (a OR b) AND (a OR c) > > Now these two are equivalent, but it would take me forever to demonstrate that > with the full query. I'm happy the planner is going to get it right, but I'm > confused as to why the transformation occurs. > > Is it an artefact of displaying the EXPLAIN, or is it actually processed that > way? You could see how testing "a" twice could be expensive in some > situations. Looks like it actually works this way. I had the same problem several weeks ago on 7.3.3 with 4 such OR's. The final filter became monsterous, and the query was very slow. I've simply rewritten the query using UNION, and it became much faster. -- Fduch M. Pravking ---(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] Backup of multiple tables
On Fri, Sep 19, 2003 at 01:54:01PM +0200, Andreas Joseph Krogh wrote: > I usually backup my database with pg_backup without the -t option. But now I > need to only backup certain tables(say tab1 and tab2), is this possible with > pg_dump? I've tried with "pg_dump -t tab1 -t tab2" without success. Here's a perl script I used to dump all the tables separately. I'm not sure most of options do work there, I didn't test ;-) It won't be hard to make it dump certain tables, I think. -- Fduch M. Pravking dump.pl Description: Perl program ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Backup of multiple tables
On Fri, Sep 19, 2003 at 04:30:57PM +0200, Andreas Joseph Krogh wrote: > > Here's a perl script I used to dump all the tables separately. > > I'm not sure most of options do work there, I didn't test ;-) > > It won't be hard to make it dump certain tables, I think. > > Thanks for your suggestion, but the problem with it is that I may end up with > inconsistencies if data is inserted/updated or deleted in one of the tables > during the backup, so I would miss the "snapshot"-effect. You can try to explicitly lock all tables being dumped from the script before and release them after dump is complete... But there could be dead-lock conditions. What will gurus say? -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] STRICT function returning a composite type
I noted that such a function returns an empty rowset if a NULL value is
passed as an argument. Is it a bug or feature? I wish it was a feature,
because I probably want to use this behavour.
Here's an example:
CREATE TYPE ts_bounds AS (
sdate timestamptz,
edate timestamptz
);
CREATE OR REPLACE FUNCTION overlap_bounds(timestamptz, timestamptz, timestamptz,
timestamptz)
RETURNS ts_bounds AS '
DECLARE
sdate1 ALIAS FOR $1;
edate1 ALIAS FOR $2;
sdate2 ALIAS FOR $3;
edate2 ALIAS FOR $4;
res ts_bounds%rowtype;
BEGIN
res.sdate := CASE WHEN sdate1 > sdate2 THEN sdate1 ELSE sdate2 END;
res.edate := CASE WHEN edate1 < edate2 THEN edate1 ELSE edate2 END;
IF res.sdate > res.edate THEN
res.sdate := NULL;
res.edate := NULL;
END IF;
RETURN res;
END' LANGUAGE 'plPgSQL' STRICT;
fduch=# SELECT * from overlap_bounds('-infinity', 'today', 'yesterday', 'infinity');
sdate | edate
+
2003-11-12 00:00:00+03 | 2003-11-13 00:00:00+03
(1 row)
fduch=# SELECT * from overlap_bounds('-infinity', 'today', 'yesterday', null);
sdate | edate
---+---
(0 rows)
What I want is to get no rows if given intervals don't overlap instead of:
fduch=# SELECT * from overlap_bounds('-infinity', 'yesterday', 'today', 'infinity');
sdate | edate
---+---
|
(1 row)
Is it possible without returning SETOF ts_bounds?
fduch=# SELECT version();
version
-----
PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
--
Fduch M. Pravking
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] STRICT function returning a composite type
On Thu, Nov 13, 2003 at 05:14:27PM +, Richard Huxton wrote: > RETURNS NULL ON NULL INPUT or STRICT indicates that the function always > returns NULL whenever any of its arguments are NULL. If this parameter is > specified, the function is not executed when there are NULL arguments; > instead a NULL result is assumed automatically. Does "NULL result" mean an empty rowset if the function returns a record? -- Fduch M. Pravking ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] STRICT function returning a composite type
On Thu, Nov 13, 2003 at 12:27:58PM -0500, Tom Lane wrote: > "Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > > Does "NULL result" mean an empty rowset if the function returns a record? > > No, it means a null record. "Empty rowset" would apply to a function > declared to return SETOF something. (I believe that is how we > interpret the concept of strictness for functions returning sets.) Very well then... Can I return a null record from such function explicitly? Sorry, I could't find it anywhere in docs or examples. -- Fduch M. Pravking ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] STRICT function returning a composite type
On Thu, Nov 13, 2003 at 12:35:41PM -0500, Tom Lane wrote: > "Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > > Very well then... Can I return a null record from such function > > explicitly? Sorry, I could't find it anywhere in docs or examples. > > Not sure. Seems like you should be able to, but I've never tried it. Thanks for a quick response, guys. I'll try to find it myself and will let you know if I did ;) -- Fduch M. Pravking ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Validity check in to_date?
I just discovered that to_date() function does not check if supplied
date is correct, giving surprising (at least for me) results:
fduch=# SELECT to_date('31.11.2003', 'DD.MM.');
to_date
2003-12-01
or even
fduch=# SELECT to_date('123.45.2003', 'DD.MM.');
to_date
2007-01-03
to_timestamp() seems to work the same way. It's probably useful sometimes,
but not in my case... Is it how it supposed to work?
If so, how can I do such a validity check?
If not, has something changed in 7.4?
In any case, I have to find a workaround now and will appreciate any help.
fduch=# SELECT version();
version
-
PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
--
Fduch M. Pravking
---(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] Validity check in to_date?
On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote: > As far as I know these results are correct in terms of the underlying > C-library function mktime(). This function is intended to be used when > adding/subtracting intervals from a given timestamp. Which one? mktime() or to_date()? I'm not sure it's handy to use to_date() for any calculations, so I'm surprised why doesn't it work just as date_in() do. > I don't know of any postgres function doing the check you're looking for. Yes, the only thing I could think now is to do something like s/([0-9]+)\.([0-9]+)\.([0-9]+)/\3-\2-\1/ and then pass it to CAST(... AS date) using ISO DateStyle. (I could simply use German DateStyle in case of DD.MM., but I deal with several date formats, e.g. DD/MM/.) > But I can't believe this is the first time this topic is brought up. > You may search the archives on "date plausibility" are related terms. I'm sure too, but it's really hard to find a good keyword sequence when searching such sort of things :( Anyway, thank you for attention. -- Fduch M. Pravking ---(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] Validity check in to_date?
On Tue, Dec 02, 2003 at 10:57:14AM +0100, Karel Zak wrote: > > > If not, has something changed in 7.4? > > No change in 7.4. Maybe in 7.5 or in some 7.4.x. Well, let's see. > The others PostgreSQL stuff which full parse (means check ranges) > date/time is less optimistic with this: > > # select '31.11.2003'::date; > ERROR: date/time field value out of range: "31.11.2003" Exactly! But date_in formats are too limited and "floaty", especially in 7.3 or less. -- Fduch M. Pravking ---(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] Validity check in to_date?
On Tue, Dec 02, 2003 at 07:44:54PM +0900, Iain wrote: > T've been following this thread with interest because I have a related > problem. Basically we are storing dates in CHAR fields with al the > associated problems. I'd like to do it, but changing everything to date > fields isn't practical for now, so as a stopgap solution, I want to provide > some validation at the database level. > > I tried: > > create domain ymdtest2 as char(10) constraint valid_date check > (VALUE::DATE); There's no conversion function from char(n) to date, but there's one from text to date. Try using check (VALUE::text::date). -- Fduch M. Pravking ---(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] Validity check in to_date?
On Tue, Dec 02, 2003 at 01:55:06PM +0300, Alexander M. Pravking wrote: > Try using check (VALUE::text::date). Assuming check expects boolean result, it's (VALUE::text::date IS NOT NULL) -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Problem with intervals
On Mon, Dec 01, 2003 at 09:09:20PM -0800, Bob Smith wrote:
> I'm getting an unexpected result using intervals in an expression:
>
> select ('2003-10-26 0:00:00'::timestamp + '1 day'::interval)::date;
> date
>
> 2003-10-26
> (1 row)
Try using '2003-10-26 0:00:00'::date + 1;
integers do not lie ;-)
> When I get rid of the date cast it becomes clear what is happening:
>
> select '2003-10-26 0:00:00'::timestamp + '1 day'::interval;
> ?column?
>
> 2003-10-26 23:00:00-08
> (1 row)
>
> Is this a Postgres bug, or is this correct SQL behavior? I'm running
> Postgres 7.2.2.
It has been discussed several times, Tom Lane offered to add 'day' as
a separate interval unit (like 'second' and 'month' at this moment),
but noone took a shot at it, AFAIK.
Note also, that in 7.3 "timestamp" means "timestamp without time zone",
while in 7.2 it's "timestamp with time zone".
--
Fduch M. Pravking
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Materialized View Summary
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I've written a summary of my findings on implementing and using materialized views in PostgreSQL. I've already deployed eagerly updating materialized views on several views in a production environment for a company called RedWeek: http://redweek.com/. As a result, some queries that were taking longer than 30 seconds to run now run in a fraction of a millisecond. You can view my summary at http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Comments and suggestions are definitely welcome. - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAO3eZqp6r/MVGlwwRAnpEAKC8+/lFyPBbXetPEfFLwgUvJZLCmgCfYlmR 0vZmCcbGSNT/m/W8QOIhufk= =snCu -END PGP SIGNATURE- ---(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: [HACKERS] [SQL] Materialized View Summary
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm not sure if my original reply made it through. Ignore the last one if it did. On Tuesday 24 February 2004 1:48 pm, Robert Treat wrote: > On Tue, 2004-02-24 at 12:11, Richard Huxton wrote: > > On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote: > > > I've written a summary of my findings on implementing and using > > > materialized views in PostgreSQL. I've already deployed eagerly > > > updating materialized views on several views in a production > > > environment for a company called RedWeek: http://redweek.com/. As a > > > result, some queries that were taking longer than 30 seconds to run > > > now run in a fraction of a millisecond. > > > > > > You can view my summary at > > > http://jonathangardner.net/PostgreSQL/materialized_views/matviews.h > > >tml > > have you done much concurrency testing on your snapshot views? I > implemented a similar scheme in one of my databases but found problems > when I had concurrent "refresh attempts". I ended up serializing the > calls view LOCKing, which was ok for my needs, but I thought > potentially problematic in other cases. > We are running into some small problems with deadlocks and multiple inserts. It's not a problem unless we do a mass update to the data or something like that. I'm interested in how you solved your problem. I am playing with an exclusive lock scheme that will lock all the materialized views with an exclusive lock (see Section 12.3 for a reminder on what exactly this means). The locks have to occur in order, so I use a recursive function to traverse a dependency tree to the root and then lock from there. Right now, we only have one materialized view tree, but I can see some schemas having multiple seperate trees with multiple roots. So I put in an ordering to lock the tables in a pre-defined order. But if the two dependency trees are totally seperate, it is possible for one transaction to lock tree A and then tree B, and for another to lock tree B and then tree A, causing deadlock. Unfortunately, I can't force any update to the underlying tables to force this locking function to be called. So we will probably call this manually before we touch any of those tables. In the future, it would be nice to have a hook into the locking mechanism so any kind of lock on the underlying tables can trigger this. Also, building the dependency trees is completely manual. Until I can get some functions to actually assemble the triggers and such, automatic building of the trees will be difficult. - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAPFqRqp6r/MVGlwwRAnvPAJ90lEEyaBzAfUoLZU93ZDvkojaAwwCdGjaA YBlO57OiZidZuQ5/S0u6wXM= =bMYE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] query optimization
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 04 March 2004 9:56 am, Charles Hauser wrote: > All, > > I have the following query which is running quite slow on our server > and was hoping someone would have suggestions how I might improve it. > Might want to try emailing the performance list as well, after you take Tom's advice. They tend to get more excited about performance issues. - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFASKq6qp6r/MVGlwwRAjeiAKCJpD/imnxP71f1GU8zpUVrWvd+qACeNcOF 9KvCM7HSp7jr9Ep9Xrs050M= =Ph6a -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Triggers
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 26 February 2004 2:18 am, Philip J. Boonzaaier wrote: > The technical reference gives an example of a trigger on a table - > employee Just to test this, I have created the following table, > CREATE TABLE employee > (name VARCHAR(30), > age int4, > state VARCHAR(2), > manager VARCHAR(3), > adult VARCHAR(3)); > > The I created a simple Function, as follows : > > CREATE FUNCTION trig_insert_update_check_emp() RETURNS opaque AS ' > BEGIN > IF new.age > 20 THEN > new.adult = ''yes''; > ELSE > new.adult = ''no''; > END IF; > END; > ' LANGUAGE 'plpgsql'; Couple of comments: 1) Your trigger functions needs to return something. In this case, you would "RETURN NEW". 2) I believe trigger functions need to "RETURNS TRIGGER", not "RETURNS opaque", but I could be wrong. Give that a try. Question: 1) What version of PostgreSQL are you running? - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFASKulqp6r/MVGlwwRAgpLAKCycwL/i+/mb6bW1W4QjHKBO9e0xQCgl42q pmohSw7PZiuIWgOQXxtgvI0= =4iDJ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] VIEW on lookup table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Friday 27 February 2004 2:18 pm, JJ Gabor wrote: > Hello all, > > I have a lookup table consisting of 100,000+ rows. > > 99% of the lookup values resolve to 'Unknown'. > > Building the lookup table takes a long time. > > I would like to remove the 'Unknown' entries from the > table and provide a VIEW to emulate them. > > The VIEW would need to provide all 100,000+ rows by > using the reduced lookup data and generating the > remaining values on the fly. > > The lookup table structure: > > CREATE TABLE lookup_data ( > > id1 INTEGER, > id2 INTEGER, > name TEXT, > > PRIMARY KEY (id1, id2) > ); > > id1 is an INTEGER; from 0 through to 50,000+ > id2 is an INTEGER; either 9 or 16. > > Example data: > > INSERT INTO lookup_data (id1, id2, name) VALUES (1, 9, 'a'); > INSERT INTO lookup_data (id1, id2, name) VALUES (1, 16, 'b'); > INSERT INTO lookup_data (id1, id2, name) VALUES (2, 9, 'c'); > INSERT INTO lookup_data (id1, id2, name) VALUES (2, 16, 'd'); > INSERT INTO lookup_data (id1, id2, name) VALUES (3, 9, 'e'); > INSERT INTO lookup_data (id1, id2, name) VALUES (3, 16, 'f'); > INSERT INTO lookup_data (id1, id2, name) VALUES (4, 9, 'g'); > INSERT INTO lookup_data (id1, id2, name) VALUES (4, 16, 'h'); > INSERT INTO lookup_data (id1, id2, name) VALUES (8, 9, 'i'); > INSERT INTO lookup_data (id1, id2, name) VALUES (8, 16, 'j'); > INSERT INTO lookup_data (id1, id2, name) VALUES (10, 9, 'k'); > INSERT INTO lookup_data (id1, id2, name) VALUES (10, 16, 'l'); > .. > > In the example data, entries where id1 is 5,6,7,9 are 'Unknown'; > > The VIEW would return: > > id1, id2, name > 1, 9, 'a' > 1, 16, 'b' > 2, 9, 'c' > 2, 16, 'd' > 3, 9, 'e' > 3, 16, 'f' > 4, 9, 'g' > 4, 16, 'h' > 5, 9, 'Unknown' > 5, 16, 'Unknown' > 6, 9, 'Unknown' > 6, 16, 'Unknown' > 7, 9, 'Unknown' > 7, 16, 'Unknown' > 8, 9, 'i' > 8, 16, 'j' > 9, 9, 'Unknown' > 9, 16, 'Unknown' > 10, 9, 'k' > 10, 16, 'l' > > I am using Postgres 7.2.1, which prevents me using a > function to return a result set. > > Can I achieve this in pure SQL? Yes. If you create a table with all of the values, 1 to 100,000+, and then join that with lookup_data, using a "left outer join", and then use a case statement for the value -- when NULL, 'Unknown', then it should work. I would look at bending the requirements a bit before I do this. Why do you want the string "Unknown" and not NULL? What is this table going to be used for? Also, just because you can't write a function in the database to do this doesn't mean you can't write a function in perl or python outside of the database to do it. Also, seriously consider upgrading to 7.4.1. 7.2 is ancient and really shouldn't be used anymore. - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFASK0wqp6r/MVGlwwRAub2AKCUcqvFvkD1KjXLEeg8osybgw5kqwCgiq8W YiJY3ZYsAXNfjjBTCF0vGKE= =5EIl -END PGP SIGNATURE- ---(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] Break a report in Run Time
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thursday 11 March 2004 9:11 am, Jander wrote: > I need to abort the SQL process running in DB Server. > If you are in psql, you can cancel any query with CTRL-C. Otherwise, you can kill the process running the query using the "kill" command. If that doesn't work you can kill (but not -9!) the backend process handling your query. > > - Original Message - > From: "Jonathan Gardner" <[EMAIL PROTECTED]> > To: "Jander" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Wednesday, March 10, 2004 6:09 PM > Subject: Re: [SQL] Break a report in Run Time > > > On Wednesday 10 March 2004 10:23 am, Jander wrote: > > >I have a application with a lof of reports. I need > > > to break a report in Run Time. How can I do this? > > > > Could you clarify what you mean by "break a report in run time"? > > > > -- > > Jonathan Gardner > > [EMAIL PROTECTED] > > > > ---(end of > > broadcast)--- TIP 4: Don't 'kill -9' the > > postmaster - -- Jonathan Gardner [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFAUJIpqp6r/MVGlwwRAi8eAJ4q9UHzimQtlR3I+XdGICvfF8ZfbgCdHU6q 77RNJ+5WBwAwrZ1pbD+7meg= =BsCQ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Row count after SELECT INTO?
Dear SQL and plPgSQL gurus, I seek for your wisdom. I have a variable assignment via SELECT INTO in my function, and I want to separate 3 cases: 1) no rows found; 2) one row found; 3) many rows found (ambiguous select). The first case is simple, I use FOUND variable for it. But how about the third? I tried to GET DIAGNOSTICS nrows = ROW_COUNT, bit it seems to get only value of 0 or 1. Is this how it supposed to be when SELECTing INTO a single variable? The only way I see now is a FOR ... IN SELECT loop, and I woner if there is a simpler solution. Could you please help me? -- Fduch M. Pravking ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Memory usage on subselect
Hello, PostgreSQL users and developers. I've got a memory usage problem when I try to do a subselect on the same table as the main select (PostgreSQL 7.3.4 on FreeBSD). Here's my query: SELECT sreq(s1.id, 'ipacct_ip', now()), s1.* FROMservices s1 WHERE EXISTS ( SELECT 1 FROMservices s2 WHERE s2.id != s1.id AND sreq(s2.id, 'ipacct_ip', now()) = sreq(s1.id, 'ipacct_ip', now()) AND s2.sdate < now() AND s2.edate > now() ) AND s1.sdate < now() AND s1.edate > now(); I.e. I want to find all records from services which have equal values of sreq(...) for them (additionally filtering only those which are actual now). The "services" table is indexed only on "id" column and has about a thousand tuples. sreq(integer, text, timestamptz) is a strict immutable function written in SQL. EXPLAIN says the following: Seq Scan on services s1 (cost=0.00..38628.80 rows=38 width=55) Filter: ((sdate < now()) AND (edate > now()) AND (subplan)) SubPlan -> Seq Scan on services s2 (cost=0.00..56.08 rows=1 width=0) Filter: ((id <> $0) AND (sreq(id, 'ipacct_ip'::text, now()) = sreq($0, 'ipacct_ip'::text, now())) AND (sdate < now()) AND (edate > now())) I see no evil here (of course, the query is going to be slow), but the postgres process begins to consume a lot of memory (I cancelled a query after ~500M). Am I doing something wrong or is it expected behavour? I never seen this before, so I'd think it's me who mistaken, but I can't find anything wrong for a few hours :) Here's subquerie's EXPLAIN ANALYZE for a sample (existing) s1.id: EXPLAIN ANALYZE SELECT 1 FROMservices s2 WHERE s2.id != 561 AND sreq(s2.id, 'ipacct_ip', now()) = sreq(561, 'ipacct_ip', now()) AND s2.sdate < now() AND s2.edate > now(); Seq Scan on services s2 (cost=0.00..56.08 rows=1 width=0) (actual time=177.01..177.01 rows=0 loops=1) Filter: ((id <> 561) AND (sreq(id, 'ipacct_ip'::text, now()) = sreq(561, 'ipacct_ip'::text, now())) AND (sdate < now()) AND (edate > now())) Total runtime: 177.05 msec I can provide other details, if needed. Thanks in advance. -- Fduch M. Pravking ---(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] Memory usage on subselect
On Sun, May 23, 2004 at 12:28:18PM -0400, Tom Lane wrote: > "Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > > I've got a memory usage problem when I try to do a subselect on the same > > table as the main select (PostgreSQL 7.3.4 on FreeBSD). Here's my query: > > ... > > The "services" table is indexed only on "id" column and has about a > > thousand tuples. sreq(integer, text, timestamptz) is a strict immutable > > function written in SQL. > > IIRC, there were intraquery memory leaks associated with SQL-language > functions until fairly recently. Can you try your problem case on 7.4? > Or see if you can rewrite the sreq function in plpgsql. Thanks, Tom, both 7.4.1 and plpgsql function on 7.3.4 work well. However, plpgsql function is a little slower, but that's another story. I also tried to turn off IMMUTABLE for the function, but it did not affect memory usage on 7.3.4. BTW, after cancelling the original query postgres freed all the memory, and used ~7M again, so the leak was not "forever". Good luck, thansk again ;) -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: RES: [SQL] Datetime problem
On Mon, Jun 14, 2004 at 01:20:14PM -0300, Eric Lemes wrote:
> Hello,
>
> - PostgreSQL 7.3.2 on i386-redhat-linux GCC 3.2.2
> - Timezone: Brazil (GMT-3, I think).
What's about daylight saving time for you?
I'm almost sure the DST boundary is near the date in your example.
However, with 7.3.4 on FreeBSD I get:
fduch=# SHOW TimeZone ;
TimeZone
---
Europe/Moscow
(1 row)
fduch=# SELECT to_timestamp('2004 10 31 00 00 00', ' MM DD HH MI SS');
to_timestamp
2004-10-31 00:00:00+04
(1 row)
fduch=# SELECT to_timestamp('2004 11 01 00 00 00', ' MM DD HH MI SS');
to_timestamp
2004-11-01 00:00:00+03
(1 row)
So both timestamps before and after boundary are parsed well for me.
> I think my problem is with the time zone. Using a SET TIME ZONE GMT, the
> result is Ok. But I don't know how to work with time zones correctly.
>
> When I send a date to to_timestamp, pgsql thinks this date is in GMT?
Hmm, 7.3 and 7.4 docs say that it returns timestamp (WITHOUT time zone
is default since 7.3 IIRC), but in fact it accepts and returns timestamp
WITH time zone. This is probably a documentation bug...
--
Fduch M. Pravking
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] feature request ?
On Thu, Jun 24, 2004 at 11:04:15AM +0400, sad wrote: > Now you treat NULLs as false. Nope. NULL is neither true, nor false. It's "unknown", or "undefined". fduch=# SELECT 1 WHERE NULL::boolean; ?column? -- (0 rows) fduch=# SELECT 1 WHERE NOT NULL::boolean; ?column? -- (0 rows) So if you care, you SHOULD use IS [NOT] NULL, as Michael Glaesemann suggested. If you don't want expression to be calculated twice, use a temporary variable. -- Fduch M. Pravking ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] UPDATE ... WHERE (subselect on the same table)
I was looking for how can I give an alias for the table being updated (something like UPDATE table_name table_alias SET ...), but the current syntax doesn't allow that. What I need is to: fduch=# SELECT * from test order by typ, name; typ | name | x -+--+--- 1 | bar | 1 | baz | 1 | foo | 2 | baz | 2 | foo | (5 rows) fduch=# UPDATE test SET x = 't' fduch-# where typ = 1 and exists ( fduch(# SELECT 1 from test t2 fduch(# where t2.typ = 2 and t2.name = test.name fduch(# ); UPDATE 2 fduch=# SELECT * from test order by typ, name; typ | name | x -+--+--- 1 | bar | 1 | baz | t 1 | foo | t 2 | baz | 2 | foo | (5 rows) So I have two questions: Q1, cognitive. Why the alias for the updated table is restricted? Is there any reason for that or it's just not implemented? Q2, vital. Can I be sure that the syntax I used here will work correctly, i.e. will the "test.name" always refer the column in outer table, not inner (t2)? Thanks in advance. -- Fduch M. Pravking ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] UPDATE ... WHERE (subselect on the same table)
On Tue, Jun 29, 2004 at 12:49:55PM -0400, Tom Lane wrote: > > So I have two questions: > > Q1, cognitive. Why the alias for the updated table is restricted? > > Because the SQL standard doesn't allow an alias there. We've talked > about allowing one anyway, but no one's gotten around to it. AFAICS > it would only be a marginal notational advantage, not allow you to > express queries you can't express today. > > > Q2, vital. Can I be sure that the syntax I used here will work > > correctly, i.e. will the "test.name" always refer the column in outer > > table, not inner (t2)? > > Yes. The alias *completely* hides the real name of that table > reference, so "test" will never refer to "test t2". As always, perfectly clear, thank you Tom :) I already found in docs on SELECT: When an alias is provided, it completely hides the actual name of the table or table function; /me should RTFM... (repeating hundred times) -- Fduch M. Pravking ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Indexable (constant LIKE prefix_keyfield) ?
I was impressed that LIKE operator can be indexed in 7.4 with non-C
locale. But how about the reverse condition?
What I need is to:
SELECT * FROM prefixes WHERE 'literal' LIKE prefix || '%';
or
SELECT * FROM prefixes WHERE 'literal' ~ ('^' || prefix);
Prefix is of type text (variable-length), which may contain only ASCII
chars (database runs under ru_RU.KOI8-R locale). Only the longest prefix
should be taken if more than one matches.
There's no strict definition for "prefixes" yet, and I seek for how to
make it possible to use an index by this query. The ways I see:
1. Sequentially rtrim('literal') and compare it to prefix.
Really bad idea.
2. Use 2 fields: prefix_le and prefix_gt, then
'literal' >= prefix_le AND 'literal' < prefix_gt
(or 'literal' ~>=~ prefix_le AND 'literal' ~<~ prefix_gt, but it
seems there's no need to).
a) supply both fields from outside (I don't like this idea).
b) supply only prefix (=prefix_le), and calculate prefix_gt (using
trigger?) as prefix_le "plus one".
Digging the backend sources, I've found make_greater_string used
to expand indexable LIKE or regexp condition. Can I use it for my
needs somehow? Or have I to write my own in this case?
3. Create some magical index I dunno about :)
4. SELECT * FROM prefixes
WHERE prefix <= 'literal' AND 'literal' LIKE prefix || '%'
ORDER BY prefix DESC LIMIT 1;
Looks like the best way, but I'm not sure this is always correct.
Comments, suggestions, please?
--
Fduch M. Pravking
---(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] Problem in age on a dates interval
On Mon, Jul 19, 2004 at 10:00:50AM +0100, Luis Sousa wrote: > I worked around this problem returning the difference between the two > dates, using extract doy from both. > Anyway, this will cause a bug on my code when changing the year. Any ideas? Why don't you use the minus operator? SELECT '2004-05-14 16:00'::timestamp - '2004-02-18 16:00'::timestamp; ?column? -- 86 days Or, if you need the age just in days: SELECT extract(day from '2004-05-14 16:00'::timestamp - '2004-02-18 16:00'::timestamp); date_part --- 86 or SELECT '2004-05-14 16:00'::date - '2004-02-18 16:00'::date; ?column? -- 86 Note that '2004-05-14 16:00'::date is actually '2004-05-14 00:00'::date, so the last two are not always equal. > Tom Lane wrote: > > >Theodore Petrosky <[EMAIL PROTECTED]> writes: > > > > > >>wow at first I thought I had my head around a leap > >>year problem so I advanced your query a year > >> > >> > > > >I think what's going on here is a difference of interpretation about > >whether an "M months D days" interval means to add the months first > >or the days first. For instance > > > >2005-02-18 plus 2 months = 2005-04-18, plus 24 days = 2005-05-12 > > > >2005-02-18 plus 24 days = 2005-03-14, plus 2 months = 2005-05-14 > > > >The timestamp-plus-interval operator is evidently doing addition the > >first way, but it looks like age() is calculating the difference in a > >way that implicitly corresponds to the second way. > > > >I have some vague recollection that this has come up before, but > >I don't recall whether we concluded that age() needs to be changed > >or not. In any case it's not risen to the top of anyone's to-do list, > >because I see that age() still acts this way in CVS tip. > > > > regards, tom lane -- Fduch M. Pravking ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] HOW TO HANDLE ZEROS IN DATE FIELD?
I have a project that is taking input from another system. I have certain columns defined as 'Date' Columns. On input I will get '00' in this field which causes the insert to fail. I have read the docs on default and it is unclear to me if this will work. Does anyone have experience in solving this problem. The other alternative I thought of was to write a trigger to fix it ?? James M Doherty [EMAIL PROTECTED] Georgetown, TX 78626 "There is no luck without discipline" IRISH PROVERB ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CHECK col A not NULL if col B='x'
On Tue, Sep 28, 2004 at 03:02:02PM +0100, T E Schmitz wrote: > Hello, > > Is it possible to set up a table CHECK, which ensures that column A is > NOT NULL if column B = 'x' ? Sure. [EMAIL PROTECTED] CREATE TABLE test ( [EMAIL PROTECTED](# a integer check (case when b = 'x' then a is not null else true end), [EMAIL PROTECTED](# b text); CREATE TABLE [EMAIL PROTECTED] INSERT INTO test VALUES (null, '123'); INSERT 107538 1 [EMAIL PROTECTED] INSERT INTO test VALUES (null, 'x'); ERROR: new row for relation "test" violates check constraint "test_a" [EMAIL PROTECTED] INSERT INTO test VALUES (1, 'x'); INSERT 107539 1 -- Fduch M. Pravking ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] TEXT::CIDR/INET::CIDR output confusion
It looks a bit strange that CIDR output depends on datatype it has been casted from: fduch=# SELECT '1.1.1.1'::cidr; cidr 1.1.1.1/32 (1 row) fduch=# SELECT '1.1.1.1'::inet::cidr; cidr - 1.1.1.1 (1 row) However these two seem to be 'equal' in terms of backend: fduch=# SELECT '1.1.1.1'::inet::cidr = '1.1.1.1'::inet; ?column? -- t (1 row) fduch=# SELECT '1.1.1.1'::inet::cidr = '1.1.1.1'::cidr; ?column? -- t (1 row) I'm just curious how can it even be... fduch=# SELECT version(); version PostgreSQL 7.4.5 on i386-portbld-freebsd5.3, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728 -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Question about update syntaxt
I am trying to use an application (Through ODBC) that uses the following update syntax UPDATE MyTable SET MyTable.id=2 WHERE id=1 unfortunatly I get an error message ERROR: column "mytable" of relation "mytable" does not exist Question is, is ther something I can configure that will make Postgresql accept these kind of statments ? ---- Michael M Friedel Research & Development ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] 'select where' using multiple columns.
Is there a way to do something like a 'select * where forename_1,forename_2,surname like '%String%' ?? You could try the following, but it won't get a medal for performance... SELECT * FROM xpto WHERE forename_1 LIKE '%String%' OR forename_2 LIKE '%String%' OR surname LIKE '%String%' Helder M. Vieira ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Fwd: How to encrypt a column
I'm interested in encrypting an column in table. Are there any example
using "C" to create the encrypted column, inserting and retreiving data
to/from it?
the table is:
CREATE TABLE mytable (
id SERIAL PRIMARY KEY,
crypted_content BYTEA );
I'm getting (null) in the field with the following sql statement:
strcpy(data, "data to be encrypted");
sprintf(query_buff, "insert into mytable values('%s', '%s')", key,
encrypt(data, 'foo', 'bar'));
PQexec(conn, query_string);
Another question is can the encrypted column be of type "text" ?
I'd check a previous critical point ... The encrypt() function output.
What is the range of characters in encrypt() output ?
Control, null or false escape characters, if not properly escaped, could be
misleading either sprintf or the query processor in pg.
Helder M. Vieira
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] table listing queries
Namely, in MySQL I can say: "SHOW TABLES FROM 'dbname'" to list tables in a database I'm not currently connected to. In MySQL, table definitions are replicated outside the database files, and might therefore be available without connecting to a specifc database. This probably happens because of the need for some kind of unified repository of objects pertaining to different database engines. Helder M. Vieira ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
