A list of common tasks or commands used frequently in PostGreSQL
- Locate the port entry in
postgresql.conf
file. In windows the path isC:\Program Files\PostgreSQL\{VERSION}\data
and change the value to your new port. - Restart PostGres service. In windows search and open the application
services.msc
and restart Postgres from there. - Change the port for pgAdmin. If you have pgAdmin installed, you need to change the port for that server.
- First Right click on the specific server and click disconnect.
- Right click again, go to
properties -> connection
and edit the port to your new port. - Finally connect back your server from pgAdmin with step 4 as reference.
- Using psql commandline:
\d+ <table_name>
- Using SQL:
SELECT *
FROM information_schema.columns
WHERE table_schema = <table_name>
AND table_name = <table_name>;
pg_dump -C -h remotehost -U remoteuser dbname | psql -h localhost -U localuser dbname
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;
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;