A temporary table is a transient table that exists for the duration of a database session. Temporary tables are automatically dropped at the end of a session or transaction. CREATE TEMPORARY TABLE temp_table_name(column_list ); **A Temporary table is only visible to the session that created it. In other words, it is invisible to other sessions. … Create Temporary Table
Comparing Two Tables There are several ways to compare the contents of two tables to find the differences between them. Example: Two tables to be compared are created and sample data is entered. Deleting Duplicate Rows Example: We executed the same insert commands several times and created duplicate lines. To view duplicate lines. In the … SQL Examples
To connect to the database from the terminal screen; To connect to the database on another machine; When you connect to a database, you can change the connection to a new database under a user specified by the user. The previous link is closed. If you omit the user parameter, it is assumed to be … psql Commands
It is a function that returns the number of rows with the given value in parentheses. Counts records in the database. Query that returns how many rows are in a table: Use it with a WHERE condition; The query finds and groups the staff_id values in the payment table. Calculates the number of rows that … Mathematical Operations Date Time Functions
A primary key is a column or a group of columns used to uniquely identify a row in a table. Technically, it is a combination of the NULL constraint and the UNIQUE constraint. In case the primary key consists of two or more columns, the primary key constraint is defined as follows. If no name … Primary key
A foreign key is a column or group of columns in a table that references the primary key constraint of another table. The foreign key constraint preserves the referential integrity of the data between the child and parent tables. The foreign key constraint indicates that values in a column or group of columns in the … Foreign Key
The CHECK constraint is a type of constraint that makes sure the values in a column meet a certain requirement. If the values qualify the CHECK constraint, PostgreSQL adds or updates these values to the column. Otherwise, PostgreSQL will reject the changes and throw a constraint violation error. The last query returns an error because … Check Constraint
When the UNIQUE constraint is applied, every time you add a new row it checks if the value is already in the table. If the value exists in the column, it rejects the change and returns an error. The same process is performed for updating existing data. Example: The command adds a unique constraint to … Unique Constraints
In database theory, NULL represents unknown or missing information. NULL is not the same as an empty string or number of zeros. Courtesy to this constraint, any row in the column to which this constraint is added cannot be left blank. A value must be entered. To add NOT NULL constraint to the existing column;
Boolean: Used when TRUE, FALSE or unknown. It can take NULL values. Character: PostgreSQL provides three types of character data: CHAR(n), VARCHAR(n), and TEXT. CHAR(n) is a space-filled fixed-length character. If you add a string expression that is shorter than the column’s length, PostgreSQL fills in the blanks. If you add a string expression longer … Data Type