[SQL] Re: a script that queries db periodically

Thank you for all your response. Not only do I benefit from your response, but hopefully others benefit as well. Thanks again. - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700

[SQL] a script that queries database periodically

Hello, My boss wants me to write a script that will send a email report to him monthly with the result of database queries. For example, an email would be like: Monthly report +--+-+---+ | Vehicles | Mileage | # of Signouts | +--+-+---+ |

[SQL] Big Sub-select statement

Hi, Let's say if I have a log table with (log_id, emp_id, book_id, author_id), and there are foreign tables related to each id except the log_id. Is it possible to write up a big sub-select statement to retrieve all possible info? eg1; (big sub_select) select ltb.log_id, ltb.emp_id, ltb.book_id

[SQL] nested transactions

Hi, Just out of curiousity, does Postgres support nested transactions? - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version

[SQL] Division

Hi, I tried the write up the following SQL statement: result1 from sql1 = (1,2,3,4,5) result2 from sql2 = (4,5) result3 = result1/result2 (or is it result2/result1) = (1,2,3) What do I have to do to get the (1,2,3) result? I tried "query EXCEPT query", but it seems like EXCEPT needs both table

[SQL] Group by within table joins

Hi, I have the following SQL: SELECT ltb.v_id, count(ltb.v_id) AS num_of_times_borrowed, vtb.equip_attr[1] AS year, vtb.equip_attr[3] AS model, vtb.equip_attr[4] AS type FROM log_tb ltb, vehicle_tb vtb WHERE ltb.v_id=vtb.equip_id GROUP BY ltb.v_id ORDER BY year; "ERRO

[SQL] Fetch the latest log for each product

Hi, I bet people have asked this question several times, but oh well, please do anwser again. Thanks. =) I have a product table and a log file. product_tb --- prod_id prod_name ... log_tb - log_id prod_id cust_id transact_date ... How do I fetch the latest log for each produ

Re: [SQL] Changing user passwords

Rob van der Leek wrote: > A question about the ALTER USER command I couldn't find in the > standard documentation: > > - How do I assign an 'undefined' password? > > ALTER USER nobody WITH PASSWORD ""; > > works, but doesn't assign an undefined password to nobody (like the > `pg_passwd nobody` co

[SQL] BLOBs

Hi, everyone, Browsing through the online manual, I didn't find anything related to BLOBs, but I know there must be BLOBs since many people are asking about it on the list and there is a pg_fetch_object() in PHP for Postgres. Could anyone points me an URL where I can look it up? Thanks. -Berni

[SQL] Change attribute of a column

Hi, I have a field in text[] and I want to change it to a datetime[]. Is it possible? how? Thanks very much. - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73

Re: [SQL] Select by priority again

"D'Arcy J.M. Cain" wrote: > [Note: PHP mailing list removed from Cc as this is strictly a > PostgreSQL question I think.] > > Thus spake Bernie Huang > > Sorry about this, but I don't quite get previous postings regarding > > select by pri

[SQL] Select by priority again

Sorry about this, but I don't quite get previous postings regarding select by prioriry. I have the same question with my sql. I want to be able to select cell_phone if there is one in the table; otherwise, I will select work_phone. table -- id, name, address, cell_phone, work_phone,... and

[SQL] Order by in Select

Hello, I have a list of id's that I need to sort and fetch. eg; 1,3,6,5,4,3,4,5,6,7,5,4,2 Select id >From table Order By id Asc; it becomes like: 1,2,3,3,4,4,4,5,5,5,6,6,7 What can I add to the above sql statement so that is fetches the specified id first in a sorted list? eg: if $specified_

Re: [SQL] update some elements in the array

Jouni Ahto wrote: > On Thu, 13 Jul 2000, Bernie Huang wrote: > > > I want to update the last three element without affecting other > > elements. I could have done it by retrieving all elements in the array > > first and the do a > > > > UPDATE table > >

[SQL] update some elements in the array

Hello, Could someone please tell me that is there anyway that I can update only a few elements in a Postgres array. eg; I have an array of text, declared as 'text[]'. {"1","2","3","4","5"} ==> {"1","2","7","8","9"} I want to update the last three element without affecting other elements. I

[SQL] Timestamp problem

Hi, I have Postgres 7.0, and I created a table table - ... borrow timestamp return timestamp Now, I thought timestamp is in sec eg; 35434434, but in PostgreSQL, it shows up as datetime datatype eg; 2000-06-07 17:00:05-07. I was wondering is this format a correct one for timestamp, or i

[SQL] Sub_select problem

Good day, everyone, I have 2 tables and 1 datafile as following: vehicle_tb --- v_idint4 primary key display_idint4 yeartext maketext modeltext ... vehicle_borrow_log employee_idint4 (foreign key to emp_tb) v_idint4 (foreign key to vehic

[SQL] inheritance and primary key

Hi, Is primary key similar to 'not null columns with indices'? The reason I am asking because I found that when you inherit table B from table A, the primary key in A won't be inherited to B, but only the columns. I was wondering how do you inherit a primary key or, even, a foreign key? If it'

[SQL] Problem regarding 'select...as...'

Hi, I have a following query: "select equip_attr[1], equip_attr[2] as year, make from vehicle_tb;" which gives me the following error: "ERROR: Attribute 'make' not found" Is there something wrong with my "as" usage? Thanks - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;wo

[SQL] SQL 'Case When...'

Hi, Don't know if it's possible, but how do I make the following SQL statments right? table --- name int4 attribute text[] select attribute[1] from table case when attribute[1]='yes' then select attribute[2] from table else select attribute[3] from table; Basically, I want to c

[SQL] Fetch an element in an array

Hi, This is more of a PHP problem, but I wish if someone knows this can help me. How do I fetch an element in a Postgres array? I have a table like employee col1 int4 col2 text[] now I used the php api $row = pg_fetch_array($exec, 0); which gives me the following: echo $row['col2'

[SQL] Please ignore [PSQL and PHP]

Hello, Never mind the last email subjected "PSQL and PHP". I found the bug. In PHP script, instead of using insert into employee values ( 'Bernie', '{$phone, $fax, ...}' ); or insert into employee values ( 'Bernie', '{"$phone", "$fax", ...}' ); or any other possible combo, I

[SQL] Problem with array

Hi, Instead of using select car_name from car_table where car_property[1]='1995'; (*) Can I use where car_property['year']='1995'; in Postgres?? I want to use a more descriptive name than just using index for the array (not just during insertion but as well as to specify it i

[SQL] storing images!

Hi, Does PostgreSQL support storing image pointers or something alike. Basically, I want to retrieve relative images together with info. Thanks. - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technolog

[SQL] constraint

Hi, I am a newbie to SQL. Could someone please explain why do I need a contraint, and how do I use it in SQL? An example is needed. Thanks - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Se

[SQL] template or index of some kind

Hi, I have a situation where there are hundreds of inventories that needs to be stored into postgres. And I am thinking of doing a template to ease the track of those inventories. Idea 1: each inventory may be an individual table, and the problem is how can I keep track of those tables? Idea 2