Posts by Clodoaldo Neto • 1,006 points
43 posts
-
1
votes3
answers392
viewsA: UPDATE SUM +1 to decimal
Just add to it yourself: SET uniqueid = uniqueid + 1
postgresqlanswered Clodoaldo Neto 1,006 -
0
votes1
answer276
viewsA: Postgresql PL/PGSQL function
The only observation is that it is not necessary plpgsql, simply sql: create function sppreenchecombomunicipio (p_uf text) returns table ( municipio text, cod_municipio int ) as $$ select…
-
1
votes1
answer60
viewsA: Date no postgresql.PL/SQL
It returns nothing because the date in the table is 18-02 and today is 17-02. Anyway it is much simpler: create or replace function get_d () returns setof date as $$ select diaEntrega from mercado…
-
4
votes2
answers950
viewsA: How to ignore accentuation in a database search?
Use unaccent: where unaccent(Municipio.descricao) ilike unaccent('%Sao Paulo%') https://www.postgresql.org/docs/current/static/unaccent.html To install as super user: create extension unaccent;…
-
3
votes5
answers14274
viewsA: psql command at prompt. (postgresql database)
Create a password file containing a line as *:*:teste:postgres:senha The file has to be %APPDATA%\postgresql\pgpass.conf To find out which directory %APPDATA% type in the prompt: echo %APPDATA%…
-
0
votes1
answer278
viewsA: How to calculate cumulative total of 365 days joining two different years?
select to_char(date_trunc('year', ad.data - interval '8 months'), 'YYYY'), sum(ad.value) from mytable ad group by 1
-
1
votes3
answers102
viewsA: Take Report with zero months
Make a outer join with generate_series: select to_char(d.data,'yyyy/mm') as Mês, count(fi.*) as valor from financeiro fi right join generate_series( '2016-01-01'::date, '2016-12-01, '1 month' ) gs…
-
1
votes3
answers92
viewsA: Capture which key Unique was raped
create table t ( email text unique, login text unique ); with d (email, login) as ( values ('[email protected]','fulano') ), e as ( select exists (select 1 from t inner join d using(email)) as…
postgresqlanswered Clodoaldo Neto 1,006 -
0
votes3
answers1596
viewsA: How to order records in the query according to another ordination?
This query returns the total value and the sum of the installments paid for each id_gerencial: select id_gerencial, dp.valor as valor_P, sum(dm.valor) as valor_M from desmembramento dp inner join…
-
1
votes1
answer750
viewsA: Postgress Crosstab - Return and sql tuple descriptions are incompatible
The second column of the consultation provided to crosstab exists only for sorting and is not returned. In your case just extract the date from one of the columns timestamp returnees: select teste,…
-
0
votes1
answer64
viewsA: How to concatenate a value with a string
To decrease 48 years of a date: select now() - 48 * interval '1 year'; ?column? ------------------------------- 1968-10-07 20:46:38.699564+00 Note that there is no need to concatenate. Just…
postgresqlanswered Clodoaldo Neto 1,006 -
3
votes2
answers3541
viewsA: What command returns the tables that are in LOCK in Postgresql?
select c.relname, n.nspname, l.locktype, l.mode, l.granted, l.fastpath from pg_locks l inner join pg_database d on l.database = d.oid inner join pg_class c on l.relation = c.oid inner join…
postgresqlanswered Clodoaldo Neto 1,006 -
2
votes1
answer3263
viewsA: How to assign the query cost to a variable in Postgres
I did passing an array of queries but can also be referring to a table of queries. Dynamic SQL. create or replace function custo_consulta(_consulta text[]) returns table (consulta text, planejamento…
-
0
votes2
answers758
viewsA: Average between timestamp dates
Subtract the lowest date from the highest and divided by line count - 1: with cadastro (data) as ( values ('2016-08-17 12:29:01'::timestamp), ('2016-08-17 12:34:13'), ('2016-08-17 12:39:26') )…
postgresqlanswered Clodoaldo Neto 1,006 -
1
votes2
answers68
viewsA: Group two tables
The correlated subconsultation is executed once for each line of the result which can have a very bad performance. Instead make the tables join: select p.name, p.email, count(*) as eventos from…
postgresqlanswered Clodoaldo Neto 1,006 -
2
votes2
answers1286
viewsA: Multiply the amount of elements from two different tables
select (select count(*) from pessoas) * (select count(*) from carros)
-
2
votes2
answers279
viewsA: Syntax for INTERVAL use
Better than concatenating strings is doing arithmetic: t1.data_abertura::date - t2.garantia * interval '1 month' <= t1.data_nf The suit of the guy interval can be multiplied gives great…
postgresqlanswered Clodoaldo Neto 1,006 -
1
votes1
answer1471
viewsA: Merge different columns in the same result
Two versions to test the best performance. First: with u as ( select id, regra from usuario where id = 2 ) select * from ( select id, regra from u union all select usuario_id, regra from permissao p…
-
1
votes1
answer284
viewsA: Comparison with recorded data such as JSON
Factoring in your case: case tbl_cliente_admin.nome is not null and tbl_hd_chamado_extra.hd_chamado is not null and finalizada is null when true then case split_part(split_part(json, '","', 1),…
postgresqlanswered Clodoaldo Neto 1,006 -
1
votes5
answers1137
viewsA: Postgresql - Remove tuple referenced by another table
Do count can be slow. Solution with the exists: select *, not ( exists ( select 1 from telemetria where id_sistema = s.id ) or exists ( select 1 from alerta where id_sistema = s.id ) or exists (…
-
0
votes2
answers402
viewsA: Pass ROWTYPE parameter with the EXECUTE command
The main problem with your function is that you have to return a value row as in select my_table from my_table. I gave a simplified: create or replace function test_conditions() returns void as $$…
-
0
votes3
answers1803
viewsA: Tables/Temporary content in the database?
The best way to separate users and applications within the same base is by using schematics: http://www.postgresql.org/docs/current/static/ddl-schemas.html A scheme is part of the qualification of…
-
0
votes1
answer105
viewsA: psycopg2.pool.Simpleconnectionpool, Operationalerror exception takes time to launch
Use the parameter connect_timeout in seconds: psycopg2.pool.SimpleConnectionPool ( 1, 2, host = 'ip-do-server', user = 'postgres', password = 'postgres', database = 'nomebanco', port = 5432,…
-
0
votes2
answers90
viewsA: postgres sql Insert into
insert into concelho(id,censo,concelho) select "CONCELHO","CENSO","CONCELHO_DSG" from dblink( 'dbname=meu_db host=meu_host user=meu_user password=minha_senha' ::text, 'SELECT…
postgresqlanswered Clodoaldo Neto 1,006 -
1
votes2
answers614
viewsA: Duplicate results when searching with POSTGRES
select produto_id, nome, valor, loja_id, nome, situacao, categoria_id, id_pai, nome, nome_pai from ( select distinct produto.id as…
postgresqlanswered Clodoaldo Neto 1,006 -
0
votes2
answers46
viewsA: Select of dates
select * from jmh_licenca l inner join jmh_servidor s on l.lic_ser_id = s.ser_id inner join jmh_doenca d on l.doe_id = d.doe_id where l.lic_data_inicial…
-
2
votes2
answers182
viewsA: How to return dates with a zero count
In Postgresql it is possible to generate series of dates with the function generate_series. Making a right join of this with the from of your original query all dates will be returned: select…
-
0
votes1
answer336
viewsA: Return last entry of each object in the database
select distinct on (carro) * from monitor_evento order by carro, id_serial desc
-
1
votes3
answers137
viewsA: Postgresql Query - How to do this?
with t (id, c2, c1) as ( values (11, 1, 'A'), (12, 2, 'D'), (13, 3, 'G'), (14, 1, 'B'), (15, 2, 'E'), (16, 3, 'H'), (17, 1, 'C'), (18, 2, 'F'), (19, 3, 'I') ) select i, c1[1] as c1, c1[2] as c2,…
-
2
votes1
answer1385
viewsA: Error: table name specified more than Once
update app set inclusao = usuario.inclusao from usuario where app.usuario = usuario.codigo
-
1
votes4
answers146
viewsA: sql - SELECT for recent data only
select distinct on (backup) * from t order by backup, data desc
-
0
votes3
answers354
viewsA: Return the last entry of different Postgresql objects
distinct on return only one row for each carro_id. In the clause order by decide which one. select distinct on (carro_id) * from carro inner join evento using(carro_id) order by carro_id, data desc,…
-
0
votes3
answers2241
viewsA: How to create nonexistent columns in a Datatable?
coalesce returns the value of the second parameter if the first is null coalesce(count(*), 0) as qtd…
-
1
votes1
answer1837
viewsA: Select Rows in a Postgresql array
It seems you want to aggregate the table rows orderItem select array_agg(orderItem) as "_AAA" from orderItem where orderId = 10 This should return a list of tuples.…
-
1
votes1
answer1473
viewsA: Single column calculation in Postgresql
Your question is very confusing but it seems that you want to group by the user: select usuario, coalesce(sum(b.pontos), 0) as pontosp, coalesce(sum(c.pontos), 0) as pontosn, coalesce(sum(b.pontos),…
postgresqlanswered Clodoaldo Neto 1,006 -
3
votes2
answers556
viewsA: Group Postgresql query results
You don’t have to use case. It’s easier to count the nonzero: select count(m.statusmat.descricao = 'Completo' or null) as "Completo", count(m.statusmat.descricao = 'Cursando' or null) as "Cursando",…
-
1
votes2
answers263
viewsA: Rails and postgres group_by data considering time zone
For daylight saving time use BRST. select( "date(created_at at time zone 'UTC' at time zone 'BRST') as dia, count(*) as qtd" ).group("dia")
-
9
votes4
answers860
viewsA: Select with indefinite amount of conditions
It is not necessary to assemble strings, which is actually bad practice. Just pass the parameters in the form of an array with either of the two syntaxes: select * from t where descricao ilike any…
-
2
votes3
answers6508
viewsA: Difference in postgresql date months
If it is necessary only the whole number of months: select ( select count(*) - 1 from generate_series(date1, date2, '1 month') ) as meses from (values ('2013-03-16'::date, '2014-07-16'::date) ) g…
postgresqlanswered Clodoaldo Neto 1,006 -
1
votes2
answers558
viewsA: Group plots showing the value of the first and last plot
Correlated sub-consultations (queries within the select) can be very bad for performance. So it is better to one of the following two solutions. The first using distinct on select * from ( select…
postgresqlanswered Clodoaldo Neto 1,006 -
9
votes3
answers2325
viewsA: Order By - Leave specific record for first
No need to use case select * from t order by store_name != 'Boston' The false sorts before the true. And you can do a normal sort with the other lines: order by store_name != 'Boston', store_name…
-
3
votes2
answers135
viewsA: How do I sum up the 2-interval difference in Postgresql?
Using coalesce is simpler: select sum(coalesce(end_date, now()) - start_date)
-
3
votes4
answers1530
viewsA: Problem with sorting in Postgresql
Postgresql uses the collations provided by the system. Check which ones are available: $ locale -a In Linux Fedora the collation "pt_BR" works correctly. Try other collations in your query. Note…