Re: [SQL] Foreign keys and fixed values...

Hi- On Jul 29, 2009, at 4:16 PM, Kjell Rune Skaaraas wrote: Hello, I'm having a problem using foreign keys, or at least in a way i find good. Basicly I have a table I wish to use as a foreign key with lookup "id" and "code", like: 1,a 1,b 1,c 2,a 2,d I have of course an UNIQUE on (id,c

Re: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL

On Jul 28, 2009, at 5:10 PM, nha wrote: Hello, Le 28/07/09 14:25, Daryl Richter a écrit : On Jul 28, 2009, at 5:58 AM, Gau, Hans-Jürgen wrote: hello list, i have some problems with an sql-statement which runs on oracle but not on postgresql (i want update only if result of SELECT is not

Re: [SQL] sql-porting-problem oracle to postgresql with UPDATE/IS NOT NULL

On Jul 28, 2009, at 5:58 AM, Gau, Hans-Jürgen wrote: hello list, i have some problems with an sql-statement which runs on oracle but not on postgresql (i want update only if result of SELECT is not empty, the SELECT-queries are identical): UPDATE table1 t1 SET (t1.id) =

Re: [SQL] i have table

On 10/4/06 12:20 PM, "Aaron Bono" <[EMAIL PROTECTED]> wrote: > On 10/4/06, Erik Jones <[EMAIL PROTECTED]> wrote: >> >> Aaron Bono wrote: >>> On 10/4/06, *Erik Jones* <[EMAIL PROTECTED] > >>> wrote: >>> >>> There is one non-SQL related reason that I like to be able t

Re: [SQL] i have table

On 10/3/06 6:47 AM, "Penchalaiah P." <[EMAIL PROTECTED]> wrote: > > Hi ... > > I have one table with 12 fields.. > > > now I want to add one more field in this table.. but that field has to > come next to cda_no.. I mean as a 3rd field.. If I am adding that field > it is coming last field ...

Re: [SQL] nested select within a DISCTINCT block

On 9/14/06 1:13 PM, "zqzuk" <[EMAIL PROTECTED]> wrote: > > Hi, here i have a problem with this task... > > I have a table "cancellation" which stores cancelled bookings and details of > charges etc > and a table "bookings" which stores details of bookings, for example: > > cancellation(cancell

Re: [SQL] Joint a table back on itself?

On 9/12/06 11:55 AM, "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hi all, > > I have a union query that generates a table with > directional measurments (a=azimuth, i=depth) at > various depths (md) down a hole. The results look > like: > hole_id | md | a| i|e >

Re: [SQL] Substitute a Character

On 9/6/06 12:53 PM, "Judith" <[EMAIL PROTECTED]> wrote: > Hello everybody!! > > I have a field type text with folios like this: A98526 > > but I want to change de A for a 0 like this: 098526, exists a way to > do this in a query??? select translate( 'A98526', 'A', '0' ); transla

Re: [SQL] how do I check for lower case

On 8/10/06 4:32 PM, "Juliann Meyer" <[EMAIL PROTECTED]> wrote: > I have a table with a column, lets call it identifier, that is defined > as varchar(8) that should never contain lower case letters. Its a large > table. Is there a way to query the table to see if any values in this > column are l

Re: [SQL] Advanced Query

On Jun 6, 2006, at 12:32 PM, Andrew Sullivan wrote: On Tue, Jun 06, 2006 at 09:20:13AM -0700, codeWarrior wrote: I would hope that your choice to use postgreSQL is because it is superior technology that scales well financially... not because you get a warm fuzzy from all your friends on the

Re: [SQL] bug with if ... then ... clause in views

On Jan 18, 2006, at 4:18 AM, Emil Rachovsky wrote: While trying to create some views I stumbled on some problem with using the if-then clause. Here is a simple example : CREATE OR REPLACE VIEW public.SomeView as select d.id, if (true) then d.DocNumber endif from public.Z_Documents as d; I

Re: [SQL] constraint and ordered value

On Dec 29, 2005, at 2:16 AM, Bruno Wolff III wrote: On Wed, Dec 28, 2005 at 00:52:18 +0700, David Garamond <[EMAIL PROTECTED]> wrote: Is it possible to use only CHECK constraint (and not triggers) to completely enforce ordered value of a column (colx) in a table? By that I mean: 1. Rows

Re: Fwd: Re: [SQL] Referencing

[EMAIL PROTECTED] wrote: Quoting Daryl Richter <[EMAIL PROTECTED]>: [EMAIL PROTECTED] wrote: > Quoting Daryl Richter <[EMAIL PROTECTED]>: >> It's hard to say without knowing more precisely what you are trying to >> model, but I think this push you in the

Re: Fwd: Re: [SQL] Referencing

[EMAIL PROTECTED] wrote: > Quoting Daryl Richter <[EMAIL PROTECTED]>: > >> It's hard to say without knowing more precisely what you are trying to >> model, but I think this push you in the right direction: >> [snipped old schema] > > Okay, but refer

Re: Fwd: Re: [SQL] Referencing

: create view buy as select a.id, b.id as "input_id", a.product_id, a.value from activity a join inputb on b.activity_id = a.id; The SELL view is left as an exercise for the reader. -- Daryl Richter Platform Author & Director of Techn

Re: [SQL] automatic update or insert

e done directly in SQL? Brgds Robert ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Daryl Richter Platform Author & Director of Technology (( Brandywi

Re: [SQL] Merging lines with NULLs (with example data)

) from test t1 where proto='TCP' and to_date(t1.tstamp,'-MM-DD') = to_date(test.tstamp,'-MM-DD') ) as count_tcp from test where tstamp >= (now() - interval '6 days' ) and dst_port = 2290 order by gday; Harald'

Re: [SQL] Design problem : using the same primary keys for inherited

-- 1 name char(20) not null-- "Actor" ); create table person( id serial primary key, -- 1 namechar(20) not null,-- "David" role_id int not null references role -- 1 ); David -- Daryl Richter Director of Technol

Re: [SQL] SEVEN cross joins?!?!?

Frank Bax wrote: At 09:00 AM 10/12/05, Daryl Richter wrote: Richard Huxton wrote: Frank Bax wrote: [snip] Richard, you've summed it up nicely. Splitting locations into subsets (like 2,2,3) doesn't work because it is possible that low values in one location can be offs

Re: [SQL] SEVEN cross joins?!?!?

lp? -- Richard Huxton Archonet Ltd -------(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Daryl Richter (daryl (at)(brandywine dot com)) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] changing a column's position in table, how do you do that

Ferindo Middleton Jr wrote: Is there a way to change the position attribute of a column in a table? I have data that I need to import into various tables in my db on a consistent basis... I usually us e the COPY ... FROM query but I can't control the -order- of the fields my client dumps the da

Re: [SQL] how to do 'deep queries'?

Anthony Molinaro wrote: > Daryl, > > >>Whether you feel that is unnecessary or not, it *is* the ANSI Standard > > >>and is thus, by definition, "how queries should be written." > > > I disagree 100%. Oracle and db2 introduced window functions years > before > Ansi added them. Should we not have u

Re: [SQL] Updating cidr column with network operator

[EMAIL PROTECTED] wrote: Am 27.09.2005 um 16:02 schrieb Daryl Richter: An attribute is redundant if it repeats a fact that can be learned without it. If one table contains IP addresses and another contains networks, then you can associate IP addresses and networks with a join of the

Re: [SQL] Updating cidr column with network operator

Michael Fuhr wrote: > On Mon, Sep 26, 2005 at 12:34:59PM +0200, Axel Rau wrote: > >>Am 26.09.2005 um 02:05 schrieb Michael Fuhr: >> >>>On Fri, Sep 23, 2005 at 09:19:25PM +0200, Axel Rau wrote: >>> I'm sure this would be the cleanest solution but remember networks change. >>> >>>Yes, which

Re: [SQL] how to do 'deep queries'?

Anthony Molinaro wrote: that query is 100% correct. it's just an equijoin (a type of inner join) between 3 tables. the syntax you show is how queries should be written and is more representative of what a joins between relations really are: Cartesian products with filters applied the ansi

Re: [SQL] Updating cidr column with network operator

Axel Rau wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Am 22.09.2005 um 22:26 schrieb Daryl Richter: Axel Rau wrote: Thank you for responding, Daryl, Am 22.09.2005 um 16:45 schrieb Daryl Richter: Axel Rau wrote: Hi SQLers, I have a fk from address to network and try to update

Re: [SQL] Updating cidr column with network operator

Axel Rau wrote: Thank you for responding, Daryl, Am 22.09.2005 um 16:45 schrieb Daryl Richter: Axel Rau wrote: Hi SQLers, I have a fk from address to network and try to update the foreign key column to point at the network, "it belongs to": CREATE TABLE network ( id

Re: [SQL] Updating cidr column with network operator

Axel Rau wrote: Hi SQLers, I have a fk from address to network and try to update the foreign key column to point at the network, "it belongs to": CREATE TABLE network ( id cidr PRIMARY KEY , -- 'PK, ,IPv4/6 Network address' ) CREATE TABLE address ( id inet PRIMAR

Re: [SQL] showing multiple REFERENCE details of id fields in single

Ferindo Middleton Jr wrote: Thanks Daryl. The query you wrote works perfectly for the results I was trying to get at. I modified it a little because the real thing I needed to be able to see is the course_title and not the the id of the courses: SELECT a.course_title AS class_title, c.co

Re: [SQL] showing multiple REFERENCE details of id fields in single

Ferindo Middleton Jr wrote: I have a table which has two id fields which REFERENCE data back at another table. It's setup like this: class_prerequisite_bindings(id SERIAL, class_id INTEGER REFERENCES classes(id), prerequisiteINTEGER REFERENCES classes(id)) The classes table is li

Re: [SQL] Need help with 'unique parents' constraint

Leif B. Kristensen wrote: On Sunday 11 September 2005 16:04, Greg Sabino Mullane wrote: Not just old-fashioned, it's the biological law! (among homo sapiens anyway). I'd approach this with a trigger, as you can do complex checks and get back nice customized error messages. A sample script foll

Re: [SQL] insert only if conditions are met?

cord for EMPLOYEE A to TABLE A IF the sum of the hours worked by EMPLOYEE A on TABLE A is not equal to N Is this possible? Sure, given a suitable schema It is not clear to me, if the hours worked are to be found in the same table you want to insert into, or not. gnari -- Daryl

Re: [SQL] ORDER records based on parameters in IN clause

st cleanly ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Daryl Richter Platform Author & Director of Technology v: 610.361.1000 x202 (( Brandywine Asset Management ) (