PostgreSQL DDL Operations: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(75 intermediate revisions by the same user not shown)
Line 1: Line 1:
=Internal=
=Internal=
* [[PostgreSQL Operations#Subjects|PostgreSQL Operations]]
* [[PostgreSQL DML Operations]]
=<tt>psql</tt> Command Line=
==Specify the Database to Interact with==
<syntaxhighlight lang='bash'>
psql -d|--dbname= <database-name> ...
</syntaxhighlight>
==Providing a Password to psql in Command Line==
If a database is protected by a password, psql will challenge for password interactively. That can be avoided in scripts as follows:
export PGPASSWORD=''password''; psql -l -h ''some.host'' -U ''some-user'' ...
==<span id='Specifying_SQL_Commands_with_psql'></span>In-Line SQL Commands==
<syntaxhighlight lang='bash'>
psql ... -c "''sql-command''"
</syntaxhighlight>
Example:
<syntaxhighlight lang='bash'>
psql -h something.ak29cdi3ewgm.ca-central-1.rds.amazonaws.com -U infinity -d test_10 -c "GRANT ALL PRIVILEGES ON DATABASE test_10 TO infinity;"
</syntaxhighlight>
==File-Stored SQL Commands==
<syntaxhighlight lang='bash'>
psql -f|--file= <file-name> ...
</syntaxhighlight>
=Version=
Server version:
pg_config --version
Client version:


* [[PostgreSQL Operations#Subjects|PostgreSQL Operations]]
psql --version


=Database Cluster=
Also, execute:


==initdb==
SELECT version():


{{External|https://www.postgresql.org/docs/9.5/static/app-initdb.html}}
=<span id='Database_Cluster'></span>Cluster=
==<tt>initdb</tt>==
{{External|https://www.postgresql.org/docs/14/app-initdb.html}}
<code>initdb</code> will create a new [[PostgreSQL_Concepts#Cluster|database cluster]].


initdb will create a new database cluster.
<syntaxhighlight lang='bash'>
initdb --locale=C -E UTF-8 /opt/brew/var/postgresql@14
</syntaxhighlight>


=Database=
=Database=
See {{Internal|PostgreSQL_Concepts#Database|PostgreSQL Database}}


==List Databases==
==List Databases==
Line 18: Line 63:


or
or
   
 
SELECT datname FROM pg_database;
  > \l
 
or
 
<syntaxhighlight lang='sql'>
SELECT datname FROM pg_database;
</syntaxhighlight>


==Create a Database==
==Create a Database==
Via a command:
<syntaxhighlight lang='bash'>
createdb <dbname>
</syntaxhighlight>
<syntaxhighlight lang='bash'>
createdb playground
</syntaxhighlight>
Via SQL over an established <code>psql</code> session:
<syntaxhighlight lang='sql'>
CREATE DATABASE <dbname>;
</syntaxhighlight>
Also see: {{Internal|PostgreSQL_Concepts#Database_Name_Case_Sensitivity|Database Name Case Sensitivity}}
==Connect to a Database==
{{Internal|PostgreSQL Connecting to a Database#Overview|Connecting to a Database}}
==Drop a Database==


createdb <''dbname''>
Via a command:


createdb -h localhost -U admin playground
<syntaxhighlight lang='bash'>
dropdb <dbname>
</syntaxhighlight>


or
Via SQL over an established <code>psql</code> session:
 
<syntaxhighlight lang='sql'>
DROP DATABASE <dbname>;
</syntaxhighlight>
 
This command will not work if there are users connected to the database:
 
<font size=-2>
ERROR:  database "..." is being accessed by other users
DETAIL:  There are 10 other sessions using the database.
</font>
 
The database can though be forcefully dropped as follows ('''enclose the database name in single quotes'''):


CREATE DATABASE <''dbname''>;
<syntaxhighlight lang='sql'>
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<db-name>';
</syntaxhighlight>


==Connect to a Database==
This will report a number of backends and it will take a while until all will be terminated. You may need to run it several times to insure that all backends are gone.


psql <''database-name''>
If there is a lot of activity on the database, you may optionally disallow new connections:


==Drop a Database==
<syntaxhighlight lang='sql'>
UPDATE pg_database SET datallowconn = 'false' WHERE datname = '<db-name>';
ALTER DATABASE <db-name> CONNECTION LIMIT 1;
</syntaxhighlight>


DROP DATABASE
When all backends are gone, the database can be dropped.


=Users=
=Users=


{{Internal|PostgreSQL_Concepts#Database_User|Postgres Users}}
The following [[PostgreSQL_Concepts#Userr|user]] operations are supported.


==List Users==
==List Users==
Line 49: Line 140:


or  
or  
<syntaxhighlight lang='sql'>
SELECT usename FROM pg_user;
</syntaxhighlight>


SELECT usename FROM pg_user;
It is <code>usename</code>, not <code>username</code>.


==Create User==
==Create User==


  createuser name
Command line:
 
  createuser <''user_name''>


or
or SQL client:


CREATE USER name;
<syntaxhighlight lang='sql'>
CREATE USER <user_name>;
</syntaxhighlight>


==Change Password==
==Change Password==
Line 64: Line 162:
In psql, for the '''current''' user:
In psql, for the '''current''' user:


  \password  
  \password <''user_name''>


or
or
<syntaxhighlight lang='sql'>
ALTER USER user_name WITH PASSWORD 'new_password';
</syntaxhighlight>
==Granting Privileges on a Database==
<syntaxhighlight lang='sql'>
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO  <user>;
</syntaxhighlight>
Alternative from command line:
psql -h something.ak29cdi3ewgm.ca-central-1.rds.amazonaws.com -U infinity -d test_10 -c "GRANT ALL PRIVILEGES ON DATABASE test_10 TO infinity;"


ALTER USER user_name WITH PASSWORD 'new_password';
==Delete a User==
<syntaxhighlight lang='sql'>
DROP USER <username>
</syntaxhighlight>


=Tablespace=
=Tablespace=
Line 83: Line 195:
  psql
  psql
  \dt
  \dt
<syntaxhighlight lang='sql'>
SELECT * FROM pg_catalog.pg_tables;
</syntaxhighlight>
==Describe Table Structure==
\d <''table_name''>


==Create a Table==
==Create a Table==


CREATE TABLE poc_library_component
<syntaxhighlight lang='sql'>
(
CREATE TABLE poc_library_component
  "id" integer NOT NULL,
(
  "name" text,
  "id" int NOT NULL,
  "desc" text,
  "name" text,
    CONSTRAINT poc_library_component_pk PRIMARY KEY (id)
  "desc" text,
)
  "color" varchar(10),
WITH (
  CONSTRAINT poc_library_component_pk PRIMARY KEY (id)
  OIDS=FALSE
)
);
WITH (
ALTER TABLE poc_library_component OWNER TO is3_as;
  OIDS=FALSE
);
ALTER TABLE poc_library_component OWNER TO is3_as;
</syntaxhighlight>
 
The table name is case insensitive, it can be specified using upper case or lower case, it will be reported in lowercase.
 
The type names are case insensitive.
 
The column names are case sensitive.
 
For documentation on types, see: {{Internal|PostgreSQL_Concepts#Data_Types|PostgreSQL Data Types}}


Note: apparently, PostgreSQL does not like uppercases in column names.
===Create a Table Only If Does Not Exist===
<syntaxhighlight lang='sql'>
CREATE TABLE IF NOT EXISTS <table_name> ...
</syntaxhighlight>

Latest revision as of 04:12, 14 October 2023

Internal

psql Command Line

Specify the Database to Interact with

psql -d|--dbname= <database-name> ...

Providing a Password to psql in Command Line

If a database is protected by a password, psql will challenge for password interactively. That can be avoided in scripts as follows:

export PGPASSWORD=password; psql -l -h some.host -U some-user ...

In-Line SQL Commands

psql ... -c "''sql-command''"

Example:

psql -h something.ak29cdi3ewgm.ca-central-1.rds.amazonaws.com -U infinity -d test_10 -c "GRANT ALL PRIVILEGES ON DATABASE test_10 TO infinity;"

File-Stored SQL Commands

psql -f|--file= <file-name> ...

Version

Server version:

pg_config --version

Client version:

psql --version

Also, execute:

SELECT version():

Cluster

initdb

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

initdb will create a new database cluster.

initdb --locale=C -E UTF-8 /opt/brew/var/postgresql@14

Database

See

PostgreSQL Database

List Databases

psql -l [-h ... -U ... -d ...]

or

> \l

or

SELECT datname FROM pg_database;

Create a Database

Via a command:

createdb <dbname>
createdb playground

Via SQL over an established psql session:

CREATE DATABASE <dbname>;

Also see:

Database Name Case Sensitivity

Connect to a Database

Connecting to a Database

Drop a Database

Via a command:

dropdb <dbname>

Via SQL over an established psql session:

DROP DATABASE <dbname>;

This command will not work if there are users connected to the database:

ERROR:  database "..." is being accessed by other users
DETAIL:  There are 10 other sessions using the database.

The database can though be forcefully dropped as follows (enclose the database name in single quotes):

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<db-name>';

This will report a number of backends and it will take a while until all will be terminated. You may need to run it several times to insure that all backends are gone.

If there is a lot of activity on the database, you may optionally disallow new connections:

UPDATE pg_database SET datallowconn = 'false' WHERE datname = '<db-name>'; 
ALTER DATABASE <db-name> CONNECTION LIMIT 1;

When all backends are gone, the database can be dropped.

Users

The following user operations are supported.

List Users

psql
\du

or

SELECT usename FROM pg_user;

It is usename, not username.

Create User

Command line:

createuser <user_name>

or SQL client:

CREATE USER <user_name>;

Change Password

In psql, for the current user:

\password <user_name>

or

ALTER USER user_name WITH PASSWORD 'new_password';

Granting Privileges on a Database

GRANT ALL PRIVILEGES ON DATABASE <dbname> TO  <user>;

Alternative from command line:

psql -h something.ak29cdi3ewgm.ca-central-1.rds.amazonaws.com -U infinity -d test_10 -c "GRANT ALL PRIVILEGES ON DATABASE test_10 TO infinity;"

Delete a User

DROP USER <username>

Tablespace

List Tablespaces

psql
\db

Tables

List Tables

psql
\dt
SELECT * FROM pg_catalog.pg_tables;

Describe Table Structure

\d <table_name>

Create a Table

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

The table name is case insensitive, it can be specified using upper case or lower case, it will be reported in lowercase.

The type names are case insensitive.

The column names are case sensitive.

For documentation on types, see:

PostgreSQL Data Types

Create a Table Only If Does Not Exist

CREATE TABLE IF NOT EXISTS <table_name> ...