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

AAAAAAAAAAAAAAAAAAA#

If you connect over localhost (127.0.0.1) 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 127.0.0.1 database

BBBBBBBBBBBBBBBBBBBBBBB#

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%\postgresql\pgpass.conf
  %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.

rr#

ggg#

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#

MODEL.query()
          .where('created_at', '>=', '2009-01-01T00:00:00Z')
          .where('created_at', '<', new Date())
          .orderBy('created_at','desc|asc')

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
```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
```sql
-- generate some data
DROP TABLE tmp.orders;
CREATE TABLE tmp.orders
    ( id INTEGER NOT NULL
    , 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
WHERE NOT EXISTS (
    SELECT * FROM sel sx
    WHERE sx.id = s0.id
    AND sx.odate > s0.odate
    )
    ;

result:

DROP TABLE
CREATE TABLE
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index "orders_pkey" for table "orders"
ALTER TABLE
INSERT 0 6
 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