[SQL] Data Calculation
I need some help for which I could not find much help for within the
Postgres book I am looking at, or the online tutorial. I don't know if
this is even possible, but here goes...
I am writing for advice, as the method I followed is not the most
effecient, I think.
Scenario: Grading results. I have two tables set up - one with the
answerkey and one with the students results from a test:
answerkey:
question_number int,
answer varchar(2)
So something like this (select * from answerkey):
1 | 2 | 3 .. | 30
--
A | B | C. | D
Student results are similar as the answerkey table, but contain a unique
student id:
student_id | 1 | 2 | 3 .
-
010019 | B | C | C
029393 | B | B | C.
Currently, to calculate how each student did, within Perl, I obtain the
table results in an array and do an array calculation:
if ($student_answer->[$i] eq $correct_answer[$i-1]){$answer_correct++;}
This works fine and is quite fast, though could postgres be used to do
this faster?
The reason being is that once I have the number of correct answers for a
student, I then calculate the % score for each student, based on the
weight of the question (also in another table).
Now, all this data that Perl calculates is displayed for the end user in a
table. The user can also sort by a field, such as %.
Because Perl did the % calculations, I have to re-calculate everything and
dump it into a temporary table, to make sorting easier:
student_id, answer_correct, weights_score, percentage_score
Then, if the user wants to sort by the percentage field, I do a select *
from temp_answers order by $field.
This works fine, and of a class with 500 students, all this happens in
about 10 seconds.
I am new to the Postgres world, and am just wondering: is there any way I
can make Postgres do all the calculations, therefore, bypassing the need
to create a temporary table upon every lookup, just for sorting purposes?
A second scenario is this. I have a list of 12,000 students. The end user
selects what criteria to search for (ie. to look up students belonging in
a certain class and/or teacher). The select is fine and works, however,
then the user needs to be taken to a reports page, where this a different
Perl program running for each different report.
How do I pass this SQL statement to the perl programs? Currently, I select
the students that match the criteria and put their IDs into a temporary
table and pass the name of this table name to the other perl programs. Is
there a way to bypass this creation of a table?
Thank you very much for your time.
Ogden Nefix
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] Change of the datatype of a column
1:16p Dear all, How is that I can alter the datatype of the column . - Warm Regards Shÿam Peri II Floor, Punja Building, M.G.Road, Ballalbagh, Mangalore-575003 Ph : 91-824-2451001/5 Fax : 91-824-2451050 DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.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.1:16pDear all, How is that I can alter the datatype of the column .- Warm Regards Shÿam Peri II Floor, Punja Building, M.G.Road, Ballalbagh, Mangalore-575003 Ph : 91-824-2451001/5 Fax : 91-824-2451050 DISCLAIMER: This message contains privileged and confidential information and is intended only for the individual named.If you are not the intended recipient you should not disseminate,distribute,store,print, copy or deliver this message.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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL]
= Marcello Miorelli - SoftByte Systems Ltda. 55 54 224 42 00 # 210 Senior Software Engineer Software Developer Languages: C, C++,VB, Delphi, SQL,Assembly Databases: Interbase, PostgreSQL, MS SQL Server, Oracle Stored Procedures, Triggers and User Defined Functions Hare Krishna Hare Krishna Krishna Krishna Hare Hare Hare Rama Hare Rama Rama Rama Hare Hare __ Yahoo! Mail: 6MB di spazio gratuito, 30MB per i tuoi allegati, l'antivirus, il filtro Anti-spam http://it.yahoo.com/mail_it/foot/?http://it.mail.yahoo.com/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] BLOBS AND FILES
Hello folks, How can we insert a file into a blob field in a table? Could someone please explain something regarding this? It is ver important. tia marcello miorelli __ Yahoo! Mail: 6MB di spazio gratuito, 30MB per i tuoi allegati, l'antivirus, il filtro Anti-spam http://it.yahoo.com/mail_it/foot/?http://it.mail.yahoo.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Data Calculation
According to your scenario I did the following
create table answerkey (
question_number int,
answer varchar(2));
create table studentanswer (
student_id varchar(6),
answer01 varchar(2),
answer02 varchar(2));
insert into answerkey values(1,'A');
insert into answerkey values(2,'B');
insert into studentanswer values ('010019','B','C');
insert into studentanswer values ('029393','B','B');
create view studentanswer_boolean as
select student_id,
answer01 = (select answer from answerkey where question_number=1) as a01,
answer02 = (select answer from answerkey where question_number=2) as a02
from studentanswer ;
select * from studentanswer_boolean ;
create view studentanswer_numeric as
select student_id,
case when answer01 = (select answer from answerkey where question_number=1) then 1
else 0 end as a01,
case when answer02 = (select answer from answerkey where question_number=2) then 1
else 0 end as a02
from studentanswer ;
select * from studentanswer_numeric ;
create view studentanswer_sumcorrect as
select student_id,
(case when answer01 = (select answer from answerkey where question_number=1) then 1
else 0 end)+
(case when answer02 = (select answer from answerkey where question_number=2) then 1
else 0 end)
as "sum_of_correct_answers"
from studentanswer ;
HTH
About your second scenario:
Have you seen PL/Perl - Perl Procedural Language within the documentation?
Regards, Christoph
> I need some help for which I could not find much help for within the
> Postgres book I am looking at, or the online tutorial. I don't know if
> this is even possible, but here goes...
>
> I am writing for advice, as the method I followed is not the most
> effecient, I think.
>
> Scenario: Grading results. I have two tables set up - one with the
> answerkey and one with the students results from a test:
>
> answerkey:
>
> question_number int,
> answer varchar(2)
>
> So something like this (select * from answerkey):
>
> 1 | 2 | 3 .. | 30
> --
> A | B | C. | D
>
> Student results are similar as the answerkey table, but contain a unique
> student id:
>
> student_id | 1 | 2 | 3 .
> -
> 010019 | B | C | C
> 029393 | B | B | C.
>
> Currently, to calculate how each student did, within Perl, I obtain the
> table results in an array and do an array calculation:
>
> if ($student_answer->[$i] eq $correct_answer[$i-1]){$answer_correct++;}
>
> This works fine and is quite fast, though could postgres be used to do
> this faster?
>
> The reason being is that once I have the number of correct answers for a
> student, I then calculate the % score for each student, based on the
> weight of the question (also in another table).
>
> Now, all this data that Perl calculates is displayed for the end user in a
> table. The user can also sort by a field, such as %.
>
> Because Perl did the % calculations, I have to re-calculate everything and
> dump it into a temporary table, to make sorting easier:
>
> student_id, answer_correct, weights_score, percentage_score
>
> Then, if the user wants to sort by the percentage field, I do a select *
> from temp_answers order by $field.
>
> This works fine, and of a class with 500 students, all this happens in
> about 10 seconds.
>
> I am new to the Postgres world, and am just wondering: is there any way I
> can make Postgres do all the calculations, therefore, bypassing the need
> to create a temporary table upon every lookup, just for sorting purposes?
>
> A second scenario is this. I have a list of 12,000 students. The end user
> selects what criteria to search for (ie. to look up students belonging in
> a certain class and/or teacher). The select is fine and works, however,
> then the user needs to be taken to a reports page, where this a different
> Perl program running for each different report.
>
> How do I pass this SQL statement to the perl programs? Currently, I select
> the students that match the criteria and put their IDs into a temporary
> table and pass the name of this table name to the other perl programs. Is
> there a way to bypass this creation of a table?
>
> Thank you very much for your time.
>
> Ogden Nefix
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] now() in loop statement
Hello, What is wrong with this function because the mytime variable contain the same value in each iteration: CREATE OR REPLACE FUNCTION public.time_test() RETURNS int4 AS ' DECLARE count int4; mytime timestamp; BEGIN count := 0; mytime := now(); while count <= 25 loop mytime := now(); RAISE NOTICE \'Time: %\',mytime; count := count + 1; end loop; RAISE NOTICE \'Count: %\',count; RETURN count; END; ' LANGUAGE 'plpgsql' VOLATILE; the result is: NOTICE: Time: 2003-09-29 15:52:06.745317 . . . NOTICE: Time: 2003-09-29 15:52:06.745317 --same with the first value!!! NOTICE: Count: 26 Thanks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] now() in loop statement
Hello, What is wrong with this function because the mytime variable contain the same value in each iteration: Just like it should be. now() gives you always the same value inside single transaction. If you need time for performance tests - use "timeofday". Regards, Tomasz Myrta ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] now() in loop statement
> > Hello, > > What is wrong with this function because the mytime variable contain the > same value in each iteration: > CREATE OR REPLACE FUNCTION public.time_test() >RETURNS int4 AS > ' > DECLARE > count int4; > mytime timestamp; > BEGIN > count := 0; > mytime := now(); > while count <= 25 loop > mytime := now(); > RAISE NOTICE \'Time: %\',mytime; > count := count + 1; > end loop; > RAISE NOTICE \'Count: %\',count; > RETURN count; > END; > ' >LANGUAGE 'plpgsql' VOLATILE; > > the result is: > NOTICE: Time: 2003-09-29 15:52:06.745317 > . > . > . > NOTICE: Time: 2003-09-29 15:52:06.745317 --same with the first value!!! > NOTICE: Count: 26 > > Thanks. > Change mytime := now(); to mytime := ''now''; and RTFM for details. Regards, Christoph ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] now() in loop statement
On 29 Sep 2003 at 15:58, Kerv wrote: > What is wrong with this function because the mytime variable contain the > same value in each iteration: >From http://www.postgresql.org/docs/7.3/static/functions- datetime.html: It is important to realize that CURRENT_TIMESTAMP and related functions return the start time of the current transaction; their values do not change during the transaction. timeofday() returns the wall clock time and does advance during transactions. -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL]
Wouldn't: insert into r select count(*) from users where date( lastlogin) > current_date - MaxDays * interval '' 1 day'' group by date( lastlogin); be more efficient? Tom Lane wrote: > > Dan Langille <[EMAIL PROTECTED]> writes: > > WHERE lastlogin between current_date - interval \''' || > > quote_literal(i - 1) || '' days\' > > AND current_date - interval \''' || > > quote_literal(i) || '' days\'''; > > IIRC, quote_literal() puts single quotes around its result. So you have > too many quotes there. Given that you know i is an integer, you don't > really need quote_literal for it. Actually, you don't need EXECUTE > here at all. Why not just > > FOR i IN 1..MaxDays LOOP > SELECT count(*) > INTO r > FROM users > WHERE lastlogin between current_date - (i-1) * interval ''1 day'' > AND current_date - i * interval ''1 day''; > RETURN NEXT r; > END LOOP; > > regards, tom lane > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL]
On 29 Sep 2003 at 10:04, Jean-Luc Lachance wrote:
> Wouldn't:
>
> insert into r
> select count(*)
> from users
> where date( lastlogin) > current_date - MaxDays * interval '' 1 day''
> group by date( lastlogin);
>
> be more efficient?
Yes it would, by a factor of 5.
freshports=# explain analyse select * from LoginCounts(3);
QUERY PLAN
--
Function Scan on logincounts (cost=0.00..12.50 rows=1000 width=8)
(actual time=1141.04..1141.06 rows=3 loops=1)
Total runtime: 1141.13 msec
(2 rows)
freshports=# explain analyse select count(*)
freshports-# from users
freshports-# where date( lastlogin) > current_date - 3 * interval ' 1
day'
freshports-# group by date( lastlogin);
QUERY PLAN
--
-
Aggregate (cost=539.78..552.75 rows=173 width=8) (actual
time=197.54..198.97 rows=3 loops=1)
-> Group (cost=539.78..548.42 rows=1730 width=8) (actual
time=196.97..198.43 rows=110 loops=1)
-> Sort (cost=539.78..544.10 rows=1730 width=8) (actual
time=196.95..197.39 rows=110 loops=1)
Sort Key: date(lastlogin)
-> Seq Scan on users (cost=0.00..446.75 rows=1730
width=8) (actual time=0.87..195.38 rows=110 loops=1)
Filter: ((date(lastlogin))::timestamp without
time zone > (('now'::text)::date - '3 days'::interval))
Total runtime: 199.33 msec
(7 rows)
freshports=#
Thank you.
--
Dan Langille : http://www.langille.org/
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[SQL] SRF Functions don't want to return empty tuple
Hi I have a problem with SRF functions on a RedHat 8.0 system with PostgreSQL 7.3.2. Schema is following : CREATE TABLE public.agents ( numero_age int4 NOT NULL, nom_age varchar(30) NOT NULL, prenom_age varchar(30) NOT NULL, date_entree_age date NOT NULL, identite varchar(50), CONSTRAINT agents_pkey PRIMARY KEY (numero_age) ) WITH OIDS; INSERT INTO public.agents VALUES (7, 'PERAVIF', 'JO', '1967-06-18', '') CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS 'SELECT * FROM agents WHERE numero_age = $1' LANGUAGE 'sql' VOLATILE; When, I try : SELECT * FROM liste_agents(7) Everything is Ok, I get my agent. But if I try : SELECT * FROM liste_agents(0) (... or any other numero_age not in the table...) I get a error message : ExecMakeTableFunctionResult: Invalid result from function returning tuple What's wrong ? I guessed null values returned by the query didn't match fields declared not null in the table schema, so I create a custom type (with CREATE TYPE) but it neither doesn't work. Hint (?) : I've activated Plpython for this Database. Thanks Eric GRIMOIS Concepteur de logiciels SEI - CPAM du Val d'Oise ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Thanks for all replies
Thank you, Kerv ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Especial delimiters caracter
Thanks for your replay...
I resolve the problem with sed before i post the question.
But a really want to know if i can handle all with posgre script.
Mariano
-Mensaje original-
De: Richard Huxton [mailto:[EMAIL PROTECTED]
Enviado el: Miércoles, 24 de Septiembre de 2003 14:19
Para: Montervino, Mariano; '[EMAIL PROTECTED]'
Asunto: Re: [SQL] Especial delimiters caracter
On Tuesday 23 September 2003 15:56, Montervino, Mariano wrote:
> running the following script
>
> select ('cabal\\' || user_name) into temporary user_access from
> tbl_user_access where access_profile_id = 4;
> copy user_access to '/temp/users.txt' delimiters '|';
>
> The result of the output file is...
> domain\\users
> but the field in the temp table is...
> domain\users
>
> I need the same result like temp table. How could i do it??
> RGDS
$ cat fakedump.txt
C:\\Windows\\system32\\drivers|this\ttabbed\ttext\n
$ perl -p -e 's//\\/g;' fakedump.txt
C:\Windows\system32\drivers|this\ttabbed\ttext\n
As you can see, this only handles \\ not \t=tab.
You could also do this in sed:
$ sed -e 's//\\/g' fakedump.txt
HTH
--
Richard Huxton
Archonet Ltd
---(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] Need to overcome UNION / ORDER BY restriction
I have a table: id | priority | seniority -+--+--- 902 |2 | 271 902 |1 | 271 902 |3 | 271 924 |2 | 581 924 |1 | 581 924 |3 | 581 935 |1 | 276 935 |2 | 276 972 |2 | 275 (9 rows) I'd need to get a result set where rows are sorted according to these rules: 1. first all rows with priority = 1 sorted according to seniority 2. then the rest of the rows sorted by seniority, priority Something like this: SELECT * from foo where priority = 1 order by seniority union select * from foo where priority > 1 order by seniority, priority but this gives parse error because of the restrictions with ORDER BY and UNION (I suppose..) select * from foo order by case when priority = 1 then priority else seniority end; goes fine, but it's not quite enough and when I try select * from foo order by case when priority = 1 then priority else seniority, priority end; it's parse error at or near ",". Any suggestions? Thanks in advance, Timo --- CREATE TABLE foo ( id integer, priority integer, seniority integer ); INSERT INTO foo VALUES (902, 2, 271); INSERT INTO foo VALUES (902, 1, 271); INSERT INTO foo VALUES (902, 3, 271); INSERT INTO foo VALUES (924, 2, 581); INSERT INTO foo VALUES (924, 1, 581); INSERT INTO foo VALUES (924, 3, 581); INSERT INTO foo VALUES (935, 1, 276); INSERT INTO foo VALUES (935, 2, 276); INSERT INTO foo VALUES (972, 2, 275); ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Problems to be solved as soon as possible
Dear Support Team, We are hosting a website which was originally designed in some version of Debian Linux and having PostgreSQL as back end. The developers have made a script which creates several tables in the database. Some of the tables have datatypes like DateTime. When i run those scripts in PostgreSQL of Red hat Linux 7.2, they run perfectly. But when i run the same scrips in PostgreSQL of Redhat Linux 9.0, it displays an error message of "data type DATETIME not found." So can you please guide me what changes i have to make in the script of creating tables at my end as a customer so that my application runs smoothly. Regards, The Guardian. Ph: 31074723 / 32511869. Website: www.guardianindia.com Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] SQL Syntax problem
Hello. I've got a problem in porting the following select statement from Oracle to Postgres, because of the characters after "b.bet_id" and "f.ask_id" in the where clause: (+) I don't know what these characters mean and how I can transform these into PostgreSql Syntax. select... fromauswahlkatalog k, anspruchkorrektur a, beteiligter b, v_betkorr f where k.awk_id = a.awk_id and b.bet_id(+) = a.bet_idemp and a.ask_id = f.ask_id(+); Hoping for help. Doris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Need to overcome UNION / ORDER BY restriction
On Mon, Sep 29, 2003 at 17:27:47 +0300, Timo <[EMAIL PROTECTED]> wrote: > > I'd need to get a result set where rows are sorted according to these rules: > > 1. first all rows with priority = 1 sorted according to seniority > 2. then the rest of the rows sorted by seniority, priority You can do something like: bruno=> select * from foo order by priority <> 1, seniority, priority; id | priority | seniority -+--+--- 902 |1 | 271 935 |1 | 276 924 |1 | 581 902 |2 | 271 902 |3 | 271 972 |2 | 275 935 |2 | 276 924 |2 | 581 924 |3 | 581 (9 rows) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SRF Functions don't want to return empty tuple
- Original Message - From: "Stephan Szabo" <[EMAIL PROTECTED]> To: "GRIMOIS Eric" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Monday, September 29, 2003 6:40 PM Subject: Re: [SQL] SRF Functions don't want to return empty tuple > > On Mon, 29 Sep 2003, GRIMOIS Eric wrote: > > > Hi > > > > I have a problem with SRF functions on a RedHat 8.0 system with PostgreSQL > > 7.3.2. > > > > Schema is following : > > > > CREATE TABLE public.agents ( > > numero_age int4 NOT NULL, > > nom_age varchar(30) NOT NULL, > > prenom_age varchar(30) NOT NULL, > > date_entree_age date NOT NULL, > > identite varchar(50), > > CONSTRAINT agents_pkey PRIMARY KEY (numero_age) > > ) WITH OIDS; > > > > INSERT INTO public.agents VALUES (7, 'PERAVIF', 'JO', '1967-06-18', '') > > > > CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS 'SELECT * > > FROM agents WHERE numero_age = $1' LANGUAGE 'sql' VOLATILE; > > Are you sure you don't want setof public.agents if you want to be able to > return an empty set? > Yes, you're right. Now, it works. Thank you very much The syntax I used was wrong, but is accepted by Postgres. What does it mean ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] postgres index on ILIKE
Alex, > I read your article about indexing in postgres which unfortunately ends > after the 2nd part. So I decided to ask you by email. Yeah, yeah, I know. The completed version will probably become part of a published book. We'll see. > Is it somehow possible to create an index for the ILIKE comparision? > I set up a database which contains the paths to all files in my network > neighborghood. Short of creating your own datatype, you can't index for ILIKE. Instead, you create an index on the LOWER() of the column ... CREATE INDEX idx_table_lower_text ON table(lower(text_field)); Then, you make sure when querying to query the lower function: SELECT * FROM table WHERE lower(text_field) LIKE 'xxxyy%'; This will use the index wherever it improves execution. I suggest that you join the PGSQL-SQL mailing list for future questions of this type. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Oracle 'connect by prior' now eaiser in 7.3?
if you need oracle's syntax and can recompile pgsql see patch at http://gppl.terminal.ru/index.eng.html regards, evgen potemkin --- On Tue, 23 Sep 2003, Merrall, Graeme wrote: > > I'll start by admitting that I've not looked at it myself yet but I'm > just wondering if any others have looked at this yet. A while ago we > migrated a complex web-app from Oracle to postgres. It went very well > when it came to replace 'connect by prior'. Because we were time poor > we couldn't spend much time looking the best way to emulate this in > postgres and the stored procs seemed complex. One look at the OpenACS > information sent me screaming for the hills :) > > Given a table of > PARENT_NODE_ID NOT NULL NUMBER > CHILD_NODE_IDNOT NULL NUMBER > ORDINAL NUMBER > > And Oracle SQL of > select child_node_id, level > from node_relationships > connect by prior child_node_id = parent_node_id > start with parent_node_id=10 > order by Hierarchy.Branch(level, ordinal) > > Am I right in thinking that recursive procedures and procs returning row > sets would allow us to better emulate this behaviour? As anyone looked > at it yet? > > Cheers, > Graeme > > ---(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 > ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SRF Functions don't want to return empty tuple
On Monday 29 September 2003 17:18, GRIMOIS Eric wrote: > Hi > > I have a problem with SRF functions on a RedHat 8.0 system with PostgreSQL > 7.3.2. > > Schema is following : > > CREATE TABLE public.agents ( > numero_age int4 NOT NULL, > nom_age varchar(30) NOT NULL, > prenom_age varchar(30) NOT NULL, > date_entree_age date NOT NULL, > identite varchar(50), > CONSTRAINT agents_pkey PRIMARY KEY (numero_age) > ) WITH OIDS; > > INSERT INTO public.agents VALUES (7, 'PERAVIF', 'JO', '1967-06-18', '') > > CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS 'SELECT > * FROM agents WHERE numero_age = $1' LANGUAGE 'sql' VOLATILE; You want "RETURNS SET OF public.agents" -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] help
Hi, there I'm attempting to install PostgreSQL-7.3.3 under Windows 2000 professional. -cygwin-1.5.5-1 -cygipc-2.01-2 but i got trouble when performing initdb command, all the interactive information listed as follows, - [EMAIL PROTECTED] ~ $ ipc-daemon2 & [1] 4300 [EMAIL PROTECTED] ~ $ initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user "shengqj". This user must also own the server process. The database cluster will be initialized with locale C. creating directory /data/base... ok creating directory /data/global... ok creating directory /data/pg_xlog... ok creating directory /data/pg_clog... ok creating template1 database in /data/base/1...IpcSemaphoreCreate:semget(key=1,num=17,03600)failed: Function not implemented initdb failed. Removing /usr/local/pgsql/data. [1]+ Done. ipc-daemon2 [EMAIL PROTECTED] ~ - I have try many times, why? Hope to get help. Thanks! Shengqj [EMAIL PROTECTED]
Re: [SQL] postgres index on ILIKE
Alex, > mydb=# explain select filename from neighbors where filename ilike > '%test%'; QUERY PLAN > - > Seq Scan on neighbors (cost=0.00..17449.64 rows=774 width=37) > Filter: ((filename)::text ~~* '%test%'::text) I just looked at your query. What you have above is an "unanchored text search", where you are looking for a snippet of text anywhere in the field. Such a search *cannot* be indexed using a regular index. Instead, you need to employ the TSearch full text searching module for PostgreSQL. hmmm ... the Tsearch home page appears to be down. Check out the readme in your postgreSQL source code: PG_SOURCE/contrib/tsearch/readme -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] does postgresql execute unions in parallel?
If I have several selects joined with unions does postgresql execute the concurrently or not? thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] SRF Functions don't want to return empty tuple
On Mon, 29 Sep 2003, GRIMOIS Eric wrote: > Hi > > I have a problem with SRF functions on a RedHat 8.0 system with PostgreSQL > 7.3.2. > > Schema is following : > > CREATE TABLE public.agents ( > numero_age int4 NOT NULL, > nom_age varchar(30) NOT NULL, > prenom_age varchar(30) NOT NULL, > date_entree_age date NOT NULL, > identite varchar(50), > CONSTRAINT agents_pkey PRIMARY KEY (numero_age) > ) WITH OIDS; > > INSERT INTO public.agents VALUES (7, 'PERAVIF', 'JO', '1967-06-18', '') > > CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS 'SELECT * > FROM agents WHERE numero_age = $1' LANGUAGE 'sql' VOLATILE; Are you sure you don't want setof public.agents if you want to be able to return an empty set? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Is there something wrong with Perl's dbi and PostgreSQL?
I just tried running a perl script that I wrote about a year ago. It had
worked for me in the past. Now it doesn't work. I can view data from a
table, but I am unable to either drop a table or create a table. I'm
running PostgreSQL 7.3.4 and DBI 1.35 along with Perl 5.8.1.
Here's my script for creating a table:
#!/usr/bin/perl -w
# createfoo.pl
use strict;
use DBI;
my $dbh = DBI->connect( 'dbi:Pg:dbname=test','dbuser','password',
{
RaiseError => 1,
AutoCommit => 0
}
) || die "Database connection not made:
$DBI::errstr";
my $query = $dbh->prepare(
"CREATE TABLE foo (foo_id INT4," .
"foo_name TEXT)"
);
$query->execute();
$dbh->disconnect();
Has anything changed to DBI? I haven't found anything on google
suggesting that it has.
--
Jeff Self
Dept. of Information Technology
City of Newport News
(757)926-3741
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] does postgresql execute unions in parallel?
teknokrat wrote: > If I have several selects joined with unions does postgresql execute the > concurrently or not? No. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SQL Syntax problem
This kind of conditions are left or right joins, depending on which side of the equal sign you have the (+). Something like this select ... from auswahlkatalog k, INNER JOIN anspruchkorrektur a ON (k.awk_id = a.awk_id), LEFT JOIN beteiligter b ON (b.bet_id = a.bet_idemp), RIGHT JOIN v_betkorr f ON (a.ask_id = f.ask_id) should give you the same results. On Sun, 2003-09-28 at 09:43, Doris Bernloehr wrote: Hello. I've got a problem in porting the following select statement from Oracle to Postgres, because of the characters after "b.bet_id" and "f.ask_id" in the where clause: (+) I don't know what these characters mean and how I can transform these into PostgreSql Syntax. select... from auswahlkatalog k, anspruchkorrektur a, beteiligter b, v_betkorr f where k.awk_id = a.awk_id and b.bet_id(+) = a.bet_idemp and a.ask_id = f.ask_id(+); Hoping for help. Doris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster signature.asc Description: This is a digitally signed message part
Re: [SQL] Need to overcome UNION / ORDER BY restriction
"Timo" <[EMAIL PROTECTED]> writes: > SELECT * from foo where priority = 1 order by seniority > union select * from foo where priority > 1 order by seniority, priority > but this gives parse error because of the restrictions with ORDER BY and > UNION (I suppose..) You'd need to parenthesize: (SELECT * from foo where priority = 1 order by seniority) UNION ALL (select * from foo where priority > 1 order by seniority, priority) Otherwise the ORDER BY is considered to apply to the whole UNION result (it's effectively got lower binding priority than the UNION). Note also that you *must* use UNION ALL, else UNION will attempt to eliminate duplicates, and mess up the sort order while at it. See also Bruno's solution nearby. Not sure which of these approaches would be faster; try both. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Problems to be solved as soon as possible
Dear Support Team, We are hosting a website which was originally designed in some version of Debian Linux and having PostgreSQL as back end. The developers have made a script which creates several tables in the database. Some of the tables have datatypes like DateTime. When i run those scripts in PostgreSQL of Red hat Linux 7.2, they run perfectly. But when i run the same scrips in PostgreSQL of Redhat Linux 9.0, it displays an error message of "data type DATETIME not found." So can you please guide me what changes i have to make in the script of creating tables at my end as a customer so that my application runs smoothly. It looks like Postgresql problem, not a Redhat problem. Probably datetime is some not longer supported datatype. General date and time datatype is "timestamp". Try to change your "datetime" to "timestamp" - it should help. Anyway, what versions your Postgresql rpm's have? Regards, Tomasz Myrta ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SRF Functions don't want to return empty tuple
On Mon, 29 Sep 2003, GRIMOIS Eric wrote: > > On Mon, 29 Sep 2003, GRIMOIS Eric wrote: > > > > > CREATE TABLE public.agents ( > > > numero_age int4 NOT NULL, > > > nom_age varchar(30) NOT NULL, > > > prenom_age varchar(30) NOT NULL, > > > date_entree_age date NOT NULL, > > > identite varchar(50), > > > CONSTRAINT agents_pkey PRIMARY KEY (numero_age) > > > ) WITH OIDS; > > > > > > INSERT INTO public.agents VALUES (7, 'PERAVIF', 'JO', '1967-06-18', > '') > > > > > > CREATE FUNCTION public.liste_agents(int4) RETURNS public.agents AS > 'SELECT * > > > FROM agents WHERE numero_age = $1' LANGUAGE 'sql' VOLATILE; > > > > Are you sure you don't want setof public.agents if you want to be able to > > return an empty set? > > > > Yes, you're right. Now, it works. > > Thank you very much > > The syntax I used was wrong, but is accepted by Postgres. What does it mean IIRC it means it returns exactly one object of the type defined by agents. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Is there something wrong with Perl`s dbi and PostgreSQL?
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
> I just tried running a perl script that I wrote about a year ago. It had
> worked for me in the past. Now it doesn't work.
You have AutoCommit set to off, and never commit the transaction. Therefore,
the table creation is rolled back. Add a
$dbh->commit()
after your execute line and it should work as expected.
Also note that your "die" on connect will not work because you have RaiseError
set: Leave it off for the connect, then turn it on again immediately via:
$dbh->{RaiseError}=1;
- --
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200309291445
-BEGIN PGP SIGNATURE-
Comment: http://www.turnstep.com/pgp.html
iD8DBQE/eH4AvJuQZxSWSsgRAgkVAJ46YX5iJ1+pbeJOQ6RJYId/6yhOWQCeKy7R
doP2RZN1y353MT+c4KdYywA=
=KUHS
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Problems to be solved as soon as possible
Tomasz Myrta <[EMAIL PROTECTED]> writes: >> datatypes like DateTime. When i run those scripts in PostgreSQL of Red hat >> Linux 7.2, they run perfectly. But when i run the same scrips in PostgreSQL >> of Redhat Linux 9.0, it displays an error message of "data type DATETIME not >> found." > It looks like Postgresql problem, not a Redhat problem. Probably > datetime is some not longer supported datatype. Yes, "datetime" is an ancient name for what we now call by the SQL-standard name "timestamp with time zone". If changing to the standard name makes for too many changes in your application code, you could use a domain as a kind of type alias: create domain datetime as timestamp with time zone; regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Problems to be solved as soon as possible
"The Guardian", > Dear Support Team, We are not a "support team". We are your fellow PostgreSQL users, and are helping you out of our personal generosity and desire to promote Postgres use. Nobody on this list is paid to answer your question. You are expected to "pay" for this support by, in turn, helping other users who are less experienced than you. This peer-to-peer help strategy has kept PostgreSQL going for 16 years, and is often faster than traditional commercial support. However, you should remember that the people you are talking to are not paid support staff, and remember that the people answering your questions are doing you a favor. > We are hosting a website which was originally designed in some version of > Debian Linux and having PostgreSQL as back end. The developers have made a > script which creates several tables in the database. Some of the tables have > datatypes like DateTime. When i run those scripts in PostgreSQL of Red hat > Linux 7.2, they run perfectly. But when i run the same scrips in PostgreSQL > of Redhat Linux 9.0, it displays an error message of "data type DATETIME not > found." The DATETIME data type was included in PostgreSQL versions 6.5 to 7.1 (as I recall) in order to provide compatibility with certain commercial database products. Before 7.2, the core developers decided that it was silly to maintain support for a data type which was not SQL-standard just for compatibility with 2 commercial databases, and so support for DATETIME was phased out over the next two versions. You can fix your scripts by doing a search-and-replace on DATETIME and replacing it with TIMESTAMP, which provides the same functionality. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Help with pivoting tables
Hi, I am having some diffuculty with pivoting the results of a query. I am using the following SQL in an attempt to aggreate the data from a table. --Begin Query-- SELECT groupid, activity_date, count(CASE activity_subcode WHEN 100 THEN 1 ELSE NULL END) AS Attended, count(CASE activity_subcode WHEN 101 THEN 1 ELSE NULL END) AS Unexcused, count(CASE activity_subcode WHEN 102 THEN 1 ELSE NULL END) AS Absent, count(CASE activity_subcode WHEN 103 THEN 1 ELSE NULL END) AS Called, count(*) AS total FROM activity WHERE activity_date BETWEEN '06/02/2003' AND '06/06/2003' GROUP BY groupid, activity_date, activity_subcode ORDER BY groupid, activity_date --End Query-- The output is coming back like: Groupid activity_date attended unexcused absent called total - BNEIO 2003-06-04 7 0 0 0 7 BNEIO 2003-06-04 0 2 0 0 2 BNEIO 2003-06-05 4 0 0 0 4 BNEIO 2003-06-05 0 5 0 0 5 I need the output to come back with the groups_id and activity_date combined to look like this: Groupid activity_date attended unexcused absent called total - BNEIO 2003-06-04 7 2 0 0 9 BNEIO 2003-06-05 4 5 0 0 9 Any ideas? Thanks, Ben --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.521 / Virus Database: 319 - Release Date: 9/23/2003 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Help with pivoting tables
would SELECT groupid, activity_date, sum(TMP.Attended) AS Attended, sum(TMP.Unexcused) AS Unexcused, sum(TMP.Absent) AS Absent, sum(TMP.Called) AS Called FROM ( SELECT groupid, activity_date, count(CASE activity_subcode WHEN 100 THEN 1 ELSE NULL END) AS Attended, count(CASE activity_subcode WHEN 101 THEN 1 ELSE NULL END) AS Unexcused, count(CASE activity_subcode WHEN 102 THEN 1 ELSE NULL END) AS Absent, count(CASE activity_subcode WHEN 103 THEN 1 ELSE NULL END) AS Called, count(*) AS total FROM activity WHERE activity_date BETWEEN '06/02/2003' AND '06/06/2003' GROUP BY groupid, activity_date, activity_subcode ORDER BY groupid, activity_date ) TMP GROUP BY groupid, activity_date ORDER BY groupid, activity_date do what you want? On Mon, 2003-09-29 at 16:50, Ben Schneider wrote: Hi, I am having some diffuculty with pivoting the results of a query. I am using the following SQL in an attempt to aggreate the data from a table. --Begin Query-- SELECT groupid, activity_date, count(CASE activity_subcode WHEN 100 THEN 1 ELSE NULL END) AS Attended, count(CASE activity_subcode WHEN 101 THEN 1 ELSE NULL END) AS Unexcused, count(CASE activity_subcode WHEN 102 THEN 1 ELSE NULL END) AS Absent, count(CASE activity_subcode WHEN 103 THEN 1 ELSE NULL END) AS Called, count(*) AS total FROM activity WHERE activity_date BETWEEN '06/02/2003' AND '06/06/2003' GROUP BY groupid, activity_date, activity_subcode ORDER BY groupid, activity_date --End Query-- The output is coming back like: Groupid activity_date attended unexcused absent called total - BNEIO 2003-06-04 7 0 0 0 7 BNEIO 2003-06-04 0 2 0 0 2 BNEIO 2003-06-05 4 0 0 0 4 BNEIO 2003-06-05 0 5 0 0 5 I need the output to come back with the groups_id and activity_date combined to look like this: Groupid activity_date attended unexcused absent called total - BNEIO 2003-06-04 7 2 0 0 9 BNEIO 2003-06-05 4 5 0 0 9 Any ideas? Thanks, Ben --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.521 / Virus Database: 319 - Release Date: 9/23/2003 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html signature.asc Description: This is a digitally signed message part
Re: [SQL]
On 29 Sep 2003 at 10:04, Jean-Luc Lachance wrote: > Wouldn't: > > insert into r > select count(*) > from users > where date( lastlogin) > current_date - MaxDays * interval '' 1 day'' > group by date( lastlogin); > > be more efficient? Yes it would, by a factor of 5. P.S. but it would not show dates for which there are no logins. The above can return zero rows. The previous example always returns MaxDays rows. -- Dan Langille : http://www.langille.org/ ---(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] How to figure out when was a table created
Folks, I have a list of tables for which I want to get the date they were created...and if possible the date last updateded. I suspect there is a pg_??? table that can answer this question but I don't know what it is and I cannot find it mentioned in any docs. Any suggestions...tia -D p.s. Love this forum! ---(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] insert error
Hi, I am getting the following error while executing insert command after I have started the erserver. I did not get this error when erserver was not started. I installed the latest versions of postgresql (7.3.4) and erserver (1.2). Please advise. Thanks Regards, Suresh Basandra pwtestdb=# insert into person (empid) values (1); FATAL: Pre-7.3 object file made an elog() call. Recompile. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] SQL Syntax problem
> I've got a problem in porting the following select statement from Oracle to > Postgres, because of the characters after "b.bet_id" and "f.ask_id" in the > where clause: (+) > I don't know what these characters mean and how I can transform these into > PostgreSql Syntax. > > > select... > from auswahlkatalog k, anspruchkorrektur a, beteiligter b, v_betkorr f > where k.awk_id = a.awk_id and b.bet_id(+) = a.bet_idemp > and a.ask_id = f.ask_id(+) This (+) means JOIN e.g. (+)-marked equations used as a joining condition To translate it to PGSQL syntax simply remove (+) :-) (only one thing i forgot: isn't it OUTER JOIN?...) ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Conditional row grained + FK dependency oriented lazy replication
Hi, first off, i broadcast my case to as many people as possible, maybe off topic for many, but maybe as well of interest for some. Also i speak sort of DBMirror terminology ( a great and simple tool). The problem is as follows: Tables must be replicated to remote sites (1 master, many slaves). The tables are classified as 1) Non replicated (nothing is specified for them, and eventually nothing is done) 2) Replicated to all slaves 3) Conditionally replicated, so that a row is replicated only if it matches some criteria (usually a comparison of a column value against a remote site ID) 4) Implicitly replicated. This is the hard case. Nothing is explicitly declared for those tables, but the necessity of replicating them comes from the FK constraints that tables in case 3) point to. The problem with those tables is that we cannot know in advance if any future record in some table of case 3) will point to a record currently getting inserted into the db, and also there is a need for accounting info for those tables, since we want to know whether they got inserted at a remote site or not. Imagine we have a table parts with part numbers,descriptions,manuals,etc Now we have lets say 1 master db in the computer center, and many slaves for each retailer shop we have. Each shop maintains an inventory of items,supplies,etc... Each item has a FK to a part. (instance of a part). Now we dont want the huge ammount of parts to be replicated to each remote site, but only those parts, whose partno *will* be referenced by an item for a specific remote site. (That is we want to lazilly simulate the actions of a replication trigger as if we knew in advance that this part will be referenced by an item in the remote site). All i currently am aware off is DBMirror (i took a look at erserv, which didnt seem that spectacular), and i'd like to know if people have faced or solved this problem. In asynchronous situations where bandwidth cost is of primary concern (e.g.dial-up through satellite), i think that replicating useless data is a big loss. Any comments? Thanx. -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: achill ( at ) matrix ( dot ) gatewaynet ( dot ) com mantzios ( at ) softlab ( dot ) ece ( dot ) ntua ( dot ) gr ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Result set granularity..
Hi, Since I went from Oracle to PostgreSQL I've been missing the "invisable" column 'rownum'. I often used it to lower the granularity of my data. For example, say I have a large table containing some sort of statistical data and want to plot a graph using it. If the graph is 600 pixels wide I might as well lower the granularity of my incoming data to 600 measure points before plotting. In Oracle I used to do this by using the modulus operator on the rownum column as a restriction. SELECT FROM WHERE mod(rownum, 5) = 0; The query above would give me every fifth row of the original result set and would save me from sending the data over my database connection and do the lowering of the granularity in the application. I have two questions, one dependent on the answer on the other one.. 1) Is it possible to achieve this any other _easy_ way? Perhaps it would be feasible to write a wrapper using a counter which makes the SELECT and then return every fifth row to the SELECT calling the wrapper. But then I assume the data still has to "travel" one step which puts on some overhead. 2) Would it be possible to add one more limit argument to the non-standard set of LIMIT and OFFET - a 'SCALE n' or 'GRANUL n' argument which would return every n row of the initial result set. I think that would be gladly accepted for folks working with statistical data. -ra ---(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] Result set granularity..
On Sat, 27 Sep 2003, Rasmus Aveskogh wrote:
>
> Hi,
>
> Since I went from Oracle to PostgreSQL I've been missing the "invisable"
> column 'rownum'. I often used it to lower the granularity of my data.
> For example, say I have a large table containing some sort of statistical
> data and want to plot a graph using it.
> If the graph is 600 pixels wide I might as well lower the granularity of
> my incoming data to 600 measure points before plotting.
>
> In Oracle I used to do this by using the modulus operator on the rownum
> column as a restriction.
>
> SELECT FROM WHERE mod(rownum, 5) = 0;
>
> The query above would give me every fifth row of the original result set
> and would save me from sending the data over my database connection and do
> the lowering of the granularity in the application.
>
> I have two questions, one dependent on the answer on the other one..
>
> 1) Is it possible to achieve this any other _easy_ way?
> Perhaps it would be feasible to write a wrapper using a counter which
> makes the SELECT and then return every fifth row to the SELECT calling the
> wrapper. But then I assume the data still has to "travel" one step which
> puts on some overhead.
The standard "trick" in Postgresql is to create a temporary sequence and
select that as well:
create temp sequence aaa;
postgres=# select *, nextval('aaa') from accounts;
to get a row number. The sequence creation and use is pretty fast, and
I've used it before.
>
> 2) Would it be possible to add one more limit argument to the non-standard
> set of LIMIT and OFFET - a 'SCALE n' or 'GRANUL n' argument which would
> return every n row of the initial result set. I think that would be
> gladly accepted for folks working with statistical data.
Using the same trick, you can get every 5th row like this:
select * from (select *, nextval('aaa') as row from accounts) as a where a.row%5=4;
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
