Users

Create a user:

(postgres@host)_$: createuser -P <user>
pass -> a
superuser -> y

User will be superuser:

(postgres@host)_$: psql -c "ALTER ROLE <user> WITH SUPERUSER;"

User will not be superuser:

(postgres@host)_$: psql -c "ALTER ROLE <user> WITH NOSUPERUSER;"

User with all privileges on a database:

(postgres@host)_$: psql -c "GRANT ALL PRIVILEGES ON DATABASE <database> to <user>;"

List users:

(postgres@host)_$: psql -c "\du"

Change user’s password:

(postgres@host)_$: psql -c "ALTER ROLE <user> WITH PASSWORD 'p455w0rd';"

Delete user:

(postgres@host)_$: dropuser -i <user>
(postgres@host)_$: psql -c "DROP USER <user>;"

User owner of a database:

(postgres@host)_$: psql database-devel
database-devel=#  SELECT d.datname as "Name",pg_catalog.pg_get_userbyid(d.datdba) as "Owner"
FROM pg_catalog.pg_database d
WHERE d.datname = 'database-devel'
ORDER BY 1;

List roles:

(postgres@host)_$: psql database-devel
database-devel=#  SELECT rolname FROM pg_roles;
 rolname
----------
 postgres
 myrole

Databases

Create a database:

(postgres@host)_$: createdb -O <user> <database>

Connect to a database:

(postgres@host)_$: psql <database> -U <user>

Delete a database:

(postgres@host)_$: dropdb -i '<database>'

List all databases:

(postgres@host)_$: psql --list
(postgres@host)_$: psql -c "\l"
(postgres@host)_$: psql -c "\list"

Get the database’s OID:

(postgres@host)_$: psql -c "SELECT datname,oid FROM pg_database;"
(postgres@host)_$: psql -c "SELECT oid FROM pg_database WHERE datname = 'database';"

Rename a database:

(postgres@host)_$: psql -c "ALTER DATABASE oldname RENAME TO newname;"

Database creation time: (It works so long as we don’t change versions)

(postgres@host)_$: ls -la ./9.1/main/base/<OID>/PG_VERSION

Number of tables in the database:

(postgres@host)_$: psql -c "SELECT COUNT(*) FROM pg_stat_user_tables ;" database
(postgres@host)_$: psql -c "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'public';" database

Tables

Describe tables:

(postgres@host)_$: psql database-devel
database-devel=# \dt          -- All tables
database-devel=# \ds          -- All sequences
database-devel=# \d table     -- Basic information
database-devel=# \d+ table    -- Advanced information

Owner of a table:

(postgres@host)_$: SELECT relname, relowner FROM pg_class WHERE relname LIKE '%cardbin%';

Change the owner of a table:

(postgres@host)_$: psql -c "ALTER TABLE cardbin OWNER TO <user>;"

Number of rows per table:

database-devel=# SELECT schemaname,relname,n_live_tup
  FROM pg_stat_user_tables
  ORDER BY n_live_tup DESC;

Hard disk size

Hard disk size of a database:

(postgres@host)_$: psql
postgres=# SELECT pg_database_size('database-devel') AS bytes;
  bytes
----------
 80118584
(1 row)

That size should be close to the one we get with du:

_$: cd /var/lib/postgresql/9.1/main/base
_$: du 247897/ -cb | grep total
80139064	total

Hard disk size of a table:

(postgres@host)_$: psql database-devel
database-devel=# SELECT pg_table_size('ip') AS bytes;
  bytes
----------
 19144704
(1 row)

Edit columns

Editing columns from pgAdmin might hang if there are more active connections at that time. But we can edit them from the command line:

(postgres@host)_$: psql database-devel
database-devel=# ALTER TABLE crmgrouplang RENAME COLUMN groupid TO crmgroupid;
database-devel=# ALTER TABLE crmgrouplang DROP COLUMN crmgroupid;

database-devel=# ALTER TABLE clientprodescription ALTER COLUMN translation TYPE text;
database-devel=# ALTER TABLE list ALTER COLUMN lat TYPE double precision USING NULLIF(lat, '')::double precision;
database-devel=# ALTER TABLE list ALTER COLUMN lon TYPE double precision USING NULLIF(lon, '')::double precision;

database-devel=# ALTER TABLE web DROP CONSTRAINT web_countrycod_fkey;
database-devel=# ALTER TABLE web DROP COLUMN countrycod;

database-devel=# ALTER TABLE page ADD COLUMN typecs varchar(1) NOT NULL DEFAULT 'b';
database-devel=# ALTER TABLE page ALTER COLUMN typecs DROP DEFAULT;

database-devel=# ALTER TABLE listlead ADD COLUMN ismailed boolean NOT NULL DEFAULT False;

database-devel=# ALTER TABLE crmsupporttype ALTER COLUMN useraddid SET  NOT NULL;
database-devel=# ALTER TABLE crmsupporttype ALTER COLUMN useraddid DROP NOT NULL;

Edit tables

(postgres@host)_$: psql database-devel
database-devel=# ALTER TABLE listconditionhist RENAME TO liststatehist;
database-devel=# DROP TABLE oldclientpro;

Edit sequences, indices, foreign keys and primary keys

Sequences

database-devel=# SELECT relname FROM pg_class WHERE relkind='S' ORDER BY relname;
database-devel=# ALTER SEQUENCE listconditionhist_id_seq RENAME TO liststatehist_id_seq;

database-devel=# SELECT last_value FROM user_id_seq;
database-devel=# SELECT pg_catalog.setval('user_id_seq', (SELECT max(id) FROM "user"));
database-devel=# SELECT pg_catalog.setval('user_id_seq', 23, false);    -- next id == 23
database-devel=# SELECT pg_catalog.setval('client_id_seq', (SELECT max(id) FROM client));

Indices

database-devel=# SELECT * FROM pg_indexes WHERE indexname='client_pkey';
database-devel=# SELECT * FROM pg_indexes WHERE tablename='client';
database-devel=# ALTER INDEX listconditionhist_pkey RENAME TO liststatehist_pkey;
database-devel=# DROP  INDEX clientpro_pkey;
database-devel=# CREATE UNIQUE INDEX clientpro_pkey ON clientpro (id);
database-devel=# CREATE INDEX list_priceforsale_asc ON list (proceforsale ASC);
database-devel=# REINDEX INDEX client_pkey;
database-devel=# REINDEX TABLE client;
database-devel=# REINDEX DATABASE "database-devel";

Foreign keys

-- PostgreSQL <= 9.1
database-devel=# ALTER TABLE liststatehist DROP CONSTRAINT listconditionhist_listid_fkey;
database-devel=# ALTER TABLE liststatehist ADD  CONSTRAINT liststatehist_listid_fkey FOREIGN KEY (listid) REFERENCES list(id);

-- PostgreSQL >= 9.2
database-devel=# ALTER TABLE liststatehist RENAME CONSTRAINT listconditionhist_listid_fkey TO liststatehist_listid_fkey

Primary keys

a) With implicit index:

database-devel=# ALTER TABLE clientpro ADD PRIMARY KEY (id) ;

b) With explicit index:

database-devel=# CREATE UNIQUE INDEX clientpro_pkey ON clientpro (id)
database-devel=# ALTER TABLE clientpro ADD PRIMARY KEY USING INDEX clientpro_pkey ;
```sql


## Edit the owner of a table or sequence

```sql
database-devel=# ALTER TABLE <tablename> OWNER TO <username>;
database-devel=# ALTER SEQUENCE <sequencename> OWNER TO <username>;
```sql


## Check the columns order

```sql
(postgres@host)_$: psql database-devel
database-devel=# SELECT attrelid, attname, attnum FROM pg_attribute WHERE attname='datelast';
 attrelid | attname  | attnum
----------+----------+--------
    65010 | datelast |     11
    65193 | datelast |     34
    65437 | datelast |      9

database-devel=# SELECT attrelid, attname, attnum FROM pg_attribute WHERE attrelid=65193;
 attrelid |      attname       | attnum
----------+--------------------+--------
    65193 | tableoid           |     -7
    65193 | cmax               |     -6
    65193 | xmax               |     -5
    65193 | cmin               |     -4
    65193 | xmin               |     -3
    65193 | ctid               |     -1
    65193 | id                 |      1
    65193 | webid              |      2
    65193 | clientid           |      3
    65193 | isactive           |      4
    65193 | istop              |      5
    65193 | typecs             |      6
    65193 | statecs            |      7
    65193 | refinternal        |      8
    65193 | refclient          |      9
    65193 | loc1countrycod     |     10
    65193 | loc2zoneid         |     11
    65193 | loc3townid         |     12
    65193 | loc4districtid     |     13
    65193 | loc5neighborhoodid |     14
    65193 | address            |     15
    65193 | postalcod          |     16
    65193 | ishidden           |     17
    65193 | typelisthiddencod  |     18
    65193 | geo1               |     19
    65193 | geo2               |     20
    65193 | isgeoauto          |     21