Thread: SQL and function reference?
Hey, everyone: My first post here, so nice to meet y'all. I've recently started working with PostgreSQL (in fact, recently started working with SQL...) and although I'm getting off to a good start, sometimes things go bump in the night. In particular, I quite frequently don't know where to find answers; and I don't want to post every bloody question here. It's slow for me, and doubtless 99% will be FAQs or would have been solved by RTFM... if I could find the darn FM to R. In general, could someone point me to a good reference for SQL and PostgreSQL commands? Immediately after subbing to this list, for example, I saw the "extracting date from timestamp" thread which mentions the date_trunc() function. This one function does part of what I'd like to do in one query (thanks!) but I had not found it before. Where can I find either a reference or a tutorial to SQL in general and to this kind of functions? I'll also shoot off a quick three questions if y'all don't mind, in separate threads to make life easy on the archives. Cheers! -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
Hi Rodolfo, > In general, could someone point me to a good reference for SQL and > PostgreSQL commands? On any recent PostgreSQL (8.0.0 or 7.4.6) have a look at parts II 'The SQL Language' and VI 'Reference', especially the 'SQL Commands' chapter of the manual (locally installed somewhere in your /usr/share/doc/postgresql* (just a guess looking at one of your other messages) or the online manual). Cheers, Alex PS: Just a general hint: If you can include your PostgreSQL version and operating system in your messages you make it easier to diagnose problems and provide helpful answers.
In general, the manual is quite good. It is fully searchable and easy to read. It can be found here: http://www.postgresql.org/docs/8.0/interactive/ There are also a books, three of which are available online: http://www.postgresql.org/docs/books/ And, of course, there is always the MANY postgresql email lists and their SEARCHABLE archives: http://archives.postgresql.org/ Open source and the postgresql community are full of resources, and those above are only a few. Sean ----- Original Message ----- From: "Rodolfo J. Paiz" <rpaiz@simpaticus.com> To: <pgsql-novice@postgresql.org> Sent: Monday, January 24, 2005 8:39 PM Subject: [NOVICE] SQL and function reference? > Hey, everyone: > > My first post here, so nice to meet y'all. I've recently started working > with PostgreSQL (in fact, recently started working with SQL...) and > although I'm getting off to a good start, sometimes things go bump in > the night. > > In particular, I quite frequently don't know where to find answers; and > I don't want to post every bloody question here. It's slow for me, and > doubtless 99% will be FAQs or would have been solved by RTFM... if I > could find the darn FM to R. > > In general, could someone point me to a good reference for SQL and > PostgreSQL commands? Immediately after subbing to this list, for > example, I saw the "extracting date from timestamp" thread which > mentions the date_trunc() function. This one function does part of what > I'd like to do in one query (thanks!) but I had not found it before. > Where can I find either a reference or a tutorial to SQL in general and > to this kind of functions? > > I'll also shoot off a quick three questions if y'all don't mind, in > separate threads to make life easy on the archives. > > Cheers! > > -- > Rodolfo J. Paiz <rpaiz@simpaticus.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 >
On Jan 24, 2005, at 8:39 PM, Rodolfo J. Paiz wrote: > Hey, everyone: Hi Rodolfo. Funny how we keep running into each other. :) > My first post here, so nice to meet y'all. I've recently started > working > with PostgreSQL (in fact, recently started working with SQL...) and > although I'm getting off to a good start, sometimes things go bump in > the night. > > In particular, I quite frequently don't know where to find answers; and > I don't want to post every bloody question here. It's slow for me, and > doubtless 99% will be FAQs or would have been solved by RTFM... if I > could find the darn FM to R. http://www.postgresql.org/docs/manuals/ > In general, could someone point me to a good reference for SQL and > PostgreSQL commands? Immediately after subbing to this list, for > example, I saw the "extracting date from timestamp" thread which > mentions the date_trunc() function. This one function does part of what > I'd like to do in one query (thanks!) but I had not found it before. > Where can I find either a reference or a tutorial to SQL in general and > to this kind of functions? The postgres manuals are very comprehensive but aren't tailored to SQL beginners. However, the development team strives for compliance with the SQL standards, so you should be ok with a good SQL (non-db-specific) book. I picked up Bruce Momjian's PostgreSQL book some time ago. While much of it is dated with regards to 7.4 and 8.0, it is still a good introduction to basic postgres methodologies. > I'll also shoot off a quick three questions if y'all don't mind, in > separate threads to make life easy on the archives. Bzzzt! Sorry, you've exceeded your daily quota. You will be kicked. ;-) -- Jason Dixon DixonGroup Consulting http://www.dixongroup.net
On Tue, 2005-01-25 at 13:27 +1100, Alexander Borkowski wrote: > On any recent PostgreSQL (8.0.0 or 7.4.6) have a look at parts II 'The > SQL Language' and VI 'Reference', especially the 'SQL Commands' chapter > of the manual (locally installed somewhere in your > /usr/share/doc/postgresql* (just a guess looking at one of your other > messages) or the online manual). > I thought the stuff in /usr/share/postgresql... *was* the online manual. In any event, thanks a lot for the reference. I've only been attempting to use PostgreSQL for about a week, and have made extensive use of this manual; but I had not yet found those sections. Thanks to the several pointers on this thread, I'm doing better now. After a good 15 minutes of searching, I'm still looking for a way to use age() to get how many *days* ago something happened, expressed as an integer rather than an interval. However, I'm definitely doing better than I was yesterday! FYI, I'm using PostgreSQL 7.4.6 on Fedora Core 3. :-) Cheers, -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
On Mon, 2005-01-24 at 21:13 -0500, Jason Dixon wrote: > Hi Rodolfo. Funny how we keep running into each other. :) > Small Net, isn't it? <grin> Good to see you again, man... very good. > http://www.postgresql.org/docs/manuals/ > I didn't check that one, since my perception was that the full online manual was already included on my hard drive and I *had* been consulting that one. However, the lack of a search function in the HTML pages led me to grep through the files lots of times... less than efficient. I'm starting to check out some of the other references offered as well. Thanks! -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
On Tue, Jan 25, 2005 at 10:07:35AM -0600, Rodolfo J. Paiz wrote: > > After a good 15 minutes of searching, I'm still looking for a way to use > age() to get how many *days* ago something happened, expressed as an > integer rather than an interval. Subtracting two dates yields an integer (see the "Date/Time Operators" table in the "Date/Time Functions and Operators" section of the documentation). If you have timestamps then you could cast them to date and then subtract. You could also use extract(epoch from interval_value) to get the number of seconds in an interval, then divide that by 86400 (24*60*60) to get days. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Tue, 2005-01-25 at 10:00 -0700, Michael Fuhr wrote: > On Tue, Jan 25, 2005 at 10:07:35AM -0600, Rodolfo J. Paiz wrote: > > > > After a good 15 minutes of searching, I'm still looking for a way to use > > age() to get how many *days* ago something happened, expressed as an > > integer rather than an interval. > > Subtracting two dates yields an integer (see the "Date/Time Operators" > table in the "Date/Time Functions and Operators" section of the > documentation). If you have timestamps then you could cast them > to date and then subtract. You could also use extract(epoch from > interval_value) to get the number of seconds in an interval, then > divide that by 86400 (24*60*60) to get days. > The idea here is to have a formula that says "select * from flights where date was less than 30 days ago" and have that formula work properly. So if I want to subtract two dates for this purpose, then I am going to need a today() function which I also have not found. I see now how the extract(epoch from interval) could be used, but then why is there no extract(days|months|hours from interval), or why can I not format an interval as "XXXXX seconds" or "YYY days"? Would make things so much simpler! Cheers, -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
On Wed, 2005-01-26 at 10:39 -0600, Rodolfo J. Paiz wrote: > The idea here is to have a formula that says "select * from flights > where date was less than 30 days ago" and have that formula work > properly. So if I want to subtract two dates for this purpose, then I am > going to need a today() function which I also have not found. > Silly me: current_date(). <smile> Cheers, -- Rodolfo J. Paiz <rpaiz@simpaticus.com>
> The idea here is to have a formula that says "select * from flights > where date was less than 30 days ago" and have that formula work > properly. How about: select * from flights where date < cast(current_date + interval '30 days ago' as date); (assuming there is a date column (of type date) on flights). So if I want to subtract two dates for this purpose, then I am > going to need a today() function which I also have not found. > > I see now how the extract(epoch from interval) could be used, but then > why is there no extract(days|months|hours from interval), or why can I > not format an interval as "XXXXX seconds" or "YYY days"? Would make > things so much simpler! You can do this. For example select current_timestamp + interval '1000000 seconds'; You can find the documentation on how to use the interval type in "Data Types" / "Date/Time Types". Cheers, Alex
Alexander Borkowski <alexander.borkowski@abri.une.edu.au> writes:
> How about:
> select *
> from flights
> where date < cast(current_date + interval '30 days ago' as date);
This is the hard way ... you can just add/subtract a date and an
integer, instead:
... where date < (current_date - 30);
regards, tom lane
Hello, I'm trying to use information from the pg_attribute table, especially the atttypid and atttypmod columns. Can I count on these numbers to remain unchanged or do I need to cast them somehow, or get at this information some other way? TIA Ray -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.7.3 - Release Date: 1/24/2005
Afton & Ray Still <rastill@shaw.ca> writes:
> I'm trying to use information from the pg_attribute table, especially the
> atttypid and atttypmod columns.
> Can I count on these numbers to remain unchanged or do I need to cast them
> somehow, or get at this information some other way?
Well, for the built-in types: we're not in the habit of changing them,
but we reserve the right to do so. For user-defined types: a
dump/reload will almost certainly assign new type OIDs. So it depends
what you want to do and what time horizon you have for "unchanged".
If you would prefer to work with a textual type name then see
format_type(), which seems sadly undocumented but you use it like
format_type(atttypid, atttypmod).
regards, tom lane