Re: [GENERAL] Strange limit and offset behaviour....

> > I have the following sql: > > SELECT * from table order by dato asc limit 20 offset 0 > > > This gives me different rows than the 20 first rows when running the > following sql: > > SELECT * from table order by dato asc > > > Shouldn't the 20 first rows in the second sql statment be the

Re: [GENERAL] trying to make sense of deadlocks

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Richard Yen > Sent: Monday, February 09, 2009 4:18 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] trying to make sense of deadlocks > > Hi, > > I'm tryin

Re: [GENERAL] Query question

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of Sharma, Sid > Sent: Tuesday, February 24, 2009 12:47 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Query question > > Hi, > > I am new to Postgres and

Re: [GENERAL] how to use pg_dump to dump tables whose owner is me

--- On Tue, 3/3/09, Roger Chen wrote: > >> Hi, > >> Can anyone tell me how to do that? I could find > that in man page of > >> pg_dump. Thanks. > > > > pg_dump -t table1 -t table2 > > > Thanks. If there are many tables owned by me and some other > users, are > there any easy ways to do that? >

Re: [GENERAL] After Upgrade from 8.2.6 to 8.3.6: function to_timestamp does not exist

> > > > OK, so you want to see if a timestamp is greater than now()? Why not > > just compare them? > > > > where a.from_datetime >= now() > > No, not the whole timestamp. I dont want to check the time. > So I had to truncate the datetime with: > > date_trunc('day', a.from_datetime) >= date_tr

Re: [GENERAL] After Upgrade from 8.2.6 to 8.3.6: function to_timestamp does not exist

> > > > This query makes little sense. Why are you trying to convert a > > timestamp to a timestamp? Is this a bizarre substitute for > date_trunc()? > > The "from_datetime" column is of type "timestamp" but I want to check > only the date, not the time. > In this example I want to retrieve al

Re: [GENERAL] Postgresql

d already exist. What can i do? format my pc? Depends. If the OP is running linux (or a *BSD), he can su to root and replace the password for the preferred user (postgres, probably). The passwd program will allow this. If the machine runs Microsoft, I've no idea at all since we don

Re: [GENERAL] UPDATE... FROM - will ORDER BY not respected?

Carlo Stonebanks wrote: (FOR PG VERSION 8.3.6 running on Windows 2003 Server x64) We have a function that assigns unique ID's (to use as row identifiers) to a table via an UPDATE using nextval(). This table is imported from another source, and there is a "sequencing" field to let the query kno

Re: [GENERAL] UPDATE... FROM - will ORDER BY not respected?

Carlo Stonebanks wrote: I think the ORDER BY is free to update the rows in any order it needs to. The key is to put the sequence further down. How about this? Adam - thanks. Unless I hear otherwise I will assume that you mean the UPDATE is free to update the rows in any way it wants - irrega

Re: [GENERAL] limit-offset different result sets with same query

Emanuel Calvo Franco wrote: > > Executing 'select * from datos limit 1 offset 15' two times i have different > result sets. > When i execute 'explain analyze verbose ' i see that (as > expected) the seq scan > is occurring. > > > That's correct? Is logical that if the scan is sequential in

Re: [GENERAL] Question on Foreign Key Structure/Design

APseudoUtopia wrote: Hey list, I have a table with user IDs, among other information. I also have a table of comments that users can place on a page. CREATE TABLE "users" ( "id" SERIAL PRIMARY KEY, ... ); CREATE TABLE "comments" ( "id" SERIAL PRIMARY KEY, "userid" INTEGER REFERENCES "use

Re: [GENERAL] Migrating MySQL app to postgres?

ke it. Can always modify that, too. Otherwise, I'll probably roll my own with python and postgres. Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 ---(

Re: [GENERAL] Migrating MySQL app to postgres?

n restart httpd. Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, pleas

Re: [GENERAL] Migrating MySQL app to postgres?

On Fri, 22 Apr 2005, Joshua D. Drake wrote: Depends on how it was written. If they used Pear it shouldn't be "too" difficult. It will take work no matter what. Thanks, Josh. I should have mentioned that it's written using php as the middleware. Rich -- Dr. Richard B. Shepar

Re: [GENERAL] Migrating MySQL app to postgres?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 22 Apr 2005, David Roussel wrote: Rich, what's wrong with the app as it is? Why change anything? David, Because I don't have MySQL installed, have no need for it, and have used postgres for about eight years so I know it well. Ric

[GENERAL] Migrating MySQL app to postgres?

olved? TIA, Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Migrating MySQL app to postgres?

. I'm still not sure about the reports, but one step at a time. That is, will the pygresql display nicely formatted reports based on sql queries to the backend? Need to find out. Again, thanks, Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) <http://www.appl-e

Re: [GENERAL] Migrating MySQL app to postgres?

tion) with usable connect libs for quite some time. Ruby on Rails has promise, but also feels like it's really just getting started. I don't make my living writing software so I'm neither a language bigot nor a language collector. If it works, it's good enough. :-) Thanks,

[GENERAL] Primary Key and Indices

I'm converting mysql tables to postgres. One of the tables has this: PRIMARY KEY (org_id, contact_id), KEY contact (contact_id, org_id) Is there really a difference in the two indices if the sequence of fields is reversed? Thanks, Rich -- Dr. Richard B. Shepard, President Applied Ecos

Re: [GENERAL] Primary Key and Indices

On Sun, 24 Apr 2005, Ragnar Hafstað wrote: yes. for example, only the first one can be used for ORDER BY org_id gnari, Thank you. I thought it might be the case but I wasn't sure. Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) <http://www.appl-ecosys.com&

Re: [GENERAL] Migrating MySQL app to postgres?

yrus" that also looks quite robust and useful. Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 1: subs

Re: [GENERAL] blob storage

*" is IM for postgres, eh? PostgreSQL-7.x on the 2.2.x kernel series has a limit of 2G. However, on the 2.4.x kernels the limit is 2T; postgres-8.x on the 2.4.x kernels can store 4T per table. If that pinches your need, I'd love to be selling you storage solutions. :-) Rich -- Dr. Richard B. S

[GENERAL] Adding Records With SERIAL Primary Key

mode (rather than one at a time, manually). The core table has a SERIAL data type field as the primary key. How is this field assigned values? Then, how do I load the related tables so they reference the proper records? Thanks, Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc

Re: [GENERAL] Adding Records With SERIAL Primary Key

, Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 6: Have you searched our list archives?

Re: [GENERAL] Inserting a record into another table ... inside a

t is the best way to achieve this ? You want search_path -- see Pardon my ignorance, but why wouldn't it work to specify the schema with the table; e.g., update myschema.table1? Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) <http://www.appl-ecosys.com>

Re: [GENERAL] Inserting a record into another table ... inside a

same structure I suspect you are looking for ways to simplify the code without using fully qualified tables. Hmmm-m-m. Perhaps you are correct. I'd have thought of a loop, but I don't know the organization of those schema. Anyway, thanks for removing my puzzlement. Rich -- Dr. Richard

Re: [GENERAL] Adventures in Quest for GUI RAD

a GUI GUI developer; a RAD if you wish. It works with C++ and with python (my lanuage of choice now). All these tools are free. Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 5

Re: [GENERAL] perl and insert

ry this (although the performance gains depend on database you're using) my $st = $dbh->prepare ('INSERT INTO table (line) VALUES (?)'); while (my $line = ) { $st->execute ($line); } - Rich Doughty ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[GENERAL] Referencing Serial Type as Foreign Key

or is the datatype SERIAL because that's what the referenced primary_key_field is? Thanks, Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc. (TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 ---

Re: [GENERAL] Referencing Serial Type as Foreign Key

On Fri, 3 Jun 2005, Joshua D. Drake wrote: Serial is a psuedo type to integer/big (depending on the type of serial). Josh, That's what I thought. What you have above should work fine. OK. Many thanks, Rich -- Dr. Richard B. Shepard, President Applied Ecosystem Services, Inc

Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase

On Wed, 12 Oct 2005, Jan Wieck wrote: Oracle could even develop an exceptional interest in keeping PostgreSQL alive as it's "future DB engineer forge". Jan, Or, to demonstrate that it's not a monopoly. There will be two choices: Oracle and postgres. Rich -- Dr

[GENERAL] Outer join query plans and performance

ined on h1, but it isn't. It only joins on t. can anyone give any tips on improving the performance of the second query (aside from changing the join order manually)? Thanks -- - Rich Doughty ---(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] Outer join query plans and performance

Rich Doughty wrote: I'm having some significant performance problems with left join. Can anyone give me any pointers as to why the following 2 query plans are so different? [snip] knew i'd forgotten something... select version();

Re: [GENERAL] Only MONO/WinForms is a way to go

t of other countries. Many schools -- including a large number in Oregon and Washington -- run linux for both students and administrators using the Linux Terminal Server Project (LTSP/K12). Well, that's enough. If you look around you, you'll recognize how silly that reads. At least, I hop

Re: [GENERAL] Development of cross-platform GUI for Open Source DBs

On Tue, 28 Nov 2006, Merlin Moncure wrote: For a general purpose language, lately I've been taking a really good look at 'D', which looks to be an amazing language. Has anybody tried to hook up postgresql to D? No, I haven't. But, if you want a cross-platform language and GUI toolkit, consi

[GENERAL] Prelink errors?

The following log entries are generated by the prelink utility on my RHEL 4 server. I'm using the postgres 8.2.1 rpm binaries from postgresql.org I know prelink is probably unnecessary for postgres, but perhaps this error message could be easy corrected (maybe it's caused by a bug elsewhere?) p

Re: [GENERAL] Problems With VIEWS

Select * from pg_rules ? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeanna Geier Sent: Wednesday, January 10, 2007 5:43 PM To: Richard Broersma Jr; pgsql-general Subject: Re: [GENERAL] Problems With VIEWS I guess I have a general question regardi

Re: [GENERAL] Optimize expresiions.

How about this? select item, very_expensive_function(item) as exp, cheap from ( Select item, cheap_function(item) as cheap From atable where cheap_function(item) > 0 ) sub where very_expensive_function(item) > 0 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTEC

Re: [GENERAL] Backup the part of postgres database

Roopa, You can use the command pg_dump to backup specific tables. But you won't be able to restrict the backup to specific rows. (You can always create a separate table just for backups, and dump just that table). -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]

Re: [GENERAL] Dynamic loading of Perl Code in Postgres functions

You treat it like any other perl code (you don't have to do anything special just because it's in postgres): If it's pure perl code, see "require" http://perldoc.perl.org/functions/require.html If it's a perl module, see "use" http://perldoc.perl.org/functions/use.html If you want to access

Re: [GENERAL] Process won't start in Windows

Robert, Open "Computer Management" and find the postgres service. There should be an item saying "Run As". You want that to be a non-privledged account. Typically, postgres asks you what account to use when you install it. Just make sure that account has not been granted administrative rights

Re: [GENERAL] How I can read-back a serial value just inserted?

If it's a recent PG: select lastval() See this: http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of dfx Sent: Monday, January 01, 2007 11:46 AM To: pgsql-general@postgresql.org Subject

Re: [GENERAL] Finding bogus dates

I suppose you could create a boolean function that does a cast, and catches the execption, returning NULL. If that doesn't work, a perl stored procedure using Date::Calc and check_date() ? - Original Message From: Scott Ribe <[EMAIL PROTECTED]> To: pgsql-general postgresql.org S

Re: [GENERAL] Example of RETURNING clause to get auto-generated keys from INSERT

Let's say you have a table with "id, value" columns. And your normal query would be this: INSERT into mytable (id,value) values (1,"foo"),(2,"bar"); Your new query would be like this: INSERT into mytable (id,value) values (1,"foo"),(2,"bar") RETURNING id; And you would get a result back with

[GENERAL] Cannot Restart PostgreSQL-8.1.4

tl: neither postmaster nor postgres running' which is true. I don't know what I did to break the installation, but I would greatly appreciate help getting it running again ASAP. TIA, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosyst

Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4 -- SOLVED!

nning. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 ---(end o

Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4

.so.4.1* -rwxr-xr-x 1 root root 111532 2006-05-24 15:06 libpq.so.4.1* What version of libpq.so do I need? Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.c

Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4

is journey). I don't know how to run ldd on a perl script. (It's ok to have multiple versions of the client library installed...) Sure. I do for many libraries. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Eco

Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4

ol lookup error: /usr/lib/perl5/site_perl/5.8.8/i486-linux/auto/DBD/Pg/Pg.so: undefined symbol: PQserverVersion The version of DBD::Pg is 1.4.9 which is the most recent one available. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Ser

Re: [GENERAL] column insert/alter got me stumped!

I think you want this: update tablename set columname = 'value' where column2 = 'value2'; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of John Smith Sent: Wednesday, January 24, 2007 5:59 PM To: pgsql-general@postgresql.org Subject: [GENERAL] column in

Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4

m May 2006). That I built and installed this morning. It's calling /usr/lib/libpg.so.3.1, but I also have /usr/lib/ligpg.so.4.1. The perl script, login.pl, hasn't changed in a couple of years. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Eco

Re: [GENERAL] Cannot Restart PostgreSQL-8.1.4

at occupied me since yesterday afternoon. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 --

Re: [GENERAL] Duplicate key violation

Sounds like you'll either need an explicit "LOCK TABLE" command, set your transaction isolation to serializable, or use advisory locking. http://www.postgresql.org/docs/8.2/interactive/explicit-locking.html#LOC KING-TABLES http://www.postgresql.org/docs/8.2/interactive/transaction-iso.html#XACT -

Re: [GENERAL] Can you specify the pg_xlog location from a config

You can also "mount" a partition as a directory under an existing drive letter, using the "disk management" utility built-in to windows. It's not as granular as the junction method, but there's a built-in gui for it, and its more widely known and used. -Original Message- From: "Jere

Re: [GENERAL] PostgreSQL 9.0

that an upgrade tool (that doesn't exist yet) could be used to make the changes with minimal downtime. Bruno, So, to migrate from -8.1.4 to -8.2.1 I don't need to dump and restore? Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied

Re: [GENERAL] PostgreSQL 9.0

On Tue, 30 Jan 2007, Michael Glaesemann wrote: It was *discussed*. 8.1 to 8.2 (as does any move from M.x to M.y where x ­ y) requires a dump and reload. Michael, That's what I thought. However, it never hurts to ask. :-) Rich -- Richard B. Shepard, Ph.D. |

Re: [GENERAL] PostgreSQL 9.0

On Tue, 30 Jan 2007, Michael Glaesemann wrote: Or check the release notes :) Oooh! What a novel idea. :-) I don't have the time -- or the need right now -- to upgrade so it's on the back burner. Thanks, Michael, Rich -- Richard B. Shepard, Ph.D. |The Env

Re: [GENERAL] pg migrator

ook I could find it on the site, but only manually moving through the pages. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 F

Re: [GENERAL] pg migrator

On Tue, 30 Jan 2007, Erik Jones wrote: I had the same problem: searching for 'pg_migrator' found nothing. However, searching for 'migrator' got it. A-ha! I didn't try that, just fell back to Google. :-) Thanks, Erik, Rich -- Richard B. Shepard, Ph.D.

[GENERAL] DBMS Engines and Performance

s these issues have come up over time on this list. But, I would like some response from more knowledgeable folks on the quoted statement above, just for my own edification. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.

Re: [GENERAL] DBMS Engines and Performance

applcations. So, repeating all the 'mine's bigger than yours' arguments will not convince anyone differently. :-) Many thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <h

Re: [GENERAL] DBMS Engines and Performance

e, but I've seen too many poorly managed companies to be surprised any longer. Across many industries I wonder why some companies manage to have survived as long as they have. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services,

Re: [GENERAL] DBMS Engines and Performance

On Tue, 30 Jan 2007, Rich Shepard wrote: business sense. However, this seems to be what every CRM/SFA[1] Oops! [1] Customer Relations Management/Sales Force Automation. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc

Re: [GENERAL] DBMS Engines and Performance

feasible. Ron, In the case of the system I'd like to use, it's not a company but a group of talented coders. And, they do use a lot of MySQL-specific features. If I had the money I'd sponsor the port, but I don't. I also don't have the time or expertise to underake it

Re: [GENERAL] DBMS Engines and Performance

I could benefit greatly from one of these CRM tools to track our marketing/sales efforts. From all I've seen xrms has the feature set I like the best, but I'll take any that work. I don't have the time, financial resources, or postgres/php skills to make the conversion on my own. I'

Re: [GENERAL] 'greatest' function?

I don't see greatest or least anywhere on that page... But they ARE here: http://www.postgresql.org/docs/8.2/interactive/functions-conditional.htm l#AEN13140 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of brian Sent: Wednesday, February 07, 2007 10:07

Re: [GENERAL] SQL textbook

'database design,' and some confuse design tools with design concepts. Only Jan can decide what references are pertinent to his needs. Again, Google is your friend. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.|

Re: [GENERAL] SQL textbook

On Thu, 8 Feb 2007, Ted Byers wrote: Do you have any URLs for the books that are available for download? Google is your friend. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <h

Re: [GENERAL] Priorities for users or queries?

There is a function pg_backend_pid() that will return the PID for the current session. You could call this from your updating app to get a pid to feed to the NICE command. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Benjamin Arai Sent: Saturday, Feb

Re: [GENERAL] Function to return list of all prime numbers in range

Hi Melvin, Here is a slightly optimized version of this function It returns the exact same results, just runs about 1000x faster. I've also marked it as "immutable", that's probably what you wanted, not Volatile. select all_prime(1,11000) -- Total runtime: 2868.264 ms select all_prime

Re: [GENERAL] Function in psql to Compare two numbers and return the bigger value

You want GREATEST() -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Emi Lu Sent: Tuesday, February 13, 2007 12:37 PM To: pgsql-general@postgresql.org Subject: [GENERAL

Re: [GENERAL] suggestions on improving a query

This line: Index Scan using plp_total_idx on dockscore_plp (cost=0.00..16733229.92 rows=4669988 width=80) (actual time=98.323..322537.605 rows=25197 loops=1) Means the planner did what it did, because it estimated there would be nearly 5 million rows. However, there were only 25,000. Have t

Re: [GENERAL] Problem writing sql statement....

Or, if you need the whole row: SELECT at1.* FROM a_table as at1 WHERE EXISTS ( SELECT 1 FROM a_table as at2 WHERE at2.my_date = at1.my_date AND at2.prod_id = at1.prod_id AND at2.primary_key <> at1.primary_key ) This form can easily be adjusted to show only certain duplicates, or only to

Re: [GENERAL] How would you handle updating an item and related stuff all at once?

I'm not sure I understand completely, but here's one idea. in the backend, when the user submits their changes: 1) Pull the current state of AccountEmployeeRelation for the account you're working on 2) Compare the current state to what the user posted, and determine what needs to be added and del

Re: [GENERAL] boolean operator on interval producing strange results

All of these statements produce 'f' for me as well, via 8.2.1 on RHEL 4. select ((now() - '1 day'::interval)::timestamp - now()) < 0; select ((now() - '1 day'::interval)::timestamptz - now()) < 0; select ('-1 days'::interval) < 0; But all of these return 't': select ((now() - '1 day'::interval

Re: [GENERAL] postgresql vs mysql

I'm not apologizing for their past mistakes.. But the issue you cite is no longer true: "As of 5.0.2, the server requires that month and day values be legal, and not merely in the range 1 to 12 and 1 to 31, respectively." mysql> use test Database changed mysql> create table test ( td DATE );

Re: [GENERAL] postgresql vs mysql

> It's got a query parser that's dumb as a brick. While we're on this topic... I have a question on these series of queries: -- Query A select count(*) from customers c where not exists ( select 1 from orders o where o.customer_id = c.customer_id ) -- Query B select count(*) from customers c

Re: [GENERAL] postgresql vs mysql

u have a credit card, would you prefer the balance to be blank or unknown? How would the bank select all those with no balance rather than those for whom a balance is unknown? I'm sure you can think of dozens of more situations like these. Rich -- Richard B. Shepard, Ph.D.

Re: [GENERAL] postgresql vs mysql

a bit strange to question the validity of zero. NULL solves as many intransigent problems with digital data storage and manipulation in databases as zero did in the realm of counting. HTH, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applie

Re: [GENERAL] pulling hair out trying to force replan

> I've got some pretty big tables with partial indexes > on very specific values. It seems as though no matter > what I try to force a replan it won't plan to use the > partial indexes because it seems to be caching a plan > valid for all potential parameters. I'm using hibernate > which uses

[GENERAL] Post-Reboot Issue: Postmaster Not Accessible

p/.s.PGSQL.5432"? The server is running locally. How can I test whether it's accepting connections on the socket? And, what can I do to get it back to normal and accpting connections? Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applie

Re: [GENERAL] Post-Reboot Issue: Postmaster Not Accessible

pting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? I used to be able to connect to an existing database just by typing it's name after the command. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.|

Re: [GENERAL] Post-Reboot Issue: Postmaster Not Accessible

remedy this? Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-8863 --

Re: [GENERAL] Post-Reboot Issue: Postmaster Not Accessible -- FIXED

boot process to see what's going wrong. Ahhh-h-h! That did it. I did not realize that I could kill the socket and have postgresql start it again upon loading. It's there now (owned by postgres.users), and I can access both the shell and my accounts via SQL-Ledger. Thanks very much, Ric

Re: [GENERAL] Post-Reboot Issue: Postmaster Not Accessible

vious postmaster's life. Something certainly did the wrong thing. Now that I know I can safely delete the socket file I'm prepared if this ever happens again. Thank you, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosyste

[GENERAL] Custom session variables?

Is there any way to define custom variables per session scope? In oracle, we do this using package variables. Basically when a user logs into our application, it generates one-time session information that we want to make available to all the procedures and triggers, without passing it directly (

Re: [GENERAL] Custom session variables?

Any way to pull the value of a config variable into a pl/pgsql variable? > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Friday, March 02, 2007 3:46 PM > To: David Fetter > Cc: Adam Rich; pgsql-general@postgresql.org > Subject: Re: [GENER

Re: [GENERAL] Re: Anyone know a good opensource CRM that actually installs with Posgtres?

o idea. Doesn't make any sense to me. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.com> Voice: 503-667-4517 Fax: 503-667-886

Re: [GENERAL] Debugging Server Code

Kenneth Downs wrote: > I'm guessing that there is no step-wise debugger for pgsql. If there > is, then glory be and call me a dummy for not R'ing TFM. > > In fact, I've never heard of a step-wise debugger for any DB > server, am I wrong? > Yes. I've done step-wise debugging in Oracle (PL/S

Re: [GENERAL] exception handling in plperlu

You'll want something like this: my $dbh = eval { DBI->connect();}; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jasbinder Singh Bali Sent: Friday, March 16, 2007 9:55 AM To: Martijn van Oosterhout; Tom Lane; pgsql-general@postgresql.org Subje

Re: [GENERAL] Business days

On Wed, 25 Apr 2007, Eddy D. Sanchez wrote: I need to get the a total number of business days (from monday to friday) between two dates. Someone can help me please. Joe Celko's "SQL for Smarties, 2nd Edition" has exactly this solution. Check it out! Rich -- Richard B

[GENERAL] Upgrade Process

5) Run 'psql -f pg8.1.4.sql postgres' as user postgres. Have I missed a critical step? The upgrade will replace the existing files with the new ones in the same directories. TIA, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permittin

Re: [GENERAL] Upgrade Process

pull from the old version. The catch is that you need enough room for both copies of the database. OK. I'll do this. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://

[GENERAL] Preferred Installation/Data Directories

all refer to an installation in /usr/local/. Is there a preference? Does it matter? On my system /usr/local/ is 20 times the size of /var/, so it makes more sense to store data where there's more room. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Ap

Re: [GENERAL] Preferred Installation/Data Directories

ly the initscript but the SELinux policy, which by default will constrain the postmaster to write only under /var/lib/pgsql/. I see in postgresql.SlackBuild where the data directories are specified. I'll change that from /var/lib/pgsql/data to /usr/local/pgsql/data and rebuild the package

Re: [GENERAL] Preferred Installation/Data Directories

hose /home directory will you put the data? Since /usr/local, /opt, and similar filesystems are for local interest stuff, they make ideal data storage areas. Neither is affected by distribution upgrades so the data are safe there. Rich -- Richard B. Shepard, Ph.D. |The Environmental

[GENERAL] CONSTRAINT name With Domain As Check

in the Permits table as follows? CREATE TABLE Permits ( ... type Permit_Type CONSTRAINT invalid_permit_type ... ); If not, does the constraint name belong in the domain definition? Rich -- Richard B. Shepard, Ph.D. |The Environmental Pe

[GENERAL] Temporal Units

do I need two columns (frequency and name) and do the conversion to a common time unit behind the scenes? Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.com> Voi

Re: [GENERAL] Temporal Units

hmetic to see if required sampling was done and entered in the application. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) <http://www.appl-ecosys.com> Voice: 503-

[GENERAL] DRI in Docs?

I cannot find a discussion of the meaning and use of Declarative Referential Integrity (DRI) in the on-line docs ToC or Index. Please tell me in which chapter/section this is covered. Thanks, Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem

<    1   2   3   4   5   6   7   8   >