[SQL] Problem with serial counters

2011-02-25 Thread Jacques Lebrun 
When I install my application, I use a SQL script to create the different 
tables.
Most of my tables start with a Serial column called RowId.
In my setup script I insert a few records into some tables, providing all 
fields, including the RowId (1, 2, 3, etc).

The problem I have is that when my user tries to insert a new record without 
the RowId field,
Postgres complains that it finds a duplicate key.  
I guess when I do an insert with all the fields (including the RowId), Postgres 
does not increment the serial counter.

I cannot remove the RowId field from the Insert of my setup script because this 
setup script is also used by customers using MySQL.

What can I do to force PostGres to update the internat serial counter when I do 
an insert with a specified value for the serial?



Jacques Lebrun
Web Mobile Inc
819-563-0133
jleb...@cplus.org

[SQL] what's wrong in this procedure?

2011-02-25 Thread Camaleon 
This error is returned Erro de SQL:

ERROR:  column "Aguardando Pagto" does not exist at character 352  >>>


create or replace function get_historico()   RETURNS SETOF 
twiste.type_cur__historico AS '

   SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS 
transacoes
   FROM ofertas o
   JOIN transacao t ON o.ofertas_id = t.ofertas_id
   JOIN municipio m ON o.municipio_id = m.municipio_id
  WHERE  o.data_fim <= now() AND t.status IN("Aguardando Pagto", "Em análise", 
"Aprovado", "Completo")
  GROUP BY o.data_fim;
'
language 'sql';



>> the column is t.status and not "Aguardando Pagto";
>> what's wrong ?  thanks

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

Re: [SQL] what's wrong in this procedure?

2011-02-25 Thread Adrian Klaver 

On 02/25/2011 09:46 AM, Camaleon wrote:

This error is returned Erro de SQL:

ERROR:  column "Aguardando Pagto" does not exist at character 352>>>


create or replace function get_historico()   RETURNS SETOF 
twiste.type_cur__historico AS '

SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS 
transacoes
FROM ofertas o
JOIN transacao t ON o.ofertas_id = t.ofertas_id
JOIN municipio m ON o.municipio_id = m.municipio_id
   WHERE  o.data_fim<= now() AND t.status IN("Aguardando Pagto", "Em análise", 
"Aprovado", "Completo")
   GROUP BY o.data_fim;
'
language 'sql';




the column is t.status and not "Aguardando Pagto";
what's wrong ?  thanks




Try single quotes, 'Aguardando Pagto'

--
Adrian Klaver
adrian.kla...@gmail.com

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

Re: [SQL] what's wrong in this procedure?

2011-02-25 Thread Yeb Havinga 

On 2011-02-25 19:33, Adrian Klaver wrote:

On 02/25/2011 09:46 AM, Camaleon wrote:

This error is returned Erro de SQL:

ERROR:  column "Aguardando Pagto" does not exist at character 352>>>


create or replace function get_historico()   RETURNS SETOF 
twiste.type_cur__historico AS '


SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, 
count(t.*) AS transacoes

FROM ofertas o
JOIN transacao t ON o.ofertas_id = t.ofertas_id
JOIN municipio m ON o.municipio_id = m.municipio_id
   WHERE  o.data_fim<= now() AND t.status IN("Aguardando Pagto", "Em 
análise", "Aprovado", "Completo")

   GROUP BY o.data_fim;
'
language 'sql';




the column is t.status and not "Aguardando Pagto";
what's wrong ?  thanks




Try single quotes, 'Aguardando Pagto'

and also
create or replace function get_historico()   RETURNS SETOF 
twiste.type_cur__historico AS $$

instead of
create or replace function get_historico()   RETURNS SETOF 
twiste.type_cur__historico AS '


together with the corresponding
$$
language 'sql';
instead of
'
language 'sql';

at the end

regards,
Yeb Havinga


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

Re: [SQL] Problem with serial counters

2011-02-25 Thread Vibhor Kumar 

On Feb 25, 2011, at 2:39 AM, Jacques Lebrun wrote:
> What can I do to force PostGres to update the internat serial counter when I 
> do an insert with a specified value for the serial?
>  

After inserting the all the Data, use ALTER SEQUENCE Command:
http://www.postgresql.org/docs/8.4/static/sql-altersequence.html

Thanks & Regards,
Vibhor


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

Re: [SQL] what's wrong in this procedure?

2011-02-25 Thread Rob Sargent 


On 02/25/2011 10:46 AM, Camaleon wrote:
> This error is returned Erro de SQL:
> 
> ERROR:  column "Aguardando Pagto" does not exist at character 352  >>>
> 
> 
> create or replace function get_historico()   RETURNS SETOF 
> twiste.type_cur__historico AS '
> 
>SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS 
> transacoes
>FROM ofertas o
>JOIN transacao t ON o.ofertas_id = t.ofertas_id
>JOIN municipio m ON o.municipio_id = m.municipio_id
>   WHERE  o.data_fim <= now() AND t.status IN("Aguardando Pagto", "Em 
> análise", "Aprovado", "Completo")
>   GROUP BY o.data_fim;
> '
> language 'sql';
> 
> 
> 
>>> the column is t.status and not "Aguardando Pagto";
>>> what's wrong ?  thanks
> 

Single quotes (') not double quotes (")

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