pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedLooking find the each row size of table
4+ messages / 4 participants
[nested] [flat]
* Looking find the each row size of table
@ 2021-02-19 12:28 dbatoCloud Solution <dbatocloud17@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: dbatoCloud Solution @ 2021-02-19 12:28 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Dear All,
I want to find out each row size of a table in PostgreSQL?
I got pg_column_size() function. Could you please someone help me? Will
this function return size in KB or MB?
Thanks & Best Wishes,
Ashok
---------------------------------------------------------------------------------------------------------------------
Ashokkumar Mani *(OCP12c/11g/10g/9i, AWS SAA, M103)*
*Dubai** , UAE* *| BLR , INDIA*
M: *+971 54 723 0075 *| *+91 90086 70302 | *WApp :* +91 81975 99922*
W: https://dbatocloudsolution.blogspot.in/
<http://dbatocloudsolution.blogspot.in/; | E: dbatocloud17@gmail.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Looking find the each row size of table
@ 2021-02-19 12:36 John Scalia <jayknowsunix@gmail.com>
parent: dbatoCloud Solution <dbatocloud17@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: John Scalia @ 2021-02-19 12:36 UTC (permalink / raw)
To: dbatoCloud Solution <dbatocloud17@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
pg_column_size returns the number of bytes with no formatting at all. Do something like select pg_size_pretty(pg_column_size(my_column) / 1024) would give you the value in Kbytes, further calculation in the same manner could convert this into Mbytes, Gbytes, or whatever you need.
—
Jay
Sent from my iPad
> On Feb 19, 2021, at 7:28 AM, dbatoCloud Solution <dbatocloud17@gmail.com> wrote:
>
>
> Dear All,
> I want to find out each row size of a table in PostgreSQL?
> I got pg_column_size() function. Could you please someone help me? Will this function return size in KB or MB?
>
>
>
>
> Thanks & Best Wishes,
>
> Ashok
>
>
>
> ---------------------------------------------------------------------------------------------------------------------
>
> Ashokkumar Mani (OCP12c/11g/10g/9i, AWS SAA, M103)
>
> Dubai , UAE | BLR , INDIA
>
> M: +971 54 723 0075 | +91 90086 70302 | WApp : +91 81975 99922
>
> W: https://dbatocloudsolution.blogspot.in/ | E: dbatocloud17@gmail.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Looking find the each row size of table
@ 2021-02-19 13:18 Laurenz Albe <laurenz.albe@cybertec.at>
parent: dbatoCloud Solution <dbatocloud17@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2021-02-19 13:18 UTC (permalink / raw)
To: dbatoCloud Solution <dbatocloud17@gmail.com>; pgsql-admin@lists.postgresql.org
On Fri, 2021-02-19 at 17:58 +0530, dbatoCloud Solution wrote:
> I want to find out each row size of a table in PostgreSQL?
> I got pg_column_size() function. Could you please someone help me? Will this function return size in KB or MB?
If your table rows are several KB in size, you may have a problem.
pg_column_size() measures the size in bytes.
In my experience the best measure is to create a table, fill it with some 10000
rows and divide the table size by the row count.
There is substantial overhead for each row, and there will be overhead and
fragmentation on the block level too.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Looking find the each row size of table
@ 2021-02-19 18:59 Carlos Armijo Severino <carlosarmijo.severino@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 4+ messages in thread
From: Carlos Armijo Severino @ 2021-02-19 18:59 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: dbatoCloud Solution <dbatocloud17@gmail.com>; pgsql-admin@lists.postgresql.org
Estimados Compañeros estoy trabajando con Postgres v12.x, he escrito un
procedimiento Almacenado que me devuelva el resultado en JSON
Y me entrega el siguiente error, favor me podran orientar de como
hacerlos??... Muchas Gracias..
Este es el procedimiento:
Dear Colleagues I am working with Postgres v12.x, I have written a Stored
procedure that returns the result in JSON
And it gives me the following error, could you please guide me on how to do
them? ... Thank you very much ..
This is the procedure:
CREATE OR REPLACE FUNCTION public.sp_ccmm_json_9( param integer, periodo
integer )
RETURNS json
LANGUAGE plpgsql
AS $function$
begin
declare ret JSON;
SELECT to_json(salida.*) AS to_json
FROM ( SELECT to_char(t2.fecha_analista::timestamp with time zone,
'Month'::text) AS mes,
count(t2.fecha_analista) AS a,
count(t2.fecha_exp) AS r
FROM tb_analistas t1
JOIN tb_ingreso_siabc t2 ON t1.id_an = t2.analista
WHERE t1.id_an = param::double precision AND
date_part('year'::text, t2.fecha_analista) = periodo::double precision AND
t2.tipo_ccmm = 1
GROUP BY (to_char(t2.fecha_analista::timestamp with time zone,
'Month'::text))
UNION
SELECT to_char(t2.fecha_analista::timestamp with time zone,
'Month'::text) AS mes,
count(t2.fecha_analista) AS a,
count(t2.fecha_exp) AS r
FROM tb_analistas t1
JOIN tb_ingreso_siabc t2 ON t1.id_an = t2.analista
WHERE t1.id_an = param::double precision AND
date_part('year'::text, t2.fecha_analista) = periodo::double precision AND
t2.tipo_ccmm = 2
GROUP BY (to_char(t2.fecha_analista::timestamp with time zone,
'Month'::text))
UNION
SELECT to_char(t2.fecha_analista::timestamp with time zone,
'Month'::text) AS mes,
count(t2.fecha_analista) AS a,
count(t2.fecha_exp) AS r
FROM tb_analistas t1
JOIN tb_ingreso_siabc t2 ON t1.id_an = t2.analista
WHERE t1.id_an = param::double precision AND
date_part('year'::text, t2.fecha_analista) = periodo::double precision AND
t2.tipo_ccmm = 3
GROUP BY (to_char(t2.fecha_analista::timestamp with time zone,
'Month'::text))) salida
ORDER BY salida.mes, salida.a, salida.r;
END;
$function$
Este Error en NodeJS cuando intenta recuperar el set de registros...
This Error in NodeJS when trying to retrieve the recordset ...
[image: image.png]
*Atentamente,*
*Carlos Armijo Severino*
*Ingeniero de Ejecución en Informática*
*Celular : +56 9776 19 419*
[image: Mailtrack]
<https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_campaign=signaturevirality5&a...;
Remitente
notificado con
Mailtrack
<https://mailtrack.io?utm_source=gmail&utm_medium=signature&utm_campaign=signaturevirality5&a...;
19/02/21
15:59:04
El vie, 19 de feb. de 2021 a la(s) 10:18, Laurenz Albe (
laurenz.albe@cybertec.at) escribió:
> On Fri, 2021-02-19 at 17:58 +0530, dbatoCloud Solution wrote:
> > I want to find out each row size of a table in PostgreSQL?
> > I got pg_column_size() function. Could you please someone help me? Will
> this function return size in KB or MB?
>
> If your table rows are several KB in size, you may have a problem.
> pg_column_size() measures the size in bytes.
>
> In my experience the best measure is to create a table, fill it with some
> 10000
> rows and divide the table size by the row count.
> There is substantial overhead for each row, and there will be overhead and
> fragmentation on the block level too.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
>
>
Attachments:
image.png (image/png, 73.1K) download
view image
end of thread, other threads:[~2021-02-19 18:59 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-02-19 12:28 Looking find the each row size of table dbatoCloud Solution <dbatocloud17@gmail.com>
2021-02-19 12:36 ` John Scalia <jayknowsunix@gmail.com>
2021-02-19 13:18 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-02-19 18:59 ` Carlos Armijo Severino <carlosarmijo.severino@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox