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, table2 WHERE (table1.key100 != table2.key100); But the query is very slow and I finally just cancel it. Any help is very much appreciated. -Ken Maybe you could use a NOT EXISTS subquery, as in SELECT count(*) from table1 WHERE NOT EXISTS(SELECT count(*) from table2 WHERE table1.key100 =table2.key100) which gives you the number of records in table1 without corresponding records in table2. That kind of query is quite fast, if there exists an index on table2.key100 hth P.Jacquot ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[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 a/b 2 12c I was thinking of something like: SELECT id, fk, CONCAT(str || '/') FROM FOO GROUP BY fk Do we have to use PL/PGSQL for that? Thanks, -- Philippe Lang, Ing. Dipl. EPFL Attik System rte de la Fonderie 2 1700 Fribourg Switzerland http://www.attiksystem.ch Tel: +41 (26) 422 13 75 Fax: +41 (26) 422 13 76 Email:[EMAIL PROTECTED] smime.p7s Description: S/MIME cryptographic signature
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 a/b > 2 12c > > I was thinking of something like: > > SELECT id, fk, CONCAT(str || '/') > FROM FOO > GROUP BY fk > > Do we have to use PL/PGSQL for that? Yes, you need a new aggregate function. http://www.postgresql.org/docs/8.0/interactive/xaggr.html HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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 record insert in that table performs. Is there a similar trade-off in PostgreSQL? The tradeoff is made in every system. The more indexes you have on a table, the more indexes need to be updated when you update/insert/delete rows. So - a unique constraint automatically implies an index in PostgreSQL (it's how it implements its unique checks). Other than that, you should only index those columns where you will want a small number of rows or quick ordering. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
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 are not in opsets, so
I would have step.run be null in some cases, likewise step.opset_num.
Null values mean I can't use these fields in a primary key, so I
propose to use UNIQUE constraints instead.
NULL means "unknown", so a UNIQUE constraint on (run, opset_num, NULL)
probably doesn't do what you want it to.
At its most basic, ('A',1,null) does NOT equal ('A',1,null). It can't do
so - two unknowns can't be said to be the same.
Is sounds like your requirement to use MS Access for ad-hoc queries
means that you will have some users that want to access the database
with MS Access as a "front-end" client tool. If that is the situation,
then you don't need to worry about the structure of the table as MS
Access relies on ODBC for this. You may also want to communicate to the
end users that MS Access is not a client-server tool; in other words,
all of the records are transferred from the server to the client's box
and then the query is executed.
Ouch! A good portion of queries will access my 4M row parameter table
in joins with other tables. It sounds like MS access is not workable.
Thanks for the info.
You can have Access "pass through" queries though which does what you want.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
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 Wed, 2006-02-08 at 15:20 -0800, Bricklen Anderson wrote: > > > [EMAIL PROTECTED] wrote: > > > Greetings, > > > > > > the following is an MySQL statement that I would like to > > > translate to PostgreSQL: > > > > > > Could someone point me to a documentation of a coresponding > > > Systax for an "IF" clause in the a SELECT, > > > or is the some other way to do this > > > > > > select > > > if(spektrum is null,' ','J'), > > > if(s19 is null,' ','J'), > > > if(OhneGrenze is null,' ','J'), > > > from namen; > > > > > > > > > Do I need to create my own function to allow this behaviour! > > > > > > > > > my best regards, > > > > > > Stefan > > > > use CASE > > > > Since I'm not a user of MySQL, and if I'm reading your query correctly: > > try > > select (CASE when spektrum is null then 'J' else spektrum end), > > ... > > > > or if you are just trying to replace nulls, then try COALESCE > > > > ---(end of broadcast)--- > > TIP 1: 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 -- Andrew Sullivan | [EMAIL PROTECTED] It is above all style through which power defers to reason. --J. Robert Oppenheimer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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 UNIQUE NOT NULL in PostgreSQL. The reason that PRIMARY KEY can't be NULL and _has to be_ UNIQUE is that it is the primary means of identifying a given record in a table. If you don't have PRIMARY KEY that is UNIQUE and NOT NULL, how are you going to identify (or reference) individual records? PostgreSQL won't allow you to reference more than one row for what I came to believe are very good reasons. - Rowan ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[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 additional column - recreate all rules Any help is welcomed! Thanks, Andreas Roth ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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. UNIQUE doesn't; you can make a FOREIGN KEY reference to a column that's only UNIQUE, but you'll always have to specify which column. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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 view: - drop depending > rules - drop view - recreate view with additional column - recreate > all rules > > Any help is welcomed! > > Thanks, > Andreas Roth Andreas, I believe that is exactly what you have to do. Kind Regards, Keith ---(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
[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 works fine, but if I insert records where "other_thing" is null, they all go in without complaint. I can insert as many duplicates as I want. I had expected two records, identical in all respects including the null value, to be rejected by the constraint. This is with: PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-20) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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 insert records > where > "other_thing" is null, they all go in without complaint. I can insert as > many duplicates as I want. This is per SQL spec. You're imagining that two nulls are considered equal, which they are not. regards, tom lane ---(end of broadcast)--- TIP 1: 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: [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, which is > never a bad thing in my opinion. In this schema overhaul I'm trying to *eliminate* arbitrary "id" columns like this. They may sometimes improve performance, but certainly obscure the meaning of the data -- naive [read-only] users accessing through excel are confused by this sort of thing. > As far as I know, though, PRIMARY KEY does the same thing as UNIQUE NOT > NULL in PostgreSQL. > > The reason that PRIMARY KEY can't be NULL and _has to be_ UNIQUE is > that it is the primary means of identifying a given record in a table. > If you don't have PRIMARY KEY that is UNIQUE and NOT NULL, how are you > going to identify (or reference) individual records? PostgreSQL won't > allow you to reference more than one row for what I came to believe are > very good reasons. Actually, I don't really see a problem here. E.g. when I want data from a step that has no parent run and no parent opset, I could say: select * from steps where run is null and opset is null and step='mystep'; I don't understand what you mean by "PostgreSQL won't allow you to reference more than one row". If the above query returns 10 rows, what's wrong with that? Nothing *requires* a PRIMARY KEY at all, anyway. -- puzzled... George -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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 PROTECTED] so that your message can get through to the mailing list cleanly
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 only distinct values such that they contain > only the substring I need. If I give 10 as substring, then it should return > only 100101 or 100102 but not both i.e if the last two characters are not > same it should not return both of them. It should return only values starting > with 10 the middle two values should be distinct and the last two characters > may be anything. You can probably use the Postgres extension DISTINCT ON to do what you want. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[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 PostgreSQL - sql forum at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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. You can't define the format in the db, but you can define the output-format with to_char(date, 'DD-MM-'); HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(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
