PostgreSQL DDL Operations: Difference between revisions

From NovaOrdis Knowledge Base
Jump to navigation Jump to search
 
(101 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>
To execute commands stored in files from an interactive session:
<syntaxhighlight lang='bash'>
postgres=# \i /Users/ovidiu/tmp/some-file.sql
</syntaxhighlight>
==Dump the SQL Command History==
<syntaxhighlight lang='bash'>
postgres=# \s ~/tmp/history.sql
</syntaxhighlight>


* [[PostgreSQL Operations#Subjects|PostgreSQL Operations]]
=Version=
 
Server version:
 
pg_config --version
 
Client version:
 
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 22: Line 75:


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


==Create a Database==
==Create a Database==
Via a command:
<syntaxhighlight lang='bash'>
createdb <dbname>
</syntaxhighlight>


createdb <''dbname''>
<syntaxhighlight lang='bash'>
createdb playground
</syntaxhighlight>


createdb -h localhost -U admin playground
Via SQL over an established <code>psql</code> session:
<syntaxhighlight lang='sql'>
CREATE DATABASE <dbname>;
</syntaxhighlight>


or
Also see: {{Internal|PostgreSQL_Concepts#Database_Name_Case_Sensitivity|Database Name Case Sensitivity}}


CREATE DATABASE <''dbname''>;
==Connect to a Database==
{{Internal|PostgreSQL Connecting to a Database#Overview|Connecting to a Database}}


Also see: {{Internal|PostgreSQL_Concepts#Database_Name_Case_Sensitivity|Database Name Case Sensitivity}}
==Drop a Database==


==Connect to a Database==
Via a command:


psql <''database-name''>
<syntaxhighlight lang='bash'>
dropdb <dbname>
</syntaxhighlight>


==Drop a Database==
Via SQL over an established <code>psql</code> session:


DROP DATABASE <''dbname''>;
<syntaxhighlight lang='sql'>
DROP DATABASE <dbname>;
</syntaxhighlight>


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


<font size=-2>
  ERROR:  database "..." is being accessed by other users
  ERROR:  database "..." is being accessed by other users
  DETAIL:  There are 10 other sessions using the database.
  DETAIL:  There are 10 other sessions using the database.
</font>


The database can though be forcefully dropped as follows:
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''>';
<syntaxhighlight lang='sql'>
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<db-name>';
</syntaxhighlight>


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.
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.
Line 58: Line 131:
If there is a lot of activity on the database, you may optionally disallow new connections:
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''>';  
<syntaxhighlight lang='sql'>
ALTER DATABASE <''db-name''> CONNECTION LIMIT 1;
UPDATE pg_database SET datallowconn = 'false' WHERE datname = '<db-name>';  
ALTER DATABASE <db-name> CONNECTION LIMIT 1;
</syntaxhighlight>


When all backends are gone, the database can be dropped.
When all backends are gone, the database can be dropped.
Line 73: Line 148:


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==
Line 84: Line 162:
or SQL client:
or SQL client:


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


==Change Password==
==Change Password==
Line 93: Line 173:


or
or
<syntaxhighlight lang='sql'>
ALTER USER user_name WITH PASSWORD 'new_password';
</syntaxhighlight>


ALTER USER user_name WITH PASSWORD 'new_password';
==Granting Privileges on a Database==
<syntaxhighlight lang='sql'>
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO  <user>;
</syntaxhighlight>
Alternative from command line:


==Granting Privileges on a Database==
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;"


GRANT ALL PRIVILEGES ON DATABASE <''dbname''> TO  <''user''>;
==Delete a User==
<syntaxhighlight lang='sql'>
DROP USER <username>
</syntaxhighlight>


=Tablespace=
=Tablespace=


==List Tablespaces==
==List Tablespaces==
<font size=-2>
\db
</font>
=Schema=
{{Internal|PostgreSQL_Concepts#Schema|Schema}}
==List Schemas==
<font size=-2>
\dn
</font>
==Show Current Schema==
<syntaxhighlight lang='sql'>
SHOW SEARCH_PATH;
</syntaxhighlight>
==Set Default Schema==
The default schema, which obviates the need to specify <code><schema-name>.<table-name></code>  can be set for the current session only, or permanently.


psql
To set the default schema for current session:
\db
<syntaxhighlight lang='sql'>
SET search_path = schema_name;
</syntaxhighlight>
 
To set the default schema permanently, on a per-database or per user-basis:
 
<font color=darkkhaki>TO TEST:</font>
<syntaxhighlight lang='sql'>
ALTER DATABASE db_name SET search_path TO schema_name;
ALTER ROLE role_name SET search_path TO schema_name;
</syntaxhighlight>
 
==Create a Schema==
<syntaxhighlight lang='sql'>
CREATE SCHEMA someschema;
</syntaxhighlight>
The schema name capitalization does not matter. When queried, the schema names will be returned in low caps.


==Drop a Schema==
<syntaxhighlight lang='sql'>
DROP SCHEMA someschema [CASCADE];
</syntaxhighlight>
If <code>CASCADE</code>, all included objects will be also dropped.
=Tables=
=Tables=


==List Tables==
==List Tables==


<font size=-1.5>
  psql
  psql
  \dt
  \dt
\dt someschema.*
</font>


SELECT * FROM pg_catalog.pg_tables;
<syntaxhighlight lang='sql'>
SELECT * FROM pg_catalog.pg_tables;
</syntaxhighlight>


==Describe Table Structure==
==Describe Table Structure==
<font size=-1.5>
\d [''schema-name''.]<''table_name''>
</font>
==Create a Table==
{{Internal|SQL_CREATE_ALTER_DROP_TABLE#CREATE|Standard SQL Syntax}}
<syntaxhighlight lang='sql'>
CREATE TABLE [someschema.]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;
</syntaxhighlight>
The schema name is case insensitive.
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'''.


\d <''table_name''>
For documentation on types, see: {{Internal|PostgreSQL_Concepts#Data_Types|PostgreSQL Data Types}}


==Create a Table==
===Create a Table Only If Does Not Exist===
<syntaxhighlight lang='sql'>
CREATE TABLE IF NOT EXISTS <table_name> ...
</syntaxhighlight>
 
==Update a Table==
{{External|https://www.postgresql.org/docs/current/ddl-alter.html}}
===Rename a Table===
===Add/Remove a Column===
{{Internal|SQL_CREATE_ALTER_DROP_TABLE#Add/Remove_a_Column|Standard SQL Syntax}}
<syntaxhighlight lang='sql'>
ALTER TABLE person ADD COLUMN birthday DATE;
</syntaxhighlight>
<syntaxhighlight lang='sql'>
ALTER TABLE person DROP COLUMN birthday;
</syntaxhighlight>
 
===Rename a Column===
===Change a Column's Default Value===
===Change a Column's Data Type===
===Add/Remove a Constraint===
 
====<tt>NOT NULL</tt>====
{{Internal|SQL_CREATE_ALTER_DROP_TABLE#NOT_NULL|Standard SQL Syntax}}
 
<code>not null</code> is handled differently, it cannot written as a table constraint, so to add it:
<syntaxhighlight lang='sql'>
ALTER TABLE sometable ALTER COLUMN somecolumn SET NOT NULL;
</syntaxhighlight>
 
If the table already contains entries with null values for the column, simply setting it to null will not work:
<font size=-2>
...
ALTER COLUMN somecolumn SET NOT NULL;
  (details: ERROR: column "somecolumn" contains null values (SQLSTATE 23502))
</font>
 
Adding the <code>NOT NULL</code> can still be done, by dropping the column and providing a default value:
<syntaxhighlight lang='sql'>
ALTER TABLE sometable DROP COLUMN somecolumn;
ALTER TABLE sometable ADD somecolumn text[] DEFAULT '{}'::text[] NOT NULL;
</syntaxhighlight>


CREATE TABLE poc_library_component
To remove the <code>NOT NULL</code> constraint:
(
<syntaxhighlight lang='sql'>
  "id" integer NOT NULL,
ALTER TABLE sometable ALTER COLUMN somecolumn DROP NOT NULL;
  "name" text,
</syntaxhighlight>
  "desc" text,
====Primary Key====
    CONSTRAINT poc_library_component_pk PRIMARY KEY (id)
{{Internal|SQL_CREATE_ALTER_DROP_TABLE#Primary_Key|Standard SQL Syntax}}
)
====Foreign Key====
WITH (
{{Internal|SQL_CREATE_ALTER_DROP_TABLE#Foreign_Key|Standard SQL Syntax}}
  OIDS=FALSE
);
ALTER TABLE poc_library_component OWNER TO is3_as;


Note: apparently, PostgreSQL does not like uppercases in column names.
=Views=
==Create a View==
{{Internal|SQL_CREATE_ALTER_DROP_VIEW#CREATE|Standard SQL Syntax}}

Latest revision as of 16:34, 31 May 2024

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> ...

To execute commands stored in files from an interactive session:

postgres=# \i /Users/ovidiu/tmp/some-file.sql

Dump the SQL Command History

postgres=# \s ~/tmp/history.sql

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

\db

Schema

Schema

List Schemas

\dn

Show Current Schema

SHOW SEARCH_PATH;

Set Default Schema

The default schema, which obviates the need to specify <schema-name>.<table-name> can be set for the current session only, or permanently.

To set the default schema for current session:

SET search_path = schema_name;

To set the default schema permanently, on a per-database or per user-basis:

TO TEST:

ALTER DATABASE db_name SET search_path TO schema_name;
ALTER ROLE role_name SET search_path TO schema_name;

Create a Schema

CREATE SCHEMA someschema;

The schema name capitalization does not matter. When queried, the schema names will be returned in low caps.

Drop a Schema

DROP SCHEMA someschema [CASCADE];

If CASCADE, all included objects will be also dropped.

Tables

List Tables

psql
\dt
\dt someschema.*

SELECT * FROM pg_catalog.pg_tables;

Describe Table Structure

\d [schema-name.]<table_name>

Create a Table

Standard SQL Syntax
CREATE TABLE [someschema.]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 schema name is case insensitive.

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> ...

Update a Table

https://www.postgresql.org/docs/current/ddl-alter.html

Rename a Table

Add/Remove a Column

Standard SQL Syntax
ALTER TABLE person ADD COLUMN birthday DATE;
ALTER TABLE person DROP COLUMN birthday;

Rename a Column

Change a Column's Default Value

Change a Column's Data Type

Add/Remove a Constraint

NOT NULL

Standard SQL Syntax

not null is handled differently, it cannot written as a table constraint, so to add it:

ALTER TABLE sometable ALTER COLUMN somecolumn SET NOT NULL;

If the table already contains entries with null values for the column, simply setting it to null will not work:

...
ALTER COLUMN somecolumn SET NOT NULL;
 (details: ERROR: column "somecolumn" contains null values (SQLSTATE 23502))

Adding the NOT NULL can still be done, by dropping the column and providing a default value:

ALTER TABLE sometable DROP COLUMN somecolumn;
ALTER TABLE sometable ADD somecolumn text[] DEFAULT '{}'::text[] NOT NULL;

To remove the NOT NULL constraint:

ALTER TABLE sometable ALTER COLUMN somecolumn DROP NOT NULL;

Primary Key

Standard SQL Syntax

Foreign Key

Standard SQL Syntax

Views

Create a View

Standard SQL Syntax