Embedded Files
DATABASE /
The PostgreSQL (or postgres) is a relational database system. Free, popular, powerful.
Database names, column names, index names etc. should be lowercase. Best practice is that the name parts are separated by underlines:
namepart1_namepart2_namepart3 (e.g. customer_addresses)
1. SETTING UP
During installation, important parameters are asked from the user:
database root username and password
communication port (default: 5432)
default user (postgres) username and password
After installation, find the bin directory (e.g. c:\Program Files\PostgreSQL\15\bin\), and add it to the PATH system variable.
2. CONSOLE
The console can be reached by launching system console and typing psql command, or launching the SQL SHELL provided by the installer.
The console will ask for user password for a local user different from the default postgres user (or whatever user was given during installation).
Workaround to use the system console:
psql -U <username> -W command, or
psql -d <db-name> -U <username> -W, or
psql -h <host address> -d <db-name> -U <username> -W, or
psql "sslmode=require host=<db-address> dbname=<db-name> user=<username>"
Saving a query to a file requires an opening and a closing command. The opening command changes the direction of output from the terminal to the given file. After this all output will append the given file. The closing command rewires the output to the terminal (file was saved continuously until this point).
EXAMPLE FOR SAVING A QUERY TO A FILE:
EXAMPLE FOR CONSOLE SQL COMMANDS:
3. INDEX (KEY) TYPES
In postgres the column types can be used for defining index (or key).
In a postgres table, indexes (keys) can be defined on a single column or on more columns. In the latter case, the combination of the columns will be indexed.
Primary key can be only one over a table. By defining it, the search and navigation in the table becomes much faster.
Any number of additional keys can be defined over a table.
Postgres creates index table (key table) per key. Such table contains existing values of the key and pointers for the row of the table. Searching in these tables is faster than searching in a whole table due to accessing much less data.
Any column type can be used in indexing in case that type can produce comparable values.
Primary key values must be not just comparable but unique as well.
In postgres, there are column types many administrators and developers use, see below.
Although the types above are used frequently, in case the [1, Long.MAX_VALUE] interval is enough for the table IDs, the next type can be used for primary index as well:
bigint
This type is matching with the Java long or Long types.
Also, the Java int and Integer types are matching with the next postgres type:
integer
4. CHARACTER TYPES
PostgreSQL provides three primary character types:
CHARACTER(n) or CHAR(n): fixed-length, blank padded
CHARACTER VARYINGING(n) or VARCHAR(n): variable-length with length limit
TEXT: variable unlimited length
(n is a natural number.)
All three types can be used for storing a single character as well as storing a text.
Because their purposes overlap, proper usage should express the data the specific column will be rendered to.
CHAR and VARCHAR, n is given:
the type can store text up to n characters (length in characters)
when trying to put longer text into, will issue an error
when trying to put longer text into, and the over-length part contains only spaces, will truncate the spaces to size and store the text
when a text explicitly casts to CHAR(n) or VARCHAR(n), will truncate to size and store the text
CHAR, n is not given:
equals with CHAR(1), single character mode
VARCHAR, n is not given:
will behave as a TEXT type with unlimited capacity
will have no difference in performance compared to TEXT
If not defined explicitly with the @Column annotation, Spring will use the next default types.
If needed explicitly, map the entity (domain) fields based upon the next.
Types definitions are valid in uppercase and lowercase forms as well (e.g. text and TEXT).
The array marking ([]) is equal with the separate array keyword (e.g. varchar(3)[] and varchar(3) array).
5. BOOLEAN, NUMERIC AND ARRAY TYPES
For wrapper types (arrays are included), the same PostgreSQL types can be used.
For primitive and wrapper arrays, for wrapper Collection types, all the above types can be used with [] marking, e.g.:
BIT[], INTEGER[]
6. OTHER TYPES
Java Spring Data JPA and Hibernate in the background does automated mapping to and from PostgreSQL types via SQL/JDBC. The latter serves as a bridge.
Mapping of the most important types are in the next table.
Most important PostgreSQL-Java type pairs:
bigint - long
bit/boolean - boolean
char - char
bytea - byte[]
7. BACKEND CONNECTION PARAMETERS
These parameters are declared in the application.properties or a Yaml file under the project directory.
Visit SPRING APPLICATION PROPERTIES page for more details.
8. LIVE DATABASE, BACKUP, RESTORE
When dealing with live database or database of which structure we should not modify, you must carefully define the database connection mode from create, drop, create-drop etc. to none. This instructs Hibernate not to touch the structure nor the data.
The data and structure in a database should be fall under regular backup. This can be done e.g. in the GUI of PostgreSQL. On the database root bring on the popup menu and select Backup or similar option.
Under the Query Options tab, do not forget to mark Include DROP DATABASE. This means upon restoring the SQL commands will firstly delete the database, then create it. If you do not select this option, restoring will fail due to existing schemes.
When restoring is necessary, in some cases the backup file which contains excessive SQL commands, when the existing database is not empty, another error will raise. Because you want to overwrite the original database contents with the saved one, you must delete the database before restoring.
In order to so this, bring up the popup menu on the database root, and select Delete (or if there are live connections you want to close immediately, choose the Delete(Force) option).
Then, Create the database with the very same name and parameters the original was created before, and then from the popup, choose the Restore option.
When the database was created via Spring Data JPA (Hibernate), it is advised to create the database by starting the application with application properties ideal for drop first, then for create-only.
Google Sites
Report abuse