Summary: in this tutorial, you will learn about the PostgreSQL SERIAL
pseudo-type and how to use the SERIAL
pseudo-type to define auto-increment columns in tables.
Introduction to the PostgreSQL SERIAL
pseudo-type
PostgreSQL Cheat Sheet tries to provide a basic reference for beginner and advanced developers, lower the entry barrier for newcomers, and help veterans refresh the old tricks. PostgreSQL also is known as Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance. PostgreSQL 8.3 Cheat Sheet: Summary of new and old PostgreSQL functions and SQL constructs complete xml query and export, and other new 8.3 features, with examples. SQLite: If you are looking for a free and lite fairly SQL-92 compliant relational database, look no further. Pete Freitag has published a Cheat Sheet for PostgreSQL. The cheat sheet is free, and prints out on two pages. This post has been migrated from a previous version of the PostgreSQL website. We apologise for any formatting issues caused by the migration. Importing Data from CSV in PostgreSQL Insert multiple rows List the tables in SQLite opened with ATTACH Meta commands in PSQL Outputting Query Results to Files with o Random Sequences Show Tables in Postgres SQL Cheat Sheet. PostgreSQL & PostGIS Cheatsheet. This is a collection of information on PostgreSQL and PostGIS for what I tend to use most often. Installing Postgres & PostGIS.
In PostgreSQL, a sequence is a special kind of database object that generates a sequence of integers. A sequence is often used as the primary key column in a table.
When creating a new table, the sequence can be created through the SERIAL
pseudo-type as follows:
By assigning the SERIAL
pseudo-type to the id
column, PostgreSQL performs the following:
- First, create a sequence object and set the next value generated by the sequence as the default value for the column.
- Second, add a
NOT NULL
constraint to theid
column because a sequence always generates an integer, which is a non-null value. - Third, assign the owner of the sequence to the
id
column; as a result, the sequence object is deleted when theid
column or table is dropped
Behind the scenes, the following statement:
is equivalent to the following statements:
PostgreSQL provides three serial pseudo-types SMALLSERIAL
, SERIAL
, and BIGSERIAL
with the following characteristics:
PostgreSQL SERIAL
example
It is important to note that the SERIAL
does not implicitly create an index on the column or make the column as the primary key column. However, this can be done easily by specifying the PRIMARY KEY
constraint for the SERIAL
column.
The following statement creates the fruits
table with the id
column as the SERIAL
column:
To assign the default value for a serial column when you insert row into the table, you ignore the column name or use the DEFAULT
keyword in the INSERT
statement.
See the following example:
Or
PostgreSQL inserted two rows into the fruits
table with the values for the id column are 1 and 2.
To get the sequence name of a SERIAL
column in a table, you use the pg_get_serial_sequence()
function as follows:
You can pass a sequence name to the currval()
function to get the recent value generated by the sequence. For example, the following statement returns the recent value generated by the fruits_id_seq
object:
If you want to get the value generated by the sequence when you insert a new row into the table, you use the RETURNING id
clause in the INSERT
statement.
The following statement inserts a new row into the fruits
table and returns the value generated for the id column.
The sequence generator operation is not transaction-safe. It means that if two concurrent database connections attempt to get the next value from a sequence, each client will get a different value. If one client rolls back the transaction, the sequence number of that client will be unused, creating a gap in the sequence.
In this tutorial, you have learned how to use the PostgreSQL pseudo-type SERIAL
to create an auto-increment column for a table.
- Was this tutorial helpful ?
A quick reference to PostgreSQL.
Created on: 2019-01-22
Tag: cheat_sheet
To check postgresql version:
source: https://stackoverflow.com/a/13733856
To create a super suer with passowrd:
To create a database on Postgresql:
To remove an extension:
To connect to remote psql host 1:
To backup a psql database without typing password 2:
To create database from sql file:
to backup a single table:
to export a sql statement output to a csv file with header:
to export a sql statement output to a geojson file:
COPY current_relation_members TO '/var/lib/postgresql/csv/current_relation_members.CSV' DELIMITER ',' CSV HEADER;
The following are the Meta-Commands for psql command. This can be used with either with -c flag of the psql command like:
Or within the interactive prompt that comes after sudo -u postgres psql command. There are many Meta-Commands and there are available in the psql document. Bellow are a few useful most useful for me:
connect to a database: c $DATEBASE_NAME
list all users: du
list all database: l or list
list all table: dt
To allow remote connections to PostgreSQL database server, first check listen_addresses in postgresql.conf:
The output would show something like this:
Now let's edit the postgresql.conf file in our editor of choice:
Search for listen_addresses, and set it to '*' for all addresses or comma separated IP address, save the file and exit. Now we need to change the pg_hba.conf file so let's open it with our editor:
More details here PostgreSQL: Documentation: Connections and Authentication.
Now add the following to the end of file:
Now save the file and exit. Now restart PostgreSQL:
OR:
source: https://bosnadev.com/2015/12/15/allow-remote-connections-postgresql-database-server/
Now connect to the remote server:
source: https://askubuntu.com/a/423181
Postgresql Cheat Sheet Pdf
- 1
- 2
- 3