postgres unique id column management

 UUID function in postgres:


UUID is a unique identifier used in primary key columns as an alternative to the sequences. You can create a table column with UUID datatype and auto generate the values for the column to ensure uniqueness.

See the example below.


Step 1: Create the extension as below to use the uuid generator function.

mytestdb=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE EXTENSION


Step 2: Create table with uuid column

mytestdb=# create table ytable (id uuid, name varchar(20));

CREATE TABLE

mytestdb=# select * from ytable;

 id | name 

----+------

(0 rows)


Step 3: Insert new values using the uuid generator during insert:


mytestdb=# insert into ytable values (uuid_generate_v4(), 'aaa');

INSERT 0 1

mytestdb=# insert into ytable values (uuid_generate_v4(), 'aaa');

INSERT 0 1

mytestdb=# insert into ytable values (uuid_generate_v4(), 'aaa');

INSERT 0 1

mytestdb=# insert into ytable values (uuid_generate_v4(), 'aaa');

INSERT 0 1

mytestdb=# select * from ytable;

                  id                  | name 

--------------------------------------+------

 3dffa4ba-8491-44f1-8573-eccd9f116a62 | aaa

 cc449a40-4d4a-4641-8abf-26fc147aa656 | aaa

 1c2d86e4-ff10-415b-b855-c701e16f4e42 | aaa

 375b94ab-88eb-4f18-8495-3e332316f09b | aaa

(4 rows)


Comments

Post a Comment

Popular posts from this blog

PostgreSQL Tools

Postgres has few tools that help to query or do maintenance tasks: 1. psql   2. pg_ctl 3. pg_lsclusters 4. pg_ctlcluster Psql is an terminal based front-end interface for PostgreSQL for querying anything about Postgres server and its databases. Few examples are here. psql   commands \? : show all psql commands. \h sql-command : show syntax on SQL command. \c dbname [username] : Connect to database, with an optional username (or  \connect ). Display Commands: You can append  +  to show more details. \l : List all database (or  \list ). \d : Display all tables, indexes, views, and sequences. \dt : Display all tables. \di : Display all indexes. \dv : Display all views. \ds : Display all sequences. \dT : Display all types. \dS : Display all system tables. \du : Display all users. \x auto|on|off : Toggle|On|Off expanded output mode. For automations this below may be helpful as this is executed from Linux command line and the output can be redirected to...
Read more

Move postgreSQL server data to new Folder Location

 Move the entire postgres server files from default location to a custom location on the server A word of Caution: This procedure involves a database shutdown and restart needed.  Please make sure you have  change request raised if you are working on production database and get it approved before scheduling this change.  You must notify the application team about the change as well and set the down time expectations. It is better to maintain the standards in the naming convention for the common scripts to monitor and support any activities. Pre-Steps: Create the required folder structure and modify the ownership and permissions on the folder structure similar to the below steps           $ sudo su - root           $ mkdir -p /myorg/postgres/           $ chown -R postgres:postgres /myorg/postgres           $ chown -R 755 /myorg/postgres Note:  D...
Read more

PostgreSQL for Oracle DBAs

 As an Oracle DBA in my past life, I have thought about giving some understanding and relatable definitions of PostgreSQL compared to Oracle Databases. I hope this helps to understand how these two relational databases are comparable for Oracle DBAs to quickly adapt to the new DB server technology. I will not be giving detailed explanations in this article. For more details on each topic, please refer to the postgreSQL documentation. Database architecture compared: Oracle : SGA: Fixed physical memory size allocated to each server out of available RAM Postgres : Total memory is allocated to postgres server running multiple databases within. Oracle : Buffer Pool Postgres : Share Buffers Oracle : Redo log buffers / Redo log files Postgres : WAL buffers / Wal files Oracle : Archivelog Files Postgres : Archivelog Files Oracle : UNDO files are used to hold original copy of the dirty pages for transaction isolation Postgres : WAL handles this. Oracle  : SMON process is responsible f...
Read more