Stack Overflow
Stack Overflow

Questions tagged [sql-domain]

A Schema may contain zero or more Domains. An SQL Domain is a named, user-defined set of valid values. Domains are dependent on some Schema — the must be unique within the Schema the Domain belongs to (it may not be the same as any in its Schema either) — and are created, altered and dropped using standard SQL statements.

A Schema may contain zero or more Domains. An SQL Domain is a named, user-defined set of valid values. Domains are dependent on some Schema — the must be unique within the Schema the Domain belongs to (it may not be the same as any in its Schema either) — and are created, altered and dropped using standard SQL statements. The Objects that may belong to a Domain are known as Domain Constraints; they depend on some Domain.

A Domain is defined by a descriptor that contains six pieces of information:

1. The <Domain name>, qualified by the <Schema name> of the Schema to which it belongs.
2. The Domain's SQL <data type> specification, including its name, length, precision and scale, as applicable.
3. The name of the Character set that the Domain's set of values must belong to (for character string types).
4. The name of the Domain's default Collation. (This is the Collation that may be used to compare a character string Domain's values in the absence of an explicit COLLATE clause.)
5. Whether reference values must be checked and whether <reference scope check action> specifies RESTRICT or SET NULL (for REF types).
6. The Domain's default value (if any).
7. A descriptor for every Constraint that belongs to the Domain.

To create a Domain, use the CREATE DOMAIN statement (either as a stand-alone SQL statement or within a CREATE SCHEMA statement). CREATE DOMAIN specifies the enclosing Schema, names the Domain and identifies the Domain's set of valid values. To change an existing Domain, use the ALTER DOMAIN statement. To destroy a Domain, use the DROP DOMAIN statement.

There is a one-to-many association between Domains and Columns: one Domain can be used to identify the set of valid values for multiple Columns.

10 questions
14
votes
2 answers

How to represent a custom PostgreSQL domain in SQLAlchemy?

I'm beginning to incorporate Alembic into my project which already uses SQLAlchemy table definitions. At present my DB schema is managed external to my application, and I want to bring the entire schema into my table definitions file. In PostgreSQL…
skyler
  • 8,010
  • 15
  • 46
  • 69
2
votes
1 answer

ALTERing a CHECK constraint in a DOMAIN

Is there a way to modify the details of an existing DOMAIN constraint in Postgres 13.4? I've been trying, and checking the docs, and suspect that the answer is: "No. DROP the constraint (or domain and constraint?), and rebuild it." This is awkward…
Morris de Oryx
  • 1,857
  • 10
  • 28
2
votes
2 answers

jOOQ and PostgreSQL domain: mapping to specific object type

I'm doing some tests using PostgreSQL 9.5 and jOOQ 3.8.4. Specifically, I want to create a domain like the following: CREATE DOMAIN my_something NUMERIC(4,2); then I want to create a type as follows: CREATE TYPE my_type ( something my_something;…
JeanValjean
  • 17,172
  • 23
  • 113
  • 157
2
votes
0 answers

How to generate domain from an enum while generating DDL from data model?

Is there a way to generate a domain for a field that is defined as enum while generating DDL from the data model? The default behaviour for fields that are defined as enum is either EnumType.STRING or EnumType.ORDINAL. In this particular case I use…
Jagger
  • 10,350
  • 9
  • 51
  • 93
1
vote
1 answer

Are custom domains arrays possible in PostgreSQL 12?

I have declared a custom domain tmoney as create domain tmoney as decimal (13,4); Then I use an array of it in a table declaration, create table test ( id int generated by default as identity primary key, volume smallint[5] not null…
coterobarros
  • 941
  • 1
  • 16
  • 25
1
vote
1 answer

Custom domain array by recurrency of custom domain

Documentation say nothing about create a domain from other custom domains. I need only to create array of (previously created) custom domains, so it is not the problem/solution discussed here. Example: CREATE DOMAIN jbag AS JSONb CHECK( VALUE IS…
Peter Krauss
  • 13,174
  • 24
  • 167
  • 304
1
vote
1 answer

Call set-returning plpgsql function for each row returned from a query

In my Postgres 9.6 database I have the following custom domain and table definition: create domain lowResData as float[21]; create table myRawValues ( id text, myData lowResData, xAxis lowResData, primary key(id) ); The…
chumbaloo
  • 671
  • 6
  • 16
0
votes
2 answers

Set DOMAIN for existing column

I have an existing table like this: CREATE TABLE public.data ( id integer, name text, sell_value real) ); and I define a domain as: CREATE DOMAIN dataDomain AS TEXT CHECK(name = ANY ('{joe, john, jack}'::text[])); How can I apply the…
Flammy
  • 25
  • 7
0
votes
1 answer

MySQL alternative to PostgreSQL's custom data type (and domain)

In PostgreSQL, if I store a specific datatype (ex. an mail address) in multiple columns across multiple tables, I could simply define a custom mail type with all constraints like so: CREATE TYPE status_enum AS enum ('accepted', 'declined',…
Programer Beginner
  • 1,377
  • 6
  • 21
  • 47
0
votes
1 answer

Does domain in text data type is stored as text in a table? (PostgreSQL)

Because there is no set type in PostgreSQL I had to use domain. I have named a domain gender. It is text type. Then in users table I have created a column with that gender type. Now, how is stored the gender in the users table? As text or binary?
ilhan
  • 8,700
  • 35
  • 117
  • 201