Postgresq Numerare le righe di una select query – SQL count row_number

In postgresql per aggiungere un contatore al risultato di una query di tipo select è possibile utilizzare la funzione row_number(). L’uso di row_number() richiede la clausola over.  Il formato generale per numerare le righe di una tabella visualizzata con una select query è :

SELECT 
 row_number() OVER() as rownum, table.*
FROM table;

es:

select row_number() OVER () as num,  sp500.* 
 FROM   sp500 WHERE date > 01-01-2010 ORDER BY date;

Se è necessario che le i records siano ordinati si può utilizzare l’espressione  OVER ( :

SELECT row_number() OVER ( ORDER BY field nulls last) as rownum, table.* 
  FROM table 
  ORDER BY field ;

es:

select row_number() OVER ( order by date nulls last ) as num,  sp500.* 
FROM sp500 WHERE date > 01-01-2010 ORDER BY date;

Se si desidera inserire anche una colonna con il numero totale si può utilizzare la funzione count (*):

select row_number() OVER( order by date nulls last ) as num,  count(*) over ()  as total; sp500.* FROM   sp500 WHERE date > 01-01-2010 ORDER BY date;

Oracle utilizza row_number

Count()

In postgresql per contare i records che soddisfano una condizione data si utilizza count utilizzando la forma Sql standard.  Ad esempio:

SELECT  count(c.itemid) AS "Num." ,  c."Country" AS codec
FROM contactsdownloads  INNER JOIN contacts AS c
ON (itemidcontacts) = c.itemid

GROUP BY codec
HAVING  count (c.itemid) > 10

Formato generale

SELECT count(* )  over () , a.*

    FROM  table a  ;

Nella query seguente  viene effettuata la somma e il count a partire non da una tabella ma da  un’altra query.
Questo consente di ottenere un count su due livelli di ragruppamento.
In pratica si effettua un primo raggruppamento dei records che ritornerà il primo conteggio quindi si elaboreranno questi dati con un secondo livello di raggruppamento. Qui si effetturà un nuovo count e si opererà la  somma dei dati del primo count.

SELECT nameprd AS "Name Product" , sum(conta) AS "files downloaded", count (itemidcontacts) AS "N.Users" 
       FROM (SELECT  nameprd , count (itemid) AS conta, itemidcontacts FROM contactsdownloads 
               WHERE createddate > '01-01-2007'
               GROUP BY nameprd, itemidcontacts
               ORDER BY conta DESC ) AS DL

     GROUP BY  nameprd
     ORDER BY sum(conta) DESC;

…

Pagina riepilogativa

•   PostgreSQL nel blog

Query SQL  – PostgreSQL

• SQL Join – Joincast su ;
• SQL Cast ;
• SQL Union Query ;
• SQL Count ;
• SQL Delete ;
• Postgresql : SQL UPDATE query ;
• postgresql : SQL SELECT query ;
• PostgreSQL SQL use CASE when … then … else ..END in SELECT
• postgresql : Eseguire una SQL UPDATE query di test ;
• PostgreSQL Total, Subtotal an alternative  select multipli e TEMPORARY TABLE;
• Postgresq Numerare le righe di una select query – SQL count row_number

Postgresql 

• postgresql check if table exist ;
• How escape single quote ‘ in postgresql  ;
• SQL Concatenare due o più campi – String Concatenation ;
• Postgresql cancella record duplicati ;
• postgresql Elencare i campi di una tabella ;
• PostgreSQL CREATE TEMPORARY TABLE ;
• Postgresql show tables, views, index, sequences ;
• Postresql varie ;
• pg_class to check if table exists ;
• Postgresql Comandi utili ;
• Postgresql reset sequence ;

query di tipo plpgsql 

• Postgres plpgsql
• plpgsql how to loop record
• plpgsql Postgres FOR LOOP END LOOP
• Postgresql plpgsql function example IF … THEN … ELSE … END IF
• PostgreSQL SQL use CASE when … then … else ..END in SELECT
• plpgsql functions nexted cycles FOR LOOP multiple IF THEN

Query SQL  – PostgreSQL

• SQL Join – Joincast su ;
• SQL Cast ;
• SQL Union Query ;
• SQL Count ;
• SQL Delete ;
• Postgresql : SQL UPDATE query ;
• postgresql : SQL SELECT query ;
• PostgreSQL SQL use CASE when … then … else ..END in SELECT
• postgresql : Eseguire una SQL UPDATE query di test ;
• PostgreSQL Total, Subtotal an alternative  select multipli e TEMPORARY TABLE;
• Postgresq Numerare le righe di una select query – SQL count row_number

Alcune risorse presenti nel blog

• Postgresql running in parallel postgresql 9.1 and 8.4 on Centos 6.3 64 bit
• Upgrade postgresql 8.4.12 To postgresql 9.1.4 Debian
• Downgrade postgresql 9.1.4 to postgresql 8.4.12 – Centos 5
• Upgrade postgresql 8.4 to postgresql 9.1 Centos 5
• Upgrade postgresql server 8.4.3 8.4.12 – Centos 5

• Installare postgreSQL 8.4 in Centos 5
• Tunnel via ssh per gestire il server postgresql con pgAdmin
• Postgresql reset sequence
• Creare uno user di tipo read-only in un database postgresql
• Script per il backup dei database di postgresql
• Postgresql Windows 2008 server R2
• Postgresql server

DB

• PostgreSQL | maurizio siagri ;
• MySQL | maurizio siagri  ;

• PostgreSQL – novice – Check if table exists | Threaded View
• PostgreSQL blog resourses

Creare eliminare database e tabelle

…

postgresql : Eseguire una SQL UPDATE query di test

In diverse circostanze risutla utile eseguire una query di UPDATE in modalità test senza che le modifiche vengano salvate e  diventino persistenti.

Per realizzare una query UPDATE di tipo test in postgresql può essere usato il seguente schema:

BEGIN;

UPDATE .........  ;

SELECT  .......   ;

ROLLBACK  ;

Di seguito un esempio pratico

BEGIN;

UPDATE esf SET 
 "IDUser" = esf.itemid WHERE 
 esf.email IN ( SELECT esf.email 
 -- SELECT contacts.itemid, contacts.confirmdate, contacts.email, esf.itemid AS e_itemid, esf.email AS e_email
 FROM contacts
 LEFT JOIN esf ON contacts.email::text = esf.email::text
 WHERE contacts.email::text = esf.email::text
 ORDER BY esf.createddate ) ;

SELECT"IDUser" FROM esf WHERE "IDUser" > 0 ;

ROLLBACK;

Esempio semplice

BEGIN ;
UPDATE esf SET "Organization" = company ;
SELECT "Organization" FROM esf WHERE "Organization" is NOT NULL ;
ROLLBACK ;

Altro esempio di sql query di test utilizzando query tools di pgadmin3 in postgresql  con le istruzioni di test roolback incluso disabilitare con il comment  —

-- BEGIN; 

UPDATE esf SET 
   "itemid" = contacts.itemid 
   FROM contacts 
   WHERE esf.email = contacts.email AND
   esf.email IN 
   ( SELECT esf.email       
       FROM contacts 
       LEFT JOIN esf ON contacts.email::text = esf.email::text
       WHERE contacts.email::text = esf.email::text AND esf.id < 2000
       ORDER BY esf.createddate ) ;  

-- SELECT"itemid" FROM esf WHERE "itemid" < 22000 ; 

-- ROLLBACK;

sql UPDATE comando base

UPDATE contacts
SET firstname=name, lastname= name
WHERE firstname IS NULL AND name IS NOT NULL AND itemid < 100
 UPDATE sales
 SET payterms =
       CASE
          WHEN (SELECT SUM(qty) FROM sales s1
              WHERE sales.stor_id = s1.stor_id) < 10
             THEN 'On Billing'
         WHEN (SELECT SUM(qty) FROM sales s1
               WHERE sales.stor_id = s1.stor_id) < 100
              THEN 'Net 30'
        ELSE 'Net 60'
        END

INSERT INTO sitepage
SELECT sitepage_backup.*
FROM sitepage_backup
WHERE sitepage_backup."IDpage" = 442

Pagina riepilogativa

•   PostgreSQL nel blog

Query SQL  – PostgreSQL

• SQL Join – Joincast su ;
• SQL Cast ;
• SQL Union Query ;
• SQL Count ;
• SQL Delete ;
• Postgresql : SQL UPDATE query ;
• postgresql : SQL SELECT query ;
• PostgreSQL SQL use CASE when … then … else ..END in SELECT
• postgresql : Eseguire una SQL UPDATE query di test ;
• PostgreSQL Total, Subtotal an alternative  select multipli e TEMPORARY TABLE;
• Postgresq Numerare le righe di una select query – SQL count row_number

Postgresql 

• postgresql check if table exist ;
• How escape single quote ‘ in postgresql  ;
• SQL Concatenare due o più campi – String Concatenation ;
• Postgresql cancella record duplicati ;
• postgresql Elencare i campi di una tabella ;
• PostgreSQL CREATE TEMPORARY TABLE ;
• Postgresql show tables, views, index, sequences ;
• Postresql varie ;
• pg_class to check if table exists ;
• Postgresql Comandi utili ;
• Postgresql reset sequence ;

query di tipo plpgsql 

• Postgres plpgsql
• plpgsql how to loop record
• plpgsql Postgres FOR LOOP END LOOP
• Postgresql plpgsql function example IF … THEN … ELSE … END IF
• PostgreSQL SQL use CASE when … then … else ..END in SELECT
• plpgsql functions nexted cycles FOR LOOP multiple IF THEN

Query SQL  – PostgreSQL

• SQL Join – Joincast su ;
• SQL Cast ;
• SQL Union Query ;
• SQL Count ;
• SQL Delete ;
• Postgresql : SQL UPDATE query ;
• postgresql : SQL SELECT query ;
• PostgreSQL SQL use CASE when … then … else ..END in SELECT
• postgresql : Eseguire una SQL UPDATE query di test ;
• PostgreSQL Total, Subtotal an alternative  select multipli e TEMPORARY TABLE;
• Postgresq Numerare le righe di una select query – SQL count row_number

Alcune risorse presenti nel blog

• Postgresql running in parallel postgresql 9.1 and 8.4 on Centos 6.3 64 bit
• Upgrade postgresql 8.4.12 To postgresql 9.1.4 Debian
• Downgrade postgresql 9.1.4 to postgresql 8.4.12 – Centos 5
• Upgrade postgresql 8.4 to postgresql 9.1 Centos 5
• Upgrade postgresql server 8.4.3 8.4.12 – Centos 5

• Installare postgreSQL 8.4 in Centos 5
• Tunnel via ssh per gestire il server postgresql con pgAdmin
• Postgresql reset sequence
• Creare uno user di tipo read-only in un database postgresql
• Script per il backup dei database di postgresql
• Postgresql Windows 2008 server R2
• Postgresql server

DB

• PostgreSQL | maurizio siagri ;
• MySQL | maurizio siagri  ;

• PostgreSQL – novice – Check if table exists | Threaded View
• PostgreSQL blog resourses

Creare eliminare database e tabelle

creare eliminare database e tabelle

crea database:

create database myportal;

cancella database:

drop database myportal;

crea tabella di backup

CREATE TABLE contacts_backup
       AS SELECT * FROM contacts;

CREATE TABLE "contacts" (
      "itemid" integer NOT NULL,
      "lastname" varchar(25),
      "firstname" varchar(25),
      "email" varchar(50),
      Constraint "contacts_pkey" Primary Key ("itemid")

elimina  tabella

drop table contacts_backup

sql delete

delete FROM links_backup WHERE itemid < 97;

Postgresql cancella record duplicati

Esempio di query SQL per cancellare records duplicati in postgresql

DELETE
 FROM downloads
 WHERE itemid NOT IN
 (SELECT MAX(dtupla.itemid)
 FROM downloads As dtupla
 GROUP BY dtupla.email,  dtupla. filename,  dtupla.date_part("hour", createddate)

Alternativa per la cancellazione di record duplicati limitando la ricerca ai records
con count (*)   > 1

DELETE
 FROM downloads
 WHERE itemid NOT IN
 (SELECT MIN(dtpl.itemid)
 FROM downloads As dtpl
 GROUP BY dtpl.email, dtpl.filename, date_trunc('minute',dtpl.createddate)
 HAVING count(*) >= 1);
 SELECT 1;

Conta record che hanno alcuni campi chiave duplicati e che pertatno potrebbero essere record duplicati

SELECT *
 FROM ( SELECT email, date_trunc('hour',createddate), filename, count (filename) as totuples
 FROM contactsdownloads
 GROUP BY email, filename , date_trunc('hour',createddate)
 ORDER BY totuples DESC, date_trunc('hour',createddate) ) AS foo
 WHERE totuples > 1

Risorse:

Postgresql 

• postgresql check if table exist ;
• How escape single quote ‘ in postgresql  ;
• SQL Concatenare due o più campi – String Concatenation ;
• Postgresql cancella record duplicati ;
• postgresql Elencare i campi di una tabella ;
• PostgreSQL CREATE TEMPORARY TABLE ;
• Postgresql show tables, views, index, sequences ;
• Postresql varie ;
• pg_class to check if table exists ;
• Postgresql Comandi utili ;
• Postgresql reset sequence ;

Plpgsql 

• plpgsql how to loop record
• PostgreSQL SQL use CASE when … then … else ..END in SELECT
• plpgsql functions nexted cycles FOR LOOP multiple IF THEN
• Postgresql plpgsql function example IF … THEN … ELSE … END IF
• plpgsql Postgres FOR LOOP END LOOP

SQL  – PostgreSQL

• SQL Joincast su ;
• SQL Cast ;
• SQL Union Query ;
• SQL Count ;
• SQL Delete ;
• Postgresql : SQL UPDATE query ;
• postgresql : SQL SELECT query ;
• postgresql : Eseguire una SQL UPDATE query di test ;
• PostgreSQL Total, Subtotal an alternative ;

• PostgreSQL | maurizio siagri ;
• MySQL | maurizio siagri  ;

• PostgreSQL – novice – Check if table exists | Threaded View
• PostgreSQL blog resourses

Creare eliminare database e tabelle

plpgsql Postgres FOR LOOP END LOOP

Esempio di funzione plpgsql che utilizza
cicli  FOR LOOP END LOOP 
e IF FOUND THEN END IF 

CREATE OR REPLACE FUNCTION __getcatalog_ID0(integer)
RETURNS SETOF catalog AS
$BODY$
DECLARE
_mytupla1 record;
_mytupla2 record;
_myIDC int;
BEGIN
SELECT * INTO _mytupla1 FROM catalog 
    WHERE "IDCatalogFather" = $1 ;
IF FOUND THEN
     -- visualizziamo primo livello distina
     -- ed estraiamo secondo livello distinta
     FOR _mytupla1 IN SELECT * FROM catalog
         WHERE ("IDCatalogFather") = $1
     LOOP
         return next _mytupla1;
         -- estraiamo secondo livello
        _myIDC =  _mytupla1."IDCatalog" ;
        SELECT  * INTO _mytupla2
        FROM catalog  WHERE "IDCatalogFather" = _myIDC ;
        IF FOUND THEN
             return next _mytupla2;
        END IF ;
     END LOOP;
 ELSE
    _myIDC = _mytupla."IDCatalog" ;
    FOR _mytupla in select * from catalog
        WHERE ("IDCatalog") = $1 ;
    LOOP
        return next _mytupla;
    END LOOP;
 END IF;
END ;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER

Risorse:

Plpgsql 

• plpgsql how to loop record
• PostgreSQL SQL use CASE when … then … else ..END in SELECT
• plpgsql functions nexted cycles FOR LOOP multiple IF THEN
• Postgresql plpgsql function example IF … THEN … ELSE … END IF
• plpgsql Postgres FOR LOOP END LOOP

Postgresql 

• postgresql check if table exist ;
• How escape single quote ‘ in postgresql  ;
• SQL Concatenare due o più campi – String Concatenation ;
• Postgresql cancella record duplicati ;
• postgresql Elencare i campi di una tabella ;
• PostgreSQL CREATE TEMPORARY TABLE ;
• Postgresql show tables, views, index, sequences ;
• Postresql varie ;
• pg_class to check if table exists ;
• Postgresql Comandi utili ;
• Postgresql reset sequence ;

SQL  – PostgreSQL

• SQL Joincast su ;
• SQL Cast ;
• SQL Union Query ;
• SQL Count ;
• SQL Delete ;
• Postgresql : SQL UPDATE query ;
• postgresql : SQL SELECT query ;
• postgresql : Eseguire una SQL UPDATE query di test ;
• PostgreSQL Total, Subtotal an alternative ;

• PostgreSQL | maurizio siagri ;
• MySQL | maurizio siagri  ;

• PostgreSQL – novice – Check if table exists | Threaded View
• PostgreSQL blog resourses

Creare eliminare database e tabelle

Postgresql reset sequence

Consistenti modifiche in una tabella rendono necessario talvota resettare il valore
della sequence associata.

Reset sequences : examples query

SELECT  SETVAL(‘documents_itemid_seq’,
( SELECT MAX (itemid)
FROM documents))  WHERE ( SELECT MAX(itemid)  FROM documents) > 0;

select setval(‘events_itemid_seq’,  (select max(itemid) from events))  where(select max(itemid) from events) > 0;

Risorse:

Fixing Sequences – PostgreSQL wiki

Risorse:

Postgresql 

• postgresql check if table exist ;
• How escape single quote ‘ in postgresql  ;
• SQL Concatenare due o più campi – String Concatenation ;
• Postgresql cancella record duplicati ;
• postgresql Elencare i campi di una tabella ;
• PostgreSQL CREATE TEMPORARY TABLE ;
• Postgresql show tables, views, index, sequences ;
• Postresql varie ;
• pg_class to check if table exists ;
• Postgresql Comandi utili ;
• Postgresql reset sequence ;

SQL  – PostgreSQL

• SQL Joincast su ;
• SQL Cast ;
• SQL Union Query ;
• SQL Count ;
• SQL Delete ;
• Postgresql : SQL UPDATE query ;
• postgresql : SQL SELECT query ;
• postgresql : Eseguire una SQL UPDATE query di test ;
• PostgreSQL Total, Subtotal an alternative ;

• PostgreSQL | maurizio siagri ;
• MySQL | maurizio siagri  ;

Plpgsql 

• plpgsql how to loop record
• PostgreSQL SQL use CASE when … then … else ..END in SELECT
• plpgsql functions nexted cycles FOR LOOP multiple IF THEN
• Postgresql plpgsql function example IF … THEN … ELSE … END IF
• plpgsql Postgres FOR LOOP END LOOP

• PostgreSQL – novice – Check if table exists | Threaded View
• PostgreSQL blog resourses

Postgresql cancella record duplicati

Creare eliminare database e tabelle

postgresql : SQL UPDATE query

SQL UPDATE from another table con SELECT from

update  a table contacts  with value from another table users_ltd if they are matching on email  contacts.email= esf.email

1) version

UPDATE contacts
SET countrycode = users_ltd.countrycode
FROM users_ltd
WHERE contacts.email = users_ltd.email AND internal IS false;

update  a table contacts  with value from another table esf if they are matching on contacts contacts.itemid= esf.itemid

2) version

 UPDATE contacts
   SET countrycode =
   (SELECT SUBSTR( esf."Country" ,1,2)
      FROM esf
          WHERE  contacts.itemid = esf.itemid );

 

sql UPDATE con WHERE … IN ( SELECT ) e SELECT con INNER JOIN

UPDATE users_ltd
SET internal = false  

WHERE email IN (
select  users_ltd.email
FROM users_ltd
  INNER JOIN contacts
  ON users_ltd.email = contacts.email
  WHERE internal IS NULL
  ORDER BY contacts.email )

sql Update con due subqueries.

UPDATE  sitepage
   SET  numint = 1.1 * (SELECT   SUM(qty)
   FROM  sitepage_backup
   WHERE sitepage."IDpage" = sitepage_backup."IDpage" )
   WHERE "IDModuleCall" IN
     (SELECT "ModuleID"
      FROM   modules
      GROUP BY  "ModueleID"
      HAVING    sum("ModuleID") >=30)

Update completo di tutte le colonne della tabella

UPDATE sitepage AS S
   SET "IDMenuCatalog"=SB."IDMenuCatalog", "Page"=SB."Page", "PageKey"= SB."PageKey", "Menu"=SB."Menu", 
       "Pagetitle"= SB."Pagetitle", "Banner"=SB."Banner", "Pic3c"=SB."Pic3c", "Selezionato"=SB."Selezionato", "HTML"=SB."HTML", 
       "Description"=SB."Description", "PageBody"=SB."PageBody", "SolutionBody"=SB."SolutionBody", "Nota"=SB."Nota", "HtmlBody"=SB."HtmlBody", 
       "CreatedByUser"=SB."CreatedByUser", "CreatedDate"=SB."CreatedDate", "PageDescription"=SB."PageDescription", "PageKeywords"= SB."PageKeywords", 
       "ModuleIDCall"=SB."ModuleIDCall", "ModuleID"= SB."ModuleID"
 FROM sitepage_backup  AS SB
 WHERE S."IDpage" = 63;

sql eseguire una UPDATE query di TEST in Postgresql

schema:

BEGIN;

UPDATE .........  ;

SELECT  .......   ;

ROLLBACK  ;

Esempio pratico

BEGIN;

UPDATE esf SET 
 "IDUser" = esf.itemid WHERE 
 esf.email IN ( SELECT esf.email 
 -- SELECT contacts.itemid, contacts.confirmdate, contacts.email, esf.itemid AS e_itemid, esf.email AS e_email
 FROM contacts
 LEFT JOIN esf ON contacts.email::text = esf.email::text
 WHERE contacts.email::text = esf.email::text
 ORDER BY esf.createddate ) ;

SELECT"IDUser" FROM esf WHERE "IDUser" > 0 ;

ROLLBACK;

sql UPDATE comando base

UPDATE contacts
SET firstname=name, lastname= name
WHERE firstname IS NULL AND name IS NOT NULL AND itemid < 100
 UPDATE sales
 SET payterms =
       CASE
          WHEN (SELECT SUM(qty) FROM sales s1
              WHERE sales.stor_id = s1.stor_id) < 10
             THEN 'On Billing'
         WHEN (SELECT SUM(qty) FROM sales s1
               WHERE sales.stor_id = s1.stor_id) < 100
              THEN 'Net 30'
        ELSE 'Net 60'
        END

INSERT INTO sitepage
SELECT sitepage_backup.*
FROM sitepage_backup
WHERE sitepage_backup."IDpage" = 442

sql update usando la funzione replace

UPDATE links
 SET url= Replace (url, '/en/products/', '/it/prodotti/' )
 -- WHERE url LIKE '/en/products%';    // agiungere questa riga per avere in query tools le righe modificate

Risorse:

SQL  – PostgreSQL

• SQL Joincast su ;
• SQL Cast ;
• SQL Union Query ;
• SQL Count ;
• SQL Delete ;
• Postgresql : SQL UPDATE query ;
• postgresql : SQL SELECT query ;
• postgresql : Eseguire una SQL UPDATE query di test ;
• PostgreSQL Total, Subtotal an alternative ;

• PostgreSQL | maurizio siagri ;
• MySQL | maurizio siagri  ;

Postgresql 

• Postgresql reset sequence ;
• postgresql check if table exist ;
• How escape single quote ‘ in postgresql  ;
• SQL Concatenare due o più campi – String Concatenation ;
• Postgresql cancella record duplicati ;
• postgresql Elencare i campi di una tabella ;
• PostgreSQL CREATE TEMPORARY TABLE ;
• Postgresql show tables, views, index, sequences ;
• Postresql varie ;
• pg_class to check if table exists ;
• Postgresql Comandi utili ;

Plpgsql 

• plpgsql how to loop record
• PostgreSQL SQL use CASE when … then … else ..END in SELECT
• plpgsql functions nexted cycles FOR LOOP multiple IF THEN
• Postgresql plpgsql function example IF … THEN … ELSE … END IF
• plpgsql Postgres FOR LOOP END LOOP

• PostgreSQL – novice – Check if table exists | Threaded View
• PostgreSQL blog resourses

Postgresql cancella record duplicati

Creare eliminare database e tabelle

..