PostgreSQL (pg) snippets#
dbfiddle online dbms REPL#
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 (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
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:
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_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 ) ;
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