PostgreSQL DDL Operations: Difference between revisions
(69 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: | |||
psql --version | |||
Also, execute: | |||
SELECT version(): | |||
{{External|https://www.postgresql.org/docs/ | =<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 | <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 67: | ||
or | 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== | |||
Via a command: | |||
<syntaxhighlight lang='bash'> | |||
dropdb <dbname> | |||
</syntaxhighlight> | |||
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: | 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'''): | ||
<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 123: | ||
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: | ||
<syntaxhighlight lang='sql'> | |||
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 140: | ||
or | or | ||
<syntaxhighlight lang='sql'> | |||
SELECT usename FROM pg_user; | |||
</syntaxhighlight> | |||
It is <code>usename</code>, not <code>username</code>. | |||
==Create User== | ==Create User== | ||
Line 84: | Line 154: | ||
or SQL client: | or SQL client: | ||
<syntaxhighlight lang='sql'> | |||
CREATE USER <user_name>; | |||
</syntaxhighlight> | |||
==Change Password== | ==Change Password== | ||
Line 93: | Line 165: | ||
or | or | ||
<syntaxhighlight lang='sql'> | |||
ALTER USER user_name WITH PASSWORD 'new_password'; | |||
</syntaxhighlight> | |||
==Granting Privileges on a Database== | ==Granting Privileges on a Database== | ||
<syntaxhighlight lang='sql'> | |||
GRANT ALL PRIVILEGES ON DATABASE <dbname> TO <user>; | |||
</syntaxhighlight> | |||
Alternative from command line: | |||
GRANT ALL PRIVILEGES ON 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;" | ||
==Delete a User== | ==Delete a User== | ||
<syntaxhighlight lang='sql'> | |||
DROP USER <username> | |||
</syntaxhighlight> | |||
=Tablespace= | =Tablespace= | ||
==List Tablespaces== | ==List Tablespaces== | ||
<font size=-2> | |||
\db | |||
</font> | |||
=Schema= | |||
\ | ==List Schemas== | ||
<font size=-2> | |||
\dn | |||
</font> | |||
=Tables= | =Tables= | ||
Line 113: | Line 199: | ||
==List Tables== | ==List Tables== | ||
<font size=-2> | |||
psql | psql | ||
\dt | \dt | ||
</font> | |||
<syntaxhighlight lang='sql'> | |||
SELECT * FROM pg_catalog.pg_tables; | |||
</syntaxhighlight> | |||
==Describe Table Structure== | ==Describe Table Structure== | ||
<font size=-2> | |||
\d [''schema-name''.]<''table_name''> | |||
</font> | |||
==Create a Table== | |||
<syntaxhighlight lang='sql'> | |||
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; | |||
</syntaxhighlight> | |||
==Create a Table== | 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}} | |||
===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=== | |||
===Rename a Column=== | |||
===Change a Column's Default Value=== | |||
===Change a Column's Data Type=== | |||
===Add/Remove a Constraint=== | |||
====<tt>NOT NULL</tt>==== | |||
<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> | |||
To remove the <code>NOT NULL</code> constraint: | |||
<syntaxhighlight lang='sql'> | |||
ALTER TABLE sometable ALTER COLUMN somecolumn DROP NOT NULL; | |||
</syntaxhighlight> |
Revision as of 21:10, 6 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> ...
Version
Server version:
pg_config --version
Client version:
psql --version
Also, execute:
SELECT version():
Cluster
initdb
initdb
will create a new database cluster.
initdb --locale=C -E UTF-8 /opt/brew/var/postgresql@14
Database
See
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:
Connect 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
List Schemas
\dn
Tables
List Tables
psql \dt
SELECT * FROM pg_catalog.pg_tables;
Describe Table Structure
\d [schema-name.]<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:
Create a Table Only If Does Not Exist
CREATE TABLE IF NOT EXISTS <table_name> ...
Update a Table
Rename a Table
Add/Remove a Column
Rename a Column
Change a Column's Default Value
Change a Column's Data Type
Add/Remove a Constraint
NOT NULL
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;