Questions tagged [composite-types]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
26 views

Create index on array of composite types

I have this example create type price_schedule_day as enum ('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday'); create type price_element as ( price_start ...
mslot's user avatar
  • 417
0 votes
1 answer
99 views

Composite data type returned from one postgresql function seems to arrive differently in the caller

I have the following composite data type in a postgres db: CREATE TYPE IncreaseCredentialsType AS ( increase_account_id text, api_key text, url text ); I'm returning a value of this type at the ...
cayblood's user avatar
  • 101
0 votes
1 answer
71 views

Is it slower to pass a composite type representing a table to a function then the individual arguments?

Let's say you want to create a function CREATE FUNCTION f(a,b,c,x,y,z) FROM t; Is it any slower to restructure the function such that this works, CREATE FUNCTION f(t) FROM t;
Evan Carroll's user avatar
  • 61.4k
0 votes
2 answers
142 views

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

Let's say I have a simple table foo, and domain 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; ...
Evan Carroll's user avatar
  • 61.4k
0 votes
1 answer
614 views

How do you pass the array of composite type to the argument of an SQL function?

Database example: https://dbfiddle.uk/sERgZPiB Tables and types CREATE TABLE accounts ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, login text NOT NULL, password text NOT NULL, email ...
Biller Builder's user avatar
1 vote
1 answer
215 views

Best way to cast a VIEW row type to the underlying TABLE row type

I have a table with an index and a row-level security policy. Due to this problem (more details: 1, 2, 3, 4, 5), the index is not used when the policy applies, which makes my queries unbearably slow. ...
Bergi's user avatar
  • 504
0 votes
1 answer
948 views

How do I create a primary composite key from three tables?

Suppose I have tableA with A.id, tableB with B.id and table C with C.id. Now I want to create a table D that has a primary composite key(A.id, B.id, C.id). How can I do this? create tableD( A.id ....
Drilon Aliu's user avatar
1 vote
1 answer
616 views

Postgres before insert trigger access field of a type

In the DB, I have a type and a table that one of it's rows is that type, such as this: CREATE TYPE state (last_active timestamp, ...) CREATE TABLE devices ( state state, ... ) For any interaction ...
bca's user avatar
  • 113
1 vote
1 answer
327 views

Variadic functions and composite types

I have a function like so: update_stuff_variadic( VARIADIC _stuff_array stuff[] ) I understand that this syntax requires _stuff_array to match the column structure of the stuff[] table. Instead ...
user973347's user avatar
0 votes
1 answer
269 views

Define recursive composite types

Is it possible to define recursive composite types in Postgres 13? create type "t" as ( "a" int, "b" t ); I am aware that I can use a table with a parent-child ...
geeko's user avatar
  • 21
0 votes
2 answers
49 views

Finding "one of" entries to a composite key/index

Problem: Assuming I have a table like this: CREATE TABLE `test` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `a_id` bigint unsigned NOT NULL, `b_id` bigint unsigned NOT NULL, `c_id` bigint ...
Darkproduct's user avatar
11 votes
2 answers
3k views

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 ...
Link0's user avatar
  • 213
-1 votes
1 answer
1k views

why is mysql not using the composite index on my query?

Here is my query. select user_id, sum(user_points.points) overallpts, username from user inner join user_points on user.user_id = user_points.user_id where user_points.date >= '2020-01-01 00:00:00' ...
JRA's user avatar
  • 129
0 votes
1 answer
2k views

(PostgreSQL 11) Reading in an array of JSON objects (or composite types) from a CSV file into Postgres

So I am trying to import rows into a table using a CSV file, but have been running into problems I haven't been able to solve. I would like one column of a row to be an array of JSON objects. How I ...
light's user avatar
  • 15
-1 votes
1 answer
867 views

Designing a database for car dealerships [closed]

I am starting in designing a database for car dealerships (plural). I graduated recently and this is my first gig. The idea is that I am designing a backend that couple of dealerships will use. So we ...
Snorrinn's user avatar
0 votes
1 answer
1k views

Use insert trigger to copy a modified NEW record to another table, with unknown columns

I use a Postgres extension (promscale) that dynamically creates tables with similar schemas: the tables all have a 'time' and a 'value' columns, then a number of labels columns. Upon insert in any of ...
Franck's user avatar
  • 13
3 votes
1 answer
3k 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 ...
Tom Ellis's user avatar
  • 1,539
1 vote
0 answers
171 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 ...
gernot's user avatar
  • 145
0 votes
1 answer
270 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 ...
LaTeX2enub1336's user avatar
1 vote
1 answer
914 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'...
user2959071's user avatar
1 vote
1 answer
973 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(...
Avi's user avatar
  • 265
0 votes
0 answers
1k 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), ...
Andrei Khotko's user avatar
0 votes
1 answer
1k 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 ...
StixO's user avatar
  • 167
0 votes
1 answer
2k 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 ...
gernot's user avatar
  • 145
3 votes
2 answers
4k 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 ...
Jason's user avatar
  • 35
2 votes
2 answers
58 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 ...
oorst's user avatar
  • 133
2 votes
0 answers
2k 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 ...
Abdulhaq Shah's user avatar
5 votes
3 answers
12k 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 (just ...
VH-NZZ's user avatar
  • 236
0 votes
1 answer
124 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 ...
Zeruno's user avatar
  • 457
11 votes
3 answers
4k 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: ...
Metropolis's user avatar
3 votes
3 answers
3k 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 ...
rRr's user avatar
  • 151
1 vote
1 answer
5k 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 ...
Tim's user avatar
  • 113
0 votes
1 answer
4k 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 ...
chuseuiti's user avatar
  • 103
7 votes
2 answers
2k 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 ...
Katrin's user avatar
  • 359
1 vote
1 answer
49 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 ...
da99's user avatar
  • 359
7 votes
1 answer
4k 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 ...
Shawn's user avatar
  • 171
4 votes
3 answers
3k 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 ( ...
Evan Carroll's user avatar
  • 61.4k
6 votes
1 answer
2k 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 ...
Evan Carroll's user avatar
  • 61.4k
1 vote
1 answer
10k 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 ...
Vincent Vost's user avatar
5 votes
2 answers
1k 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....
geeko's user avatar
  • 21
1 vote
1 answer
959 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 ...
geeko's user avatar
  • 21
2 votes
1 answer
3k 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, ...
P Daddy's user avatar
  • 121
1 vote
0 answers
535 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-...
user3747260's user avatar
2 votes
1 answer
2k 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:...
mat_boy's user avatar
  • 317
7 votes
2 answers
22k 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: ...
Postgresql_enthu's user avatar
2 votes
0 answers
76 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 ...
Willem-Aart's user avatar
1 vote
0 answers
2k 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 ...
CS Lewis's user avatar
  • 121
9 votes
2 answers
7k 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)) ...
lcjury's user avatar
  • 247
9 votes
1 answer
6k 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 ...
czerny's user avatar
  • 523
3 votes
1 answer
324 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 ...
Ian Timothy's user avatar