Re: [GENERAL] Sort by foreign date column
and how about this: SELECT TO_CHAR(chdate, 'DD-MM-') FROM mytable ORDER BY chdate::date; From: Mike Mascari [EMAIL PROTECTED] To: Andrey Y. Mosienko [EMAIL PROTECTED] CC: Stephan Szabo [EMAIL PROTECTED], Postgres [EMAIL PROTECTED] Subject: Re: [GENERAL] Sort by foreign date column Date: Tue, 21 Aug 2001 03:16:55 -0400 Andrey Y. Mosienko wrote: Stephan Szabo wrote: On Tue, 21 Aug 2001, Andrey Y. Mosienko wrote: I have table with date type column: chdate date; SELECT chdate from mytable; chdate 1999-01-02 But in Russia we have the next date format: DD-MM-. When I do coversion to char in SELECT: TO_CHAR(chdate,'DD-MM-') everything is ok, but sort by this column executes as sort for char type. How can I display my native date format and do right sorting by this column? Wouldn't select to_char(chdate, 'DD-MM-') from mytable order by chdate; work? Works. But sorting performs as for CHAR TYPE! I don't know why the sorting isn't functioning correctly for the 'date' data type (locale issue?), but this should definitely work: SELECT TO_CHAR(chdate, 'DD-MM-') FROM mytable ORDER BY EXTRACT(EPOCH from chdate); Hope that helps, Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] chr() in 7.0.3 !
hi all, I have pg 7.0.3 installed. I can use ascii() to get the ascii code of a character but I can not use chr() to reverse the function... is it a matter of the pg version I have? If so, is there any other way to get the text from the ascii number? TIA Omid Omoomi _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] OT: Design Books
Hi, The one I recommend is: Introduction to System Analysis and Design by Igor Hawryszkiewycz ISBN 013 896887 X Hope that helped Omid Omoomi From: David Wheeler [EMAIL PROTECTED] To: PostgreSQL-general [EMAIL PROTECTED] Subject: [GENERAL] OT: Design Books Date: Sun, 19 Aug 2001 11:48:57 -0700 (PDT) Hey All, I'm looking for a couple of solid books to help me broaden my knowledge of database design so that I can improve my designs. So what do you like? What stuff have you read and would recommend? I'm most interested in fundamentals of both standard relational database design and object database design. Anything with a PostgreSQL emphasis is an added bonus ;-) TIA! David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp ---(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: Re: [GENERAL] index on a box
select * from pg_users; From: ÀîÁ¢Ð [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED], Dustin Sallings [EMAIL PROTECTED] CC: [EMAIL PROTECTED] [EMAIL PROTECTED] Subject: Re: Re: [GENERAL] index on a box Date: Sun, 08 Jul 2001 21:02:32 +0800 Tom Lane£¬ Does anyone who knows a user's information is storeed in what pgSQL's system table? Thank all! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT'ing a function call
Command: create rule
Description: define a new rule
Syntax:
CREATE RULE rule_name AS ON
{ SELECT | UPDATE | DELETE | INSERT }
TO object [WHERE qual]
DO [INSTEAD] [action|NOTHING|[actions]];
HTH
Omid
From: Jared H. Hudson [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: [GENERAL] SELECT'ing a function call
Date: Fri, 6 Jul 2001 12:25:21 -0500
I would like to be able to define a table with 1 column containing user
names,
and the next column being a dynamically generating function that returns a
user's quota.
I think I see how to create the shared object, and the SQL function. But,
how
do I create a table that uses a function for a column based on the value of
another
column.
For example, I tried:
CREATE TABLE test (
num1 INTEGER,
num2 INTEGER DEFAULT add_one('num1')
);
But that said that pg_atoi doesn't understand num1 -- so apprenting atoi is
interpreting
num1 as a string, so I tried ...DEFAULT add_one(num1) which gave a
different
error.
Is this even possible? To have a trigger based on a select statement. I
looked at the SQL
TRIGGER command and it seems to only work for INSERT, UPDATE and DELETE. I
need a trigger
for SELECT's.
Thanks,
Jared H.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
_
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
---(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: [GENERAL] How to retrieve table definition in SQL
Hi, There are some system tables in any pg database which contain information about table/field names/types and descriptions. use -e with psql command, and look at the sql code when running /d commands. regards. Omid Omoomi From: Stan Jacobs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [GENERAL] How to retrieve table definition in SQL Date: Sun, 19 Mar 2000 03:40:43 -0800 (PST) Hi everyone, This probably isn't a Postgres-specific question, but I'm hoping that someone knows the answer to this off the top of their heads... :-) I'd like to retrieve the table definition via SQL. I'm using ColdFusion to access a PostgreSQL 6.5.3 database, and I'd like to retrieve the table info, field names/types/sizes, so that my Coldfusion page/script can dynamically build the html forms to edit the tables. Any ideas how to get to this in SQL? I have another C++ class which builds nice table headers with this info, but ColdFusion doesn't seem to do that with the returned data. Thanks! - Stan - __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] Nested tables
Table ACL: Attribute |Type | Modifier ---+-+ uniqid| varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null read | boolean | not null default 'f'::bool write | boolean | not null default 'f'::bool execute | boolean | not null default 'f'::bool delete| boolean | not null default 'f'::bool Table myItem: Attribute|Type | Modifier +-+-- uniqid | varchar(12) | not null date_created | timestamp | not null date_modified | timestamp | not null author | text| not null title | text| not null access_control | acl | not null PostgreSQL lets me create the class myItem with a "acl" field type, but I can't figure out how to insert multiple values (or any values!) into the access_control field... So you will have your tables with data redundancies and not normalized. __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] RestrictionClauseSelectivity
hi,put it in quotes: delete from aktuelles where index='9'; better not using some predefined key words like "index" as field names. From: Ingo Assenmacher [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [GENERAL] RestrictionClauseSelectivity Date: Wed, 23 Feb 2000 12:55:43 +0100 Hi All! I have the following table: Name: recent +--+--+---+ | Field | Type| Length| +--+--+---+ | datum| date not null | 4 | | aktuell_text | text not null | var | | erzeuger | char() | 5 | | index| int2 | 2 | +--+--+---+ Filled with some data (about 9 rows). I want to delete a specific row with: =delete from aktuelles where index=9; which results in: ERROR: RestrictionClauseSelectivity: bad value 2.280761 Where can I find help on this? I am using PostgreSQL V6.4 on a Linux/i386 2.2.10 System. Thanks for your help! Regards, Ingo. __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] ORDER BY problems
hi, As far as I understand,tracking_num is primary key and uniqe! So when you sort the date by a uniqe field their would be no place for other fields to be sort on! May be you are going to sort the data some other way. so I think your query is doing well. Omid Omoomi From: David Shrewsbury [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [GENERAL] ORDER BY problems Date: Fri, 18 Feb 2000 20:52:06 + I cannot seem to get ORDER BY to work properly when I want to sort using two different columns. I have code similar to the following: SELECT tracking_num, username FROM reports WHERE customer='$customer' ORDER BY tracking_num, username This will sort by tracking_num (int4, primary key), but NOT by the username (varchar, not null). The confusing thing is if I use: ORDER BY username, tracking_num it works, but it sorts by username first and then the tracking number which is the reverse of what I want to do. Why doesn't the first bit of code work as I expect? -David __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] PSQL Function() help....
Hello, Sure you can use arrays as returned results in your function. regards. Omid Omoomi From: Peter Eisentraut [EMAIL PROTECTED] To: Dale Anderson [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [GENERAL] PSQL Function() help Date: Wed, 12 Jan 2000 04:58:53 +0100 On 2000-01-12, Dale Anderson mentioned: Here is what I am trying to do. I an trying to create a function that is passed two numbers, get_people(2000,1). The first value is a year, and the second is a week. What I want the function to do, is to select all the names from a name table, and return a list of names that don't have an entry in the data table for the year, and week specified. Can this be done?? No. Functions cannot return result sets. Certainly a deficiency, but nobody is perfect. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden __ Get Your Private, Free Email at http://www.hotmail.com
Re: [GENERAL] when are indexes used?
hi, I just wait afew days to see whether some one ( with more info about this issue in pg ) reply. but ... so here is my word : From: InfraRED [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: [GENERAL] when are indexes used? Date: Sun, 19 Sep 1999 17:29:57 +0200 (CEST) I noticed that indexes are not used sometimes when they could speed up queries: explain select * from auth where uid=30; Index Scan using auth_uid_key on auth (cost=2.05 rows=1 width=40) explain select * from auth where uid30; Seq Scan on auth (cost=2.06 rows=11 width=40) Testing on my machine it works fine. I mean this query uses the index file on my database. explain select * from auth order by uid; Sort (cost=2.06 rows=32 width=40) - Seq Scan on auth (cost=2.06 rows=32 width=40) are there any ways to speed up queries like these? the exact usage alg. of indexes is documented somewhere? when is this going to be fixed? It seems that in pg one may only use row field name at the where clause part of the query. ie : select * from auth where uid= 30 ; but if you want to use the index file,don't use it like this : select * from auth where uid= ( 28 + 2 ) ; also you may not use any function on the clause. ie : select * from auth where sqrt(uid)= 30 ; So you have to make the where clause statement very simple with no complexity. Though, due to my old knowledge about oracle, some databases supports these features a little stronger. I will also be glad to hear about more docs about indexing on pg. regards omid. __ Get Your Private, Free Email at http://www.hotmail.com
