Useful_Code
- Spatial Location of documents in ref_data_source
- pgRouting
- Example SQL Update command to change the length of character varying data type on table with associated views
- Generate SQL commands to set all loaded_by columns to have default value of the current user
- Generalte SQL commands to add comment to columns
- List dependent objects
- Rollback example
- Adding an Auto-Increment Column to a Table
Spatial Location of documents in ref_data_source
pgRouting
<http://www.pgrouting.org/>
Example SQL Update command to change the length of character varying data type on table with associated views
from http://sniptools.com/databases/resize-a-column-in-a-postgresql-tabl...
UPDATE pg_attribute SET atttypmod = 100+4 WHERE attrelid = 'assay'::regclass AND attname = 'sample_id';
Generate SQL commands to set all loaded_by columns to have default value of the current user
select 'ALTER TABLE '|| table_name||' ALTER COLUMN '|| column_name || ' SET DEFAULT "current_user"();' from information_schema.columns where table_schema = 'dh' and column_name = 'loaded_by'
Generalte SQL commands to add comment to columns
select ' COMMENT ON COLUMN dh.' ||table_name||'.'|| column_name || ' IS _dh.' || table_name || '.' || column_name || ': _ ;' from information_schema.columns where table_schema = 'dh';
List dependent objects
begin; drop object cascade; rollback;
or
$ psql -U username -E -c '\d+ table' database
Rollback example
begin; ALTER TABLE dh_collar DROP CONSTRAINT dh_collars_new_pkey cascade; rollback;
output
NOTICE: drop cascades to 5 other objects DETAIL: drop cascades to constraint dh_sample_weight_hole_id_fkey on table dh_sample_weight drop cascades to constraint qc_dh_hole_id_fkey on table qc_dh drop cascades to constraint dh_magsus_hole_id_fkey on table dh_magsus drop cascades to constraint dh_event_hole_id_fkey on table dh_event drop cascades to constraint dh_alteration_hole_id_fkey on table dh_alteration Query returned successfully with no result in 61 ms.
Adding an Auto-Increment Column to a Table
alter table mytable add myid serial;
Type 'serial':
1. Sets type to int.
2. Creates sequence for you.
3. Populates unique values in all existing rows using sequence.
4. sets default on column to nextval of sequence.
**Try the following query to create an auto increment colomn on exixting table
ALTER TABLE your_table
ADD colomn_name serial UNIQUE;
