Postgres tips

Log to database

psql -p port -U user database

Show databases

\l

Show tables

\d

Describe table

\d+ table_name

Exit

\q

Import script from terminal

(when database was exported in sql file)

psql -p port -U user -d dest_db -a -f fileName.sql
   (psql -U prov -d prov -a -f model-prov.sql)

Select TOP 5 records, char_length

SELECT string1, char_length(string1) FROM table1 ORDER BY createDate DESC LIMIT 5;

Dump (export) database

pg_dump -U user source_db -p port -f fileName.sql

List sequences

\ds
or by using SQL:
SELECT c.relname FROM pg_class c WHERE c.relkind = 'S';

list sequence dependencies

SELECT p.relname, a.adsrc FROM pg_class p JOIN pg_attrdef a ON (p.relfilenode = a.adrelid);

To get last value of a sequence use the following query:

SELECT last_value FROM test_id_seq;

remove a sequence

DROP SEQUENCE sequenceName;

Change column type

ALTER TABLE ad_hoc_history ALTER COLUMN parameters TYPE text;
(http://www.postgresql.org/docs/9.3/static/sql-altertable.html)

* change the size of a varchar column:
ALTER TABLE simlocation ALTER COLUMN location TYPE varchar(30);

drop constrain (NOT NULL)

ALTER TABLE table ALTER COLUMN column DROP NOT NULL;

Is some range present in system

Table is msisdn and msisdn is key, but it is string.
select count(*) from msisdn where to_number(msisdn,'9999999999999') >=4366305707030 and to_number(msisdn,'9999999999999') <=4366305712029;

Ref:
http://www.postgresqlforbeginners.com/2010/11/interacting-with-postgresql-psql.html
http://www.postgresql.org/docs/9.3/static/app-psql.html
http://www.postgresql.org/docs/9.3/static/functions-string.html
dump:
http://www.thegeekstuff.com/2009/01/how-to-backup-and-restore-postgres-database-using-pg_dump-and-psql/
http://www.postgresql.org/docs/9.3/static/app-pgdump.html
sequences:
http://www.neilconway.org/docs/sequences/
http://blog.sensible.io/2013/08/24/postgresql-sequences-and-array-column-types.html http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/PostgreSQL_x14316_001.htm
http://www.techonthenet.com/postgresql/functions/to_number.php