Re: [SQL] Non Matching Records in Two Tables

Ken Hill wrote: I need some help with a bit of SQL. I have two tables. I want to find records in one table that don't match records in another table based on a common column in the two tables. Both tables have a column named 'key100'. I was trying something like: SELECT count(*) FROM table1,

[SQL] Concatenate strings using GROUP BY

Hi, Let's say we have a query like: SELECT id, fk, str FROM foo ... which returns: idfkstr --- 1 1 a 2 1 b 3 2 c Is there a pure SQL way of contactenating str values into a single string, and get instead: idfkstr --- 1 1

Re: [SQL] Concatenate strings using GROUP BY

am 09.02.2006, um 10:35:03 +0100 mailte Philippe Lang folgendes: > idfkstr > --- > 1 1 a > 2 1 b > 3 2 c > > Is there a pure SQL way of contactenating str values into a single string, > and get instead: > > idfkstr > --- > 1 1

Re: [SQL] Column Index vs Record Insert Trade-off?

Ken Hill wrote: Is there a performance trade-off between column indexes and record inserts? I know that in MS Access there is such a trade-off. This being indexes make SQL queries perform faster at the cost of record insert speed. Put another way, the more column indexes in a table, the slower a

Re: [SQL] unique constraint instead of primary key? what

george young wrote: On Wed, 08 Feb 2006 18:34:22 -0800 Ken Hill <[EMAIL PROTECTED]> threw this fish to the penguins: On Wed, 2006-02-08 at 21:04 -0500, george young wrote: So the 'steps' table is logically indexed by (run, opset_num, step_num). But some opsets are not in runs, and some steps

Re: [SQL] Syntax for "IF" clause in SELECT

What you want is the SQL-standard CASE statement. A On Wed, Feb 08, 2006 at 06:06:10PM -0800, Ken Hill wrote: > This has been something I've been trying do so that I can do some column > comparisons as part of "data-cleaning" work. I'll let you know if this > helps me accomplish my task! > > On

Re: [SQL] Non Matching Records in Two Tables

You can use an EXCEPT clause. ---(end of broadcast)--- TIP 6: explain analyze is your friend

Re: [SQL] unique constraint instead of primary key? what disadvantage(ODBC usage)?

If my tables have one or more UNIQUE constraints/indices, I still add a "id SERIAL PRIMARY KEY" field to most of my tables. This makes referencing easier and faster. It also improves consistency, which is never a bad thing in my opinion. As far as I know, though, PRIMARY KEY does the same thing as

[SQL] Change definition of a view

Hello, one question: Is it possible to add or remove a column from a view without drop the view and recreate it? If one or more rules depend on a view, it's very hard to extend a view. I use the following procedure to extend a view: - drop depending rules - drop view - recreate view with additi

Re: [SQL] unique constraint instead of primary key? what disadvantage(ODBC usage)?

"BigSmoke" <[EMAIL PROTECTED]> writes: > As far as I know, though, PRIMARY KEY does the same thing as UNIQUE NOT > NULL in PostgreSQL. They are 99.9% the same --- the *only* difference AFAIR is that PRIMARY KEY establishes a default column-to-reference for FOREIGN KEY references to the table. UNI

Re: [SQL] Change definition of a view

On Thu, 9 Feb 2006 18:11:24 +0100, Andreas Roth wrote > Hello, > > one question: Is it possible to add or remove a column from a view > without drop the view and recreate it? > > If one or more rules depend on a view, it's very hard to extend a > view. I use the following procedure to extend a

[SQL] UNIQUE, btree index allows duplicate records, if some fields are null

This threw me for a loop. Is this my fault, or a problem in postgres? I have a table with the following: "eg_vehicle_event_pkey" PRIMARY KEY, btree (event_id) "no_duplicate_events" UNIQUE, btree (thing, other_thing, "timestamp", number, other_number) The "no_duplicate_events" constraint

Re: [SQL] UNIQUE, btree index allows duplicate records, if some fields are null

Bryce Nesbitt <[EMAIL PROTECTED]> writes: > I have a table with the following: > "eg_vehicle_event_pkey" PRIMARY KEY, btree (event_id) > "no_duplicate_events" UNIQUE, btree (thing, other_thing, > "timestamp", number, other_number) > The "no_duplicate_events" constraint works fine, but if I

Re: [SQL] unique constraint instead of primary key? what

On 9 Feb 2006 08:22:59 -0800 "BigSmoke" <[EMAIL PROTECTED]> threw this fish to the penguins: > If my tables have one or more UNIQUE constraints/indices, I still add a > "id SERIAL PRIMARY KEY" field to most of my tables. This makes > referencing easier and faster. It also improves consistency, whi

Re: [SQL] unique constraint instead of primary key? what

I mean that you can't easily base a foreign key constraint on a field that is not NOT NULL UNIQUE. - Rowan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PRO

Re: [SQL] query

On Tue, Feb 07, 2006 at 01:45:50 -0800, "superboy143 (sent by Nabble.com)" <[EMAIL PROTECTED]> wrote: > > I have a table in which I have a field with format like 100101. It has many > values like 100101, 100102, 100103, 100201, 100202, 100301. I have to write a > query such that I have to get

[SQL] date

Hello, How can I write an sql query in postgresql so that I can insert a date into a table in the format DD-MM-, and when I select the date from the table I should get the date in the same format. -- View this message in context: http://www.nabble.com/date-t1097526.html#a2865031 Sent from the

Re: [SQL] date

am 09.02.2006, um 22:18:09 -0800 mailte superboy143 (sent by Nabble.com) folgendes: > > Hello, > > How can I write an sql query in postgresql so that I can insert a date into > a table in the format DD-MM-, and when I select the date from the table > I should get the date in the same format