PostgreSQL Useful commands
- To copy a table to create new one
- CREATE TABLE table_name_2 ( like table_name_2 including all)
- Insert columns from one table to another
- insert into table_name (phone_no,subscriber_name,address,pincode,place,district) select phone_no,subscriber_name,address,pincode,place,district from tabele_2;
- Insert column of one table from another table, where second table colum length is less than 95
- insert into table_back
- (
- phone_no,subscriber_name,address,pincode,place,district
- ) select phone_no,subscriber_name,address,pincode,place,district from table_2 where length (subscriber_name) > 95;
- Delete complete content from table
- Delete a column from table
- DELETE FROM table_name WHERE phone_no = 7382613796;
- Change table name
- ALTER TABLE table_name RENAME TO TABLE_NAME_2;
- Change column datatype
- ALTER TABLE table_name ALTER COLUMN phone_no TYPE bigint USING (phone_no::bigint);
- phone_no is the column name.
- Delete row when length of a column not equal to 10
- delete FROM table_name WHERE length(phone_no)!=10;
- Update column by its first n characters, if the character length is to be reduced
- update test_1 set subscriber_name = left(subscriber_name,10);
- To create new table from old table, sorted by systemtime
- create table new_racktemp as select * from racktemp order by systemtime;
- Alter/ Update existing table by inserting new column, setting it with primary key and insert the column with serial numbers starting from 1.
- alter table new_racktemp add column slno bigserial primary key;
- For creating sequence number and updating column with that (not tested)
- CREATE SEQUENCE seq;
- ALTER SEQUENCE seq RESTART WITH 1;
- UPDATE racktemp SET slno=nextval('seq');
- Dump and restore for command line
- Go to "cmd"
- Change directory to the Postgres Bin folder
- cd // cd C:\Program Files\PostgreSQL\9.3\bin
- Use pg_dump to backup
- pg_dump.exe -U postgres -t "table_name" "Data_base_name" > "Path&Name_Backup"
- //pg_dump.exe -U postgres -t consotable hpc > C:\Users\CFD\Desktop\consot.backup
- Use psql to restore to another database
- psql.exe -U postgres "DB_name" < "Path for backup file"
- //psql.exe -U postgres testdb < C:\Users\CFD\Desktop\consot.backup
- Get values of the last updated row inreference to serial number
- SELECT * from table_name order by slno desc limit 1
