« PostgreSQL » : différence entre les versions

De Banane Atomic
Aller à la navigationAller à la recherche
Ligne 62 : Ligne 62 :
! Description
! Description
|-
|-
| integer || int, int4 || || signed four-byte integer
| integer || int, int4 || int || signed four-byte integer
|-
|-
| smallint || int2 || || signed two-byte integer
| smallint || int2 || short || signed two-byte integer
|-
|-
| bigint || int8 || || signed eight-byte integer
| 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)
|-
| 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)
|-
| date || || Date || calendar date (year, month, day)
|-
| time [ (p) ] [ without time zone ] || || Time || time of day (no time zone)
|-
| time [ (p) ] with time zone || timetz || Time || time of day, including time zone
|}
|}



Version du 12 février 2024 à 01:06

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

Table

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

CREATE TABLE [table] (
    [column]    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    [column]    varchar(40) NOT NULL UNIQUE CHECK ([column] <> '')
);

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
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)
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)
date Date calendar date (year, month, day)
time [ (p) ] [ without time zone ] Time time of day (no time zone)
time [ (p) ] with time zone timetz Time time of day, including time zone

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]