Re: [GENERAL] [PL/pgSQL] Commit every N rows. Is it possible ?
Jeremiasz Miedzinski wrote: 2006/11/9, Richard Huxton : It's not clear to me why your function does what it does anyway. I can't see why you wouldn't just do this as standard queries. As it was mentioned on http://orafaq.com/faqplsql.htm Contrary to popular believe, one should *COMMIT less frequently* within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the rollback segments will be cleared for new transactions, causing ORA-1555 errors. So, I understand that if function/procedure in postgreSQL is treated as one transaction I can for example execute 15000 delete queries and nothing similar to ORA-1555 shouldn't happen. I don't believe we have ORA-1555 errors in PG. We don't have the resources to implement all of Oracle's failure modes :-) Two areas where you might want to keep an eye on resource usage though: 1. Lots of savepoints (exception handling in plpgsql) 2. Returning large result sets (where the function will assemble the entire set before returning it). Consider returning a cursor if you want millions of rows. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: 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 & from where to start & admin pgsql on red hat
jatrojoomla wrote: Hi Group! I am trying to work & connect php wirh PGSQL on Red Hat ES 3. I got some Important path like: /usr/share/pgsql/contrib /usr/include/pgsql /usr/bin But I don't know how to start/stop pgsql server, 1. Buy a book on Linux/Red Hat server admin 2. Read it, paying particular attention to: a. Installation/setup b. Backup/restore c. Logging d. Basic system administration e. Basic security procedures 3. Make sure you have backed up and recovered the system a couple of times. 4. Try something like /etc/init.d/postgresql (start|stop|restart) or presumably there's a GUI tool on RHES3 > Insert data from client pgsql , 1. man psql 2. http://www.postgresql.org/docs/8.1/static/client-interfaces.html 3. man pg_dump 4. man pg_restore 5. http://search.cpan.org/~dbdpg/DBD-Pg-1.49/Pg.pm etc. > view database files, 1. man psql 2. http://www.pgadmin.org/ > admin database & configuration file. 1. http://www.postgresql.org/docs/8.1/static/admin.html 2. http://www.postgresql.org/docs/8.1/static/reference-client.html 3. There are also books available. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why overlaps is not working
> CREATE OR REPLACE FUNCTION f_v_same_day_overlaps(date, > date, date, date, out overlaps bool) as > $_$ > SELECT (($3 between $1 and $2) or ($4 between $1 and $2)); > $_$ language sql; Thank you. In my application second and fourth parameters can be NULL which means forever. So I tried the code: CREATE OR REPLACE FUNCTION public.doverlaps(date, date, date, date, out bool) IMMUTABLE AS $_$ IF $1 is NULL OR $3 IS NULL THEN RAISE EXCEPTION 'doverlaps: first or third parameter is NULL % %',$1,$3; END IF; IF $2 is null and $4 is null THEN SELECT true; RETURN; END IF; IF $2 is null THEN SELECT $1<=$4; RETURN; END IF; IF $4 is null THEN SELECT $2>=$3; RETURN; END IF; SELECT ($3 between $1 and $2) or ($4 between $1 and $2); $_$ language sql; This causes error ERROR: syntax error at or near "IF" SQL state: 42601 Character: 109 So I changed code to CREATE OR REPLACE FUNCTION public.doverlaps(date, date, date, date, out bool) IMMUTABLE AS $_$ SELECT ($3 between $1 and coalesce($2, '1231')) or (coalesce($4, '1231') between $1 and coalesce($2, '1231')); $_$ language sql; It this best solution ? How many times this is slower than expression in where clause? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
"Andrus" <[EMAIL PROTECTED]> writes: > This causes error > > ERROR: syntax error at or near "IF" > SQL state: 42601 > Character: 109 SQL has no "IF". Use plpgsql instead. > How many times this is slower than expression in where clause? You can time it. :-) But I don't believe it will be too slow since it is a simple operation... -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why overlaps is not working
Andrus wrote: CREATE OR REPLACE FUNCTION f_v_same_day_overlaps(date, date, date, date, out overlaps bool) as $_$ SELECT (($3 between $1 and $2) or ($4 between $1 and $2)); $_$ language sql; Thank you. In my application second and fourth parameters can be NULL which means forever. No it doesn't. NULL means "unknown". You're just using it to represent "forever". There is a value "infinity" for timestamps, but unfortunately not for dates. Otherwise, I'd suggest that you use that instead. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
> In my application second and fourth parameters can be NULL which means > forever. > It this best solution ? > How many times this is slower than expression in where clause? I am not sure if this would work for you, but instead of using NULL to represent infinity, why not use 'infinity' to represent infinity? logs=# select 'infinity'::timestamp; timestamp --- infinity (1 row) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
Richard Broersma Jr <[EMAIL PROTECTED]> writes: >> In my application second and fourth parameters can be NULL which means >> forever. >> It this best solution ? >> How many times this is slower than expression in where clause? > > I am not sure if this would work for you, but instead of using NULL to > represent infinity, why not use 'infinity' to represent infinity? If he casts all his dates to timestamps then this might be a good option. -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SOLVED] Re: [GENERAL] authentication question
Just in case others follow in my footsteps - this may prove to be helpful. Summary of problem: CentOS 4.4 - SELinux enabled - authorizing pam based users ### Created file /etc/pam.d/postgresql (I'm using LDAP) [*] # cat /etc/pam.d/postgresql #%PAM-1.0 auth required pam_stack.so service=system-auth auth required pam_nologin.so accountrequired pam_stack.so service=system-auth password required pam_stack.so service=system-auth sessionrequired pam_stack.so service=system-auth sessionrequired pam_loginuid.so ### Set SELinux security contexts for this file # chcon -u system_u -r object_r /etc/pam.d/postgresql ### Already had installed rpm selinux-policy-targeted-sources ### You will need this package ### ### Added to file /etc/selinux/src/targeted/policy/domains/local.te # cat /etc/selinux/targeted/src/policy/domains/local.te # postgres/pam allow postgresql_t self:netlink_audit_socket create; allow postgresql_t self:netlink_audit_socket nlmsg_relay; allow postgresql_t self:netlink_audit_socket read; allow postgresql_t self:netlink_audit_socket write; allow postgresql_t var_lib_t:file read; ### the last line of the changes to local.te were necessary only for ### postgresql user to be able to read /var/lib/pgsql/.ldaprc ### ### now load this new policy into selinux # cd /etc/selinux/targeted/src/policy # make reload Now, I am able to log in as a user from LDAP - with the obvious provisions that the user is a user in postgres (password not needed since that is from LDAP), and pg_hba.conf is properly configured. [*] Tom's suggestion for /etc/pam.d/postgresql file #%PAM-1.0 authrequiredpam_stack.so service=system-auth account requiredpam_stack.so service=system-auth Thanks Tom/Alvaro Craig ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] SQL syntax additions for 8.2
Does anyone know of a link that I can read about newly added SQL syntax for 8.2. Currently,n I want to see if 8.2 will allow row constructors in update statements like: update foo set (a,b,c) = (1,2,3); Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 1: 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] SQL syntax additions for 8.2
am Sat, dem 11.11.2006, um 10:44:54 -0800 mailte Richard Broersma Jr folgendes: > Does anyone know of a link that I can read about newly added SQL syntax for > 8.2. Currently,n I > want to see if 8.2 will allow row constructors in update statements like: How about the release notes? http://developer.postgresql.org/pgdocs/postgres/release-8-2.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] SQL syntax additions for 8.2
> am Sat, dem 11.11.2006, um 10:44:54 -0800 mailte Richard Broersma Jr > folgendes: > > Does anyone know of a link that I can read about newly added SQL syntax for > > 8.2. Currently,n > I > > want to see if 8.2 will allow row constructors in update statements like: > > How about the release notes? > http://developer.postgresql.org/pgdocs/postgres/release-8-2.html Thanks, that what I would looking for. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] encoding advice requested
My database locale is en_US, and by default my databases are UTF8. My application code allows the user to paste text into a box and submit it to the database. Sometimes the pasted text contains non UTF8 characters, typically the "fancy" forms of quotes and apostrophes. The database does not appreciate it when the application attempts to store these characters. What is the best option to deal with this problem? a) I think I could re-create the database with a LATIN1 encoding. I'm not real experienced with different encodings, are there any issues with combining en_US and LATIN1? b) I can issue a SET CLIENT_ENCODING TO 'LATIN1'; statement every time I open a connection. A brief test indicates this will work. c) I can figure out how to filter the text in the application program...but this seems like wasted work considering I could use either option A or B. Opinions? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why overlaps is not working
In my application second and fourth parameters can be NULL which means forever. No it doesn't. NULL means "unknown". You're just using it to represent "forever". My table represents employee absence starting and ending dates. If end day is not yet known, it is represented by NULL value. My query should threat unknown value as never ending absence to return estimated number of work days. Infinity date value is missing in SQL standard. I do'nt know any other good way to represent missing ending date. There is a value "infinity" for timestamps, but unfortunately not for dates. Otherwise, I'd suggest that you use that instead. I tried to use timestamp 'infinity':: date but this does not work if both b and d are infinity since select timestamp 'infinity':: date<=timestamp 'infinity':: date returns null. Andrus. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why overlaps is not working
If he casts all his dates to timestamps then this might be a good option. Thank you. where (a::timestamp, coalesce(b, '1231')::timestamp) overlaps (c::timestamp, coalesce(d, '1231')::timestamp) would be simplest solution. However select (date '20050101'::timestamp, date '20060101'::timestamp) overlaps (date '20060101'::timestamp, date '20070101'::timestamp) returns false So this cannot used for date overlapping. Which sytax to use to substract/add a minute to make this correct? Andrus. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Why overlaps is not working
I am not sure if this would work for you, but instead of using NULL to represent infinity, why not use 'infinity' to represent infinity? Infinity dehaves differenty than ordinal dates and nulls. If both b and d are infinity then comparison fails: select timestamp 'infinity':: date<=timestamp 'infinity':: date returns null. So infinity introduces third kind of FUD in addition to usual date and null comparisons. NULLs in SQL are disaster. With infinity SQL is double disaster. In samples I used DATE '93112' but this is incorrect. I must use maximum allowed date or max_timestamp casted to date. Is it reasonable to use it ? I hope that MAX_DATE <= MAX_DATE returns true. Which is the value of MAX_DATE is Postgres ? Andrus. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Speed of postgres compared to ms sql, is this article/comment off?
In an interview "An interview with Adam Machanic" at http://www.simple-talk.com/sql/sql-server-2005/sql-server,-postgressql-and-fish-curry/ at the bottom of the page is a post by Andrew Clarke that says pgsql is much slower in comparison to ms sql. I've read a fair number of posts recently debunking the usual "mysql is faster than pgsql" drone, but a comparison with ms sql is less often heard. Can someone who has first hand experience with both databases comment? Article is Aug 2006. I don't care if pgsql is somewhat slower than sql server 2005, but I do care if it's a lot slower, particularly running queries with complex joins. Here is the relavant part PostgreSQL! Although I have a certain fondness for it and very much hope it will eventually succeed, we must be realistic. Species have evolved in the time it takes to execute a decent bit of SQL. It runs sometimes at a tenth of the speed of SQLite. Have a look at some of the public comparative benchmarks. As an exercise, I once created a reasonably simple customer database containing a million customer records along with all the usual NAD data. I installed it on SQL Server and PostgreSQL. (thanks to the EMS tools, bless them). They were both on the same Windows 2000 box. The SQL Server system, on average, took a twentieth of the time to produce results from SQL, and the more joins, the more astonishing the difference. I'd assumed that I'd made some ghastly mistake in the installation of PostgreSQL so I got a PostgreSQL expert to check my installation. No mistake. He said that maybe it would run faster on Linux. I tried that, but failed to be excited. -- View this message in context: http://www.nabble.com/Speed-of-postgres-compared-to-ms-sql%2C-is-this-article-comment-off--tf2614575.html#a7297298 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 1: 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] encoding advice requested
On Sat, Nov 11, 2006 at 02:45:00PM -0500, Rick Schumeyer wrote: > My database locale is en_US, and by default my databases are UTF8. > > My application code allows the user to paste text into a box and submit > it to the database. Sometimes the pasted text contains non UTF8 > characters, typically the "fancy" forms of quotes and apostrophes. The > database does not appreciate it when the application attempts to store > these characters. What encoding does your application use? Whatever that is, that's what you should be using in your SET client_encoding statement. Note you can set that variable per user also. > b) I can issue a SET CLIENT_ENCODING TO 'LATIN1'; statement every time I > open a connection. A brief test indicates this will work. This is the solution, assuming ofcourse your application is in Latin1. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Speed of postgres compared to ms sql, is this article/comment off?
novnov <[EMAIL PROTECTED]> writes: > http://www.simple-talk.com/sql/sql-server-2005/sql-server,-postgressql-and-fish-curry/ > at the bottom of the page is a post by Andrew Clarke that says pgsql is much > slower in comparison to ms sql. With no details, it's hard to rate that as anything but content-free FUD. I don't doubt that he saw a serious performance differential on whatever his test case was, but without seeing the test case it's impossible to know why, or whether it would be fixable. Nor do we know what PG version he was testing. Given that he mentions Windows 2000, it's not unlikely that he was testing a Cygwin port of PG 7.something, in which case just moving to an 8.1 native port would help a lot. Also, given that he mentions that complex joins were bad, it could be that he forgot to analyze, or had a corner case where the row estimates were bad anyway, or had an outer-join scenario where reordering of the outer joins is really essential for performance. (We've fixed the latter as of 8.2, I think, but I will agree it's a serious weak spot in existing PG releases.) But this is all speculation... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Speed of postgres compared to ms sql, is this
I agree with what you say. And I'd still be interesting in hearing of first hand experience with the speed of the two databases from someone who is 'good' at both. The article commentor was obviously not a pgsql expert. I've heard recently that pgsql is as fast as mysql, so it seems odd that ms sql would be faster than pgsql. The actual test, what was use to benchmark, would make a difference of course. I'm hoping someone with a good handle on both databases has direct experience can chime in here. Tom Lane-2 wrote: > > novnov <[EMAIL PROTECTED]> writes: >> http://www.simple-talk.com/sql/sql-server-2005/sql-server,-postgressql-and-fish-curry/ >> at the bottom of the page is a post by Andrew Clarke that says pgsql is >> much >> slower in comparison to ms sql. > > With no details, it's hard to rate that as anything but content-free FUD. > > I don't doubt that he saw a serious performance differential on whatever > his test case was, but without seeing the test case it's impossible to > know why, or whether it would be fixable. Nor do we know what PG > version he was testing. Given that he mentions Windows 2000, it's not > unlikely that he was testing a Cygwin port of PG 7.something, in which > case just moving to an 8.1 native port would help a lot. > > Also, given that he mentions that complex joins were bad, it could be > that he forgot to analyze, or had a corner case where the row estimates > were bad anyway, or had an outer-join scenario where reordering of > the outer joins is really essential for performance. (We've fixed the > latter as of 8.2, I think, but I will agree it's a serious weak spot in > existing PG releases.) But this is all speculation... > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > > -- View this message in context: http://www.nabble.com/Speed-of-postgres-compared-to-ms-sql%2C-is-this-article-comment-off--tf2614575.html#a7298762 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Speed of postgres compared to ms sql, is this
From: novnov [mailto:[EMAIL PROTECTED]To: pgsql-general@postgresql.orgSent: Sat, 11 Nov 2006 20:15:08 -0500Subject: Re: [GENERAL] Speed of postgres compared to ms sql, is this I agree with what you say. And I'd still be interesting in hearing of first hand experience with the speed of the two databases from someone who is 'good' at both. The article commentor was obviously not a pgsql expert. I've heard recently that pgsql is as fast as mysql, so it seems odd that ms sql would be faster than pgsql. The actual test, what was use to benchmark, would make a difference of course. I'm hoping someone with a good handle on both databases has direct experience can chime in here.We use both native PostgreSQL and SQL Server Desktop Edition (MSDE), but not generally for exactly the same things.We use PostgreSQL on a multi-platform (HP-UX, Solaris, Linux, Windows) commercial application suite. Works great handles tens of gigabytesper day in data insertions, updates, and retirements with almostzero administration. Servers running the database stay up forever.We chose PostgreSQL as the best value after analyzing competing commercial and open source RDBMS systems in a formal DAR (Decision Analysis Resolution)process.We use MSDE on a government project where both the specific database version and committee-designed database schema from hell were mandated.There are plenty of instances when SQL Server is less than optimal andmaddening. The MSDE version also limits connections, throttles performancefor simultaneous queries, etc. And there's no way you'd ever pick it formultiplatform use or portability.
Re: [GENERAL] Table and Field namestyle best practices?
Re this part of what you wrote: "For column names, I recommend using whatever is natural in the decribing a field, irrespective of what the field is actually pointing towards. For example, a table representing an object with a creator attribute pointing to a user would have a column "creator_id" referencing "users (id)" -- not, say, "creator_user_id", which is superfluous. The id suffix is there to tell me it's an identifier, not the creator proper." Rereading it I'd like to clarify my current approach. The reason a fully qualified pointer (ala "creator_user_id") is cool is that it's obvious what it's about in comparison with another field "modified_user_id". One points to the creator and the other to the most recent modifier of the record. Following your path to this kind of clarity one could name the fields "creator_id" and "modifier_id". Those are ok but in more complex schemas (esp ones that are not 'common' objects like "user") it might not be so obvious that the 'creator' part is an alias for 'user'. The approach I've taken so far makes it obvious but is more verbose of course. Not saying my legacy approach is better than your suggestion, but I did want to note the rationale for the pattern I arrived at ages ago. A decision to simplify in pgsql by using all lowercase forces me to rethink my entire namestyle practice...probably only seasoned devs can understand how weird that is to consider. Alexander Staubo wrote: > > On Nov 8, 2006, at 18:49 , novnov wrote: > >> I've been using namestyles with mixed case like OrgID. That is much >> more >> readable than orgid. Probably a good convention to adopt would be >> to use >> namestyles like org_id. That change I can live with. > > Both are perfectly acceptable, though the mixed-case version has > drawbacks. ANSI SQL's case-insensitivity makes mixed-case identifiers > something of a second-rate citizen; "orgid" and "OrgID" are both > going to be resolved to the same object, unless you explicitly double- > quote it. Ambiguity is rarely a problem, but because there are no > checks for consistency, inconsistencies tend to sneak in, especially > in team projects; some people might type "OrgID", some "OrgId", and > so on. > > Note that lower-case, underscore-delimited variable identifiers are > consistent with mainstream coding conventions for C, C++, Ruby, > Python and most languages in the Unix world (Java and JavaScript > being notable exceptions). After several years of trying to go > against the grain and be consistent across the board, I ended up > giving in and always using whatever is appropriate in the language/ > environment I work in. > >> But another issue is the way that I've been naming foreign key >> references. I >> tend to prefix field names with a table based acronym. So names like >> OrgName, OrgCity. Foreign key fields incorporate the ref'd pk. So, >> if I have >> a table tblEmployee, fk to the org table would be like EmpOrgID. I >> know many >> would simply use OrgID as the fk in tblEmployee, but I have liked >> compounding the names because is results in completely unambiguous >> field >> names throughout the db. If I'm giving up the mixed case naming, I >> could use >> emp_fname, emp_lname, emp_org_id, but I'm not sure if that's best. > > For column names, I recommend using whatever is natural in the > decribing a field, irrespective of what the field is actually > pointing towards. For example, a table representing an object with a > creator attribute pointing to a user would have a column "creator_id" > referencing "users (id)" -- not, say, "creator_user_id", which is > superfluous. The id suffix is there to tell me it's an identifier, > not the creator proper. > > In your case, in the table "organizations" the column names would be > "id", "name", "city" and so on, and a table "employees" would have a > column "organization_id" with a foreign-key reference. This simple > convention translates more easily to one's mental model of a query > such as "select all employees where the organization name is 'foo'", > which becomes: > >select * from employees >join organizations on id = organization_id >where organizations.name = 'foo' > > as opposed to > >select * from tblEmployees >join Org on OrgId = EmpOrgId >where Org.OrgName = 'foo' > > or something. > > I am curious as to why you need to prefix table names with "tbl" in > the first place. > > Alexander. > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > > -- View this message in context: http://www.nabble.com/Table-and-Field-namestyle-best-practices--tf2596761.html#a7299349 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archive
Re: [GENERAL] wildcard alias
On 11/10/06, Matthew Terenzio <[EMAIL PROTECTED]> wrote: I suppose I should have named things differently but is there a way to join two tables with a bunch of identical column names and rather than explicitly alias each column just use some sort of wildcard like: SELECT tablename.* AS alias.* Well: qnex=# CREATE TABLE tab1 (a int, b int); qnex=# CREATE TABLE tab2 (a int, b int); qnex=# INSERT INTO tab1 VALUES(1,2); qnex=# INSERT INTO tab1 VALUES(3,4); qnex=# INSERT INTO tab2 VALUES(1,7); qnex=# INSERT INTO tab2 VALUES(3,12); And you want to, instread of: qnex=# SELECT * FROM tab1 JOIN tab2 USING(a); a | b | b ---+---+ 1 | 2 | 7 3 | 4 | 12 Do something like: qnex=# SELECT tab1.*, alias.* FROM tab1 JOIN tab2 alias USING(a); a | b | a | b ---+---+---+ 1 | 2 | 1 | 7 3 | 4 | 3 | 12 ...it is possible, certainly, but I guess you want to rather have different column names. Then aliasing table names doesn't change column names, "tab2.b" will be "b" just as well as "alias.b" will be column labeled "b". If you want to make a quick&ugly trick, do something like: qnex=# CREATE VIEW tab2_renamed AS SELECT a AS tab2_a, b AS tab2_b FROM tab2; qnex=# SELECT * FROM tab1 JOIN tab2_renamed ON (a=tab2_a); a | b | tab2_a | tab2_b ---+---++ 1 | 2 | 1 | 7 3 | 4 | 3 | 12 Other than that, I don't see too many options. Also, consider using * in queries as a bad coding style, and try to avoid it. Regards, Dawid ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why overlaps is not working
> My table represents employee absence starting and ending dates. > If end day is not yet known, it is represented by NULL value. > My query should threat unknown value as never ending absence to return > estimated number of work days. > Infinity date value is missing in SQL standard. > I do'nt know any other good way to represent missing ending date. > > There is a value "infinity" for timestamps, but unfortunately not for > > dates. Otherwise, I'd suggest that you use that instead. > I tried to use > timestamp 'infinity':: date > but this does not work if both b and d are infinity since > select timestamp 'infinity':: date<=timestamp 'infinity':: date > returns null. This might explain why you are getting null; logs=# select 'infinity'::date; ERROR: invalid input syntax for type date: "infinity" logs=# select 'infinity'::timestamp; timestamp --- infinity (1 row) apparently date doesn't know anything about infinity. However, from what I've read in my "SQL for smarties" book regarding temporial database design, unknown future dates were stored as: '-12-31' Would this help, since any enddate with this value would be be enterpreted as an enddate that has not yet occured? when you arrive at the date for records effective period to close just update the enddate to the today's date. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Automatically Creating INSERT/UPDATE/DELETE Rules on Views
On Friday 10 November 2006 18:39, Matt Miller wrote:
> I'm converting a large Oracle DB that uses hundreds of updatable views.
> ora2pg is quite useful, but it looks like it doesn't do anything to
> create the rules that are needed to make the views updatable. Under
> Oracle simple views like "select * from the_table" are automatically
> updatable, so the Oracle schema doesn't have any triggers or the like
> that I could import as PG rules.
>
You might be able to use the information in pg_depend to write a script to
generate the rules for any view that only has a single table in it's
dependency tree. (I've really no idea if this would work, so if you try it
and it succeeds please post a note or some code back to the group!)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Using GIN indexes on 8.2
On Friday 10 November 2006 14:41, Jeff Davis wrote:
> On Fri, 2006-11-10 at 20:39 +0300, Teodor Sigaev wrote:
> > >> Use @>, <@ operations instead of @ and ~
> > >> Look for discussions in -hackers for reasons of changing names
> > >
> > > Ah, many thanks. How about updating those web pages? :)
> >
> > Now they are in core:
> > http://developer.postgresql.org/pgdocs/postgres/functions-array.html
> >
> > >> Pls, why don't you use tsearch2 with GIN?
> > >
> > > Perhaps -- is there any documentation on this?
> >
> > Nothing special, just use GIN index instead of GiST.
>
> I think a section showing when to use GiST versus GIN would be very
> helpful (assuming there are still good reasons to use GiST). I would
> certainly read it.
>
I was wondering this same thing, but it appears the trade-off is disk
space/update speed (GiST) vs. query times (Gin). Magnus had a couple of nice
posts in his blog you might want to check out:
http://people.planetpostgresql.org/mha/
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Utility to Convert MS SQL Server to Postgres
On Saturday 11 November 2006 01:26, novnov wrote:
> Any recommondations for a util that helps with conversion of MS SQL Server
> databases to Postgres? Constraints, triggers, etc?
>
> Thanks
There are a couple of articles on this at
http://www.postgresql.org/docs/techdocs.3, which I believe have some pointers
to afew scripts. (Right click and save to file any pdf docs you run across)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Table and Field namestyle best practices?
On 11/8/06, novnov <[EMAIL PROTECTED]> wrote: I am very curious to hear the various conventions folks here have arrived at. I don't expect there to be consensus, but the various rationales might help me arrive at an approach that works well for me. Personally I use all lower caps names a typical table might look: CREATE TABLE names ( name_id serial PRIMARY KEY, name varchar(100) UNIQUE NOT NULL, birth date ); CREATE INDEX names_birth_index ON names (birth) CREATE INDEX names_name_lower_index ON names (lower(name)); CREATE TABLE winners ( winner_id serial PRIMARY KEY, name_id integer REFERENCES names ); CREATE VIEW winner_names_view AS SELECT * FROM winners JOIN names USING (name_id); ...generally I don't like naming columns like 'id' -- if I put full names, like name_id then JOIN ... USING(col_id) or NATURAL JOINs are easy and straightforward. Sometimes I put a trailing "_view" to mark that given table is really a view. My index names are composed of table_col1_col2_index or table_col1_function_index (like the above lower() case). If index is unique, I use "_key" as a suffix instead of "_index". I know couple of people who name their tables like T_Name, T_Winner etc. (and V_For_Views), but I consider it a bit superfluous for my tastes. And if I have whole a lot tables, I like to keep them organized into schemas, which are powerful beings in PostgreSQL. Regards, Dawid ---(end of broadcast)--- TIP 1: 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
[GENERAL] DB Designer??
Hello List: There is a DB Designer for mySQL. Is there a similar DB designer for postgreSQL? Kirt
Re: [GENERAL] Why overlaps is not working
Richard Broersma Jr <[EMAIL PROTECTED]> writes: > apparently date doesn't know anything about infinity. It doesn't, but we have a TODO item to make it do so, which would presumably include making the timestamp-to-date cast do something more sensible with an infinity timestamp. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Using GIN indexes on 8.2
On Sat, 11 Nov 2006, Robert Treat wrote: On Friday 10 November 2006 14:41, Jeff Davis wrote: On Fri, 2006-11-10 at 20:39 +0300, Teodor Sigaev wrote: Use @>, <@ operations instead of @ and ~ Look for discussions in -hackers for reasons of changing names Ah, many thanks. How about updating those web pages? :) Now they are in core: http://developer.postgresql.org/pgdocs/postgres/functions-array.html Pls, why don't you use tsearch2 with GIN? Perhaps -- is there any documentation on this? Nothing special, just use GIN index instead of GiST. I think a section showing when to use GiST versus GIN would be very helpful (assuming there are still good reasons to use GiST). I would certainly read it. I was wondering this same thing, but it appears the trade-off is disk space/update speed (GiST) vs. query times (Gin). Magnus had a couple of nice posts in his blog you might want to check out: http://people.planetpostgresql.org/mha/ This is written in tsearch2 documentation http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html#indexes Also, see our slides http://www.sai.msu.su/~megera/wiki/tsearch2slides The best practice is to use GIN for archive search and GiST for online one. Table inheritabce with CE would help maintaining of good search performance and fast update. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] DB Designer??
You can try one of these: 1. DBVisualiser http://minq.se 2. PostgreSQL Autocad http://www.rbt.ca/autodoc/index.html 3. Druid http://sourceforge.net/projects/druid 4. SQLManager http://sqlmanager.net/en/products/postgresql 5. Aqua Data Studio http://www.aquafold.com/index-postgresql.html 6. Data Architect http://www.thekompany.com/products/dataarchitect 7. Some more are available at the link below: http://www.postgresql.org/docs/techdocs.54 For Documentation generator tool, please follow the following link: http://directory.fsf.org/text/doc/ Hope this helps...Shoaib MirEnterpriseDB (www.enterprisedb.com)On 10/30/06, kbajwa <[EMAIL PROTECTED]> wrote: Hello List: There is a DB Designer for mySQL. Is there a similar DB designer for postgreSQL? Kirt
Re: [GENERAL] Utility to Convert MS SQL Server to Postgres
You can use the DTS of SQL Server by specifying ODBC source for PostgreSQL.Thanks,---Shoaib MirEnterpriseDB (www.enterprisedb.com)
On 11/12/06, Robert Treat <[EMAIL PROTECTED]> wrote:
On Saturday 11 November 2006 01:26, novnov wrote:> Any recommondations for a util that helps with conversion of MS SQL Server> databases to Postgres? Constraints, triggers, etc?>> Thanks
There are a couple of articles on this athttp://www.postgresql.org/docs/techdocs.3, which I believe have some pointersto afew scripts. (Right click and save to file any pdf docs you run across)
--Robert TreatBuild A Brighter LAMP :: Linux Apache {middleware} PostgreSQL---(end of broadcast)---TIP 2: Don't 'kill -9' the postmaster
