Unfortunately Postgresql does not have Auto Increment columns so instead we create a Sequence that will act as a counter First we’ll need to create a sequence that we want to attch to our table CREATE SEQUENCE mytable_primary_id_seq; Then we need to assign the sequence to the column in our table that will act as the Autoincrement column ALTER TABLE mytable ALTER COLUMN primary_id...
How to add a primary key to an existing table PostgreSQL
ALTER TABLE table ADD primary key (primary_key_id);
Replace table with your table name and primary_key_id with your Primary Key
How to Drop Foreign Key in PostgreSQL table
ALTER TABLE myTable DROP CONSTRAINT table_foreign_key;
How to view your PostgreSQL history
PostgreSQL stores all the history SQL commands that were executed in the .psql_history file within your home directory
To view it execute the command below
cat ~/.psql_history
How to remove the DEFAULT value of a table column in PostgreSQL
Execute the following command
ALTER TABLE your_table ALTER COLUMN your_column DROP DEFAULT;
Replace your_table with the table and your_column with the column name where you wish to remove the default value
The above command is the same as setting the DEFAULT to NULL
How to add a comment to a PostgreSQL table
If you have a table with the name transactions and you need to add a comment to this table for future references you can add a comment by using the code below, replacing the table name and the comment to suite your needs.
COMMENT ON TABLE "transaction" IS 'This table keeps a log of all orders that went through the website';
How to assign Select Privileges to a specific user in PostgreSQL
GRANT SELECT ON table TO user;
Replace table with your database table and user with the user that should get Select privileges
How to connect to a remote database using PostgreSQL on the command line
On the command line type in the following command
psql -h 196.41.214.143 -U USERNAME -d DATABASE
Replace USERNAME with the username associated to the database and DATABASEwith the database name.
After executing the command it will prompt you for you password
How to uninstall PostgreSQL from Fedora Core 15
Open a new Terminal Window and type in the following command:
sudo yum remove postgresql-server
The above will remove PostgreSql from Fedora 15
How to add more than 1 column at a time to a PostgreSQL database table
ALTER TABLE users ADD COLUMN fax int, ADD COLUMN phone int;
ERROR: permission denied for sequence
Problem: PHP Warning: pg_query(): Query failed: ERROR: permission denied for sequencename in PostgreSql
Solution:
To fix this problem, change the owner to the role that needs to access this sequence
ALTER TABLE sequencename OWNER TO rolename;
Change sequencename to your sequence name and rolename to the owner
How to change the owner of a Database in PostgreSQL
First you need to login with the Super User e.g.
psql postgres
After logged-in type the following command
ALTER DATABASE database OWNER TO newuser;
Replace database with your database and newuser with your username
How to rename a table column in PostgreSQL
ALTER TABLE tablename RENAME COLUMN oldColumnName TO newColumnName;