Re: [GENERAL] Shortcut evaluation in OR or IN
On Mon, May 06, 2013 at 02:16:38PM +1200, Tim Uckun wrote: > Say I have a select like this. > > SELECT * FROM table where field = X OR field = Y limit 1 > > And I have two records one that matches X and one that matches Y will I > always get X because the evaluation will stop after the first clause in the > OR matches? > > What about for IN (X, Y) There is no short-circuiting; you'll get one record or the other but no guarantee which. If you want to guarantee what order records come out in you need to add an ORDER BY. In the specific case you're describing you could do ORDER BY field = X DESC and get the order you're looking for. > how about if I am doing an update > > UPDATE table1 set x=table2.y where table1.field1 = table2.field1 OR > table1.field2=table2.field2 > > Will it update based on field1 if both fields match? An update affects all rows that match the given condition so you'd get both rows updated in this case. There's no LIMIT or ORDER BY available in UPDATE. Richard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Checking for changes in other tables
On Fri, Apr 26, 2013 at 11:01:28AM +0200, CR Lender wrote: > Is there any way to ensure that all donors and recipients in eu_loans > are in the EU, without altering the countries and persons tables? One way to do this would be to add countries to the eu_loans table so it looks like this: create table eu_loans ( donor text not null, donor_country char(2) not null, recipient text not null, recipient_country char(2) not null, primary key(donor, recipient), foreign key (donor, donor_country) references persons (name, country) on update cascade, foreign key (recipient, recipient_country) references persons (name, country) on update cascade ); then create an sql function to tell you whether a country is in the eu: create function country_in_eu (char(2)) returns bool as $$ select count(*) > 0 from countries where code = $1 and eu = 't' $$ language 'sql'; and add two constraints to eu_loans: alter table eu_loans add constraint donor_in_eu check(country_in_eu(donor_country)); alter table eu_loans add constraint recipient_in_eu check(country_in_eu(recipient_country)); This will give an error if someone moves outside the EU (but not if a country leaves the EU). It may or may not seem elegant depending on your thinking but it does have the effect you're looking for. Of course you could set things up so that you could do an insert to eu_loans specifying just the donor and recipient names and the system would populate the country fields for you by looking up in persons, throwing an error if appropriate. Richard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FreeBSD 5.2.1, postgresql 7.4.5 and shared memory settings
On Sun, Jan 30, 2005 at 03:21:25PM -0500, Rick Apichairuk wrote: > I am trying to increase the number of max_connections to 200 and found > out I couldn't even start postgresql. So I tried to increase > max_connections gradually (currently at 50 from 40 default) and adjust > shared memory settings along the way. Even at 50 max_connections, I > still cannot get postgresql to start. > > The following is the error message I receive: > > pgsql FATAL: could not create shared memory segment: Invalid argument > DETAIL: Failed system call was shmget(key=5432001, size=35323904, > 03600). > To reduce the request size (currently 35323904 bytes), > kern.ipc.shmmax: 33554432 Looks like the request size is just higher than shmmax. The majority of the request is for the shared buffers, so if you can't increase shmmax any more you can reduce your shared buffers downwards from 4096 and this will give you some room for more connections. Richard ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Apparently I don't understand full outer joins....
On Tue, Jan 25, 2005 at 12:29:07PM -0800, Ben wrote: > select > coalesce(a.n,0) as a, > coalesce(b.n,0) as b, > coalesce(c.n,0) as c, > coalesce(a.s,b.s,c.s) as s > from > ( select 1 as n, 0 as s) a full outer join > ( select 1 as n, 1 as s) b full outer join > ( select 2 as n, 2 as s) c > on > a.s = b.s and > b.s = c.s > > > and get a syntax error at the end of my query. Apparently what I'm > trying to do doesn't make sense? The ON clauses have to be attached directly to the outer joins. So you probably mean select coalesce(a.n,0) as a, coalesce(b.n,0) as b, coalesce(c.n,0) as c, coalesce(a.s,b.s,c.s) as s from ( select 1 as n, 0 as s) a full outer join ( select 1 as n, 1 as s) b on a.s = b.s full outer join (select 2 as n, 2 as s) c on b.s = c.s; Richard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pg SQL question
On Sat, Jan 22, 2005 at 02:03:58PM -0700, Ed L. wrote: > > There's probably an obvious answer for this, but I couldn't see it in the > docs. What's the simplest way to concatenate multiple same-column values > in SQL? You can create an aggregate that does nothing but concatenate the entries: CREATE AGGREGATE concat ( BASETYPE = TEXT, SFUNC = textcat, STYPE = TEXT, INITCOND = '' ); This uses the "textcat" function, which is already lurking in Postgres to implement the || operator. Then you can go: SELECT concat(entry) FROM ( SELECT * FROM speech ORDER BY id ) AS lines; And it will do what you want. The subselect with the ORDER BY guarantees that the lines come out in the order you put them in. Richard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Sorting street addresses
On Thu, Oct 28, 2004 at 03:36:00PM -0400, Robert Fitzpatrick wrote: > I would like all those on the same street grouped together. Is there any > tricks to getting the street names sorted first, possibly where numbers > and strings separate? You could do something like CREATE FUNCTION streetname(text) RETURNS text AS ' SELECT substring($1 FROM ''[a-zA-Z ]+$'') ' LANGUAGE 'SQL'; and then add an ORDER BY streetname(address) to your select. Richard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] [SQL] simple addition of constraints problem
On Tue, Jun 08, 2004 at 05:51:22PM -0400, Michelle Murrain wrote: > I'd like to make 'courseid' in this second table a foreign key, > referencing 'courseid' in the first table. So I did this command: > > charter_dev2=# ALTER TABLE coursesevaluations ADD CONSTRAINT rc_ce > FOREIGN KEY (courseid) REFERENCES rcourseinfo (courseid); > > And I get this result: > > NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY > check(s) > ERROR: rc_ce referential integrity violation - key referenced from > coursesevaluations not found in rcourseinfo What this is telling you is not that there is any problem with your formulation of the constraint (which as far as I can tell is what you want), but that the existing data in the tables violates the constraint you're trying to impose. If you do something like SELECT rcoursesevaluations.courseid FROM rcoursesevalutations NATURAL LEFT JOIN rcourseinfo WHERE rcourseinfo.courseid IS NULL you should see which courseids appear in rcoursesevaluations but not in rcourseinfo. Richard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] generic insert into table
On Mon, Jun 07, 2004 at 02:29:33PM -0700, Dennis Gearon wrote:
> I have the following code from an application that is 'mysql_centric'. I
> want to make it generic across all databases, if it's possible,
> especially postgres :-)
>
> mysql version:
>INSERT INTO calendar_setting SET setting='colorEvent',value='#C2DCD5';
That would be
INSERT into calendar_setting (setting, value)
VALUES ('colorEvent', '#C2DCD5');
This is the standard syntax, which is not quite the same thing as
saying it will work on all DBs - it will certainly work on both
Postgres and MySQL.
Richard
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Lost indexes
On Fri, Jul 18, 2003 at 08:26:59AM -0300, Vilson farias wrote: > Greetings, > > I've been fighting against a very strange behaviour found in PostgreSQL > 7.1.2 on a RedHat 6.2. I have a very simple table called site_site and I > lost it's indexes everytime I run a vaccum. Do you know why this happens? Is > there a way to get around or fix this kind of problem?I put a full sequence > of steps I've used to make it happen. The indexes are not lost; after you have done an ANALYZE, the system knows that they won't speed up your queries, so it chooses sequential scans instead. If you put a lot of data in the table and ANALYZE again, you'll find that the indexes are used. Richard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Can you help a newbie?
On Wed, Feb 07, 2001 at 06:53:13AM -0600, Jamie Bumsted wrote: > Hi All, > I am new to pgsql, I have inherited a database with no documentation, i.e. > nobody knows the passwords for any of the database accounts. Anybody have > any ideas on how to change the passwords without first knowing the > passwords? You can edit your pg_hba.conf to allow local access with no passwords (add a line like "local all trust" above other non-comment lines) and then connect to the database as the database superuser (usually "postgres") and use "alter user" to change the passwords to whatever you like. Don't forget to change pg_hba.conf back afterwards. Richard
Re: [GENERAL] Newbie Question
On Wed, Nov 01, 2000 at 12:32:58PM -0500, Nick Fankhauser wrote: > According to the documentation and Bruce M's book, there is no limit. I've > never hit a limit while putting some pretty large (three page) narratives in > a text field. There is no limit on the "text" type as such, but there is still the limit on the total size of a row: about 8k by default, 32k if you're willing to recompile (see the FAQ). This limit will be removed entirely by TOAST in 7.1 . Richard
[GENERAL] "initdb -t" ate my baby
Apologies to anyone who has already seen this once; I sent it about the time that many of us seemingly stopped receiving mail from the list, so I don't know whether most people got it or not. I've found what may be a bug in initdb, a bug in its documentation, a misunderstanding on my part, or a combination of the above. I'm using 7.0.2 under Debian GNU/Linux. The User's Guide says of the "-t" or "--template" option: > Replace the template1 database in an existing database system, and don't > touch anything else. [...] > You can't destroy anything by running initdb with the --template option. But when I try it: barbra:/home/rp$ /usr/lib/postgresql/bin/initdb -D /var/lib/postgres/data -t Updating template1 database only. This database system will be initialized with username "postgres". This user will own all the data files and must also own the server process. Creating template database in /var/lib/postgres/data/base/template1 000911.16:55:00.054 [2376] FATAL 2: BootStrapXLOG failed to create control +file (/var/lib/postgres/data/pg_control): 17 000911.16:55:00.054 [2376] FATAL 2: BootStrapXLOG failed to create control +file (/var/lib/postgres/data/pg_control): 17 initdb failed. Removing /var/lib/postgres/data. Removing temp file /tmp/initdb.2356. Oh dear. My whole data directory has been blown away. What seems to be happening is that initdb removes the existing template1, then calls the "postgres" bootstrap backend, which tries to create the whole data directory from scratch, although most of it is still there. It fails and aborts with the two FATAL errors above. initdb notices that it has failed and goes to its cleanup function, "exit_nicely", which removes any directories that initdb *might* have created, including my data directory. There seem to be two separate problems here: 1) initdb invokes the postgres backend in the wrong way for the purpose when the "-t" option is given. 2) the cleanup function doesn't know about the "-t" option, so it removes things that in this particular case it shouldn't. The second is easy to fix with a quick patch. The first is beyond my knowledge of the internals. But as I've never seen anyone else mention this problem, I suspect I've just completely misunderstood something. Would anyone care to comment? Richard
Re: [HACKERS] Re: [GENERAL] Revised Copyright: is this more palatable?
On Wed, Jul 05, 2000 at 11:13:45PM -0300, The Hermit Hacker wrote: > On Wed, 5 Jul 2000, Jim Wise wrote: > > > I'd like to point out a couple things that are _not_ wrong with the > > current license: > > > > 1.) With the current license, contributors to the code are not opened > > to legal liability for the code they contribute. The BSD license > > very clearly disclaims all warranty on the part of not only UCB but > > also all contributors > > Actually, this is the only thing that I do feel the current license is > missing ... unless I'm reading something wrong, it all focuses on > disclaming "UNIVERSITY OF CALIFORNIA"s liability ... that one is very > specific ... Since no-one else has mentioned this yet, I will: the Postgres license, i.e., the file COPYRIGHT at the top level of the distribution, isn't exactly identical to what's commonly known as "the BSD license". The Postgres copyright, the BSD 4.4 copyright (http://www.freebsd.org/copyright/license.html), and the FreeBSD copyright (http://www.freebsd.org/copyright/freebsd-license.html), are all differently worded in parts, although clearly the same in intent. The latter is almost identical to the BSD license template at http://www.opensource.org/licenses/bsd-license.html . All of them except ours say something like "REGENTS AND CONTRIBUTORS" when they're disclaiming warranties; we just have the University of California doing so. The simplest way to change our license if we want to make sure that it explicitly disclaims warranties on behalf of all contributors seems to be to add to the existing California paragraphs a dead standard BSD license with our contributors referred to collectively, which is what Marc has proposed. There may be people who for one reason or another (usually US law, as far as I can see) would like to see more changes, but I can't see what's objectionable about this one. Richard
