Common How Todos with PostGreSQL

postgres-howto.md

Common How Todos with PostGreSQL

A list of common tasks or commands used frequently in PostGreSQL

---

Change port number

1. Locate the port entry in postgresql.conf file. In windows the path is C:\Program Files\PostgreSQL\{VERSION}\data and change the value to your new port.
2. Restart PostGres service. In windows search and open the application services.msc and restart Postgres from there.
3. Change the port for pgAdmin. If you have pgAdmin installed, you need to change the port for that server.
4. First Right click on the specific server and click disconnect.
5. Right click again, go to properties -> connection and edit the port to your new port.
6. Finally connect back your server from pgAdmin with step 4 as reference.

List columns in a table

1. Using psql commandline: \d+ <table_name>
2. Using SQL:

SELECT *
  FROM information_schema.columns
 WHERE table_schema = <table_name>
   AND table_name   = <table_name>;

Dump data from remote database to local database

pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname

Change VARCHAR Column type to DOUBLE PRECISION (A table with data)

When altering a column from one type to the other eg. VARCHAR to FLOAT or DOUBLE PRECISION, you may run into an error like column "latitude" cannot be cast automatically to type "DOUBLE PRECISION", this is the command to your rescue.

ALTER TABLE photos ALTER COLUMN latitude TYPE DOUBLE PRECISION USING latitude::float;

Change enum data type to VARCHAR

Enums are just VARCHARS with constraints set. Assuming the column name is status you need to drop that constraint with:

ALTER TABLE mytable DROP CONSTRAINT mytable_status_check;