[SQL]

Im running/playing with PG 8.0 locally and want to install the contrib/dblink and contrib/crosstab. Can this be done on Windows, is there a GMAKE.exe for Windows?? Someone enlighten me! __This email, including attachments,

Re: [SQL] multi column foreign key for implicitly unique columns

Ð ÐÑÐ, 18.08.2004, Ð 04:45, Josh Berkus ÐÐÑÐÑ: > Markus, Hi Josh, > Hey, I see you figured out a workaround to writing a trigger for this. Let's > see if we can make it work. > > ERROR: there is no unique constraint matching given keys > for referenced table "objects" > > The reason

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

Josh Berkus wrote: The Problem: for each "case" there are from zero to eight "timekeepers" authorized to work on the "case", out of a pool of 150 "timekeepers". This data is stored vertically: authorized_timekeepers: case_id | timekeeper_id 213447 | 047 132113 | 021 132113 | 115 132

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

Greg, Stephan, > (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 1) AS tk2, bz! Thanks for playing. LIMIT and OFFSET, sadly, are not SQL standard. They're only portable to MySQL. This has to port to SQL Server and Oracle. > If that works for 3 (and I think that's standard behav

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Names shortened to spare the line lengths: SELECT bob.cid, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1) AS tk1, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 1) AS tk2, (SELECT tid FROM ats WHERE cid=bob.cid LIMIT 1 OFFSET 2) AS

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

On Tue, 17 Aug 2004, Josh Berkus wrote: > I have a wierd business case. Annoyingly it has to be written in *portable* > SQL92, which means no arrays or custom aggregates. I think it may be > impossible to do in SQL which is why I thought I'd give the people on this > list a crack at it. Solve

Re: [SQL] SQL Challenge: Arbitrary Cross-tab

I would use my report writer, but in any case you'd want at least 2 separate queries, maybe three to keep it simple and readable. If you are allowed to use stored procedures you can build up the output by using simple concats instead of text aggregation (which is a procedure of simple concats).

[SQL] SQL Challenge: Arbitrary Cross-tab

Folks, I have a wierd business case. Annoyingly it has to be written in *portable* SQL92, which means no arrays or custom aggregates. I think it may be impossible to do in SQL which is why I thought I'd give the people on this list a crack at it. Solver gets a free drink/lunch on me if we

Re: [SQL] multi column foreign key for implicitly unique columns

Markus, Hey, I see you figured out a workaround to writing a trigger for this. Let's see if we can make it work. ERROR: there is no unique constraint matching given keys for referenced table "objects" The reason for this is that CASCADE behavior gets quite odd when there is an FK ref

Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

On Tue, Aug 17, 2004 at 16:51:21 +0200, Markus Bertheau <[EMAIL PROTECTED]> wrote: > ?? ??, 17.08.2004, ?? 16:26, Jean-Luc Lachance ??: > > This query does not make sense to me. > > Why would you create an updatable subquery just to get the highest value? > > To make sure that the hi

Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

If your intent is to insert a new record with position incremented by 1, you should use a trigger. Look at the autoincrement thread from few days ago. Markus Bertheau wrote: Ð ÐÑÑ, 17.08.2004, Ð 16:12, Bruno Wolff III ÐÐÑÐÑ: SELECT MAX(position) FROM (SELECT position FROM classes WHERE name = '

Re: [SQL] multi column foreign key for implicitly unique columns

Stephan Szabo <[EMAIL PROTECTED]> writes: > ... It sounds like it'd be a pain at best. Also, that would directly violate the SQL spec's model of dependencies, with possibly unpleasant consequences. The current implementation does exactly what SQL says to do. I cite from SQL99 11.99 DROP CONSTRAI

Re: [SQL] multi column foreign key for implicitly unique columns

On Tue, 17 Aug 2004, Markus Bertheau wrote: > В Втр, 17.08.2004, в 17:06, Stephan Szabo пишет: > > On Tue, 17 Aug 2004, Markus Bertheau wrote: > > > > > В Втр, 17.08.2004, в 16:46, Tom Lane пишет: > > > > > > > I think one reason for this is that otherwise it's not clear which > > > > unique const

Re: [SQL] multi column foreign key for implicitly unique columns

Ð ÐÑÑ, 17.08.2004, Ð 17:06, Stephan Szabo ÐÐÑÐÑ: > On Tue, 17 Aug 2004, Markus Bertheau wrote: > > > Ð ÐÑÑ, 17.08.2004, Ð 16:46, Tom Lane ÐÐÑÐÑ: > > > > > I think one reason for this is that otherwise it's not clear which > > > unique constraint the FK constraint depends on. Consider > > > > > >

Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

On Tue, 17 Aug 2004, Markus Bertheau wrote: > В Втр, 17.08.2004, в 16:12, Bruno Wolff III пишет: > > > SELECT MAX(position) FROM (SELECT position FROM classes WHERE name = > > > 'foo' FOR UPDATE OF classes) AS foo > > > > > > It's clear which rows should be locked here, I think. > > > > Even if it

Re: [SQL] multi column foreign key for implicitly unique columns

On Tue, 17 Aug 2004, Markus Bertheau wrote: > Ð ÐÑÑ, 17.08.2004, Ð 16:46, Tom Lane ÐÐÑÐÑ: > > > I think one reason for this is that otherwise it's not clear which > > unique constraint the FK constraint depends on. Consider > > > > create table a (f1 int unique, f2 int unique); > > > > cr

Re: [SQL] multi column foreign key for implicitly unique columns

Ð ÐÑÑ, 17.08.2004, Ð 16:46, Tom Lane ÐÐÑÐÑ: > I think one reason for this is that otherwise it's not clear which > unique constraint the FK constraint depends on. Consider > > create table a (f1 int unique, f2 int unique); > > create table b (f1 int, f2 int, >

Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

Ð ÐÑÑ, 17.08.2004, Ð 16:26, Jean-Luc Lachance ÐÐÑÐÑ: > This query does not make sense to me. > Why would you create an updatable subquery just to get the highest value? To make sure that the highest value will be the highest value (or then second-highest) after I commit the transaction. See my oth

Re: [SQL] multi column foreign key for implicitly unique columns

Stephan Szabo <[EMAIL PROTECTED]> writes: > No, actually, it's that the SQL92 (at least) spec says explicitly that > there must be a unique constraint across all of the columns specified, not > merely across a subset. > "then the set of column names of that shall be > equal to the set of column n

Re: [SQL] multi column foreign key for implicitly unique columns

On Tue, 17 Aug 2004, Richard Huxton wrote: > Markus Bertheau wrote: > > Hi, > > > > PostgreSQL doesn't allow the creation of a foreign key to a combination > > of fields that has got no dedicated unique key but is unique nonetheless > > because a subset of the combination of fields has a unique c

Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

This query does not make sense to me. Why would you create an updatable subquery just to get the highest value? Maybe you are trying to achieve something other than what the query suggest. You wou care to put in words what you want to do? JLL Markus Bertheau wrote: Hi, why is the following query

Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

Ð ÐÑÑ, 17.08.2004, Ð 16:12, Bruno Wolff III ÐÐÑÐÑ: > > SELECT MAX(position) FROM (SELECT position FROM classes WHERE name = > > 'foo' FOR UPDATE OF classes) AS foo > > > > It's clear which rows should be locked here, I think. > > Even if it was allowed, it probably wouldn't be good enough because

Re: [SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

On Tue, Aug 17, 2004 at 13:07:43 +0200, Markus Bertheau <[EMAIL PROTECTED]> wrote: > Hi, > > why is the following query not allowed: > > SELECT MAX(position) FROM (SELECT position FROM classes WHERE name = > 'foo' FOR UPDATE OF classes) AS foo > > It's clear which rows should be locked here, I

Re: [SQL] Verifying data type

Thanks, but the number may be a float, like '1.23,00', '12.323,00', '12,34' :( The regex works fine if it just an integer... Have some regex to "compare"? Tom Lane wrote: =?ISO-8859-1?Q?Elieser_Le=E3o?= <[EMAIL PROTECTED]> writes: I need to verify if the data in p_valor is

Re: [SQL] Inheriting text[] field

10x I suppose you are right:) Regard Kaloyan Iliev Tom Lane wrote: Kaloyan Iliev Iliev <[EMAIL PROTECTED]> writes: I am useing PostgreSQL 7.2.3. test_libvar=# create table temp_a( test_libvar(# name text[] test_libvar(# ); CREATE test_libvar=# create table temp( name text[] ) inherits (t

Re: [SQL] only last records in subgroups

O kyrios Dino Vliet egrapse stis Aug 17, 2004 : > Hi there, > > I'm having hard times with the following query: > I want to select only the last records from a subgroup > in a table. But because the subgroup contains > different number of records for every id, I don't know > how to specify that.

[SQL] SELECT MAX(c) FROM (SELECT ... FOR UPDATE) AS foo

Hi, why is the following query not allowed: SELECT MAX(position) FROM (SELECT position FROM classes WHERE name = 'foo' FOR UPDATE OF classes) AS foo It's clear which rows should be locked here, I think. Thanks -- Markus Bertheau <[EMAIL PROTECTED]> ---(end of broadca

Re: [SQL] multi column foreign key for implicitly unique columns

Markus Bertheau wrote: Hi, PostgreSQL doesn't allow the creation of a foreign key to a combination of fields that has got no dedicated unique key but is unique nonetheless because a subset of the combination of fields has a unique constraint. [snip example] Is this on purpose? I think the foreign k

Re: [SQL] multi column foreign key for implicitly unique columns

Ð ÐÑÑ, 17.08.2004, Ð 11:39, Oliver Elphick ÐÐÑÐÑ: > What's the point of this? p.name is the primary key and is therefore > unique in p, so your foreign key should simply reference p.name. Having > f.type as a repetition of p.type violates normalisation principles, > since name is completely deri

Re: [SQL] CROSS-TAB query help? I have read it cant be done in on

Theo Galanakis wrote: Thanks Rickard Max may not work as not all the data is numerical. However I will give the contrib/cross-tab a go! It will work as long as you only have one non-null value per grouped field (which you did in your example). | symbol | linktype ---+

Re: [SQL] multi column foreign key for implicitly unique columns

On Tue, 2004-08-17 at 10:25, Markus Bertheau wrote: > Hi, > > PostgreSQL doesn't allow the creation of a foreign key to a combination > of fields that has got no dedicated unique key but is unique nonetheless > because a subset of the combination of fields has a unique constraint. > Example: > >

[SQL] multi column foreign key for implicitly unique columns

Hi, PostgreSQL doesn't allow the creation of a foreign key to a combination of fields that has got no dedicated unique key but is unique nonetheless because a subset of the combination of fields has a unique constraint. Example: CREATE TABLE p ( name TEXT PRIMARY KEY, "type" TEXT );