Archive for the 'PostgreSQL' Category

 

postgres notes 2 ..

Jan 28, 2016 in Database, PostgreSQL

. . .

$ createdb dbname –owner ownername

$ psql

postgres=# \set ON_ERROR_STOP on

. . .

PostgreSQL list of triggers ..

Nov 21, 2011 in Database, PostgreSQL

SELECT relname, tgname, usename 
 FROM ( 
     pg_trigger JOIN pg_class ON (tgrelid = pg_class.oid) 
               JOIN pg_proc ON (tgfoid = pg_proc.oid) 
               JOIN pg_user ON (relowner = pg_user.usesysid) 
) 
WHERE tgisinternal = FALSE [ and usename = 'user' ]

Postgresql (Postgres) Quick Notes 2..

Sep 30, 2011 in Database, PostgreSQL

— Set superuser :


ALTER ROLE username WITH SUPERUSER;

— Export / extract / dump database :


pg_dump --username=pgadmin --format=p mydb > mydb_pg_dump.sql

PostgreSQL installation notes..

Jun 26, 2008 in Database, PostgreSQL

On Linux:

1. $ [/sbin/]service postgresql initdb

2. $ [/sbin/]service postgresql start

3. The “initdb” procedure will create user “postgres”.

4. Change /var/lib/pgsql/data/pg_hba.conf

from this:

# "local" is for Unix domain socket connections only
local   all         all                               ident sameuser
# IPv4 local connections:
host    all         all         127.0.0.1/32          ident sameuser
# IPv6 local connections:
host    all         all         ::1/128               ident sameuser

to this:

# "local" is for Unix domain socket connections only
#local   all         all                               ident sameuser
local   all         all   trust
# IPv4 local connections:
#host    all         all         127.0.0.1/32          ident sameuser
host    all         all         127.0.0.1/32  trust
# IPv6 local connections:
#host    all         all         ::1/128               ident sameuser
host    all         all         ::1/128  trust

5. Restart postgres

$ [/sbin/]service postgresql restart

6. Test access:

psql -U postgres (no password)

PostgreSQL (postgres) quick notes..

Mar 28, 2008 in PostgreSQL

  • psql -U postgres [-d database]
  • \list : List of databases.
  • \d : show tables.
  • \d “table” (d-quotes) : show columns in table “table”.
  • \o : output to file.
  • \f [string] : set field separator.
  • \h : help.
  • \q : quit.
  • \c database : change database.
  • \i script_name : run an SQL script.
  • Queries:

  • select * from “table”; (d-quotes for table name)
  • SELECT column_name FROM information_schema.columns
    WHERE table_name =”_table_” ORDER BY ordinal_position;