Databases

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...

ALTER TABLE table ADD primary key (primary_key_id);

Replace table with your table name and primary_key_id with your Primary Key

ALTER TABLE myTable DROP CONSTRAINT table_foreign_key;

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

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

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';

GRANT SELECT ON table TO user;

Replace table with your database table and user with the user that should get Select privileges

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

Open a new Terminal Window and type in the following command:

sudo yum remove postgresql-server

The above will remove PostgreSql from Fedora 15

ALTER TABLE users ADD COLUMN fax int, ADD COLUMN phone int;

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

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

ALTER TABLE tablename RENAME COLUMN oldColumnName TO newColumnName;