Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [domain]

SQL Domain Constraints: constraints applied to a data type providing centralized management and data validity

From the SQL Spec: "A domain is a named user-defined object that can be specified as an alternative to a data type in certain places where a data type can be specified. A domain consists of a data type, possibly a default option, and zero or more (domain) constraints."

This feature is referred to in the spec as F251, "Domain support".

This is unrelated to Microsoft's domain controllers, and domain accounts.

Implementations

Feature Requests

29 questions
14
votes
3 answers

How to specify not null contraints for the fields of composite types in postgres

Let's say I would like to have a composite type for address, like: create type address as ( city text, address_line text, zip_code int ); And to make data integrity better, I don't want to allow NULLs to be members of city, address_line, or…
Link0
  • 243
  • 2
  • 6
11
votes
1 answer

Why does PostgreSQL allow NULLs in domains that prohibit NULL?

PostgreSQL allows NULLs in domains marked as NOT NULL. Why is this, the docs say this about it, It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint. For example, this can happen in…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
10
votes
2 answers

Show DOMAIN details

Showing the columns and constrains of a table in PostgreSQL is done with: \d+ Which lists the columns, data types and modifiers for a table. How can I show the details and constraints of Posgresql domain?
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
8
votes
2 answers

Does SQL Server support custom DOMAINs?

PostgreSQL supports the DOMAIN specification, from SQL 2011 working draft spec, A domain is a named user-defined object that can be specified as an alternative to a data type in certain places where a data type can be specified. A domain consists…
Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
4
votes
3 answers

How do I list the constraints of a DOMAIN?

Consider the following scenario: CREATE DOMAIN dom_zipcode AS text; ALTER DOMAIN dom_zipcode ADD CONSTRAINT zipchk CHECK (char_length(VALUE) = 5); Now, if I want to drop that constraint with ALTER DOMAIN, The manual says: ALTER DOMAIN name …
Adam Matan
  • 12,079
  • 30
  • 82
  • 96
4
votes
1 answer

How do I add a constraint on the UUID type of Postgres to only allow UUID v4?

I have a lot of tables in a Postgres 14 database that use the native UUID type. Is there a database-wide setting/constraint to limit this type to only allow UUID v4? A potential solution I've come across CREATE DOMAIN. I can extend the UUID type,…
Abhijit
  • 181
  • 3
4
votes
4 answers

Creating a DOMAIN in MySQL or MariaDB?

As far as I know, DOMAINS cannot be created in MySQL. Is there any disadvantage for that? Is there something to be used instead of using CREATE DOMAIN?
Ju Ju
  • 137
  • 1
  • 1
  • 7
3
votes
1 answer

SSMS cannot determine if remote computer is connected to domain

I've recently set up a new server with an SQL instance running on it (2017), I am able to connect to the server remotely using SSMS, but when I try to add a new login, I receive the attached error. Both machines are on the same domain. I have…
Joshua Cameron-Mackintosh
  • 31
  • 1
  • 2
2
votes
1 answer

Can I create a DOMAIN on a range of years

I have to create a custom domain that is an interval between two years, but i actually need both years. For example: '2013/2014' '2015/2016' Is there a way to pack both years inside a single domain, or have i to separate them in two attributes of…
Federico Ponzi
  • 177
  • 2
  • 13
2
votes
1 answer

How to apply a newly-created Domain to an existing Column?

In Postgres 9.4 Beta 1, I successfully created a domain of type VARCHAR, fuel_domain_. The domain checks that a value is any of 5 possible strings, coal, gas, wind, hydro, other. CREATE DOMAIN fuel_domain_ AS character varying COLLATE…
Basil Bourque
  • 11,188
  • 20
  • 63
  • 96
2
votes
1 answer

Can one define a NULLable composite type whose fields are NOT NULL?

I would like to define a composite type whose fields are NOT NULL; while at the same time, allow the value itself to be NULL in a table column. My first attempt was to define a DOMAIN on the composite type with a CHECK constraint that ensured the…
philomathic_life
  • 472
  • 3
  • 14
2
votes
1 answer

Creating a TYPE that is an Array within a Function (or a predefined Array TYPE) in PostgreSQL

I am researching migration of a major system from Oracle to PostgreSQL. Getting into coding functions now, and am looking to see if there is a compatible object in PostgreSQL where, dynamically within a Function I can create a TYPE as an array. The…
user210170
  • 21
  • 1
2
votes
0 answers

sys.login_token doesn't include particular windows groups while they can be listed by sys.database_principals

When I query SELECT principal_id, name, [type] FROM sys.login_token I found some local windows groups were not there. However, when I check by SELECT principal_id, name, [type] FROM sys.database_principals I can see those groups in the…
Circle Hsiao
  • 455
  • 1
  • 6
  • 19
2
votes
0 answers

In PostgreSQL, how to deal with error messages that show almost no context?

Today I realized a number of points where PostgreSQL (v10.3) is rather lackluster and sparse in its error messages. The first point is illustrated by this code: drop schema if exists X cascade; create schema X; create domain X.an_illegal_regex as…
John Frazer
  • 317
  • 1
  • 3
  • 8
2
votes
1 answer

Ranges over domains do not trigger implicit casting

As far as I was able to find out, it is currently not possible in PostgreSQL v9.5 (and above?) to obtain implicit casting with custom range types. To illustrate, when I define the following demo setup: drop schema if exists tac cascade; create…
John Frazer
  • 317
  • 1
  • 3
  • 8
1
2