[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] How to figure out when was a table created
Hi folks, I posted this question a few days ago and got no response so I guess it cannot be done (surprising!) So that leaves me with my business problem. We create a table for each days activity. After N days (typically 7 days) we can drop the table. The table name is not known so cannot force business to make tablename something like mydata_MMDDYY I'd like to be able to do something like: SELECT tablename FROM pg_??? WHERE to_char( ???, 'dd/mm/yy' ) = to_char( now() - interval '7 days', 'dd/mm/yy' ) Any suggestions? --- Prior msg was: 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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Referring to derived column name in a RECORD
Hi folks, I know I'm doing something wrong here but cannot make it work no matter how many/few quotes I use I'm trying to reference a column in a RECORD which is not a column name but a derived column. Any suggestions??? Example code below to highlight the problem: DECLARE r_app RECORD ; BEGIN FOR r_app IN SELECT to_char( created_timestamp, 'DDMMYY' ) AS "joined_on", last_name, first_name FROM customer WHERE cust_id = 123 LOOP r_app.last_name ; -- Easy to referenece this value but... r_app.joined_on ; -- HOW do I reference this value?...this does not work ... ---(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] Faster performance when using where cust_id = '123' vs cust_id = 123. Does 7.4 fix this??
We have got used to the problem that queries of the format: select * from customer where cust_id = '123' are much much faster than select * from customer where cust_id = 123 (where cust_id is defined as bigint). a. Why is this. b. Will moving to v7.4 change this so we can avoid the whole '123' casting thing. Tx folks, D ---(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] Elegant way to monitor for changes in a trigger and migrate record to history table
Folks, Perhaps you can helphell I'm sure you can! I want to monitor for changes in a table and migrate the OLD. record to audit table. Is there an elegant or generic way to do this so I can use across multiple tables with little change. E.g. IF TG_OP = 'UPDATE' THEN INSERT INTO cust_hist ( col1, col2, col3, col4 ) VALUES ( OLD.col1, OLD.col2, OLD.col4 ) ; END IF ; This hardly seems scalable for a table with 50-100 columns. I'm thinking that perhaps there is a way to use RECORD or some such variable to be able to do something like: INSERT INTO cust_hist ( OLD.* ) ; I guess something like this might also work: INSERT INTO cust_hist SELECT * FROM cust WHERE cust_id = OLD.cust_id ; But it strikes me as very very bad from a performance standpoint. Would love to hear your thoughts. -D --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.574 / Virus Database: 364 - Release Date: 1/29/2004 ---(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] Syntax for cmd to EXEC...how many quotes?
Folks, This is driving me crazy...I'm sure it's possible but that I am getting the #quotes wrong in some way... I keep getting unterminated string errors...now matter how many quotes I use. I have a FN that I want to loop through all views and populate a table with a count(*) from each views. To do it I'm doing a LOOP around all views...something like: FOR r_rec IN SELECT viewname from pg_views LOOP sql_string := 'INSERT INTO temp_table ( view_name, row_count ) SELECT ''' || r_rec.viewname || ''', count(*) FROM ' || r_rec.viewname || ' ; ' ; EXEC sql_string ; END LOOP ; END ; Building that sql_string is the problem. Any thoughts folks? -D --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.648 / Virus Database: 415 - Release Date: 3/31/2004 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Row counts/data changes. Any catalog table that has this info?
Folks, Is there a catalog table or location where I can go to find data counts for tables? It would be nice if I could do a query which returned something like: table_name#Rows cust 1000 order 5000 order_detail 9500 without having to have the overhead of querying each table in turn. If there is such a source then is it accurate as of now or last vacuum or ??? On a related note...any location where I can see the most recent date a table was changed (insert/update/delete). Tx in advance... D --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.574 / Virus Database: 364 - Release Date: 1/29/2004 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] In 7.4 ensure you have DEFAULT now () with no spaces
Just a heads up folks... In converting from 7.3 to 7.4 one got-ya we had was... We had been testing 7.4 for a few days and just noticed that some tables had created_timestamp rows with a date/time of the date the DB was created...not the date/time the insert was done. Looking at those tables the create DDL's for those few tables contained now () as in: created_timestamp timestamp DEFAULT now () -- note the space between now and () Most had correctly been defined without the space - as in now() Simple matter of doing ALTER TABLE x ALTER COLUMN y SET DEFAULT now() while still in 7.3 to fix. 7.4 Migration documentation does mentions this but it can easily slip by as it did with us... --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.704 / Virus Database: 460 - Release Date: 6/12/2004 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Unicode problem inserting records - Invalid UNICODE character sequence found (0xfc7269)
My first time using unicode. Based on reading other messages I think I've got it all setup correctly but still have prob. Running: psql 7.3.6-RH $ psql -l List of databases Name| Owner | Encoding ---+--+--- devdb | devuser | UNICODE template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII I've tried two different table structures: CREATE TABLE airport_code ( airport_name character varying(70), airport_code character varying(10), ) ; and the same but with "text" instead of character varying. For both I get the same results when I try to: INSERT INTO airport_code ( airport_name, airport_code ) values ( 'Zurich (Zürich) - Kloten', 'ZRH' ) ; I get: psql:airport_codes.sql:1728: ERROR: Invalid UNICODE character sequence found (0xfc7269) Obviously I'd like to keep the proper German spelling. What am I missing? Gotta figure its me...cause PG is so damm good otherwise. Tx, D --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.771 / Virus Database: 518 - Release Date: 9/28/2004 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Unicode problem inserting records - Invalid UNICODE character
show client_encoding gives: UNICODE databases reads: DATABASE OWNER ENCODING mydb david UNICODE testdb david SQL_ASCII table columns are character varying(255) Tx, David -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Richard Huxton Sent: Wednesday, November 10, 2004 10:34 AM To: David B Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Unicode problem inserting records - Invalid UNICODE character David B wrote: > My first time using unicode. Based on reading other messages I think I've > got it all setup correctly but still have prob. > Running: psql 7.3.6-RH [snip] > For both I get the same results when I try to: > > INSERT INTO airport_code ( airport_name, airport_code ) values ( 'Zurich > (Zürich) - Kloten', 'ZRH' ) ; > > > I get: > > psql:airport_codes.sql:1728: ERROR: Invalid UNICODE character sequence > found (0xfc7269) > > Obviously I'd like to keep the proper German spelling. What is your client encoding? (look into "show client_encoding", "\encoding" in the "Localization" chapter of the manuals) -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings --- --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.771 / Virus Database: 518 - Release Date: 9/28/2004 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] ORDER BY handling mixed integer and varchar values
Hi All, I have a tabe Product_desc varchar(100) Product_price integer Product_catvarchar(100) The problem… We have categories such as: Electronics White Goods 1 2 5 15 25 etc I have a query Select product_desc, product_price, product_cat Order by product_cat, product_price And of course I get stuff ordered as I want it. BUT… with many product categories being numeric based they come out in wrong order '10 comes before 2" etc. So I tried Select product_desc, product_price, product_cat Order by cast(product_cat as integer), product_price And that worked for the numberic based categories. I don't know of a query will be across alpha or numeric categories. Is there any elegent query you folks can think of that combines the two so I can one query that has alpha sorting on alpha categories and numeric sorting on numeric values that are in the same column?? Tia. ---(end of broadcast)--- TIP 8: explain analyze is your friend
