« PostgreSQL » : différence entre les versions

De Banane Atomic
Aller à la navigationAller à la recherche
 
(38 versions intermédiaires par le même utilisateur non affichées)
Ligne 5 : Ligne 5 :


= Database =
= Database =
<kode lang='pgsql'>
-- list databases
\l
-- connect to a database
\c myDatabaseName
-- drop a database
drop [if exists] myDatabaseName;
</kode>
<kode lang='bash'>
<kode lang='bash'>
# create a database
# create a database
Ligne 11 : Ligne 22 :
# connect to a database
# connect to a database
psql -d myDatabaseName
psql -d myDatabaseName
# drop a database
dropdb myDatabaseName
</kode>
</kode>


= [https://www.postgresql.org/docs/current/sql-createtable.html Create Table] =
<kode lang='pgsql'>
<kode lang='pgsql'>
-- connect to a database
-- show summary information about all tables in the current database
\c myDatabaseName
\dt
 
CREATE TABLE [table] (
    [column]    smallint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    [column]    text NOT NULL UNIQUE CHECK ([column] <> '')
);
</kode>
 
{{info | PostgreSQL automatically creates indexes on primary keys and unique constraints.}}
 
== [https://www.postgresql.org/docs/current/tutorial-fk.html Foreign key] ==
<kode lang='pgsql'>
CREATE TABLE group (
    id smallint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);
 
CREATE TABLE user (
    group_id smallint references group(id),
);
</kode>
 
== [https://www.postgresql.org/docs/current/ddl-generated-columns.html Generated column] ==
{{info | PostgreSQL currently implements only stored generated columns}}
<kode lang='pgsql'>
CREATE TABLE people (
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);
</kode>
</kode>


= Table =
= [https://www.postgresql.org/docs/current/sql-altertable.html Alter Table] =
<kode lang='pgsql'>
<kode lang='pgsql'>
-- show summary information about all tables in the current database
ALTER TABLE [table]
\dt
    DROP COLUMN [column] RESTRICT;
 
ALTER TABLE [table]
    ADD COLUMN [column] [type] DEFAULT 'xxx';
 
ALTER TABLE [table]
    ALTER COLUMN [column] TYPE [type];
</kode>
</kode>


Ligne 28 : Ligne 76 :
-- list all users and their permission levels
-- list all users and their permission levels
\du
\du
CREATE USER [user] WITH PASSWORD 'xxxxxx';
</kode>
</kode>
<kode lang='bash'>
createuser --interactive
dropuser <user>
</kode>
* [https://www.postgresql.org/docs/current/sql-createuser.html CREATE USER]
= [https://www.postgresql.org/docs/current/datatype.html Data types] =
{| class="wikitable wtp wtmono1 wtmono2 wtmono3"
! Name
! Alias
! .NET
! Description
|-
| integer || int, int4 || int || signed four-byte integer
|-
| smallint || int2 || short || signed two-byte integer (-32,768 to 32,767)
|-
| bigint || int8 || long || signed eight-byte integer
|-
| numeric [ (p, s) ] || decimal [ (p, s) ] || decimal || exact numeric of selectable precision
|-
| money || ||  || currency amount on 8 bytes (-92233720368547758.08 to +92233720368547758.07)
|-
| text || || string || variable-length character string
|-
| character varying(n) || varchar(n) || string || variable-length character string
|-
| character(n) || char(n) || char[n] || fixed-length character string
|-
| boolean || bool || bool || logical Boolean (true/false)
|-
| timestamp || || Datetime || date and time (no time zone)
|-
| timestamp with time zone || timestamptz || Datetime || date and time, including time zone
|-
| date || || Date || calendar date (year, month, day)
|-
| time || || Time || time of day (no time zone)
|-
| time with time zone || timetz || Time || time of day, including time zone
|}
{{info |
* no one-byte integer
* no unsigned integer}}


= Service =
= Service =
Ligne 35 : Ligne 132 :
</kode>
</kode>


= Installation =
= [https://wiki.archlinux.org/title/PostgreSQL#Installation Installation] =
<kode lang='bash'>
<kode lang='bash'>
# will install postgresql-libs, postgresql and create a system user called postgres
# will install postgresql-libs, postgresql and create a system user called postgres
Ligne 43 : Ligne 140 :
== Configuration ==
== Configuration ==
<kode lang='bash'>
<kode lang='bash'>
# create the cluster directory if needed
sudo mkdir /var/lib/postgres
sudo chown postgres:postgres /var/lib/postgres
# switch to the PostgreSQL user
# switch to the PostgreSQL user
sudo -iu postgres
sudo -iu postgres


# initialize the database cluster
# initialize the database cluster (postgres user)
initdb -D /var/lib/postgres/data
initdb -D /var/lib/postgres/data
# $LANG (en_US.UTF-8) is used to deduce the locale and the encoding
# $LANG (en_US.UTF-8) is used to deduce the locale and the encoding
# or can be defined manually: --locale=en_US.UTF-8 -E UTF8
# or can be defined manually:
initdb --locale=C.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums
# --data-checksums enable data checksumming


# start the service
# start the service
sc-start postgresql.service
sc-start postgresql.service


# create a user
# login (postgres user)
psql
 
# create a user (postgres user)
# If the new user has the same name as your Linux user, it allows you to access the PostgreSQL database shell without having to specify a user to login.
# If the new user has the same name as your Linux user, it allows you to access the PostgreSQL database shell without having to specify a user to login.
createuser --interactive
createuser --interactive
# create a database (postgres user)
createdb dbtest
# login (current user)
psql -d dbtest
</kode>
=== Access rights ===
<filebox fn='/var/lib/postgres/data/pg_hba.conf' lang='bash'>
# all local users can access to any database including superuser database
local  all  all  trust
# only local postgres user can login
local  all  postgres  peer
</filebox>
= Installation on Windows =
<kode lang='ps'>
winget install PostgreSQL.PostgreSQL
</kode>
{{info | default user and password: {{boxx|postgres}}}}
<kode lang='ps'>
# connection to the database
C:\Program Files\PostgreSQL\16\bin\psql.exe -U [user] -d [db]
</kode>
</kode>

Dernière version du 12 février 2024 à 15:15

Links

Database

Pgsql.svg
-- list databases
\l

-- connect to a database
\c myDatabaseName

-- drop a database
drop [if exists] myDatabaseName;
Bash.svg
# create a database
createdb myDatabaseName

# connect to a database
psql -d myDatabaseName

# drop a database
dropdb myDatabaseName

Create Table

Pgsql.svg
-- show summary information about all tables in the current database
\dt

CREATE TABLE [table] (
    [column]    smallint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    [column]    text NOT NULL UNIQUE CHECK ([column] <> '')
);
PostgreSQL automatically creates indexes on primary keys and unique constraints.

Foreign key

Pgsql.svg
CREATE TABLE group (
    id smallint PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
);

CREATE TABLE user (
    group_id smallint references group(id),
);

Generated column

PostgreSQL currently implements only stored generated columns
Pgsql.svg
CREATE TABLE people (
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm / 2.54) STORED
);

Alter Table

Pgsql.svg
ALTER TABLE [table]
    DROP COLUMN [column] RESTRICT;

ALTER TABLE [table]
    ADD COLUMN [column] [type] DEFAULT 'xxx';

ALTER TABLE [table]
    ALTER COLUMN [column] TYPE [type];

Users and permissions

Pgsql.svg
-- list all users and their permission levels
\du

CREATE USER [user] WITH PASSWORD 'xxxxxx';
Bash.svg
createuser --interactive
dropuser <user>

Data types

Name Alias .NET Description
integer int, int4 int signed four-byte integer
smallint int2 short signed two-byte integer (-32,768 to 32,767)
bigint int8 long signed eight-byte integer
numeric [ (p, s) ] decimal [ (p, s) ] decimal exact numeric of selectable precision
money currency amount on 8 bytes (-92233720368547758.08 to +92233720368547758.07)
text string variable-length character string
character varying(n) varchar(n) string variable-length character string
character(n) char(n) char[n] fixed-length character string
boolean bool bool logical Boolean (true/false)
timestamp Datetime date and time (no time zone)
timestamp with time zone timestamptz Datetime date and time, including time zone
date Date calendar date (year, month, day)
time Time time of day (no time zone)
time with time zone timetz Time time of day, including time zone
  • no one-byte integer
  • no unsigned integer

Service

Bash.svg
sc-start postgresql.service

Installation

Bash.svg
# will install postgresql-libs, postgresql and create a system user called postgres
sudo pacman -S postgresql

Configuration

Bash.svg
# create the cluster directory if needed
sudo mkdir /var/lib/postgres
sudo chown postgres:postgres /var/lib/postgres

# switch to the PostgreSQL user
sudo -iu postgres

# initialize the database cluster (postgres user)
initdb -D /var/lib/postgres/data
# $LANG (en_US.UTF-8) is used to deduce the locale and the encoding
# or can be defined manually:
initdb --locale=C.UTF-8 --encoding=UTF8 -D /var/lib/postgres/data --data-checksums
# --data-checksums enable data checksumming

# start the service
sc-start postgresql.service

# login (postgres user)
psql

# create a user (postgres user)
# If the new user has the same name as your Linux user, it allows you to access the PostgreSQL database shell without having to specify a user to login.
createuser --interactive

# create a database (postgres user)
createdb dbtest
# login (current user)
psql -d dbtest

Access rights

/var/lib/postgres/data/pg_hba.conf
# all local users can access to any database including superuser database
local  all  all  trust
# only local postgres user can login
local   all  postgres  peer

Installation on Windows

Ps.svg
winget install PostgreSQL.PostgreSQL
default user and password: postgres
Ps.svg
# connection to the database
C:\Program Files\PostgreSQL\16\bin\psql.exe -U [user] -d [db]