How to find the Primary Key, Unique Key and Index Indexes in Postgres DB

// For Index
SELECT  relname FROM pg_class
WHERE oid IN
    (SELECT indexrelid FROM pg_index, pg_class
       WHERE pg_class.relname='emp'
        AND pg_class.oid=pg_index.indrelid
        AND indisunique != 't' AND indisprimary != 't');

// For unique
SELECT  relname FROM pg_class
WHERE oid IN
    (SELECT indexrelid FROM pg_index, pg_class
       WHERE pg_class.relname='emp'
        AND pg_class.oid=pg_index.indrelid
        AND indisunique = 't' AND indisprimary != 't');

// For primary
SELECT  relname FROM pg_class
WHERE oid IN
    (SELECT indexrelid FROM pg_index, pg_class
       WHERE pg_class.relname='emp'
        AND pg_class.oid=pg_index.indrelid
        AND indisprimary = 't');

Comments

Post a Comment

Popular posts from this blog

WMI Static Port configuration

Step one:  Set the DCOM config to use a static port.... Basically, run "dcomcnfg" from command prompt. Navigate the tree to My Computer > DCOM Config > Windows Management and Instrumentation, select properties of that folder. Go to the Endpoints tab Select Properties button for Connection-oriented TCP/IP Use static endpoint, set the port. Step 2:  Configure WMI to use a fixed port http://msdn.microsoft.com/en-us/library/bb219447(v=VS.85).aspx At the command prompt, type  winmgmt -standalonehost Stop the WMI service by typing the command  net stop "Windows Management Instrumentation" Restart the WMI service again in a new service host by typing  net start "Windows Management Instrumentation" Establish a new port number for the WMI service by typing  netsh firewall add portopening TCP 24158 WMIFixedPort Still testing this myself, so not 100% certain it works. Another way, use the Component Services Manager to set the ra
Read more

Optimizing your JVM for Best Performance

The better your JVM performs, the better your installation of Tomcat will perform. It's as simple as that. Getting the most out of your JVM is a matter of configuring its settings to match your real-world performance needs as closely as possible. Update your JVM to the latest version, establish some accurate benchmarks so you have a way of quantifying any changes you make, and then get down to business. Effective Memory Management The main thing to consider when tuning your JVM for Tomcat performance is how to avoid wasting  memory  and draining your server's power to process requests. Certain automatic JVM processes, such as garbage collection and memory reallocation, can chew through memory if they occur more frequently than necessary. You can make sure these processes only occur when they need to by using the JAVA_OPTS -Xmx and -Xms switches to control how JVM handles its heap memory. If your JVM is invoking garbage collection too frequently, use the -Xmx switch to s
Read more

How do I disable FOREIGN KEY checking for the time of database schema migration?

How do I disable  FOREIGN KEY  checking for the time of database schema migration? Currently I found engine-based commands like: PostgreSQL:  SET CONSTRAINTS ALL DEFERRED; MSSQL:  EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"; MySQL:  SET FOREIGN_KEY_CHECKS = 0; HSQLDB:  SET REFERENTIAL_INTEGRITY FALSE; DB2:  SET INTEGRITY FOR table OFF; SQLite 3.7.16:  PRAGMA foreign_keys = false; Oracle 11g seems to have no such command at all Syntax significantly differ, in case of MSSQL I'm unable to recover previously disabled constraints (if any).
Read more