Questions tagged [composite-types]
The composite-types tag has no usage guidance.
48
questions
0
votes
0answers
10 views
Why are arrays of rows of mismatched types allowed in PostgreSQL?
I'm suprised that I can do the following. Why is that allowed? Doesn't it violate some sort of type check? Or am I misunderstanding what it is actually doing?
postgres=# SELECT ARRAY[ROW(1), ROW(...
0
votes
0answers
11 views
How to extract fields of anonymous row type in PostgreSQL
In PostgreSQL I can create an anonymous row or composite type
postgres=# SELECT ROW(1,'Hello',false) as r;
r
-------------
(1,Hello,f)
(1 row)
But how can I get the fields out again? None of ...
0
votes
0answers
20 views
Postgresql index size: penalty for composite type?
My question refers to Postgresql 12.2.
I have two almost identical tables, structure- and contentwise, the main difference being that the primary key of the second table is a composite type, while the ...
0
votes
1answer
42 views
Better way to ensure referential integrity of one column to a composite primary key?
Given a first table
CREATE TABLE table1 (
column1 INTEGER,
column2 TEXT,
PRIMARY KEY (column1, column2));
does SQL have an idiomatic way to constrain column3 in a second table
CREATE ...
1
vote
1answer
34 views
Assign a value to a field of a composite type (a row in an array of rows)?
I am currently writing a function that works with an array of rows. I need to write a value to a particular column of a particular row in the array. The following code should encapsulate the problem I'...
1
vote
1answer
69 views
Composite index performance with varchar columns
I have large fact tables with composite non-clustered indexes, something like this:
NONCLSUTERED INDEX (OrderDate,OrderType,ClientKey,ItemKey,CustomerKey)
The columns ending with "Key" are varchar(...
0
votes
0answers
112 views
Postgres. INSERT INTO table in stored procedure with array of composite type objects as procedure arguments
I have two composite types:
CREATE TYPE truck.truck_event_type AS
(
event_global_key VARCHAR(100),
event_type VARCHAR(50),
datetime_utc TIMESTAMP(4),
...
0
votes
1answer
280 views
AutoIncrement non-primary key or Make unique composite key?
I have a db with a table that has a rollover value such as OrderNumber and another column is year.
This is the primary key - a composite key - but there are other tables with which I need to use a ...
0
votes
1answer
165 views
Postgres: foreign key constraint for components of a composite type?
In Postgres, is it possible to set up a foreign key contraint involving components of a composite type, in a situation like the following one?
Suppose I have
CREATE TABLE main (
-- a and b are ...
3
votes
2answers
648 views
ENUM and ID as a Foreign Key
Using PostgreSQL 11.
I've tried digging through both Stack Overflow and here and was unable to find an answer on best practices.
I'm working on a database design and have arrived at a schema that ...
2
votes
2answers
38 views
NULL rows and IS NULL
I've been writing some queries that look like this:
SELECT
...
FROM table_a a
LEFT JOIN table_b b
ON b.id = a.id
LEFT JOIN table_c c
ON c.id = a.id
In these queries only one of the joins can ...
1
vote
0answers
524 views
Typeorm: How to make composite key in one table as foreign key in other table
I'm using Typeorm JavaScript not Typescript for PostgreSQL in Nodejs application. I have a two tables:
Resource
Passenger
In Resource Table I have id and type both are primary key and act
as ...
2
votes
2answers
2k views
Postgresql: Cast row to type
I read: Use composite type to create new table
I have a table called locations, e.g. representing objects with lat. and long. coordinates.
In another table, I declared a column of type locations (...
0
votes
1answer
42 views
PostgreSQL CREATE TYPE using multiple relations?
I'm becoming familiar with PostgreSQL's ability to define a data type https://www.postgresql.org/docs/current/sql-createtype.html using CREATE TYPE syntax.
It appears to me that this type does not ...
5
votes
3answers
1k views
Postgres jsonb vs composite type performance differences
What considerations are involved in choosing between a jsonb column and a composite type column of the same structure?
For example, consider a column like that used in the Postgres documentation:
...
2
votes
3answers
715 views
Pass array of mixed type into stored FUNCTION
I am preparing procedure, that is basically finishing payment. It takes all info as arguments and do all inserts and stuff in one transaction.
the problem here is, that one argument is list of n ...
1
vote
1answer
804 views
PostgreSQL - Return type and EXECUTE QUERY
My function returns a setof composite type, however the RETURN EXECUTE seems to return some other unknown type:
create type core.search_result as (
user_id uuid,
country core.country,
ethnicity ...
0
votes
1answer
1k views
Postgres FOREACH array as composite type
I'm trying to loop over an array with two types as a composite type, but it seems that I can not use the composite type as the variable array to loop over it. What I'm trying to accomplish:
create ...
6
votes
2answers
628 views
Is there a way to partially initialize a TYPE?
I have created a custom type:
CREATE TYPE my_type as(name text, street text, location text);
Initializing such a type is easy:
SELECT ROW('a', 'b', 'c')::my_type;
I am wondering if there is an ...
1
vote
1answer
26 views
What type of rows does `SELECT my_table FROM my_table` return?
I sometimes encounter this SELECT statement (in JSON-related answers on stackexchanges):
SELECT my_table
FROM my_table;
It seems to turn a one-column result, with multiple rows.
What would be the ...
6
votes
1answer
3k views
How to work around Sequelize not supporting composite foreign keys
I am developing an advanced query with Sequelize (hitting a PostgreSQL database). It's not difficult in pure SQL, but I can't get it work in Sequelize, in part because they don't support composite ...
4
votes
3answers
2k views
Is there anyway to UPDATE a series of columns on a row with a composite type record/row?
Take a table foo,
CREATE TABLE foo(a,b,c,d)
AS VALUES
(1,2,3,4);
I can insert into foo with a row type..
INSERT INTO foo
SELECT (foo).*
FROM foo;
INSERT INTO foo
SELECT (v).*
FROM ( ...
4
votes
1answer
964 views
Using a type in place of a column definition list?
Running the below, I get "a column definition list is required for functions returning "record"".
SELECT *
FROM json_to_record('{"a":1,"b":2,"c":3,"d":4}');
ERROR: a column definition list is ...
1
vote
1answer
6k views
PostgreSQL function param json_populate_recordset ARRAY problem
Previous question: PostgreSQL function parameters JSON to TYPE
I am trying to create a sample of code using json_populate_recordset, but it seems I am doing something wrong. I have created a test_GPS ...
4
votes
2answers
699 views
How to set/get a field of a nested composite type variable?
For example:
create type t1 as (a int2, b text, c boolean);
create type t2 as (a t1, b boolean);
create type t3 as (a t2, b int4);
Now, how can I do the following for a variable x of type t3:
x.a.a....
1
vote
1answer
664 views
PostgreSQL function's input/output as JavaScript nested objects [closed]
I am looking for a simple/clean way that can be used consistently to automatically input JavaScript nested objects to user-defined PostgreSQL functions and also automatically output to JavaScript ...
2
votes
1answer
2k views
PostgreSQL parses string literal as record before calling cast
In my PostgreSQL 9.6 database, I have a composite type, let's call that type widget. I have a PL/pgSQL* function called text_to_widget that parses textual representations of widgets to widget values, ...
1
vote
0answers
364 views
Postgres composite type not using index
I have a composite type where one subcolumn is an integer and the other is a numrange.
I'm using: PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 6.3.0-5ubuntu1) 6.3.0 20170124, 64-...
2
votes
1answer
708 views
Column does not exists when accessing inner type field in ORDER BY
In PostgreSQL 9.4 I have two types:
CREATE TYPE a_schema.type_child {
an_order smallint
}
CREATE TYPE a_schema.type_parent {
pluto uuid,
child type_child
}
Then in a view I have this script:...
4
votes
2answers
13k views
Use array of composite type as function parameter and access it
I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table.
This is my type:
...
2
votes
0answers
68 views
Any drawbacks to referencing multiple tables from a single column?
I'm working on a system with the following entities:
Projects
Sections
Activities
There is one important constraint that needs to be enforced:
Activities are always part of one project. Activities ...
1
vote
0answers
1k views
Is there a way of implementing a composite primary key with varying fields in MongoDB?
Here is the information about my development environment:
-Mongodb 3.0.0
-Microsoft Visual Studio Professional 2013
-.NET Framework 4.5
We are trying to design some MongoDB collections at work ...
8
votes
2answers
4k views
Use composite type to create new table
In ORDBMS databases I have seen that complex types can be used:
create type name as( ...)
And I can references those types when creating a new table, like:
create table example (row_name ref(name))
...
4
votes
1answer
2k views
Return type of JOINing stored procedure - PostgreSQL
Is it possible to express return type of stored procedure that returns joined tables using table type of one of joined tables?
My stored procedure looks like this:
CREATE or REPLACE FUNCTION ...
3
votes
1answer
288 views
INSERT inside PL/pgSQL function gives different result
In PostgreSQL 9.4.1 I have created a custom type my_test_type and have found that doing a basic INSERT with NULL::my_test_type gives an expected result, but with NULL::my_test_type as a function ...
1
vote
1answer
251 views
Table-based custom type does not verify check constraints
Is there any way for a table-based custom type to obey the table check constraints, other than creating my own type based on the table?
Assume the following table that serves as a source for the ...
0
votes
1answer
61 views
persistence entity with composite key
I have class named 'User'. It has the following properties
firstname
lastname
mobile
email
pin
I want to design a table or hibernate entity that will have unique email for each row; the same for ...
5
votes
2answers
2k views
Good layout of 3d point data for spatial queries in Postgres?
Like shown in another question, I deal with a lot (>10,000,000) entries of points in a 3D space. These points are defined like this:
CREATE TYPE float3d AS (
x real,
y real,
z real);
If I am ...
3
votes
1answer
155 views
Is 'internallength' always useful in custom type definition in Postgres?
In our database we use a custom type to store 3D locations:
CREATE TYPE float3d AS (
x real,
y real,
z real);
In many examples I see INTERNALLENGTH used as a parameter with the definition of ...
8
votes
1answer
8k views
How to access NEW or OLD field given only the field's name?
I'm writing a validation trigger. The trigger must validate that the sum of an array equals another field. Since I have many instances of this validation, I want to write a single procedure and create ...
8
votes
1answer
9k views
Automatic conversion of anonymous records returned from an UDF to well known table-type
Proprietary code (that we cannot change) has a bunch of user defined functions of the type:
create or replace function f() returns record as $$ ... $$
which we call in the following way (for example)...
3
votes
1answer
4k views
How to add FK on a column of composite type in PostgreSQL?
I have a table which contains a composite type and I am trying to connect a column of it to another table's PK.
CREATE TABLE aaa (col1 SERIAL PRIMARY KEY);
CREATE TYPE bbb AS (col2 INTEGER);
CREATE ...
0
votes
1answer
704 views
How is a composite database different from an ESB in that both provide a single point of access for multiple data sources
After reading this article on composite db software here
it seems to me that the composite db software is not exactly a databse store like Oracle which physically stores the data. It is more like a ...
12
votes
2answers
6k views
Composite Primary Key efficiency as a Foreign Key
I have a table with a Composite Primary key (consisting of 4 columns) which is used to ensure no duplicates are entered into the table. I am now in need of a new table which will need to reference the ...
5
votes
1answer
5k views
Foreign Keys with Composite Key
Imagine I have a table with the following dependency:
(Name, BoughtFrom, TimeBought) --> Cost
Where the three attributes in bold form a composite primary key for the table. Now then, I want to ...
3
votes
1answer
422 views
PostgresSQL: Get single attribute of UDT in SELECT statement
I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work.
Creation of type ...
4
votes
2answers
5k views
Dynamic access to record column in plpgsql function
How can I address a column from a record in a plpgsql function dynamically?
In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the ...
15
votes
1answer
27k views
Pass an array or record to a function in PostgreSQL?
I have a task to pass arrays, records, and in some cases array of records as a parameter to functions in PostgreSQL.
