Re: [SQL] Error from trigger
On Thursday 08 December 2005 05:11, Tom Lane wrote: >Just starting a fresh session should make the problem go away, or if >that's not practical update the function definition using ALTER > FUNCTION or CREATE OR REPLACE FUNCTION. (You don't need to actually > *change* anything about the function, just issue a command that could > change it.) > >If that doesn't make the error go away then we need to look more >closely at what's causing it. I did an "/etc/init.d/postgresql restart" and the problem went away. Thanks again. -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(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] select count of distinct rows
Hi, I would like to select the count of distinct rows in a table. SELECT COUNT(DISTINCT *) FROM mytable; This does not work. How can I do it with Postgres? Thanks, Otto
Re: [SQL] select count of distinct rows
On 12/10/05, Havasvölgyi Ottó <[EMAIL PROTECTED]> wrote: > Hi, > > I would like to select the count of distinct rows in a table. > > SELECT COUNT(DISTINCT *) FROM mytable; > are really all the fields distincts? the table doesn't have a pk? > This does not work. How can I do it with Postgres? > > Thanks, > Otto > -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] select count of distinct rows
Hi, Yes, I need to compare all fields. Well, in this case it does not. Is this impossible? Thanks, Otto - Original Message - From: "Jaime Casanova" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Sunday, December 11, 2005 1:57 AM Subject: Re: [SQL] select count of distinct rows On 12/10/05, Havasvölgyi Ottó <[EMAIL PROTECTED]> wrote: Hi, I would like to select the count of distinct rows in a table. SELECT COUNT(DISTINCT *) FROM mytable; are really all the fields distincts? the table doesn't have a pk? This does not work. How can I do it with Postgres? Thanks, Otto -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] select count of distinct rows
Well this should work but I tried it and it didn't. SELECT DISTINCT COUNT(*) FROM mytable; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Havasvölgyi Ottó Sent: Saturday, December 10, 2005 7:07 PM To: Jaime Casanova Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] select count of distinct rows Hi, Yes, I need to compare all fields. Well, in this case it does not. Is this impossible? Thanks, Otto - Original Message - From: "Jaime Casanova" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Sunday, December 11, 2005 1:57 AM Subject: Re: [SQL] select count of distinct rows On 12/10/05, Havasvölgyi Ottó <[EMAIL PROTECTED]> wrote: > Hi, > > I would like to select the count of distinct rows in a table. > > SELECT COUNT(DISTINCT *) FROM mytable; > are really all the fields distincts? the table doesn't have a pk? > This does not work. How can I do it with Postgres? > > Thanks, > Otto > -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] select count of distinct rows
On 12/10/05, Foster, Stephen <[EMAIL PROTECTED]> wrote: > Well this should work but I tried it and it didn't. > > SELECT DISTINCT COUNT(*) FROM mytable; > No, it shouldn't work... actually is a non-sense, count will return just one value so there is nothing to be distinct with... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] select count of distinct rows
On 12/10/05, Havasvölgyi Ottó <[EMAIL PROTECTED]> wrote: > Hi, > > I would like to select the count of distinct rows in a table. > > SELECT COUNT(DISTINCT *) FROM mytable; > > This does not work. How can I do it with Postgres? > > Thanks, > Otto > I guess what you need is to know how many times a single row is duplicated so i think what you need is something like this: SELECT fld1, COUNT(DISTINCT fld1) FROM (SELECT ROW(*) as fld1 FROM mytable) AS foo GROUP BY fld1; -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] select count of distinct rows
Hi, This works for me but the result is not that what is expected. I returns with the number of rows in the table, and the distinct has no effect because there is only one row in the result. Best Regards, Otto - Original Message - From: "Foster, Stephen" <[EMAIL PROTECTED]> To: "'Havasvölgyi Ottó'" <[EMAIL PROTECTED]>; "'Jaime Casanova'" <[EMAIL PROTECTED]> Cc: Sent: Sunday, December 11, 2005 2:12 AM Subject: Re: [SQL] select count of distinct rows Well this should work but I tried it and it didn't. SELECT DISTINCT COUNT(*) FROM mytable; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Havasvölgyi Ottó Sent: Saturday, December 10, 2005 7:07 PM To: Jaime Casanova Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] select count of distinct rows Hi, Yes, I need to compare all fields. Well, in this case it does not. Is this impossible? Thanks, Otto - Original Message - From: "Jaime Casanova" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Sunday, December 11, 2005 1:57 AM Subject: Re: [SQL] select count of distinct rows On 12/10/05, Havasvölgyi Ottó <[EMAIL PROTECTED]> wrote: Hi, I would like to select the count of distinct rows in a table. SELECT COUNT(DISTINCT *) FROM mytable; are really all the fields distincts? the table doesn't have a pk? This does not work. How can I do it with Postgres? Thanks, Otto -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] select count of distinct rows
At 07:53 PM 12/10/05, Havasvölgyi Ottó wrote: I would like to select the count of distinct rows in a table. SELECT COUNT(DISTINCT *) FROM mytable; This does not work. How can I do it with Postgres? select count(*) from (select distinct * from mytable) as x; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] select count of distinct rows
Well, I find the problem on my end. I was working with a new database that I forgot to filler yet. Yes, it works. Jaime, I think what he is trying to do is get the record count. I would agree that using that statement is a bit much. But using SELECT COUNT(*) FROM mytable; would give the same thing and should run faster. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jaime Casanova Sent: Saturday, December 10, 2005 7:13 PM To: Foster, Stephen Cc: Havasvölgyi Ottó; pgsql-sql@postgresql.org Subject: Re: [SQL] select count of distinct rows On 12/10/05, Foster, Stephen <[EMAIL PROTECTED]> wrote: > Well this should work but I tried it and it didn't. > > SELECT DISTINCT COUNT(*) FROM mytable; > No, it shouldn't work... actually is a non-sense, count will return just one value so there is nothing to be distinct with... -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] select count of distinct rows
Yes, almost. I need the list of all different rows. It's syntax error at the *. ROW(*) ^ Otto - Original Message - From: "Jaime Casanova" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Sunday, December 11, 2005 2:16 AM Subject: Re: select count of distinct rows On 12/10/05, Havasvölgyi Ottó <[EMAIL PROTECTED]> wrote: Hi, I would like to select the count of distinct rows in a table. SELECT COUNT(DISTINCT *) FROM mytable; This does not work. How can I do it with Postgres? Thanks, Otto I guess what you need is to know how many times a single row is duplicated so i think what you need is something like this: SELECT fld1, COUNT(DISTINCT fld1) FROM (SELECT ROW(*) as fld1 FROM mytable) AS foo GROUP BY fld1; -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] select count of distinct rows
Oh, that's it. Thank you all very much. Otto - Original Message - From: "Frank Bax" <[EMAIL PROTECTED]> To: Sent: Sunday, December 11, 2005 2:23 AM Subject: Re: [SQL] select count of distinct rows At 07:53 PM 12/10/05, Havasvölgyi Ottó wrote: I would like to select the count of distinct rows in a table. SELECT COUNT(DISTINCT *) FROM mytable; This does not work. How can I do it with Postgres? select count(*) from (select distinct * from mytable) as x; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[SQL] Looking for information on PostgreSQL Stored Procedures
This could be an old conversation for most. I've used PostgreSQL for a while but I haven't fully use the Procedure/Functions to it fullest until now. I need to migrate a MS-SQL 2000 database to PostgreSQL. I've read as much as I could find but I seem to be missing something. I did see last week something on PLPGSQL and read through that. But there has to be something out there that goes in depth on the SQL/Function command set(Speaking of functions/procedures). The biggest hole that I have; seems to be on Cursors; define and opening. I think the fetching and closing is pretty straight forward. But the Define and opening is causing some grief. Either I'm making to far too hard or I'm really have missing something silly. Simple example in MS-2000: CREATE PROCEDURE dbo.sp_RemoveDups AS SET NOCOUNT ON DECLARE @err int, @LastName varchar(255), @Name varchar(255), @id bigint, @LineNum bigint DECLARE NewListCursor CURSOR LOCAL FAST_FORWARD FOR SELECT Name, id FROMMailingList ORDER BY id OPEN NewListCursor SELECT @LineNum = 0 SELECT @LastName = "" FETCH NEXT FROM NewListCursor INTO @Name, @id WHILE (@@FETCH_STATUS = 0) BEGIN SELECT @LineNum = @LineNum + 1 IF @LastName = @Name DELETE FROM MailingList WHERE id = @id SELECT @LastName = @Name FETCH NEXT FROM NewListCursor INTO @LastName, @id END CLOSE NewListCursor DEALLOCATE NewListCursor RETURN (0) GO This is an example of the simple stored procedures like the ones I'm trying to migrate. PLPGSQL is ok but I thought it would run better in SQL. Just not C, Perl or TK/TCL. Those are not being used with this application and no plans to use them in the future. Thanks for any help; Lee Foster -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date: 12/9/2005 ---(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] [GENERAL] Looking for information on PostgreSQL Stored Procedures
On Sat, Dec 10, 2005 at 09:02:39PM -0600, Foster, Stephen wrote: > I did see last week something on PLPGSQL and read through that. But > there has to be something out there that goes in depth on the > SQL/Function command set(Speaking of functions/procedures). The standard functions are described in the "Functions and Operators" chapter of the documentation. Here's a link to the latest version, but use the documentation for the version you're running: http://www.postgresql.org/docs/8.1/interactive/functions.html SQL functions are documented in "Query Language (SQL) Functions": http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html > The biggest hole that I have; seems to be on Cursors; define and > opening. I think the fetching and closing is pretty straight forward. > But the Define and opening is causing some grief. Either I'm making to > far too hard or I'm really have missing something silly. What problems are you having? Without seeing what you're doing in PostgreSQL it's difficult to say what's wrong. In PL/pgSQL you can loop through query results without explicitly using a cursor; see "Looping Through Query Results": http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING [snip example] > This is an example of the simple stored procedures like the ones I'm > trying to migrate. PLPGSQL is ok but I thought it would run better in > SQL. SQL doesn't have control structures, so if you need conditionals or loops then use a procedural language like PL/pgSQL or implement the logic on the client side. For the example you posted, the following query should have the same effect (remove records with duplicate names, if I'm reading it right): DELETE FROM mailinglist WHERE id NOT IN ( SELECT DISTINCT ON (name) id FROM mailinglist ORDER BY name, id ); I don't know how well this would perform on large data sets, especially in older versions of PostgreSQL, but you could try it. I'd recommend trying it first on a test table or in a transaction that you can roll back in case it doesn't do what you want. See the SELECT documentation for a description of the non-standard DISTINCT ON clause that the above query uses: http://www.postgresql.org/docs/8.1/interactive/sql-select.html -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
