Re: [SQL] How to figure out when was a table created
On Thu, Oct 02, 2003 at 16:01:16 -0700, David B <[EMAIL PROTECTED]> wrote: > 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? You could consider changing the way you do things. You might be able to accomplish your goal by keeping the data in one table and keeping partial indexes on the table corresponding to the last seven days. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Creating Index
"CN" <[EMAIL PROTECTED]> writes: > I thought I have got no more question in this thread, and I was wrong :-( > Is the extra 300ms in UNION essential? Dividing, it looks like the Append node is taking about 3 microseconds per tuple, which seems kind of a lot considering it isn't really doing anything. I suspect though that most of this overhead is due to the EXPLAIN ANALYZE instrumentation, and would in fact go away when you are just running the query. regards, tom lane ---(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] Data type conversion
Jan Pips wrote:
How to convert the interval type into integer, float or any "countable" data
type at the time of table creation using
select ... into?
I'm guessing you want something like this:
SELECT EXTRACT('EPOCH' FROM INTERVAL
'1 days 4 hours 15 minutes 23 seconds');
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
---(end of broadcast)---
TIP 8: explain analyze is your friend
[SQL] help with rule and notification
I don't know if this is the correct forum for this question but I will start here... I have a job tracking system that I am developing with postgresql and mac os x. I have all the pieces in place (mostly) but i am having a problem with notify.. I am trying to set up things so that two (or more) people can view the same job data, and if one client updates the data the others will be notified and can update their displays. I got the notify to work (it wasn't too difficult) but now I am trying to figure out the logic. I mean the only examples I see have rules that say.. update table1, then the rule updates table2 and sends a notify to anyone listening. The information passed in the notify is a reference to the second table. Ok so far. I am having a problem with the second table update. When I update table1 (update table1 set info ='info' where jobno = '10023') how do I pick up the jobno variable in my rule? something like create rule r1 as on update to table1 do (update table2 set jobno = table1.jobno; notify table2;) so anyone listening for notifications on table2 can ask table2 for the jobno that was updated. then if they were viewing that jobno, update their display. if not just ignore the notify. maybe i'm going about this all wrong and someone can point me in a better direction. I am open to any solution... Thanks.. Ted __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Result set granularity..
Well, there is a rowid and a rownum in Oracle. I don't remember which, but one of them is significant to the current recieved result set only. Hence; you can use it to manipulate and restrict the result set rather than the data you're recieving from the tables. I.e. rownum 1 will always be the first row in the result set recived by the select. OID's in PostgreSQL, are, as you also mentioned, an object ID associated with a certain object in the database and is therefore of no use in the situation I mentioned in my earlier question. There is no predictability regarding what OID a certain row in a table will have. -ra > there is a secret column called "oid" added to tables (unless you > tell postgres not to.) > > select oid from my_table limit 10; > >oid > -- > 77412298 > 77416344 > 77417164 > 77451631 > 77455960 > 77474124 > 77480067 > 77501568 > 77503239 > 77507326 > (10 rows) > > the oid is 32 unsigned int that is incremented for each object in the > database. it is the postgres version of Oracle's rownum. > > search the docs for more info. > > > On Sat, 27 Sep 2003, Rasmus Aveskogh wrote: >> 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 >> > > ---(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] Data type conversion
How to convert the interval type into integer, float or any "countable" data type at the time of table creation using select ... into? Pips ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Regarding decode function
Hi All, Right now I have a query which uses decode function to get data in Oracle database, I want know is there any alternative function to decode which can do the decode functionality in Postgresql. Thanks in advance for your great help. Thanks Babu Mannem
Re: [SQL] How to figure out when was a table created
Can you add two columns to the table creation definition, one with a default current timestamp, the second to be updated with current time in an on update trigger. Then perhaps you could do something like SELECT min(new_insert_timestamp_column), max(new_update_timestamp_column) FROM your_table; to get the table-wide date of first entry and last update. ~Berend Tober > > Well, in certain filesystems you can have the birth time > (like ufs2) stored in the inode struct. > > So you find the file name in your $PGDATA/base directory > using the oid of your table (in pg_class), > and then you open that file with stat (2) or utimes (2) (or > from perl) to read creation data. > > All that apply for FreeBSD, see if creation time is supported > in ext2/3. > > On Thu, 2 Oct 2003, David B wrote: > >> 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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Bug in psql - Postgresql 7.3.1?
All, I’m not certain if what I’m trying to do is legal, but if I execute a statement like: UPDATE my_table SET field1=’new_value’ AND SET field2=’different_value’ WHERE my_table_id = ‘key’; in psql, it reports that it has successfully updated one record. However, the record does not appear to be updated when I subsequently issue a SELECT for that record. I’ll buy the fact that I may be using illegal syntax – I’m not SQL expert, but the update says it worked when in fact it did not. Should this have worked or should it have reported an error? For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally seen during a php4 script, but has been duplicated on the psql command line. TIA, John
Re: [SQL] Data type conversion
On Fri, Oct 03, 2003 at 09:23:46 +0200, Jan Pips <[EMAIL PROTECTED]> wrote: > How to convert the interval type into integer, float or any "countable" data > type at the time of table creation using > select ... into? You can use extract to do that. See the date/time function documentation. Note that months get treated as 30 days when extracting periods less than a month from an interval. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Bug in psql - Postgresql 7.3.1?
On Fri, Oct 03, 2003 at 09:18:44 -0400, "John B. Scalia" <[EMAIL PROTECTED]> wrote: > All, > > > > I'm not certain if what I'm trying to do is legal, but if I execute a > statement like: > > > > UPDATE my_table SET field1='new_value' AND SET field2='different_value' > WHERE my_table_id = 'key'; It should be: UPDATE my_table SET field1='new_value', field2='different_value' WHERE my_table_id = 'key'; > in psql, it reports that it has successfully updated one record. I am not sure why you aren't getting a syntax error. > However, the record does not appear to be updated when I subsequently > issue a SELECT for that record. I'll buy the fact that I may be using > illegal syntax - I'm not SQL expert, but the update says it worked when > in fact it did not. Should this have worked or should it have reported > an error? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Bug in psql - Postgresql 7.3.1?
Shouldn't that be "UPDATE my_table SET field1 = 'new_value', field2 = 'different_value' WHERE my_table_id = 'key';"? Wei On Fri, 3 Oct 2003, John B. Scalia wrote: > All, > > > > I'm not certain if what I'm trying to do is legal, but if I execute a > statement like: > > > > UPDATE my_table SET field1='new_value' AND SET field2='different_value' > WHERE my_table_id = 'key'; > > > > in psql, it reports that it has successfully updated one record. > However, the record does not appear to be updated when I subsequently > issue a SELECT for that record. I'll buy the fact that I may be using > illegal syntax - I'm not SQL expert, but the update says it worked when > in fact it did not. Should this have worked or should it have reported > an error? > > > > For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally > seen during a php4 script, but has been duplicated on the psql command > line. > > > > TIA, > > John > > ---(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] Bug in psql - Postgresql 7.3.1?
John, > > UPDATE my_table SET field1='new_value' AND SET field2='different_value' > WHERE my_table_id = 'key'; Well, your SQL is bad: UPDATE my_table SET field1='new_value, field2='different_value' WHERE my_table_id = 'key'; > in psql, it reports that it has successfully updated one record. > However, the record does not appear to be updated when I subsequently > issue a SELECT for that record. I'll buy the fact that I may be using > illegal syntax - I'm not SQL expert, but the update says it worked when > in fact it did not. Should this have worked or should it have reported > an error? It's an error if it actually occurred that way. Can you cut-and-paste your actual PSQL session? > For record, this is Postgresql 7.3.1 on Solaris 9. Problem originally > seen during a php4 script, but has been duplicated on the psql command > line. BTW, there are known bugs in 7.3.1; you should upgrade to 7.3.4. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Bug in psql - Postgresql 7.3.1?
John, > Yeah, I figured out my SQL was bad and had switched to the comma > separated version, instead. In my mind, the first form should have > caused an error. I've attached a cut-and-pasted session from psql where > I used this syntax on a test table. While edited for brevity and to > obscure passwords, this is how the output appeared. Here's your problem: accounting=# update all_user set usr_current = True AND usr_location = 1002; UPDATE 3 PostgreSQL interpreted the expression "True AND usr_location = 1002" as a single, unitary, boolean expression. AND is the boolean AND operator. Since none of the users on your list had "usr_location = 1002", you got: user_current = (True AND (usr_location = 1002)) user_current = (True AND False) user_current = False Since all 3 rows already had false, they did not appear to get updated, but in fact they were. Time to look up your order of operations! -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Bug in psql - Postgresql 7.3.1?
"John B. Scalia" <[EMAIL PROTECTED]> writes: > UPDATE my_table SET field1='new_value' AND SET field2='different_value' > WHERE my_table_id = 'key'; The other responses have focused on your obvious syntax error, but I'm assuming you didn't actually cut-and-paste that from your psql session. > in psql, it reports that it has successfully updated one record. > However, the record does not appear to be updated when I subsequently > issue a SELECT for that record. I'm wondering if you are checking from another transaction that doesn't think the first one is committed yet. Are you using BEGIN/END or autocommit-off mode? regards, tom lane ---(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] function with variable length of parameters
How do I create one, such as the tsearch2 function in tsearch2 module. It takes arbitrary number of parameters. The only difference is that it is writtein in C, instead of PL/pgsql. I looked into the doc, doesn't say much about it. Thanks Wei ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Regarding decode function
Right now I have a query which uses decode function to get data in Oracle database, I want know is there any alternative function to decode which can do the decode functionality in Postgresql. Oracle: decode (value, 0, 'zero', 1, 'one', 'unknown') In PostgreSQL you have to use CASE WHEN Syntax: ANSI: CASE WHEN value=0 THEN 'zero' WHEN value=1 THEN 'one' ELSE 'unknown' END or CASE value WHEN 0 THEN 'zero' WHEN 1 THEN 'one' ELSE 'unknown' END Regards Doris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL] HeapTuple->t_tableOid==0 after SPI_exec
Hi,
i notice that when HeapTuple data are populated by a trigger
then the table oid can be retrieved from HeapTuple->t_tableOid.
When HeapTuple is populated by
SPI_exec("select * from foobar when id=667");
tuple = SPI_tuptable->tvals[0] (id is PK and row with 667 exists)
then tuple->t_tableOid is always 0.
Is it a known issue??
Am i missing something?
-Achilleus
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] HeapTuple->t_tableOid==0 after SPI_exec
[EMAIL PROTECTED] writes:
> When HeapTuple is populated by
> SPI_exec("select * from foobar when id=667");
> tuple = SPI_tuptable->tvals[0] (id is PK and row with 667 exists)
> then tuple->t_tableOid is always 0.
The result of a SELECT is never a raw table tuple, not even when it's a
straight "select * from foo". It's a constructed tuple that belongs to
no particular table --- which makes sense because in general it wouldn't
match any particular table's rowtype.
I think in 7.4 there may be an optimization that skips the tuple
projection step in this particular case, but if you can in fact see
t_tableOid in 7.4, it'd be an implementation artifact rather than
something we will promise to support in future. The correct way if you
want to see tableoid is to select it:
select tableoid,* from foobar where ...
and then extract it from the result using the usual field-access
routines.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] HeapTuple->t_tableOid==0 after SPI_exec
[EMAIL PROTECTED] writes: > are there gonna be changes in SPI or internal structs in 7.4? No more than usual ;-). You will need to recompile shared libraries, but (in theory) source code changes shouldn't be needed. You might want to think about upgrading elog() calls to ereport() though. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] HeapTuple->t_tableOid==0 after SPI_exec
On Fri, 3 Oct 2003, Tom Lane wrote: > > I think in 7.4 there may be an optimization that skips the tuple > projection step in this particular case, but if you can in fact see > t_tableOid in 7.4, it'd be an implementation artifact rather than > something we will promise to support in future. The correct way if you > want to see tableoid is to select it: > > select tableoid,* from foobar where ... > > and then extract it from the result using the usual field-access > routines. Thanx. P.S. are there gonna be changes in SPI or internal structs in 7.4? > > regards, tom lane > -- -Achilleus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
