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 syntax in Oracle is: TYPE VAR_STRING IS VARRAY(10) VARCHAR2(30); This creates a 10 element array TYPE named VAR_STRING to hold 10 strings of 30 characters. I am playing with PG 9.6, and even in the Create Type dialog, it is not obvious to me to make a pre-defined TYPE that can be an array.

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 fields are NOT NULL; unfortunately this prevents NULL itself from being INSERTed into the table:

BEGIN;
    CREATE TYPE foo AS (x int, y int);
    CREATE DOMAIN non_null_foo AS foo CHECK((VALUE).x IS NOT NULL AND (VALUE).y IS NOT NULL);
    CREATE TABLE bar(y non_null_foo);
    INSERT INTO bar VALUES (NULL);
ROLLBACK;

errors: ERROR: value for domain non_null_foo violates check constraint "non_null_foo_check". My second attempt was to allow NULL for VALUE in the DOMAIN, but this also does not work since it now allows a value where all fields are NULL:

BEGIN;
    CREATE TYPE foo AS (x int, y int);
    CREATE DOMAIN non_null_foo AS foo CHECK(VALUE IS NULL OR ((VALUE).x IS NOT NULL AND (VALUE).y IS NOT NULL));
    CREATE TABLE bar(y non_null_foo);
    INSERT INTO bar VALUES ((NULL, NULL)); --succeeds
    INSERT INTO bar VALUES ((1, NULL)); --fails
ROLLBACK;

It's as if Postgresql is unable to distinguish between NULL and a value where all the fields are NULL. Is there something I'm missing?

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 zip_code. So I would like to have a not null constraint for those fields. Creating domain checks isn't working for me. So this code produces error:

create domain address_domain as address 
check (
  value.city is not null and 
  value.address_line is not null and
  value.zip_code is not null
);

You might say: "Well, why won't you store address as three columns, and add contraints to the fields?". And I will answer with that I would like to have ability to make address itself nullable, but if address is present, all of it's fields should be present as well. Something like this:

create table companies (
  id serial primary key,
  name text not null,
  headquaters address -- this one can be null tho
)

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 ?**

I have restored (FULL) a database using SSMS to a new server. When I did this we received errors indicating the account was unable to login, likely due to bad SID as we confirmed the account did transfer during the restore and had appropriate permissions. We have tried deleting and re-adding the user through the GUI of SSMS and receive the same login errors from our application (user does not exist). To fix this, the only solution I can find is to specify creating the login with a specific SID as the query below shows. When I execute this, SQL Server appears to have a problem with the "\" in the domain. I cannot find another way to specify this that works. Am I missing syntax? How does one go about making an AD service account login with a specified SID if the syntactical "\" is not accepted?

CREATE LOGIN [AD_DOMAIN\login]
WITH PASSWORD = 'password_here', SID = SID_HERE;

SELECT * FROM sys.sql_logins WHERE name = 'AD_DOMAIN\login';
GO

At this point it would be easier to just create a brand new service account in AD, but inquiring here to see if I am missing something stupid....

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](https://www.postgresql.org/docs/14/sql-createdomain.html) . I can extend the UUID type, set a CONSTRAINT to CHECK the version bit to be equal to 4. After that, I'd have to include a migration with a lot of ALTER TABLES. Is there any other way of doing this to an existing database?

Let's say I have a simple table foo, and [domain](https://www.postgresql.org/docs/current/domains.html) bar

CREATE TABLE foo(x)
AS VALUES (1),(2),(3);

CREATE DOMAIN bar AS text;

Is it possible to make a cast such that I can do

SELECT foo::bar FROM foo;

To start this, I create a function which takes a foo to a bar,

CREATE FUNCTION foo_to_bar(t foo)
RETURNS bar
AS $$
  SELECT FORMAT('%s AS BAR', t.x)::bar
$$
LANGUAGE SQL
IMMUTABLE;

I can see this works,

SELECT foo_to_bar(foo), pg_typeof(foo_to_bar(foo)) FROM foo;
 foo_to_bar | pg_typeof 
------------+-----------
 1 AS BAR   | bar
 2 AS BAR   | bar
 3 AS BAR   | bar

But when I do,

CREATE CAST (foo as bar) WITH FUNCTION foo_to_bar;

I get this weird warning, > WARNING: cast will be ignored because the target data type is a domain And the result is even more bizzare, $ SELECT foo::bar, pg_typeof(foo::bar) FROM foo; foo | pg_typeof -----+----------- (1) | bar (2) | bar (3) | bar (3 rows) You can see that we're returning the right type, but the value isn't what I would expect. I would expect this to produce the same value as the above where I use foo_to_bar. * Is it possible to cast to a domain type? * If no, what does the above do? Why do I get (1), (2), and (3)?

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 DROP CONSTRAINT [ IF EXISTS ] constraint_name [ RESTRICT | CASCADE ] But how can we find constraint_name? \dD only shows the constraint's definition (CHECK statement). \dD dom_zipcode ; List of domains Schema | Name | Type | Modifier | Check --------+-------------+------+----------+-------------------------------- public | dom_zipcode | text | | CHECK (char_length(VALUE) = 5) (1 row) I can dump the schema using pg_dump, but I believe there must exist a more elegant way to establish this using the psql terminal.

In the MySQL host-name does the % in %.exemple.com covers the main domain exemple.comas well or only its subdomains? For instance in this GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'rep'@'%.exemple.com'; will the exemple.com domain be allowed as well?

I have a database where the owner is Login1 but the username dbo is assigned to Login - login2. I understand in order to change the login name for the username dbo to sa I will have to use the below EXEC ***sp_changedbowner 'sa'*** but the problem here is that this will change the databasowner from Login1 to sa. How do I only change the dbo login name to sa and keep the database owner to Login1

I just built a test cluster with 2 MS SQL Server 2014 machines, 1 domain controller, an 1 file share witness. (All of them are Windows2016-based.) The 2 MS SQL Server machines are a WSFC members. When I created the Always On Availability Group, all steps succeeded except creating a listener. If I try to repeat adding a listener to this AAG, I receive a message: > **Cluster network name resource 'myaag_mylistener'** failed to create its > associated computer object in domain 'mydomain.lan' during: Resource > online. > > The text for the associated error code is: Access is denied. > > > Please work with your domain administrator to ensure that: > - The cluster identity 'MYWSFC$' has Create Computer Objects permissions. By default all computer objects are created in the same > container as the cluster identity 'MYWSFC$'. > - The quota for computer objects has not been reached. > - If there is an existing computer object, verify the Cluster Identity 'MYWSFC$' has 'Full Control' permission to that computer object using > the Active Directory Users and Computers tool. What I tried: 1. Create a computer object: mydomain.lan/Computers/mylistener and give the mydomain\MYWSFC$ "Full control" on it; 2. Remove the object mydomain.lan/Computers/mylistener and grant the mydomain\MYWSFC$ account permissions to create computer objects within mydomain.lan/Computers (List contents, Read all properties, Read permissions, Create computer objects); 3. In ADSI Edit, DC=mydomain,DC=lan: ms-DS-MachineAccountQuota raised from 10 to 15 (although there are only 5 machine accounts including mylistener$). What could still be wrong?

Sql server contains logins from 3 different domains (Under security\logins). These are mapped to database users. There are 15 databases. One of the domain/AD is planned to be decommissioned next year. 1. What elements other than login and users need to be reviewed to ensure there is no impact to sql server functionality, any jobs, sql server service account, etc? 2. Assuming domain3 will be decommissioned, what is the process to modify the login and user to domain1? For example- on the sql server, is a delete and create required or is there any way to modify existing login/users?

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 an outer-join query, if the domain column is on the nullable side of the outer join. Can this be better explained?

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 already done some prior research and opened up ports 5002, 5001, 135 and 88 in the below configuration. I have already confirmed that it is an issue with the firewall, as turning off the domain firewall allows access. Am I missing something? In case it's relevant, the server is hosted on Azure

I am new to DBMS and was reading about the ER Model and entities-attributes. I found the following line in the book which I was reading: > The domain of Composite attributes is the cross product of domains of > component attributes. Which is fairly straight forward and intuitive. However, I was not able to understand the line following it: > The domain of Multi-valued attributes is a set of subsets of values > from the basic domain. I couldn't understand this line. I know Power set , which is set of all subsets of a set. Does this line mean, The domain is the power set of the basic domain set? I tried searching on the web before posting, but couldn't find any related literature/ explanation. A simple language explanation would help! Thank you!

How can I define or change a domain based on another attribute value? F.e. domain for salary is a numeric value > 500. But if a date the person was hired > January 1st, 2013, then the salary must be > 1000.

Recently we have switched our domain controller and during the migration I have noticed on the cluster manager the below error     Cluster network name resource 'Cluster Name' encountered an error enabling the network name on this node. The reason for the failure was: 'Check if a domain controller is reachable and that the computer object is present in Active Directory'. In our environment we have always on availability group. I am able to move resources but did not try to failover . When I looked into the registry settings HKEY_LOCAL-MACHINE - Cluster - Resources I have noticed the CREATINGDC still showing the old Domain controller. Please advise whether I need to update it to the new domain controller or I can leave as is. Any help will be greatly appreciated as I fear failover will not work.

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?

I'm currently in the process of learning (Relational) Database Design based on two popular books, online tutorials and articles. Some places, when they talk about attributes constraints, they call it an **Attribute Domain**. Some other places, when they talk about attribute specification, they call it a **Field specification**. However, some other places, they say that they are both the same, but attribute domain seems to me to be part of the field specifications since by definition, attribute domain sounds like the Physical Element part of field specification: > Physical elements: Data type, Length, Decimal Places, Character support, input mask, display format What is it now?

In an ERD (as in, rectangles for entity types, diamonds for relationship types, ovals for attributes, etc.) how would one specify that an attribute is, say, a 5-letter word?