PostgreSQL Data Types

“Standard” types

• numeric, floating-point
• string
• Boolean
• date/time
• UUID
• Universally Unique Identifies.
• 16 bytes of storage.
• Example: d5f28c97-b962-43be-9cf8-ca1632182e8e
• XML
• XML type is just a text data type.
• The advantage is that it checks that the XML is well-formed.
• json, jsonB
• Text Search Type:
• Two data types which are designed to support full-text search.
• Money

Special Data types

• Network Address
• Network information like IP address.
• Using Network Address Types has following advantages
• Storage Space Saving
• Input error checking
• Functions like searching data by subnet
• Geometric
• Represent two-dimensional spatial objects.
• They help perform operations like rotations, scaling, translation, etc.
• Enumerated
• A set of values. 
• While inserting, it checks that the value is from the declared set.
• The ordering of the values in an enum type is the order in which the values were listed when the type was created.
• Example:
• ('sad', 'ok', 'happy’);
• In this example: ‘ok’ > ‘sad’ and < ‘happy’.
• Range
• Data in ranges.
• Can be a range of numeric and dates.
• Pseudo-Types
• special-purpose entries.
• Any, An array, Any element, Any enum, Nonarray, Cstring, Internal, Language_handler, Record, Trigger.

Custom data types - Composite Types

PostgreSQL user has the ability to create his own data types based on existing ones (composite types, ranges, arrays, enumerations).

CREATE TYPE inventory_item AS (name text, id integer)

SELECT item.name FROM on_hand WHERE item.price > 9.99;

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;

https://www.postgresql.org/docs/10/rowtypes.html

More about PostgreSQL data types: https://www.guru99.com/postgresql-data-types.html

PostgreSQL Databases

System databases

• Two Template Databases:
• Template0
• Template1
• postgres:
• The default database created using the template1 database.
• If you do not specify a database at connection time, you will be connected to the postgres database.
• DVD Rental sample database.
• Downolad: https://www.postgresqltutorial.com/postgresql-sample-database/

Template Databases

Database creation actually works by copying an existing DB.

The template DBs are used for user database creation.

• Two ‘default’ Template Databases: Template0, Template1.
• Template1 is the default template.
• The 2 templates contains the same data.
• Template0 is more empty/"virgin" DB.
• New encoding and locale settings can be specified when copying template0, whereas a copy of template1 must use the same settings it does.
• More templates can be created.

Tablespace

Tablespaces define locations in the file system where the files representing database objects can be stored.

Tablespace creation must be done as a database superuser.

PostgreSQL comes with two default tablespaces:

• pg_default: stores all user data (the default tablespace).
• pg_global: stores all global data.

One tablespace can be used by multiple databases.

The physical location of tablespaces: <PostgreSQLFolder>\12\data .

Tables, indexes, and entire databases can be assigned to particular tablespaces: 

    CREATE TABLE foo(i int) TABLESPACE space1;

Database Creation

Database is created by cloning one of the templates.

Database is created in one of the tablespaces.

CREATE DATABASE name

 [ [ WITH ] [ OWNER [=] user_name ]

 [ TEMPLATE [=] template ]

 [ ENCODING [=] encoding ]

 [ LC_COLLATE [=] lc_collate ]

 [ LC_CTYPE [=] lc_ctype ]

 [ TABLESPACE [=] tablespace ]

 [ CONNECTION LIMIT [=] connlimit ] ]

PostgreSQL Configurations

Configurations save in postgresql.conf , Located in <postgresqlFolder>\12\data.

Configurations can be set:
Edit postgresql.conf file itself.

• Via postgres command in the command-line.
• Some parameters can be changed in individual SQL sessions with the SET command.
• Some parameters can be changed with “ALTER SYSTEM” command.
• ALTER SYSTEM SET configuration_parameter { TO | = } { value | 'value' | DEFAULT }
• ALTER SYSTEM RESET configuration_parameter
• ALTER SYSTEM RESET ALL

Configurations can be viewed:

• select * from pg_settings
• At pgAdmin.

PostgreSQL as Object-Relational Database Management System (ORDBMS)

ORDBMS = a relational database that supports some object oriented features.

Table inheritance

Create a table from a (table-)type.

create type person_type as (id integer, firstname text, lastname text);

create table person of person_type;

Create a table inherits from other table.

create table person (id integer, firstname text, lastname text);

create table person_with_dob ( dob date ) inherits (person);

CREATE TABLE City (

  CityID INT4,

  Cityname  varchar(50),

  Country text

);

CREATE TABLE Capital (

  CountryCode char(3)

) INHERITS (City);

SELECT * FROM City;

SELECT * FROM Capital;

INSERT INTO City (CityID, Cityname, Country) 

VALUES (1, 'Tel Aviv', 'Israel');

INSERT INTO Capital (CityID, Cityname, Country, CountryCode) 

VALUES (2, 'Jerusalem', 'Israel', 'ISR’);

SELECT * FROM City;

SELECT * FROM Capital;

SELECT * FROM ONLY City;

https://www.postgresql.org/docs/8.4/tutorial-inheritance.html

Definition of methods on the type

create table person (id integer, firstname text, lastname text);

create function fullname(p_row person) returns text

As

$$

    select concat_ws(' ', p_row.firstname, p_row.lastname);

$$

language sql;

select p.fullname from person p;

Function overloading

PostgreSQL allows more than one function to have the same name, so long as the arguments are different.

https://www.postgresqltutorial.com/plpgsql-function-overloading/

Complex types

Custom data types - Composite Types

PostgreSQL user has the ability to create his own data types based on existing ones (composite types, ranges, arrays, enumerations).

CREATE TYPE inventory_item AS (name text, id integer)

SELECT item.name FROM on_hand WHERE item.price > 9.99;

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;

PostgreSQL Overview

PostgreSQL is an open source object-relational database system.

PostgreSQL is probably the most advanced database in the open source relational database market.
PostgreSQL uses and extends the SQL language combined with other features.

PostgreSQL superuser is the user named Postgres.

Supported platforms

PostgreSQL is available for all operating systems: Windows, Linux, Unix and macOS.

Supported Cloud vendors

Microsoft Azure

https://azure.microsoft.com/en-us/services/postgresql/

aws Amazon

https://aws.amazon.com/free/database/

IBM Cloud

https://www.ibm.com/cloud/databases-for-postgresql

ScaleGrid

https://scalegrid.io/postgresql.html

Google cloud SQL for PostgreSQL

https://cloud.google.com/sql/docs/postgres/

ElephantSQL PostgreSQL as a Service

https://www.elephantsql.com/

And more ...

Installation processes

Download PostgreSQL: https://www.postgresql.org/download

During the installation you will set:

• Database superuser (postgres) password.
• Port (default: 5432).

Client Tools

psql

psql is a command-line program.

psql can be used to enter SQL queries directly, or execute them from a file.

pgAdmin

pgAdmin is the PostgreSQL tool.

It's an Open Source administration and development platform for PostgreSQL.

pgAdmin is installed with PostgreSQL .

https://www.pgadmin.org/

A little bit of history

PostgreSQL evolved from the Ingres project at the University of California, Berkeley. At 1986 Michael Stonebraker and his colleagues developed Postgres.

Versions were released from time to time with more features, and in 1995 Initial release as Postgres95 was released.

In 1996 the project was renamed to PostgreSQL in order to reflect its support for SQL.

In 1997 the initial PostgreSQL release formed version 6.0.

Useful links

• PostgreSQL site: https://www.postgresql.org/
• postgresql.git: https://git.postgresql.org/gitweb/?p=postgresql.git
• PostgreSQL ODBC driver: https://www.progress.com/campaigns/datadirect/ppc/postgresql-odbc
• Configuration calculator: https://pgtune.leopard.in.ua/#/
• What is PostgreSQL? https://www.guru99.com/introduction-postgresql.html

SQL Server on GCP - You do not have permission to run the RECONFIGURE statement

Error message:

Msg 15247, Level 16, State 1, Procedure sp_configure, Line 105 [Batch Start Line 0]

User does not have permission to perform this action.

Msg 5812, Level 14, State 1, Line 2

You do not have permission to run the RECONFIGURE statement.

The case:

Trying to set configurations (sp_configure and RECONFIGURE)

Solution:

There are no permissions to run sp_configure and RECONFIGURE.

Configuring database flags is done in the GCP portal.

Can not connect SQL Server instance on GCP from SSMS

Problem:
Can not connect SQL Server instance on GCP from SSMS (SQL Server Management Studio).

Solution:
Create Authorized networks:
Go to Connections page, under Connectivity → Public IP: