Postgres

psql command not found

You want to run a Postgres script from bash

andrey@olegovich-10:/mnt/c/Users/olegovich$ psql -h localhost -p 5432 -U andrei

but you get this error

-bash: psql: command not found

This means the path to Postgres is not in $PATH

To see what is in $PATH just run

echo $PATH

/home/andrei/bin:/home/andrei/.local/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/mnt/c/Program Files (x86)/Common Files/Oracle/Java/javapath_target_1128437:/mnt/c/ProgramData/Oracle/Java/javapath_target_5252250:/mnt/c/Windows/System32:/mnt/c/Windows:/mnt/c/Windows/System32/wbem:/mnt/c/Windows/System32/WindowsPowerShell/v1.0:/mnt/c/Program Files/OpenVPN/bin:/mnt/c/Program Files (x86)/Microsoft SQL Server/Client SDK/ODBC/130/Tools/Binn:/mnt/c/Program Files (x86)/Microsoft SQL Server/140/Tools/Binn:/mnt/c/Program Files (x86)/Microsoft SQL Server/140/DTS/Binn:/mnt/c/Program Files (x86)/Microsoft SQL Server/140/Tools/Binn/ManagementStudio:/mnt/c/Program Files/MiKTeX 2.9/miktex/bin/x64:/mnt/c/Users/andreyolegovich_ru/Documents/Software/axis2-1.6.2:/mnt/c/Users/andreyolegovich_ru/Documents/Software/axis2-1.6.2/bin:/mnt/c/Windows/System32/OpenSSH:/mnt/c/Program Files/TortoiseSVN/bin:/mnt/c/Program Files/Microsoft SQL Server/Client SDK/ODBC/130/Tools/Binn:/mnt/c/Program Files/Microsoft SQL Server/140/Tools/Binn:/mnt/c/Program Files/Microsoft SQL Server/140/DTS/Binn:/mnt/c/Program Files (x86)/Intel/Intel(R) Management Engine Components/DAL:/mnt/c/Program Files/Intel/Intel(R) Management Engine Components/DAL:/mnt/c/Program Files/TortoiseGit/bin:/mnt/c/Program Files/Git/cmd:/mnt/c/Program Files/nodejs:/mnt/c/Program Files/Intel/WiFi/bin:/mnt/c/Program Files/Common Files/Intel/WirelessCommon:/mnt/c/Program Files/PuTTY:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Continuum/anaconda3:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Continuum/anaconda3/Library/mingw-w64/bin:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Continuum/anaconda3/Library/bin:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Continuum/anaconda3/Scripts:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Python/Python36/Scripts:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Python/Python36:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Microsoft/WindowsApps:/mnt/c/Users/andreyolegovich_ru/AppData/Local/atom/bin:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Python/Python36-32:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Python/Python36-32/Scripts:/mnt/c/Program Files (x86)/Nmap:/mnt/c/Program Files (x86)/Mozilla Firefox:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Microsoft/WindowsApps:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Fiddler:/mnt/c/Program Files/JetBrains/PyCharm Community Edition 2018.3.2/bin:/mnt/c/Users/andreyolegovich_ru/AppData/Roaming/npm:/mnt/c/Program Files/Intel/WiFi/bin:/mnt/c/Program Files/Common Files/Intel/WirelessCommon:/mnt/c/Users/andreyolegovich_ru/AppData/Local/Programs/Microsoft VS Code/bin:/snap/bin

ERROR: character with byte sequence 0xd0 0x9a in encoding "UTF8"
has no equivalent in encoding "WIN1252"

You probably created a database and even managed to import something into it, for example from a .csv file.

But SELECT * FROM table; no longer works, because the encoding of the database and the encoding of the file do not match.

Perhaps you already tried to set SET CLIENT_ENCODING TO 'utf8'; explicitly when importing the file. But since WIN1252 is the encoding of the database, that method did not work.

You have to bring the file and the database to the same encoding; recreate the database in utf8, for example.

How to check the encodings I described above: Checking database encodings

How to set the encoding when creating a database: Creating a database

ERROR: database "db" is being accessed by other users

If you run DROP DATABASE db; and get

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

It means a connection to the database is still open somewhere. For example, you opened it through pgAdmin.

You have to find that connection and close it

FATAL password authentication failed for user postgres

If you log in to pgAdmin but do not remember the password, you can change it in the terminal

sudo su - postgres
psql
postgres=# ALTER USER postgres PASSWORD 'new_password';

ALTER ROLE

ERROR: could not open file "/home/user&hellip" for reading: Permission denied

If you try to read from a file and get

ERROR: could not open file "/home/user/file.csv" for reading: Permission denied HINT: COPY FROM instructs the PostgreSQL server process to read a file. You may want a client-side facility such as psql's \copy. SQL state: 42501

It means postgres has insufficient rights to read the file. Simply adding read rights like

chmod +r file.csv

most likely will not solve the problem.

As an option, I suggest moving the file you need to the directory /tmp

cp /home/user/file.csv /tmp

ERROR: COPY quote must be a single one-byte character

If you try to read from a file and get

ERROR: COPY quote must be a single one-byte character
SQL state: 0A000

There is most likely a stray character in QUOTE, for example

QUOTE '\"'

Replace it with

QUOTE '"'

ERROR: date/time field value out of range

If you try to read from a .csv file and get

ERROR: date/time field value out of range: "" HINT: Perhaps you need a different "datestyle" setting. CONTEXT: "" SQL state: 22008

Your current datestyle most likely does not match the one used in the .csv file.

datestyle is the order of the date parts. It can be Day - Month - Year (DDMMYYYY), Year - Month - Day (YYYYMMDD) or, for example, the American style Month - Day - Year (MMDDYYYY)

All this matters if the column type is set to date. You can change the type to char and then datestyle no longer has to be configured.

There are many styles, and when they do not match it turns out that the month gets a value greater than 12.

As an option, you can temporarily change your datestyle before running the script.

For example, if you need to import data from a .csv with the American style, add this before the import

set datestyle to "US";

Job for postgresql.service failed because the control process exited with error code

Very often this error appears because the database was not initialized.

For example, you ran

sudo systemctl start postgresql

Job for postgresql.service failed because the control process exited with error code. See "systemctl status postgresql.service" and "journalctl -xe" for details.

Check the status

systemctl status -l postgresql.service

● postgresql.service - PostgreSQL database server Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled; vendor preset: disabled) Active: failed (Result: exit-code) since Wed 2021-06-09 12:10:31 EEST; 1min 18s ago Process: 7223 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE) Jun 09 12:10:31 localhost.localdomain systemd[1]: Starting PostgreSQL database server... Jun 09 12:10:31 localhost.localdomain systemd[1]: postgresql.service: control process exited, code=exited status=1 Jun 09 12:10:31 localhost.localdomain systemd[1]: Failed to start PostgreSQL database server. Jun 09 12:10:31 localhost.localdomain systemd[1]: Unit postgresql.service entered failed state. Jun 09 12:10:31 localhost.localdomain systemd[1]: postgresql.service failed.

Article author: Andrei Olegovich