14

Actually, since NEW is a well defined composite type, you can just access any column with plain and simple attribute notation. SQL itself does not allow dynamic identifiers (table or column names etc.). But you can use dynamic SQL with EXECUTE in a PL/pgSQL function. Demo CREATE OR REPLACE FUNCTION trg_demo1() RETURNS TRIGGER AS $func$ DECLARE ...


11

The cost of using a simple synthetic integer PK is small, and the benefit in your case would probably be quite considerable. As you point out, you'll have a much simpler FK relationship. A small PK makes for small (and fast) indices. Your total table space will probably be made less by adding such a column. If business rules ever change, you won't have to ...


10

Yes, you'd add all three columns. Assuming they have the same names in both tables, you'd use something like foreign key (Name, BoughtFrom, TimeBought) references the_other_table_name (Name, BoughtFrom, TimeBought) If you decide to use a surrogate ID number, you'll still need a unique constraint on {Name, BoughtFrom, TimeBought}. You can do that with ...


7

There is a way. Given a table t and a function f() that returns an anonymous record that would match that table type: CREATE TABLE t (id int, d date); You cannot just cast the anonymous record, since a column definition list is required for SELECT * FROM f() Quoting the manual on the SELECT command: If the function has been defined as returning the ...


7

I answered a basically identical question on SO some time ago: Array of composite type as stored procedure input Suggesting the same solution with unnest() like @a_horse did. Assuming that books_table has the same row type as your composite type books, you do not need to create the additional type at all, just use the row type of the table: CREATE TABLE ...


6

Use json_populate_record: SELECT * FROM json_populate_record(null::foo, '{"a":1,"b":2,"c":3,"d":4}') The column matching is done by name, non-existing columns are silently ignored: create type other_foo as (a int, b int, x int, y int): SELECT * FROM json_populate_record(null::other_foo, '{"a":1,"b":2,"c":3,"d":4}'); returns: a | b --+-- 1 | 2


6

That's tricky, because identifiers cannot be variables in plain SQL. You need to use dynamic SQL with EXECUTE - which is still tricky, because variables are not visible inside EXECUTE. Here is a demo how to get around this: CREATE TYPE mytype AS (id int, txt text); DO $body$ DECLARE _val mytype := (1, NULL)::mytype; _name text := 'txt'; ...


6

You can use a typed table: CREATE TYPE mytype AS (some_id int, some_col text); CREATE TABLE example OF mytype (PRIMARY KEY (some_id)); I added a PK constraint (which you did not ask for). The syntax is documented as second variant in the manual for CREATE TABLE, which also explains further down: OF type_name Creates a typed table, which takes its ...


5

As so often in the SQL world, the answer is: "It depends." Have a look at this question for some pointers: Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys? There are cases that see a performance improvement when using natural keys as Foreign Keys. However, in most cases you are going to be better of with ...


4

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) list of fields. SELECT ROW('a', 'c')::my_type(name, location); In a plain SQL cast expression, you have to provide NULL values (or some other default) for ...


3

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 -- x.a.a.c := true; -- Syntax error x2 := x.a ; x1 := x2.a ; x1.c := true ; x2.a := x1 ; x.a := x2 ; END ; $$ dbfiddle here


3

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 output column names as a whole. Expressions must be built on input columns. The manual: Each expression can be the name or ordinal number of an output column ...


3

The composite type is clean design, but it does not help performance at all. First of all, float translates to float8 a.k.a. double precision in Postgres. You are building on a misunderstanding. The real data type occupies 4 byte (not 8). It has to be aligned at multiples of 4 bytes. Measure actual sizes with pg_column_size(). SQL Fiddle demonstrating ...


3

assuming the question is about triggers (and I'm not 100% sure I understand the question), something along these lines may be the way to go: begin; set role dba; create role stack; grant stack to dba; create schema authorization stack; set role stack; -- create table t(id serial); -- create function f() returns trigger language plpgsql set search_path to '...


3

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 you almost never want to use them because it's almost always better normalizing them into their own relations. There is an exception to that though -- when you'...


2

Use: SELECT (ctyp).f1 FROM tbl; The parentheses are necessary to disambiguate tables from composite types as detailed in the manual on composite types.


2

No, I am afraid that's not possible. I have been wishing this was possible myself on several occasions. Either you have a registered row (composite) type that matches the return type or you have to list columns individually. CREATE or REPLACE FUNCTION select_join() RETURNS TABLE (col1 int, col2 date, ...) AS ... Or you create a composite type: CREATE ...


2

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 the default data type of a string literal is text. Postgres 10 ties up some loose ends for the data type unknown with this commit (with explanation). And ...


2

To create an array of a type, use an explicit array constructor: array[(1,100,'Arthur',1), (1,50,'Zaphod',1)]::books[] So to call your function you would need to use: select update_table(array[(1,100,'Arthur',1), (1,50,'Zaphod',1)]::books[]) But your function has an error: after the loop you are missing a return statement, because the one in the ...


2

I'm not sure if you want to create a table with a given composite type (as Erwin describes) or one containing a composite type. If the latter, it's almost the same. create type typename as( ...) then create table example ( row_name typename ) Typically you'd have more columns, like: create table example ( id serial primary key, some_composite ...


2

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 (SELECT ARRAY[1,2]) AS gs(x); -- array (int[]) SELECT * FROM (VALUES (1), (2) ) AS gs(x); -- setof anyelement (setof int) I am of the opinion that you do ...


2

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 composite type value to a column at the outer level. This preserves all other column values in the outer level. See y in the demo. But you cannot do the same for ...


2

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 an operation on a record, with a composite type. Two options that come to mind are jsonb and hstore. jsonb -- In a single call to jsonb_populate_record ...


2

It's a named composite type: https://www.postgresql.org/docs/current/static/sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS When you create a table, a TYPE with the same name is created. A row of that table is of that type.


2

An array always contains a single data type. ['2018-01-01',2] specifies an array with two different types: a varchar and an integer. To initialize an my_type value you need a row() constructor: row('2018-01-01',2)::my_item Elements of that type can then be put into an array: array[row('2018-01-01',2)::my_item,row('2018-01-02',5)::my_item or you can ...


2

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 equivalent to the more explicit declaration: RETURNS TABLE ( user_id uuid, country core.country, ethnicity core.ethnicity, is_smoker core.smoking_status, ...


1

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 uppercase, but in fact, your string is null and the conversion (toUpperCase) fails on null. That said, I don't see toUpperCase in the query you supplied, but ...


1

There are actually lots of posts regarding this.. Support UPDATE table SET(*)=... WIP patch for multiple column assignment in UPDATE Supporting multiple column assignment in UPDATE (9.5 project) Better handling of UPDATE multiple-assignment row expressions However, according to /u/pstef, one of these is committed, and this syntax now works on HEAD, though @...


1

JavaScript nested objects can be easily (and I'd say "naturally") represented using JSON. This is "by definition" of what JSON (JavaScript Object Notation) is. So, in your use-case use JSON On the JavaScript side: To parse the returned values from PostgreSQL (as strings), use JSON.parse(). To convert JSON to a string that can be included in a SQL ...


1

Merlin Moncure from the PostgreSQL General mailing list has an answer: The SQL standard mandates that rows containing all null values satisfy 'IS NULL = true'. However, postgres internally has nullibitily of container types that is distinct from their contents. I personally find this to be a good thing for various reasons but the facts are that ...


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