[SQL] Working slow

[Judith Altamirano]

hello every body, I'm having a data base in a point of sale that is 
getting frozen, I already have run a vacuum -z -d to reindex the data 
base and nothing happens.. Some suggestions to speed the process, Do 
you guys think that the data base is nearly to broke?



--
luxcorreoseguro


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

[SQL] effectiveness tool

[Judith Altamirano]

Hello everybody!!

  I just want to know if there be some tool to analize the performance 
of a query, I mean to qualify the effectiveness, speed, if I have the 
correct indexes.


  Hope somebody can help me

 Thanks in advanced!!

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

[SQL] pg_clog/0202 Error

[Judith Altamirano]

Hello every body I just want to know why is happening this to my data 
base I'm doing the following query:


SELECT * FROM pagos where date(fecha_pago) = '2008-12-15';


It returns the next error:

   ERROR: can't find the transaction status 538976288
   DETAIL: can't open file <> not exist file or directory


what supposes that I must do?, is the table damaged?

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

[SQL] Copy question

[Judith Altamirano]

Hello every body I'm trying to extract a query as follows:

copy (select * from clientes where id_cliente = 7895) to 
'/home/paso/sel.copy';


But it returns the next error:

ERROR:  error de sintaxis en o cerca de «(» at character 6
LINE 1: copy (select * from clientes where id_cliente = 7895) to '/h...


I was reading the manual and I think the sintaxis is correct, any idea 
wich is the cause of the error?


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

[SQL] ichar

[Judith Altamirano Figueroa]

I moved from postgres 7.0.2 to 8.0.1,

in the 7.0.2 version I process the next query, and it succeed:

select ichar(letra_ascii) || '-' || substr('0' ||num_factura,length
(num_factura)+1,5) as factura from facturas 

but in 8.0.1 it outputs the next error:

ERROR:  not exist the function ichar(integer) 
HINT:  any function match with the arguments types.
Add explicit cast of types



---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] cast

[Judith Altamirano Figueroa]

Hello I have a query that ran in 7.0.2, but in 8.0.1 does not, the query
is the next:



select   n.factura,
 n.venta_neta,
 c.nombre_cli || ' ' || c.apellido_pat_cli || ' ' || coalesce
(c.apellido_mat_cli,''),
 date(n.fecha_hora_factura),
 o.nombre_oft
from nota_venta n,
 clientes c,
 oft_central o
wheredate(n.fecha_hora_factura) >= '2005-10-01' and
 date(n.fecha_hora_factura) <= '2005-10-14' and
 n.id_cliente = c.id_cliente and
 cast(n.udf4 as integer) = o.id_oft_central and
 n.factura is not null and
 n.s_factura != 'T'
order by o.nombre_oft";

it aparently returns an error because the cast, but I need to do the
cast, does somebody knows how can I change the cast but with the same
result, thnks



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[SQL] query to file

[Judith Altamirano Figueroa]

Excuse me, how can I send a query to a file?, for example the result of
this query:

select id_articulo, articulo, color_code from articulos;



---(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] backend error

[Judith Altamirano Figueroa]

Hello everybody I have a failure making a select in a table the error that returns is the follow:

Backend message type 0x44 arrived while idle
pqReadData() -- backend closed the channel unexpectedly.
    This probably means the backend terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

 How can I do to fix the failure?

[SQL] date question

[Judith Altamirano Figueroa]

Hi everybody, in Postgres 7.0.2 I have the next query:

SELECT * from clientes_proceso where fecha_mod::date <= now() -1;

but in version 8.0.1 returns the next error:

ERROR: The operator doesn't exist: timestamp with time zone - integer

How can drop a day to now()??

Re: [SQL] time

[Judith Altamirano Figueroa]

Hello everybody!!!, I'd like to know if there another way to get the time from a timestamp type, because in earliest versions I just get the time like time(fecha) and now
this returns an error, thanks in advanced!!!

[SQL] time

[Judith Altamirano Figueroa]

Hello everybody!!!, I'd like to know if there another way to get the time from a timestamp type, because in earliest versions I just get the time like time(fecha) and now this returns an error, thanks in advanced!!!

[SQL] pg_dump

[Judith Altamirano Figueroa]

Hi

how could I restore a db if I did next:

    pg_dump -Z 9 soi > $DESTINO/soi.bkp

Thanks in advanced

[SQL] Update in all tables

[Judith Altamirano Figueroa]

    Hello everybody I need to update a field with the same value in the tables of my data base but this field exists in almost all tables and has the same value, I don't want to code a script, so my question is if there is some way to update that field with a query and affects all the tables that contain the field?

    Thanks in advanced, regards!!!