RE: [SQL] Temp tables being written to disk. Avoidable?
Howdy, > If the temp table doesn't fit in memory, we will have to put it in > backing store somewhere, and a disk is the logical place, right? I > don't see a huge advantage of putting it in memory. We could prevent > WAL writes for temp tables. That would help. Yes, if it won't fit in memory then clearly it needs to be put out to disk. I just thought if it could stay in memory then there is no real reason to bother the disk at all. I do realise that it's probably vastly easier said than done though, the more you start treating temp tables as a special case the more complex looking after them will get. Just idle speculation on my part really, I just happen to be sitting next to a server with a very noisy disk which remains idle most of the time as selects are far more common in my app than insert/updates The only exception to that is this temp table, which is been using fairly frequently and makes the thing grind away. -- Paul McGarrymailto:[EMAIL PROTECTED] Systems Integrator http://www.opentec.com.au Opentec Pty Ltd http://www.iebusiness.com.au 6 Lyon Park RoadPhone: (02) 9870 4718 North Ryde NSW 2113 Fax: (02) 9878 1755 This document and any attachments are intended solely for the named addressee(s), are confidential, and may be subject to legal professional privilege. Please notify us (on +61-2 9878 1744) as soon as possible if you have received this document in error. Any confidentiality or privilege is not waived or lost because this email has been sent to you by mistake. This document and any attachments are subject to copyright. No part of them should be reproduced or distributed by any means whatsoever without the prior consent of the copyright owner. Opentec does not warrant that this email and any attachments are error or virus free. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Interval FAQ - please review
Folks, Please review this for inaccuracies before I post it to pgsql-newbie and the docs. -Josh FAQ: Working with Dates and Times in PostgreSQL This FAQ is intended to answer the following questions: Q: Where are the DATEADD() and DATEDIFF() functions in PostgreSQL? Q: How do I tell the amount of time between X and Y? KEYWORDS: date, datetime, timestamp, operator, dateadd, datediff, interval One of PostgreSQL's joys is a robust support of a variety of date and time data types and their associated operators. This has allowed me to write calendaring applications in Postgres that would have been considerably more difficult on other platforms. Before we get down to the nuts-and-bolts, I need to explain a few things to the many who have come to us from database applications which are less ANSI 92 SQL compliant than PostgreSQL (particularly Microsoft SQL Server, SyBase and Microsoft Access). If you are already educated, you'll want to skip down to "Working with DATETIME, DATE, and INTERVAL values". (BTW, I am not on an anti-Microsoft tirade here. I use MS SQL Server as an example of a non-standards-compliant database because I am a certified MS SQL Server admin and know its problems quite well. There are plenty of other non-compliant databases on the market.) ANSI SQL and OPERATORS In the ANSI SQL world, operators (such as + - * % || NOT) are defined only in the context of the data types being operated upon. Thus the division of two integers ( INT / INT ) does not function in the same way as the divsion of two float values (FLOAT / FLOAT). More dramatically, you may subtract one integer (INT - INT) from another, but you may not subtract one string from another (VARCHAR - VARCHAR), let alone subtract a string from an integer (INT - VARCHAR). The subtraction operator (-) in these two operations, while it looks the same, is in fact not the same owing to a different datatype context. In the absence of a pre-defined context, the operator does not function at all and you get an error message. This fundamental rule has a number of tedious consequences. Frequently you must CAST two values to the same data type in order to work with them. For example, try adding a FLOAT and a NUMERIC value; you will get an error until you help out the database by defining them both as FLOAT or both as NUMERIC (CAST(FLOAT AS NUMERIC) + NUMERIC). Even more so, appending an integer to the end of a string requires a type conversion function (to_char(INT, '0')). Further, if you want to define your own data types, you must spend the hours necessary to define all possible operators for them as well. Some database developers, in a rush to get their products to market, saw the above "user-unfriendly" behavior and cut it out of the system by defining all operators to work in a context-insensitive way. Thus, in Microsoft Transact-SQL, you way add a DOUBLE and an INTEGER, or even append an INTEGER directly to a string in some cases. The database can handle the implicit conversions for you, because they have been simplified. However, the Transact-SQL developers disregarded the essential reason for including context-sensitive operators into the SQL standard. Only with real, context-sensitive operators can you handle special data types that do not follow arithmatic or concatination rules. PostgreSQL's ability to handle IP addresses, geometric shapes, and, most importantly for our discussion, dates and times, is dependant on this robust operator implementation. Non-compliant dialects of SQL, such as Transact-SQL, are forced to resort to proprietary functions like DATEADD() and DATEDIFF() in order to work with dates and times, and cannot handle more complex data types at all. Thus, to answer the first question: Q. Where are the DATEADD and DATEDIFF functions in PostgreSQL? A. There are none. PostgreSQL does not need them. Use the + and - operators instead. Read on. WORKING with DATETIME, DATE, and INTERVAL VALUES Complete docs on date/time data types may be found at: http://www.postgresql.org/idocs/index.php?datatype-datetime.html I will not attempt to re-produce them here. Instead, I will simply try to explain to the beginner what you need to know to actually work with dates, times, and intervals. DATETIME or TIMESTAMP: Structured "real" date and time values, containing year, month, day, hour, minute, second and millesecond for all useful date & time values (4713 BC to over 100,000 AD). DATE: Simplified integer-based representation of a date defining only year, month, and day. INTERVAL: Structured value showing a period of time, including any/all of years, months, weeks, days, hours, minutes, seconds, and milleseconds. "1 day", "42 minutes 10 seconds", and "2 years" are all INTERVAL values. Q. What about TIMESTAMP WITH TIME ZONE? A. An important topic, and datatype, that I don't want to get into here. See the PostgreSQL docs. Q. Which do I want to use: DATE or TIMESTAMP?
[SQL] Re: Interval FAQ - please review
> "JB" == Josh Berkus <[EMAIL PROTECTED]> writes: JB> Q. What about TIMESTAMP WITH TIME ZONE? JB> A. An important topic, and datatype, that I don't want to get into here. JB> See the PostgreSQL docs. Those docs are lacking an explanation that there is no such thing in PostgreSQL as a timestamp *without* time zone. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Re: Interval FAQ - please review
Why not just include examples of DATEDIFF and DATEADD functions? For example: CREATE FUNCTION datediff(timestamp, timestamp) RETURNS integer AS ' BEGIN RETURN $2 - $1; END; ' LANGUAGE 'plpgsql'; And similarly with DATEADD. You will increase the scope (and length) of your article, but only slightly: and add some other useful education, as well. - Original Message - From: "Josh Berkus" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, August 16, 2001 11:22 AM Subject: Interval FAQ - please review > Folks, > > Please review this for inaccuracies before I post it to pgsql-newbie and > the docs. > > -Josh > > FAQ: Working with Dates and Times in PostgreSQL > > This FAQ is intended to answer the following questions: > > Q: Where are the DATEADD() and DATEDIFF() functions in PostgreSQL? > Q: How do I tell the amount of time between X and Y? > > KEYWORDS: date, datetime, timestamp, operator, dateadd, datediff, > interval > > One of PostgreSQL's joys is a robust support of a variety of date and > time data types and their associated operators. This > has allowed me to write calendaring applications in Postgres that would > have been considerably more difficult on other > platforms. Before we get down to the nuts-and-bolts, I need to explain > a few things to the many who have come to us from > database applications which are less ANSI 92 SQL compliant than > PostgreSQL (particularly Microsoft SQL Server, SyBase and > Microsoft Access). If you are already educated, you'll want to skip > down to "Working with DATETIME, DATE, and INTERVAL > values". > > (BTW, I am not on an anti-Microsoft tirade here. I use MS SQL Server as > an example of a non-standards-compliant database > because I am a certified MS SQL Server admin and know its problems quite > well. There are plenty of other non-compliant > databases on the market.) > > ANSI SQL and OPERATORS > > In the ANSI SQL world, operators (such as + - * % || NOT) are defined > only in the context of the data types being operated > upon. Thus the division of two integers ( INT / INT ) does not function > in the same way as the divsion of two float values > (FLOAT / FLOAT). More dramatically, you may subtract one integer (INT - > INT) from another, but you may not subtract one > string from another (VARCHAR - VARCHAR), let alone subtract a string > from an integer (INT - VARCHAR). The subtraction > operator (-) in these two operations, while it looks the same, is in > fact not the same owing to a different datatype context. In > the absence of a pre-defined context, the operator does not function at > all and you get an error message. > > This fundamental rule has a number of tedious consequences. Frequently > you must CAST two values to the same data type > in order to work with them. For example, try adding a FLOAT and a > NUMERIC value; you will get an error until you help out > the database by defining them both as FLOAT or both as NUMERIC > (CAST(FLOAT AS NUMERIC) + NUMERIC). Even more > so, appending an integer to the end of a string requires a type > conversion function (to_char(INT, '0')). Further, if you > want to define your own data types, you must spend the hours necessary > to define all possible operators for them as well. > > Some database developers, in a rush to get their products to market, saw > the above "user-unfriendly" behavior and cut it > out of the system by defining all operators to work in a > context-insensitive way. Thus, in Microsoft Transact-SQL, you way > add a DOUBLE and an INTEGER, or even append an INTEGER directly to a > string in some cases. The database can handle > the implicit conversions for you, because they have been simplified. > > However, the Transact-SQL developers disregarded the essential reason > for including context-sensitive operators into > the SQL standard. Only with real, context-sensitive operators can you > handle special data types that do not follow > arithmatic or concatination rules. PostgreSQL's ability to handle IP > addresses, geometric shapes, and, most importantly for > our discussion, dates and times, is dependant on this robust operator > implementation. Non-compliant dialects of SQL, such > as Transact-SQL, are forced to resort to proprietary functions like > DATEADD() and DATEDIFF() in order to work with dates > and times, and cannot handle more complex data types at all. > > Thus, to answer the first question: > Q. Where are the DATEADD and DATEDIFF functions in PostgreSQL? > A. There are none. PostgreSQL does not need them. Use the + and - > operators instead. Read on. > > > WORKING with DATETIME, DATE, and INTERVAL VALUES > > Complete docs on date/time data types may be found at: > http://www.postgresql.org/idocs/index.php?datatype-datetime.html > I will not attempt to re-produce them here. Instead, I will simply try > to explain to the beginner what you need to know to > actually work with dates, times, and intervals. > > DATETIME or TIMESTAMP: Str
Re: [SQL] Re: Help!!! Trying to "SELECT" and get a tree structure back.
--CELKO-- wrote: > > >> The table causing my headache: > > CREATE TABLE app_components > (idNUMERIC(7) NOT NULL PRIMARY KEY, > name VARCHAR(100) NOT NULL, > description VARCHAR(500) NULL, > parent_id NUMERIC(7) NULL >REFERENCES app_components(id) >ON DELETE CASCADE, > CONSTRAINT appcomp_name_u UNIQUE (name, parent_id)); << I first tried the above approach to model trees in SQL, which also caused me headaches. The recursion needed to find all the ancestors for a given id was slow. So I bought and looked through Joe Celko's book (who recently posted on this topic). I implemented his ideas, and found that they were better than the method above (and faster, as he says), but I still wasn't satisfied. First, I didn't like that the notion wasn't easily parsable for me. Updating and deleting categories felt like hacks, and moving a category seemed like too much work. So I kept looking for new ideas to model trees in SQL. On my third try, I found a solution I was happy with, which I'll call the "sort key" method. I first read about it here: http://philip.greenspun.com/wtr/dead-trees/53013.htm (Search for "Sort keys deserve some discussion") on this page The sort key is a single string that gives you the location of a node in a tree. Used in conjunction with a parent_id, I found that most of the questions I was asking were easy to answer: Who is my parent? Who are all my ancestors? Who are my immediate children? How many descendants do I have? Who are siblings? Furthermore, it's fairly straightforward to manipulate items using this structure, and queries are fast-- most questions can answered with one SQL statement. Finally, the sort_keys are fairly human parsable, which is nice. The trade-off for all these features is that you have a fixed number of immediate children for any parent (based on how many characters are used for each piece of the sort key). I think in my application to categorize data, each parent can only have 62 immediate children. I can live with that. Cascade is a complete (free) Perl/Postgres application using this scheme if you are interested in seeing these ideas in action. It's homepage is here: http://summersault.com/software/cascade/ You'll be able to get a demo and source code from there. Thanks, -mark http://mark.stosberg.com/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Nested JOINs
Hello, I am trying to execute an SQL query that contains nested joins, but I get parser error for some reason. Below is the query I am trying to execute and the error I am getting: SELECT media FROM(dtrowmedia a INNER JOIN dtrow b ON (a.dtrow = b.objectid)) c INNER JOIN dtcol d ON (c.dtcol = d.objectid) psql: ERROR: parser: parse error at or near "inner" Note that the following query executes with no errors: SELECT media FROMdtrowmedia a INNER JOIN dtrow b ON (a.dtrow = b.objectid) INNER JOIN dtcol d ON (b.dtcol = d.objectid) What am I doing wrong? Please help me get it to work. Thanks Oleg ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: Interval FAQ - please review
> "JB" == Josh Berkus <[EMAIL PROTECTED]> writes: >> Those docs are lacking an explanation that there is no such thing in >> PostgreSQL as a timestamp *without* time zone. JB> Hmmm OK, I'll revise the A: but I *don't* want to go into Time Zone JB> issues in this intro. Any docs I can link to? The only good explanation came from the mailing list, from Tom Lane, naturally ;-). Basically, nobody wants to change it, and using a view or a client-side function to strip the timezone info is the way to do it. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Re: Interval FAQ - please review
Jeff, > Why not just include examples of DATEDIFF and DATEADD functions? > For example: > CREATE FUNCTION datediff(timestamp, timestamp) > RETURNS integer AS ' > BEGIN > RETURN $2 - $1; > END; > ' LANGUAGE 'plpgsql'; Not a bad idea. Unfortunately, DATEDIFF & DATEADD are more complicated than that. More importantly, I don't want to get into Function design in this article, which is aimed at newbies. If, however, anyone *does* have an online sample of one or both of these two functions, I'd love to link to it from the article. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Re: Interval FAQ - please review
Vivek, > JB> Q. What about TIMESTAMP WITH TIME ZONE? > JB> A. An important topic, and datatype, that I don't want to get > into here. > JB> See the PostgreSQL docs. > > Those docs are lacking an explanation that there is no such thing in > PostgreSQL as a timestamp *without* time zone. Hmmm OK, I'll revise the A: but I *don't* want to go into Time Zone issues in this intro. Any docs I can link to? -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Nested JOINs
Oleg Lebedev <[EMAIL PROTECTED]> writes: > What am I doing wrong? Using 7.0, perhaps? The query parses fine for me in 7.1. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Nested JOINs
Oleg, > Below is the query I am trying to execute and the error I am getting: > SELECT media > FROM(dtrowmedia a INNER JOIN dtrow b ON (a.dtrow = b.objectid)) c > INNER JOIN dtcol d ON (c.dtcol = d.objectid) First, the "INNER" keyword is not required on Postgres; you may omit it with impunity. (If you are doing this on MS SQL Server or MS Access and have joined this list by mistake, our advice won't be very useful to you). Second, you can't alias a JOINed set of tables; you may alias a subselect, or alias a table: FROM dtrow b ... is legal FROM (SELECT * FROM dtrow) b ... is also legal FROM (dtrow JOIN dtrowmedia ON dtrow.objectid = dtrowmedia.dtrow) b ... is not legal. Thus, the query above is best expressed simply as: SELECT media FROM dtrowmedia a JOIN dtrow b ON a.dtrow = b.objectid JOIN dtcol d ON b.dtcol = d.objectid; If you actually did need to put some extra criteria into the first join, then you would use a subselect: SELECT media FROM (SELECT * FROM dtrowmedia a JOIN dtrow b ON a.dtrow = b.objectid WHERE dtrow > 3000) c JOIN dtcol d ON c.dtcol = d.objectid; But keep in mind in this case that you cannot reference a. or b. in the SELECT list at the top, just c. because a. and b. exist only in the subselect. Now, go out and buy a copy of "SQL for Smarties". You'll be glad you did. -Josh Berkus __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Interval FAQ - please review
Josh Berkus writes: > 1. The difference between two TIMESTAMPs is always an INTERVAL > '1999-12-30'::TIMESTAMP - '1999-12-11'::TIMESTAMP = '19 days'::INTERVAL > 2. You may add or subtract an INTERVAL to a TIMESTAMP to produce another > TIMESTAMP: > '1999-12-11'::TIMESTAMP + '19 days'::INTERVAL = '1999-12-30'::TIMESTAMP > 3. You may add or subtract two INTERVALS: > '1 month'::INTERVAL + '1 month 3 days'::INTERVAL = '2 months 3 days' > INTERVAL You should probably use the correct literal syntax here, e.g., TIMESTAMP '1999-12-30' and INTERVAL '3 days'. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(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: [SQL] Nested JOINs
"Josh Berkus" <[EMAIL PROTECTED]> writes: > Second, you can't alias a JOINed set of tables; Actually you can, according to my reading of SQL92: ::= [ [ AS ] [] ] | [ AS ] [] | ::= ::= ::= ::= | ::= | | ::= [ NATURAL ] [ ] JOIN [ ] So a parenthesized is a and may be followed by [ AS ] . 7.1 gets this right. 7.0's very preliminary implementation of JOIN syntax did not. (It had a number of other bugs too, IIRC.) The spec's grammar is horribly badly written: it's ambiguous whether the parentheses should be parsed as part of a second-level which would imply that a correlation name would NOT be accepted. It took a fair amount of work to derive a grammar that was unambiguous and still accepted everything... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Fwd: [SQL] how to use record type
I have difficulties understanding how to use variable of "record" or "row" type. How do I actually insert the variables OLD or NEW or a record type into a table from within a trigger? Like doing the following: drop table th1; create table th1( id serial, text text ); drop table th_audit; create table th1_audit( ts timestamp default now() ) inherits(th1); drop function thaudit(); create function thaudit() returns opaque as ' begin -- I want to insert OLD into th_audit - how do I do this??? return NEW; end; ' language 'plpgsql'; drop trigger ta on th1; create trigger ta before delete or update on th1 for each row execute procedure thaudit(); Reagrds, Horst ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
