PostgreSQL DDL Operations: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
Line 2: Line 2:


* [[PostgreSQL Operations#Subjects|PostgreSQL Operations]]
* [[PostgreSQL Operations#Subjects|PostgreSQL Operations]]
=Connect=
{{Internal|PostgreSQL_Concepts#Database|Database}}
==Connect to the Default Database==
psql -h localhost -U admin
==Connect as a Specific User to a Database==
psql -U <''username''> <''dbname''>


=Database Cluster=
=Database Cluster=

Revision as of 23:13, 14 November 2018

Internal

Database Cluster

initdb

https://www.postgresql.org/docs/9.5/static/app-initdb.html

initdb will create a new database cluster.

Database

List Databases

psql -l

or

SELECT datname FROM pg_database;

Create a Database

createdb <dbname>
createdb -h localhost -U admin playground

or

CREATE DATABASE <dbname>;

Connect to a Database

psql <database-name>

Drop a Database

DROP DATABASE

Users

Postgres Users

List Users

psql
\du

or

SELECT usename FROM pg_user;

Create User

createuser name

or

CREATE USER name;

Change Password

In psql, for the current user:

\password 

or

ALTER USER user_name WITH PASSWORD 'new_password';

Tablespace

List Tablespaces

psql
\db

Tables

List Tables

psql
\dt

Create a Table

CREATE TABLE poc_library_component
(
  "id" integer NOT NULL,
  "name" text,
  "desc" text,
   CONSTRAINT poc_library_component_pk PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE poc_library_component OWNER TO is3_as;

Note: apparently, PostgreSQL does not like uppercases in column names.