Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

2010-03-18 Thread silly sad 

On 03/17/10 17:52, Ignacio Balcarce wrote:

CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID

  @NEWID VARCHAR(20) OUTPUT

AS

SET @NEWID = (

SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')

+ CAST(REPLICATE(0,8-LEN (ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS

INTEGER),0) + 1)) AS VARCHAR)

+ CAST(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1 AS

VARCHAR)

FROM THUBAN_SEQ

WHERE SUBSTRING(SEQ_ID,1,8)=

REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')

)

INSERT INTO THUBAN_SEQ VALUES (@NEWID)

SELECT @NEWID AS ITEM_ID;

GO



At a first glance
it looks like an

INSERT INTO thuban_seq(seq_id)
 VALUES (your_strange_string_processing( now() ))
  RETURNING seq_id;

But i couldn't interpret your extremely strange string processing with 
dates.

Please, FIRST OF ALL, get rid of this unnecessary brainfuck,
use postgres date-time arithmetic and clarify the idea of this routine.


--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

2010-03-18 Thread silly sad 

On 03/17/10 17:52, Ignacio Balcarce wrote:

-- IF EXISTS A ROW IN THE TABLE STARTING WITH THE CURRENT_DATE


Sorry, your field is not an atom => your database does not met a FIRST 
normal form.


it needs normalization urgently.

--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Simple aggregate query brain fart

2010-03-18 Thread Mark Fenbers 

I want to do:

SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;

But this doesn't work because Pg won't allow aggregate functions in a 
where clause.  So I modified it to:


SELECT id, count(*) AS cnt FROM mytable WHERE cnt > 2 GROUP BY id;

But Pg still complains (that column cnt does not exist).  When using an 
GROUP/ORDER BY clause, I can refer to a column number (e.g., GROUP BY 1) 
instead of a column name, but how can I refer to my unnamed second 
column in my where clause?


Mark

<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Simple aggregate query brain fart

2010-03-18 Thread Plugge, Joe R. 
Mark, 

Change your query to this:

SELECT id, count(*) FROM mytable GROUP BY id  HAVING count(*) > 2;

-Original Message-
From: [email protected] [mailto:[email protected]] On 
Behalf Of Mark Fenbers
Sent: Thursday, March 18, 2010 10:07 AM
To: [email protected]
Subject: [SQL] Simple aggregate query brain fart

I want to do:

SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;

But this doesn't work because Pg won't allow aggregate functions in a where 
clause.  So I modified it to:

SELECT id, count(*) AS cnt FROM mytable WHERE cnt > 2 GROUP BY id;

But Pg still complains (that column cnt does not exist).  When using an 
GROUP/ORDER BY clause, I can refer to a column number (e.g., GROUP BY 1) 
instead of a column name, but how can I refer to my unnamed second column in my 
where clause?

Mark


-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Simple aggregate query brain fart

2010-03-18 Thread Tom Lane 
Mark Fenbers  writes:
> I want to do:
> SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;

> But this doesn't work because Pg won't allow aggregate functions in a 
> where clause.

Use HAVING, not WHERE.  The way you are trying to write the query is
meaningless because WHERE filters rows before grouping/aggregation.
HAVING filters afterwards, which is when it makes sense to put a
condition on count(*).

regards, tom lane

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Simple aggregate query brain fart

2010-03-18 Thread Mark Fenbers 




Thanks, Joe and Tom.  You cleared the webs out of my brain.  I used
HAVING before, but not lately and I got rusty.
Mark

Tom Lane wrote:

  Mark Fenbers  writes:
  
  
I want to do:
SELECT id, count(*) FROM mytable WHERE count(*) > 2 GROUP BY id;

  
  
  
  
But this doesn't work because Pg won't allow aggregate functions in a 
where clause.

  
  
Use HAVING, not WHERE.  The way you are trying to write the query is
meaningless because WHERE filters rows before grouping/aggregation.
HAVING filters afterwards, which is when it makes sense to put a
condition on count(*).

			regards, tom lane

  



<>
-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

2010-03-18 Thread Justin Graf 
On 3/17/2010 9:52 AM, Ignacio Balcarce wrote:
>
> Hi all,
>
> I am facing a problem trying to convert from MSSQL procedure to 
> PostgreSQL function.
>
> CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID
>
>  @NEWID VARCHAR(20) OUTPUT
>
> AS
>
> SET @NEWID = (
>
> SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')
>
> + CAST(REPLICATE(0,8-LEN (ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS
>
> INTEGER),0) + 1)) AS VARCHAR)
>
> + CAST(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1 AS
>
> VARCHAR)
>
> FROM THUBAN_SEQ
>
> WHERE SUBSTRING(SEQ_ID,1,8)=
>
> REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')
>
> )
>
> INSERT INTO THUBAN_SEQ VALUES (@NEWID)
>
> SELECT @NEWID AS ITEM_ID;
>
> GO
>


I surprised this works in MSSQL

CREATE SEQUENCE THUBAN_SEQ
   INCREMENT 1
   MINVALUE 1
   MAXVALUE 9223372036854775807
   START 1
   CACHE 1;


Now for the function to generate the ID with the date leading

CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()
RETURNS VARCHAR

AS $$

BEGIN

--now we get the next value from the thuban_seq and add the date to the  
front.

return  to_char( current_timestamp,   'MMDD')::varchar || 
nextval('THUBAN_SEQ')::varchar

RETURN NEWID;

END;

$$ LANGUAGE plpgsql;

If this is not what your after you need to give more information what 
you want to accomplish





All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.

Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

2010-03-18 Thread Justin Graf 
On 3/18/2010 12:53 PM, Ignacio Balcarce wrote:
>
> Justin,
>
> Thanks in advance for your email. I forgot to tell than everyday IDs 
> must start from 0. So… sequence id would look like: MMDD 0001, 
> MMDD 0002, etc.
>
> Is there any way to make this sequence start from 0 every day?
>
> Thanks & Regards,
>
> Ignacio
>
> -
>
>
>
--we need to create a table so we keep track sequence number and when to 
reset the count

create table sequ_id ( id_number int, sequ_name char(25), date_lastrun );

--insert a record ;
insert into sequ_id  values (1, 'thuban_seq', current_date);


  Now for the function to generate the ID with the date leading

  CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()
  RETURNS VARCHAR

  AS $$

  BEGIN
--now update the sequ_id table so we know the value we get makes sense,
Update sequ_id set id_number = 1 where sequ_name= 'thuban_seq' and 
date_lastrun <> current_date;

  --now we get the next value from the thuban_seq and add the date to 
the  front.

  return  to_char( current_date,   'MMDD')::varchar || ' ' || 
(Select lpad( id_number::char, 7, '0' )::varchar from sequ_id where 
sequ_name= 'thuban_seq' and date_lastrun)

Update sequ_id set id_number = (id_number + 1) where sequ_name= 
'thuban_seq';


  END;
$$ LANGUAGE plpgsql;

this will do what you want.

now i  have NOT  tested this but should get you closer, inside of the 
god awful code from before.




All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.

Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

2010-03-18 Thread Justin Graf 
OOPS did not mean to click send

On 3/18/2010 12:53 PM, Ignacio Balcarce wrote:
>
> Justin,
>
> Thanks in advance for your email. I forgot to tell than everyday IDs 
> must start from 0. So… sequence id would look like: MMDD 0001, 
> MMDD 0002, etc.
>
> Is there any way to make this sequence start from 0 every day?
>
> Thanks & Regards,
>
> Ignacio
>
> -
>
>
>
--we need to create a table so we keep track sequence number and when to 
reset the count

create table sequ_id ( id_number int, sequ_name char(25), date_lastrun 
date);

--insert a record ;
insert into sequ_id  values (1, 'thuban_seq', current_date);


--- Now for the function to generate the ID with the date leading

  CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()
  RETURNS VARCHAR

  AS $$
declare creturn varchar ;

  BEGIN
--now update the sequ_id table so we know the value we get makes sense,
Update sequ_id set id_number = 1 where sequ_name= 'thuban_seq' and 
date_lastrun <> current_date;

  --now we get the next build the ID go to the table get the current 
value add some zeros in front and add the date to the  front.

creturn = to_char( current_date,   'MMDD')::varchar || ' ' || 
(Select lpad( id_number::char, 7, '0' )::varchar from sequ_id where 
sequ_name= 'thuban_seq' )

--update the sequence table
Update sequ_id set id_number = (id_number + 1) where sequ_name= 
'thuban_seq';
--return the value
return creturn ;
  END;
$$ LANGUAGE plpgsql;

this will do what you want.

now i  have NOT  tested this but should get you closer, inside of the 
god awful code from before.




All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.

Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID using actual date

2010-03-18 Thread Ignacio Balcarce 
Justin,

 

Thanks in advance for your email. I forgot to tell than everyday IDs must start 
from 0. So… sequence id would look like: MMDD 0001, MMDD 0002, 
etc.

 

Is there any way to make this sequence start from 0 every day?

 

Thanks & Regards,

 

Ignacio

 

De: Justin Graf [mailto:[email protected]] 
Enviado el: Jueves, 18 de Marzo de 2010 02:02 p.m.
Para: Ignacio Balcarce
CC: [email protected]
Asunto: Re: [SQL] MSSQL to PostgreSQL - Issue trying to generate unique ID 
using actual date

 

On 3/17/2010 9:52 AM, Ignacio Balcarce wrote: 

Hi all,

 

I am facing a problem trying to convert from MSSQL procedure to PostgreSQL 
function. 

 

CREATE PROCEDURE dbo.THUBAN_SP_GENERATEID

 

 @NEWID VARCHAR(20)  OUTPUT

AS

 

 

 

 

SET @NEWID = (

SELECT REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 
),1,10),'-','')

+ CAST(REPLICATE(0,8-LEN 
(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS

INTEGER),0) + 1)) AS VARCHAR)

+ 
CAST(ISNULL(CAST(SUBSTRING(MAX(SEQ_ID),9,8) AS INTEGER),0) + 1  AS

VARCHAR)

FROM THUBAN_SEQ

WHERE SUBSTRING(SEQ_ID,1,8)=

REPLACE(SUBSTRING(CONVERT(CHAR(10),GETDATE(),20 ),1,10),'-','')

)

 

 

INSERT INTO THUBAN_SEQ VALUES (@NEWID)

 

SELECT @NEWID AS ITEM_ID;

GO

 



I surprised this works in MSSQL

CREATE SEQUENCE THUBAN_SEQ
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;


Now for the function to generate the ID with the date leading 

CREATE OR REPLACE FUNCTION THUBAN_SP_GENERATEID()
RETURNS VARCHAR

AS $$



BEGIN

--now we get the next value from the thuban_seq and add the date to the  front. 
 

return  to_char( current_timestamp,   'MMDD')::varchar || 
nextval('THUBAN_SEQ')::varchar



RETURN NEWID;

END;

$$ LANGUAGE plpgsql;

If this is not what your after you need to give more information what you want 
to accomplish 




All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored. 

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately. 
Thank you.

[SQL] Emacs sql-postgres (please, sorry for question not about PostgreSQL).

2010-03-18 Thread Dmitriy Igrishin 
Hello all Emacs users!

I am using Emacs recently. I love sql-mode, to use with PostgreSQL,
but I have problems with it.
When my SQL file (or buffer) are small (50-100 lines) I can send
it to SQLi buffer without any problems. But when I working with
large SQL file (e.g. complex database model, thousands of lines)
and send it to SQLi buffer it does not work properly. Something
going on and in SQLi buffer (psql) I see incomplete SQL
statement, for example:
super=# super'# super'# super'# super'#
It seems to single quote was not closed. But SQL is absolutely
correct and loads fine when I load it from file directly from psql.
I think, the problem with Emacs buffers... Please, help!

And please sorry, for question not about PostgreSQL...

Regards,
Dmitriy Igrishin

Re: [SQL] Emacs sql-postgres (please, sorry for question not about PostgreSQL).

2010-03-18 Thread Tom Lane 
Dmitriy Igrishin  writes:
> I am using Emacs recently. I love sql-mode, to use with PostgreSQL,
> but I have problems with it.
> When my SQL file (or buffer) are small (50-100 lines) I can send
> it to SQLi buffer without any problems. But when I working with
> large SQL file (e.g. complex database model, thousands of lines)
> and send it to SQLi buffer it does not work properly. Something
> going on and in SQLi buffer (psql) I see incomplete SQL
> statement, for example:
> super=# super'# super'# super'# super'#
> It seems to single quote was not closed. But SQL is absolutely
> correct and loads fine when I load it from file directly from psql.
> I think, the problem with Emacs buffers... Please, help!

Might or might not be related, but I've seen psql lock up when I try to
paste more than a thousand or two characters into it via X11
copy-and-paste.  You might find that disabling libreadline (option -n
to psql) helps.

regards, tom lane

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Emacs sql-postgres (please, sorry for question not about PostgreSQL).

2010-03-18 Thread Pavel Stehule 
Hello

try to look on 
http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Terminal.27s_configuration

Regards
Pavel Stehule

2010/3/18 Dmitriy Igrishin :
> Hello all Emacs users!
>
> I am using Emacs recently. I love sql-mode, to use with PostgreSQL,
> but I have problems with it.
> When my SQL file (or buffer) are small (50-100 lines) I can send
> it to SQLi buffer without any problems. But when I working with
> large SQL file (e.g. complex database model, thousands of lines)
> and send it to SQLi buffer it does not work properly. Something
> going on and in SQLi buffer (psql) I see incomplete SQL
> statement, for example:
>     super=# super'# super'# super'# super'#
> It seems to single quote was not closed. But SQL is absolutely
> correct and loads fine when I load it from file directly from psql.
> I think, the problem with Emacs buffers... Please, help!
>
> And please sorry, for question not about PostgreSQL...
>
> Regards,
> Dmitriy Igrishin

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Emacs sql-postgres (please, sorry for question not about PostgreSQL).

2010-03-18 Thread Pavel Stehule 
2010/3/18 Tom Lane :
> Dmitriy Igrishin  writes:
>> I am using Emacs recently. I love sql-mode, to use with PostgreSQL,
>> but I have problems with it.
>> When my SQL file (or buffer) are small (50-100 lines) I can send
>> it to SQLi buffer without any problems. But when I working with
>> large SQL file (e.g. complex database model, thousands of lines)
>> and send it to SQLi buffer it does not work properly. Something
>> going on and in SQLi buffer (psql) I see incomplete SQL
>> statement, for example:
>>     super=# super'# super'# super'# super'#
>> It seems to single quote was not closed. But SQL is absolutely
>> correct and loads fine when I load it from file directly from psql.
>> I think, the problem with Emacs buffers... Please, help!
>
> Might or might not be related, but I've seen psql lock up when I try to
> paste more than a thousand or two characters into it via X11
> copy-and-paste.  You might find that disabling libreadline (option -n
> to psql) helps.
>

This behave is same without external copy/paste. SQL support in emacs
is really great with some issues - like this or missing autocomplete.

Pavel

>                        regards, tom lane
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

-- 
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql