Postgresql – Show DOMAIN details

domainpostgresql

Showing the columns and constrains of a table in PostgreSQL is done with:

\d+ <table_name>

Which lists the columns, data types and modifiers for a table.

How can I show the details and constraints of Posgresql domain?

Best Answer

To view all domains in your database, run in psql

\dD[S+] [PATTERN]      list domains

You can see the list of all psql commands with \?.

pg_constraint stores check, primary key, unique, foreign key, and exclusion constraints on tables. Column constraints are not treated specially. Every column constraint is equivalent to some table constraint. Not-null constraints are represented in the pg_attribute catalog, not here.

Related Solutions

PostgreSQL – Apply New Domain to Existing Column

Incorrect SQL was the problem. The ALTER COLUMN must be part of an ALTER TABLE.

This SQL does indeed alter the data type of the existing column from VARCHAR to the newly-created domain.

ALTER TABLE unit_
ALTER COLUMN fuel_ 
SET DATA TYPE fuel_domain_;

PostgreSQL – Show Only Duplicate Values

That can be done with a simple SELECT statement.

SELECT count(*) AS count_ , favorite_color_ 
FROM person_ 
GROUP BY favorite_color_
HAVING count(*) > 1
ORDER BY count_ DESC
;

The count(*) command is an aggregate function supported by Postgres and gives the number of rows with duplicate values in a column specified by the GROUP BY clause.

To omit the values without any duplicates, add the HAVING clause. A HAVING is similar to a WHERE but applies to each row being generated to represent the grouping.

To get results to sort with most frequent duplicate values at the top, use the ORDER BY clause. We reference the column alias count_ that we created in the first line with the AS clause.

Best Answer

Related Solutions

PostgreSQL – Apply New Domain to Existing Column

PostgreSQL – Show Only Duplicate Values

Related Question