PostgreSQL (pg) snippets#

dbfiddle online dbms REPL#

date sample dbfiddle hall of fame another example

Change 'sa' password#

Login into psql and set your password

$ psql -U postgres
db> ALTER USER postgres with password 'your-pass';

Authentication methods details:

  • trust - anyone who can connect to the server is authorized to access the database

  • peer - use client's operating system user name as database user name to access it.

  • md5 - password-base authentication

for further reference check here


If you connect over localhost ( you shouldn't experience that particular issue. I wouldn't muck much with the pg_hba.conf but instead I would adjust your connection string:

psql -U someuser -h database


You can use the following command while in a psql session to show where your config files are being read (assuming you can launch psql). This is just a troubleshooting step that can help some people:

select * from pg_settings where setting~'pgsql';

Run batch file with psql command without password#

psql --host=localhost --dbname=<dbname> --port=<Port Number>
     --username=<dbuser> --file=C:\PSQL_Script.txt --output=C:\PSQL_Output.txt

On Windows machines look for the file in:

  %APPDATA% typically resolves to: C:\Documents and Settings\My_Windows_User_Name\Application Data\.

Just go [W]R -> then paste the 1st line in

Some additional readings:

Configuring pg_hba.conf file Authentication methods: trust, peer, ident, etc.



select *
from the_table
where the_column::date between date '2015-05-20' and date '2015-06-20'
  and the_column::time between time '08:00::' and '16:00:00'

The expression the_column::date is called a cast and will convert the timestamp to a date removing the time information. the_column::time extracts the time part of the timestamp column.

The between operator will include the boundaries (e.g. rows with a time precisely at 16:00:00). If you don't want that you will need to change the between condition to a corresponding > and < condition.

select Date range#

          .where('created_at', '>=', '2009-01-01T00:00:00Z')
          .where('created_at', '<', new Date())

select Date range + 1#

select col1, col2, ...
from table1 
where date_col in (select distinct date_col 
                      from table2
                      union all
                      select distinct (date_col - '1 day'::interval)
                      from table2
                      union all
                      select distinct (date_col + '1 day'::interval)
                      from table2
This has quite good peformance because the subquery only be calculated one time and will be cache for comparing

#### another example of sql
SELECT t1.CustomerNum, t1.OrderNum As LastOrderNum, t1.LastOrderDate
  FROM table1 As t1
 WHERE t1.OrderDate = (SELECT MAX(t2.OrderDate)
                         FROM table1 t2
                        WHERE t1.CustomerNum = t2.CustomerNum
                          AND t2.OrderDate BETWEEN date1 AND date2)
   AND t1.OrderDate BETWEEN date1 AND date2

## heading

### sub-heading

####make data
-- generate some data
DROP TABLE tmp.orders;
CREATE TABLE tmp.orders
    , odate DATE NOT NULL
    , payload VARCHAR
ALTER TABLE tmp.orders ADD PRIMARY KEY (id,odate);

INSERT INTO tmp.orders(id,odate,payload) VALUES
  (1, '2011-10-04' , 'one' )
, (1, '2011-10-24' , 'two' )
, (1, '2011-10-25' , 'three' )
, (1, '2011-10-26' , 'four' )
, (2, '2011-10-23' , 'five' )
, (2, '2011-10-24' , 'six' )

-- CTE to the rescue ...
WITH sel AS (
    SELECT * FROM tmp.orders
    WHERE odate BETWEEN '2011-10-23' AND '2011-10-24'
SELECT * FROM sel s0
    SELECT * FROM sel sx
    WHERE =
    AND sx.odate > s0.odate


NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "orders_pkey" for table "orders"
 id |   odate    | payload 
  1 | 2011-10-24 | two
  2 | 2011-10-24 | six
(2 rows)

A variation on same

SELECT Customer.Num, (
    SELECT OrderNum FROM Orders WHERE CustomerNum = Customer.Num AND OrderDate BETWEEN :start AND :end ORDER BY OrderNum DESC LIMIT 1
) AS LastOrderNum
FROM Customer