Posts Tagged ‘postgres’

Quick Tip: Show Postgres/pSQL Tables in Postgres OR R in 1 Line

Outside of R:

\dt

In R:

dt <- dbGetQuery(con1, “SELECT * FROM pg_catalog.pg_tables”)

 

Postgres / PaDB: Statistics

corr(YX)double precisiondouble precisioncorrelation coefficient
covar_pop(YX)double precisiondouble precisionpopulation covariance
covar_samp(YX)double precisiondouble precisionsample covariance
regr_avgx(YX)double precisiondouble precisionaverage of the independent variable (sum(X)/N)
regr_avgy(YX)double precisiondouble precisionaverage of the dependent variable (sum(Y)/N)
regr_count(YX)double precisionbigintnumber of input rows in which both expressions are nonnull
regr_intercept(YX)double precisiondouble precisiony-intercept of the least-squares-fit linear equation determined by the (XY) pairs
regr_r2(YX)double precisiondouble precisionsquare of the correlation coefficient
regr_slope(YX)double precisiondouble precisionslope of the least-squares-fit linear equation determined by the (XY) pairs
regr_sxx(YX)double precisiondouble precisionsum(X^2) – sum(X)^2/N (“sum of squares” of the independent variable)
regr_sxy(YX)double precisiondouble precisionsum(X*Y) – sum(X) * sum(Y)/N (“sum of products” of independent times dependent variable)
regr_syy(YX)double precisiondouble precisionsum(Y^2) – sum(Y)^2/N (“sum of squares” of the dependent variable)
stddev(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numerichistorical alias for stddev_samp
stddev_pop(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericpopulation standard deviation of the input values
stddev_samp(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericsample standard deviation of the input values
variance(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numerichistorical alias for var_samp
var_pop(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericpopulation variance of the input values (square of the population standard deviation)
var_samp(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericsample variance of the input values (square of the sample standard deviation)

Quick Tip: Create a Binary Column Conditionally in Postgres/PaDB/SQL


create table c as
    select a.id, tall, funny, weight,
           (case when b.id is null then 0 else 1 end) as rich
    from tablea a left outer join
         tableb b 
         on a.id = b.id;
or
select
    a.id, tall, funny, weight,
    (b.id is not null)::integer as rich
from
    tablea a
    left outer join
    tableb b on a.id = b.id

Quick-Tip: How to Search SQL/NoSQL/Postgres Information Schema in 1 Line

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ‘%booty%’

This also works for table_name!