Skip to main content
13 votes
Accepted

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

The correct syntax for a check constraint for a composite type would look like this: create domain address_domain as address check ( (value).city is not null and (value).address_line is not null ...
Melkij's user avatar
  • 3,209
10 votes

How to extract fields of anonymous row type in PostgreSQL

New in v13 Allow ROW expressions to have their members extracted with suffix notation (Tom Lane) For example, (ROW(4, 5.0)).f1 now returns 4. select (ROW(1,'Hello',false)).f1 Note there are some ...
Peter Vandivier's user avatar
9 votes
Accepted

PostgreSQL: cast row to type

And now I'm asking myself what this means and if I could store a locations object in there. Yes, you can. (But there are not many great use cases for that.) This does not do what you seem to think it ...
Erwin Brandstetter's user avatar
7 votes
Accepted

Postgres jsonb vs composite type performance differences

The TYPE inventory_item was defined in the question (same as in the guide), so we need only to define tables tc with composite (ROW) type, and tj with JSONb type. INSERT TIME -- drop table tc; drop ...
Peter Krauss's user avatar
5 votes

Postgres jsonb vs composite type performance differences

It's not really a question of performance, Composite Types are strongly typed meaning you have to define their shape. JSON is weakly typed. Moreover, JSON has a use case, but with a composite type ...
Evan Carroll's user avatar
  • 63.7k
5 votes
Accepted

Is there a way to partially initialize a TYPE?

Code examples based on your type definition: CREATE TYPE my_type AS (name text, street text, location text); Your syntax is not possible (as you already know). A plain cast does not allow a (partial)...
Erwin Brandstetter's user avatar
4 votes

PostgreSQL function param json_populate_recordset ARRAY problem

Confusion? From the docs json_populate_recordset(base anyelement, from_json json) does not return an ARRAY, it returns a setof anyelement. That's fundamentally different from an array. SELECT * FROM ...
Evan Carroll's user avatar
  • 63.7k
4 votes
Accepted

NULL rows and IS NULL

This is a sad story. When the SQL standard committee defined the NULL-ness of composite values, they must have taken the wrong drugs. Anyway, they ruled that a composite IS NULL if all elements are ...
Laurenz Albe's user avatar
  • 53.4k
4 votes
Accepted

ENUM and ID as a Foreign Key

First, stay off enums for things like this. Enum values can never be removed, so use them only if you are sure that that will never be necessary, which doesn't seem the case here. Anyway, I would say ...
Laurenz Albe's user avatar
  • 53.4k
3 votes

Postgres jsonb vs composite type performance differences

Using a composite type Pros less disc space faster select/insert/update Using a composite type Cons it's structured (ie not flexible). You can not make up fields on the fly. longer coding time (...
Sahap Asci's user avatar
  • 2,979
3 votes

ENUM and ID as a Foreign Key

I think you're making this complex. First that blog is bad advise. Welcome to the internet. Just ignore it. Use a int PRIMARY KEY GENERATED BY IDENTITY AS DEFAULT (ie, IDENTITY COLUMN until you have ...
Evan Carroll's user avatar
  • 63.7k
3 votes
Accepted

Column does not exists when accessing inner type field in ORDER BY

Explanation for the error You get the error message: ERROR: column "tp" does not exist because you cannot use output columns in another expression in the ORDER BY clause. You can only reference ...
Erwin Brandstetter's user avatar
3 votes

How to set/get a field of a nested composite type variable?

You can assign the row type as a whole, which does what you ask for. But it also overwrites every other (sub-)field. (I use NULL for the rest.) See x in the demo. You can also assign a whole ...
Erwin Brandstetter's user avatar
3 votes

How to set/get a field of a nested composite type variable?

There's one (awful, in my opinion) workaround... you can access only one level down at a time. Use intermediate variables. DO LANGUAGE PLPGSQL $$ DECLARE x t3 ; x2 t2 ; x1 t1 ; BEGIN ...
joanolo's user avatar
  • 13.5k
3 votes
Accepted

Define recursive composite types

No, "recursive" type definitions are not possible in Postgres. You cannot reference a type in CREATE TYPE that is not registered, yet. (What would terminate the recursion anyway?)
Erwin Brandstetter's user avatar
3 votes

Can I create a cast from a composite (table or row) type to a domain?

You'll get the (1) result even without creating the cast or the function. That's simply the notation for the whole row from foo when treated as a record. CREATE TABLE foo(x) AS VALUES (1),(2),(3); ...
Christoph Berg's user avatar
2 votes

How to work around Sequelize not supporting composite foreign keys

I have never seen Sequelize, but your error reads: TypeError: Cannot read property 'toUpperCase' of undefined Where it says of undefined I would suggest your code is trying to convert a string to ...
youcantryreachingme's user avatar
2 votes

Is there a way to partially initialize a TYPE?

Yes. You can do this using any type That's constructor takes a record and supports merge (or as PostgreSQL likes to say "concatenate" (||)). An example of this is hstore. Using any type that supports ...
Evan Carroll's user avatar
  • 63.7k
2 votes
Accepted

PostgreSQL - Return type and EXECUTE QUERY

It's a mismatch of nesting levels. The return type of the function is declared as: returns setof core.search_result core.search_result being the composite type you created earlier. This is ...
Erwin Brandstetter's user avatar
2 votes

PostgreSQL parses string literal as record before calling cast

As the linked answer on SO explains, a cast is only applicable to the exact input data type declared in the CREATE CAST definition. And a string literal is of data type unknown initially - even though ...
Erwin Brandstetter's user avatar
2 votes
Accepted

Pass array of mixed type into stored FUNCTION

The solution I was looking for is this: CREATE OR REPLACE FUNCTION test_insert(daco TEXT, _journal_arr cashreg_journal[]) RETURNS TEXT LANGUAGE plpgsql AS $function$ DECLARE jr ...
rRr's user avatar
  • 151
2 votes
Accepted

Postgres: foreign key constraint for components of a composite type?

As I pointed out in comments, you could add a new composite column in main table: CREATE TYPE abtype AS ( a INTEGER, b INTEGER ); CREATE TABLE main ( -- a and b are separate fields a ...
McNets's user avatar
  • 23.8k
2 votes
Accepted

Postgres before insert trigger access field of a type

I can reproduce the problem with snippet 1 in Postgres 12: db<>fiddle here This is clearly a shortcoming of PL/pgSQL. But no need to report it as it has been fixed in Postgres 14. See: db<>...
Erwin Brandstetter's user avatar
1 vote
Accepted

Assign a value to a field of a composite type (a row in an array of rows)?

Yes, this feature is missing in the arsenal of array functions. It's rather exotic, and one would typically operate with a (temporary) table instead of an array of rows. That said, there is a way ...
Erwin Brandstetter's user avatar
1 vote
Accepted

Composite index performance with varchar columns

While some RDBMSs might be tuned to interact better with fixed-length strings (I seem to recall that maybe DB2/MVS did better with fixed-length strings, but I learned this in 1996 and it was mainframe ...
Solomon Rutzky's user avatar
1 vote
Accepted

AutoIncrement non-primary key or Make unique composite key?

As Vérace suggested, you can do both: create a surrogate (auto-increment) key to simplify joins and related table structures, and add a unique constraint on the two current PK columns to continue to ...
Josh Darnell's user avatar
  • 29.4k

Only top scored, non community-wiki answers of a minimum length are eligible