[SQL] How to convert string to integer
Dear All, How do i convert string to int select SUM(pan_1) from customers1 where name='101' When i run the above query i m getting "function sum(character varying) does not exist".. Please anyone can guide me.. Thanks
Re: [SQL] How to convert string to integer
Hello you can use a ::int for converting to integer. Or better - you can alter column to integer. It will be faster and more correct. Regards Pavel Stehule 2010/12/15 venkat : > Dear All, > How do i convert string to int > select SUM(pan_1) from customers1 where name='101' > When i run the above query i m getting "function sum(character varying) > does not exist".. > Please anyone can guide me.. > 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] How to convert string to integer
On Wed, Dec 15, 2010 at 11:23 AM, venkat wrote: > Dear All, > > How do i convert string to int > > select SUM(pan_1) from customers1 where name='101' > > When i run the above query i m getting "function sum(character varying) > does not exist".. > > Please anyone can guide me.. > > Thanks > > > select SUM(pan_1::integer) from customers1 where name='101' also you can use conversion functions described here http://www.postgresql.org/docs/8.2/static/functions-formatting.html -- --- Viktor Bojović --- Wherever I go, Murphy goes with me
[SQL] Translate Function PL/pgSQL to SQL92
Hello, I have a Function in PL/pgSQL and I need to translate it to SQL92, but I'm stuck. ### CREATE OR REPLACE FUNCTION pruebas(varchar, varchar, integer) RETURNS SETOF personal AS $delimiter$ BEGIN PERFORM id from documentos WHERE descripcion = $1; IF NOT FOUND THEN INSERT INTO documentos(descripcion) VALUES($1); END IF; INSERT INTO personal(nombre,idtipodocumento,numdoc) VALUES($2, (SELECT id from documentos WHERE descripcion = $1), $3); END; $delimiter$ LANGUAGE plpgsql; ### Tables CREATE TABLE documentos id serial NOT NULL, descripcion character varying(60), CONSTRAINT pkdocumentos PRIMARY KEY (id) CREATE TABLE personal id serial NOT NULL, nombre character varying(60), idtipodocumento smallint NOT NULL, numdoc integer, CONSTRAINT pkpersonal PRIMARY KEY (id), CONSTRAINT fkpdoc FOREIGN KEY (idtipodocumento) REFERENCES documentos (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE NO ACTION, CONSTRAINT unqnumdoc UNIQUE (idtipodocumento, numdoc) Thanks and greetings for all Marcelo -- 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] [GENERAL] How to convert string to integer
On 15 December 2010 11:23, venkat wrote: > Dear All, > > How do i convert string to int > > select SUM(pan_1) from customers1 where name='101' > > When i run the above query i m getting "function sum(character varying) > does not exist".. > > Please anyone can guide me.. > > Thanks > > > select SUM(pan_1::integer) from customers1 where name='101' but this will work only if for all rows you can convert this field to integer regards Szymon Guz
[SQL] Database consistency after a power shortage
My question is regarding a potential situation: I have a program that inserts values on 3 tables linked to each other. My program is used in a POS. In this specific case, the program has to update the tables "header_invoice", "detail_invoice" and "payments_x_header_invoice". In a normal operation, the program should insert first a registry on "header_invoice", then insert N registries on "detail_invoice" referencing the header_invoice number. After that it should insert N registries regarding the payments related to the header_invoice, referencing again the invoice. So the order goes like this: 1) Insert 1 new registry on "header_invoice" 2) Insert N registries on "detail_invoice" referencing header_invoice 3) Insert N registries on "payments_x_header_invoice" referencing the header_invoice If lets say the header_invoice registry was inserted, operation was committed and then a power shortage occurs and the system shuts down. In that case the database will never know that more registries had to be inserted, because that happened on the application level. Is there any way to make the 3 operations be one transaction for the database, so that it keeps them all consistent in case a power shortage occurs in the middle?
Re: [SQL] Database consistency after a power shortage
On Wed, Dec 15, 2010 at 8:12 AM, Alberto wrote: > My question is regarding a potential situation: > > I have a program that inserts values on 3 tables linked to each other. My > program is used in a POS. In this specific case, the program has to update > the tables "header_invoice", "detail_invoice" and > "payments_x_header_invoice". > > In a normal operation, the program should insert first a registry on > "header_invoice", then insert N registries on "detail_invoice" referencing > the header_invoice number. After that it should insert N registries > regarding the payments related to the header_invoice, referencing again the > invoice. > > So the order goes like this: > 1) Insert 1 new registry on "header_invoice" > 2) Insert N registries on "detail_invoice" referencing header_invoice > 3) Insert N registries on "payments_x_header_invoice" referencing the > header_invoice > > If lets say the header_invoice registry was inserted, operation was > committed and then a power shortage occurs and the system shuts down. In > that case the database will never know that more registries had to be > inserted, because that happened on the application level. > > Is there any way to make the 3 operations be one transaction for the > database, so that it keeps them all consistent in case a power shortage > occurs in the middle? Yes, put them in a transaction. begin; insert into head_invoice ... insert into detail_invocie ... insert into payments_x_header_invoice ... commit; Then they either all go or none go. -- 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] Database consistency after a power shortage
On Wed, Dec 15, 2010 at 11:38 PM, Scott Marlowe wrote: > On Wed, Dec 15, 2010 at 8:12 AM, Alberto wrote: > > > > > Is there any way to make the 3 operations be one transaction for the > > database, so that it keeps them all consistent in case a power shortage > > occurs in the middle? > > Yes, put them in a transaction. > > begin; > insert into head_invoice ... > insert into detail_invocie ... > insert into payments_x_header_invoice ... > commit; > > Then they either all go or none go. > But if the database transaction concept is new to you, I highly recommend you do a little reading about database transactions in general and postgres' implementation specifics as well. It can be very easy for you to make mistakes that can cause the database to get slow or use up a lot of disk if you use transactions without understanding at least a little of what is happening in the database while the transaction is open but uncommitted. Incidentally, any error on a query within the transaction will cause the transaction to automatically 'rollback' when the transaction completes, undoing all of the changes, or you can manually cancel a transaction by issuing a 'rollback;' statement instead of 'commit;' at the end.
