Tuesday, November 28, 2017

Postgress Tips and Tricks

1. Usage: Login to remote postgres.

$: psql -h xxx.xxx.com -U postgres -W

2. Usage: Create database.

$: CREATE DATABASE databaseName;
$: sudo adduser databaseName
$: sudo passwd databaseName
$: su - databaseName
$: psql -d template1 -c "ALTER USER databaseName WITH PASSWORD 'newpassword';"

3. Usage: Common command prompt options.

$: \dt //- list tables
$: \l //- list databases
$: \c //- Connect database
$: \q //- dis-connect database
$: DROP TABLE test; //- delete table;
$: TRUNCATE TABLE profile_events; // Drop table
$: ALTER SEQUENCE seq RESTART WITH 1; //- restart sequence after truncate table
$: UPDATE TABLE SET idcolumn=nextval('seq'); //- update sequence of table

4. Note:

PostgreSQL uses sequences to generate auto-numeric values, by default, Phalcon tries to obtain the generated value from the sequence table_field_seq, for example: robots_id_seq, if that sequence has a different name, the getSequenceName() method needs to be implemented:

No comments:

Post a Comment