Quickly count results:
Just a count – SELECT COUNT(*) from tablename
Count unique entries in table:
select count(columnname) from (select distinct columnname from tablename) as x;
Create a new table from the results of another table:
create table newtablename from (SELECT * from tablename where columnname != ”);
OR to create new table with unique non duplicate data from another table:
create table newtablename from (select distinct columnname from tablename) as x;
Create new table with id that is serial:
create table newtablename (id serial, firstcolumn varchar)
Rename a column name:
ALTER TABLE tablename RENAME COLUMN oldcolumnane TO newcolumname;
Search for matching data in column:
Select somecolumn from sometable where somecolumn ILIKE ‘%sounds like this%’
Dump a database structure only with no data (-s)
pg_dump -U user -s databasename > databasefilename.sql
(To dump data only, no structure use -a)
Full dump of all databases data with ‘insert into’
pg_dumpall -U dbusername -D > nameoffulldump.sql
Change or alter a data type in a table
alter table tablename alter name int;
Above ‘int’ is integer, you can change it to varchar, etc. as required
Rename Table:
ALTER TABLE oldname RENAME TO newname;
Rename Column in a Table:
ALTER TABLE name RENAME column oldcolumname TO newcolumname;
