Databases

Databases

52 bookmarks
Newest
A Look at PostgreSQL User-defined Data Types
A Look at PostgreSQL User-defined Data Types
This tutorial shows you how to create PostgreSQL user-defined data type using CREATE DOMAIN and CREATE TYPE statements.
·neon.tech·
A Look at PostgreSQL User-defined Data Types
PostgreSQL Generated Columns
PostgreSQL Generated Columns
In this tutorial, you will learn about PostgreSQL generated columns whose values are automatically calculated from other columns.
In PostgreSQL, a generated column is a special type of column whose values are automatically calculated based on expressions or values from other columns. A generated column is referred to as a computed column in the SQL Server or a virtual column in Oracle .
There are two kinds of generated columns: Stored: A stored generated column is calculated when it is inserted or updated and occupies storage space. Virtual: A virtual generated column is computed when it is read and does not occupy storage space.
A virtual generated column is like a view, whereas a stored generated column is similar to a materialized view. Unlike a material view, PostgreSQL automatically updates data for stored generated columns.
PostgreSQL currently implements only stored generated columns.
·neon.tech·
PostgreSQL Generated Columns
PostgreSQL Sequences
PostgreSQL Sequences
In this tutorial, you will learn about the PostgreSQL sequences and how to use a sequence object to generate a sequence of numbers.
In PostgreSQL, a sequence is a database object that allows you to generate a sequence of unique integers. Typically, you use a sequence to generate a unique identifier for a primary key in a table. Additionally, you can use a sequence to generate unique numbers across tables. To create a new sequence, you use the CREATE SEQUENCE statement.
Listing all sequences in a database To list all sequences in the current database, you use the following query: SELECT relname sequence_name FROM pg_class WHERE relkind = 'S';
·neon.tech·
PostgreSQL Sequences
PostgreSQL Identity Column
PostgreSQL Identity Column
This tutorial shows you how to use the GENERATED AS IDENTITY constraint to create the PostgreSQL identity column for a table.
PostgreSQL version 10 introduced a new constraint GENERATED AS IDENTITY that allows you to automatically assign a unique number to a column.
The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the good old SERIAL column.
The following illustrates the syntax of the GENERATED AS IDENTITY constraint: column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]
In this syntax: The type can be SMALLINT, INT, or BIGINT. The GENERATED ALWAYS instructs PostgreSQL to always generate a value for the identity column. If you attempt to insert (or update) values into the GENERATED ALWAYS AS IDENTITY column, PostgreSQL will issue an error. The GENERATED BY DEFAULT instructs PostgreSQL to generate a value for the identity column. However, if you supply a value for insert or update, PostgreSQL will use that value to insert into the identity column instead of using the system-generated value.
PostgreSQL allows a table to have more than one identity column. Like the SERIAL, the GENERATED AS IDENTITY constraint also uses the SEQUENCE object internally.
To fix the error, you can use the OVERRIDING SYSTEM VALUE clause as follows: INSERT INTO color (color_id, color_name) OVERRIDING SYSTEM VALUE VALUES(2, 'Green');
Alternatively, you can use GENERATED BY DEFAULT AS IDENTITY instead.
Because the GENERATED AS IDENTITY constraint uses the SEQUENCE object, you can specify the sequence options for the system-generated values.
For example, you can specify the starting value and the increment as follows: DROP TABLE color; CREATE TABLE color ( color_id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10), color_name VARCHAR NOT NULL );
In this example, the system-generated value for the color_id column starts with 10 and the increment value is also 10.
·neon.tech·
PostgreSQL Identity Column
AI Database Design Flowchart Generator
AI Database Design Flowchart Generator
Unlock efficient database design with our AI-powered Database Design Flowchart Generator! Experience fast, accurate, and intuitive creation of complex database schemas. Save time, reduce errors, and streamline your workflow — start designing smarter today!
·taskade.com·
AI Database Design Flowchart Generator
Sequel
Sequel
Converse with your database using natural language
·sequel.sh·
Sequel
Building data-centric apps with a reactive relational database
Building data-centric apps with a reactive relational database
We're exploring an approach to simplifying app development: storing all application and UI state in a client-side reactive relational database that provides a structured dataflow model.
·riffle.systems·
Building data-centric apps with a reactive relational database
SQL Server "Codify" Function
SQL Server "Codify" Function
This function will jump-start the process of converting long descriptions into meaningful abbreviations. It's great for creating "Code" columns in lookup tables.
·nolongerset.com·
SQL Server "Codify" Function
Data Quality Rules: The Definitive Guide to Getting Started — Data Quality Pro
Data Quality Rules: The Definitive Guide to Getting Started — Data Quality Pro
The reality is that all organisations possess data quality rules but they’re typically scattered widely across the organisation with no thought to standardisation, governance and re-use. The following resources will help your organisation buck that trend adopt data quality rules management habits a
·dataqualitypro.com·
Data Quality Rules: The Definitive Guide to Getting Started — Data Quality Pro
Add or connect a database with WSL
Add or connect a database with WSL
Learn how to set up MySQL MongoDB, PostgreSQL, SQLite, Microsoft SQL Server, or Redis on the Windows Subsystem for Linux.
·docs.microsoft.com·
Add or connect a database with WSL
How to Create a Handy SQL Server Backup Database Script
How to Create a Handy SQL Server Backup Database Script
Learn how to backup your databases regularly, whether a full or differential backup, by creating handy SQL server database backup scripts in this tutorial!
·adamtheautomator.com·
How to Create a Handy SQL Server Backup Database Script
Migrate SQL Server to Amazon Aurora PostgreSQL using best practices and lessons learned from the field | Amazon Web Services
Migrate SQL Server to Amazon Aurora PostgreSQL using best practices and lessons learned from the field | Amazon Web Services
Many organizations are benefiting from the performance gains and ease of administration of the Amazon Aurora database platform. The idea of breaking free from commercial licenses, backup administration, and data center maintenance is always a welcome thought. But where to begin, and how complex is the journey to migrate from SQL Server to Amazon Aurora […]
·aws.amazon.com·
Migrate SQL Server to Amazon Aurora PostgreSQL using best practices and lessons learned from the field | Amazon Web Services
The Art of PostgreSQL
The Art of PostgreSQL
The book that teaches SQL to developers: Learn to replace thousands of lines of code with simple queries!
·theartofpostgresql.com·
The Art of PostgreSQL
The Art of PostgreSQL
The Art of PostgreSQL
The book that teaches SQL to developers: Learn to replace thousands of lines of code with simple queries!
·theartofpostgresql.com·
The Art of PostgreSQL
SQL and Business Logic
SQL and Business Logic
Business logic is supposed to be the part of the application where you deal with customer or user facing decisions and computations. It is often argued that this part should be well separated from the rest of the technical infrastructure of your code. Of course, SQL and relational database design is meant to support your business cases (or user stories), so then we can ask ourselves if SQL should be part of your business logic implementation. Or actually, how much of your business logic should be SQL?
·tapoueh.org·
SQL and Business Logic
Part I. Tutorial
Part I. Tutorial
Part I. Tutorial Welcome to the PostgreSQL Tutorial. The following few chapters are intended to give a simple introduction to PostgreSQL, relational …
·postgresql.org·
Part I. Tutorial
PostgreSQL Tutorial - Learn PostgreSQL from Scratch
PostgreSQL Tutorial - Learn PostgreSQL from Scratch
Learn PostgreSQL quickly through a practical PostgreSQL tutorial designed for database administrators and application developers.
·postgresqltutorial.com·
PostgreSQL Tutorial - Learn PostgreSQL from Scratch
Postgres Guide
Postgres Guide
We here are very big fans of Postgres as a database and believe it is often the best database for the job. For many though, working with and maintaining Postgres involves a steep learning curve. This guide is designed as an aid for beginners and experienced users to find specific tips and explore tools available within Postgres. If you want to take the quick & painless approach instead, there are great services available for Postgres hosting.
·postgresguide.com·
Postgres Guide