PostgreSQL en Linux Ubuntu 14

Instalacion de PostgreSQL en Ubuntu 14

Instalar PostgreSQL Server y PostgreSQL Client

Siempre en la terminal, ejecuta este comando y confirma con una “Y” para continuar. Esto puede tardar dependiendo la velocidad de tu enlace a internet.
   
sudo apt-get install postgresql postgresql-client
 
Finalizar la instalación

Luego de la descarga de paquetes, el sistema continuará configurando el servicio. A partir de ahora, el servicio de PostgreSQL Server iniciará automática con tu sistema.

 
Configurar contraseña para el usuario postgres

PostgreSQL crea un usuario llamado postgres a nivel del sistema. Es el homónimo del usuario root en MySQL, es decir, con el usuario postgres vas a poder administrar y configurar el servidor de PostgreSQL.

El método de autenticación por defecto para este usuario es peer. Este método utiliza el nombre del usuario de tu sesión activa para la gestión de accesos al motor de base de datos. Por lo cual necesitamos iniciar sesión con este usuario.

Para iniciar sesión con un usuario diferente debemos ejecutar el siguiente comando:
   
sudo su postgres

Una vez hecho el paso anterior iniciaremos sesión en el cliente de PostgreSQL, nuevamente en la terminal ejecuta el siguiente comando:
   
psql

Dentro del cliente de PostgreSQL debes ejecutar la siguiente sentencia SQL y presiona la tecla Enter:
   
ALTER USER Postgres WITH PASSWORD '<password>';

No olvides cambiar la palabra <password> por la contraseña que deseas asignar al usuario postgres.

Para salir del cliente debes escribir el comando \q seguido de la tecla Enter.

 
Iniciar sesión con el cliente de PostgreSQL

Ahora que hemos asignado una contraseña para el usuario postgres, cuando queramos iniciar sesión desde otro usuario con el usuario postgres lo haremos mediante el siguiente comando:
   
psql -h localhost -U postgres -W

 
Instalacion de Administration

- pgAdmin III.- Es una interfaz gráfica útil para PostgreSQL, es esencial para los principiantes. Para instalarlo, escriba en la línea de comandos:

sudo apt-get install pgadmin3

Comandos PostgreSQL

# Iniciar session
psql -h localhost -U postgres -W
 
# Cerrar session
Ctrl+D
 
# Crear usuario
CREATE USER wilzonmb PASSWORD 'password';
 
# Eliminar usuario
DROP USER wilzonmb;
 
# ALTER ROLE
El manejo de roles en PostgreSQL permite diferentes configuraciones, entre ellas estan:
 
SUPERUSER/NOSUPERUSER. Super usuario, privilegios para crear bases de datos y usuarios.
CREATEDB/NOCREATEDB. Permite crear bases de datos.
CREATEROLE/NOCREATEROLE. Permite crear roles.
CREATEUSER/NOCREATEUSER. Permite crear usuarios.
LOGIN/NOLOGIN. Este atributo hace la diferencia entre un rol y usuario. Ya que el usuario tiene permisos para acceder a la base de datos a traves de un cliente.
PASSWORD. Permite alterar la contraseña.
VALID UNTIL. Expiración de usuarios.
Para cambiar la configuracion de un usuario o rol debemos ejecutar el siguiente comando.
 
ALTE ROLE <nombre del="" rol=""> WITH <opciones>
 
 
# Asignar permisos de super usuario a un usuario
ALTER ROLE wilzonmb WITH SUPERUSER;
 
#Cambiar la contraseña de un usuario
ALTER ROLE wilzonmb WITH PASSWORD 'nuevaClave';
 
# Crear una base de datos con un usuario específico como propietario
CREATE DATABASE wilzonmb_website WITH OWNER wilzonmb;
 
# Asignar todos los permisos a un usuario a una base de datos existente
GRANT ALL PRIVILEGES ON DATABASE wilzonmb_website TO wilzonmb;
 
# Listar base de datos
\l
 
# Seleccionar una base de datos o cambiar de base
\c nombre_base_datos
 
# Listar tablas de una base de datos
# Si la lista es muy larga veremos que podemos movernos hacia abajo y luego para salir solo digitamos la letra “q”
\d
 
# Para ver la información de la estructura de una tabla en especifico
\d nombre_tabla
 
# Vaciar una tabla en especifico o el famoso TRUNCATE que conocemos
# Con este comando borramos el contenido de una tabla y reiniciamos su indice sino agregamos RESTART IDENTITY nuestros indices no seran reiniciados y seguiran según el ultimo registro.
TRUNCATE TABLE nombre_table RESTART IDENTITY
 
# Crear una base de datos
CREATE DATABASE nombre_db;
 
# Borrar o eliminar una base de datos
DROP DATABASE nombre_db;
 
# Borrar o eliminar una tabla en especifico
DROP TABLE nombre_tabla;
 
# Enviar resultados de una consulta a un archivo delimitado por |
  # Cabe mencionar que el archivo necesito permisos de escritura.
COPY (SELECT * FROM tablename) TO '/home/tablename.csv' WITH DELIMITER '|';
 
# Uso de LIMIT y OFFSET
  # Donde:
  # limit: es nuestro limite de registros a mostrar
  # offset: indica desde donde comenzaran a mostrarce los registros
 
SELECT * FROM nombre_tabla LIMIT limit OFFSET offset;
 
# Uso de comillas
SELECT “column” FROM “nombre_tabla” WHERE “column” = 'value';
 
  # Generalmente podemos utilizar comillas dobles para nuestras columnas y comillas simples para nuestros valores, esto no es una regla pero a veces es necesario en casos especiales, tales como cuando ocupamos nombres reservados, por ejemplo:
  SELECT to FROM table;
  
  # En este caso tenemos un campo llamado “to”, esto nos dará un error de sintaxis, por lo tanto tendremos que usar comillas dobles:
  SELECT “to” FROM table;
  
# Salir del cliente psql
\q</opciones></nombre>

Más tips:

1.- Si se desea cambiar el tipo de datos, por ejemplo si es que queremos cambiarlo a decimal, integer, etc.

ALTER TABLE nombre_tabla ALTER COLUMN nombre_columna TYPE decimal USING (nombre_columna::decimal);
 
ALTER TABLE nombre_tabla ALTER COLUMN nombre_columna TYPE integer USING (nombre_columna::integer);