Copy into Postgres with Arrays in Schema

create type article as (title text, contents text);

WITH "raw_data" ("author", "articles") AS  (
    VALUES
        ('author1', ARRAY[('title1', 'content1'), ('title2', 'content2')]),
        ('author2', ARRAY[('title3', 'content3'), ('title4', 'content4')])
),
"articles"  AS (
    SELECT "author", ((unnest("articles"))::text::article).* FROM "raw_data"
)
SELECT * FROM "articles";

author	title	contents
author1	title1	content1
author1	title2	content2
author2	title3	content3
author2	title4	content4

postgres insert data from an other table inside array type columns

Basically, your query fails because the structures of the tables do not match - so you cannot insert into test select * from test2.

You could work around this by adding "fake" columns to the select list, like so:

insert into test
select idweb, 'foo', 0, quantities, dates  from test2 where idweb in (select id from test) 
on conflict (id) 
do update set
    quantities = array_cat(excluded.quantities, test.quantities),
    dates = array_cat(excluded.dates, test.dates); 

But this looks much more convoluted than needed. Essentially, you want an update statement, so I would just recommend:

update test
set
    dates = test2.dates || test.dates,
    quantities = test2.quantities || test.quantities
from test2
where test.id = test2.idweb

Note that this ues || concatenation operator instead of array_cat() - it is shorter to write.

Demo on DB Fiddle:

id | category | quantitie | quantities | dates        
-: | :------- | --------: | :--------- | :------------
 2 | cat2     |        99 | {22}       | {879889}     
 1 | cat1     |        33 | {34,66}    | {8776,123678}

Plpgsql; store all table names into an array

SELECT array_agg(table_name::text)
FROM information_schema.tables
WHERE table_schema='public' AND table_type='BASE TABLE';

You need to cast the table name to text. The subquery is unnecessary, and you need to use array_agg not the array pseudo-function.

Personally I don't see why you need to aggregate them into an array at all, though. I'd just:

DECLARE
    tablename text;
BEGIN
    FOR tablename IN
      SELECT table_name FROM information_schema.tables
      WHERE table_schema='public' AND table_type='BASE TABLE'
      AND ... my extra filters here ...
    LOOP
        EXECUTE format('CREATE TABLE %I AS TABLE %I', tablename || '_backup', tablename);
    END LOOP;
END;

Is it bad design to use arrays within a database?

Short answer to the title: No

A bit longer answer:

You should learn to use arrays when appropriate. Arrays are not bad design themselves, they are as atomic as a character varying field (array of characters, no?) and they exists to make our lives easier and our databases faster and lighter. There are issues considering portability (most database systems don't support arrays, or do so in a different way than Postgres)

Example:

You have a blog with posts and tags, and each post may have 0 or more tags. The first thing that comes to mind is to make a different table with two columns postid and tagid and assign the tags in that table.

If we need to search through posts with tagid, then the extra table is necessary (with appropriate indexes of course).

But if we only want the tag information to be shown as the post's extra info, then we can easily add an integer array column in the table of posts and extract the information from there. This can still be done with the extra table, but using an array reduces the size of the database (no needed extra tables or extra rows) and simplifies the query by letting us execute our select queries with joining one less table and seems easier to understand by human eye (the last part is in the eye of the beholder, but I think I speak for a majority here). If our tags are preloaded, then not even one join is necessary.

The example may be poor but it's the first that came to mind.

Conclusion:

Arrays are not necessary. They can be harmful if you use them wrong. You can live without them and have a great, fast and optimized database. When you are considering portability (e.g. rewriting your system to work with other databses) then you must not use arrays.

If you are sure you'll stick with Postgres, then you can safely use arrays where you find appropriate. They exist for a reason and are neither bad design nor non-compliant. When you use them in the right places, they can help a little with simplicity of database structures and your code, as well as space and speed optimization. That is all.

How can I get data from a certain column of a .tsv file into a Postgres array column? Is regex the right tool, or should I look for another approach?

Using awk for it. Your data with a twist in the fifth field (I left the timestamp_[123]s there for you but fixed the missing tabs):

$ cat data
1234    e@mail.addy 43210   0123456789  foo_value,bar_value 107.00  0.00    timestamp_1 timestamp_2 54321   string_2    string_3    timestamp_3 98765   12345   US  Hawaii  string_4    string_5    string_6    string_7    string_8    false   true    false
12345   e@mail.addy 43210   0123456789  foo_value}bar_value 107.00  0.00    timestamp_1 timestamp_2 54321   string_2    string_3    timestamp_3 98765   12345   US  Hawaii  string_4    string_5    string_6    string_7    string_8    false   true    false

Add curly brackets with awk and escape pre-existing if any (above, second record, fifth field):

$ awk '
BEGIN {
    FS=OFS="\t"              # set input and output delimiters to a tab
}
NR==1 {                      # first record in file
    nf=NF                    # store field count
}
NF==nf {                     # process only records with the same field count as the first record
    gsub(/\{/,"\\{",$5)      # escape left curly brackets with a \
    gsub(/\}/,"\\}",$5)      # escape right curly brackets with a \
    $5="{" $5 "}"            # surround the fifth with curly brackets
    print                    # output
}' data > processed_data     # redirect output to another file

If you don't escape them, you'll get:

psql:bar.sql:1: ERROR:  malformed array literal: "{foo_value,bar}value}"
DETAIL:  Junk after closing right brace.
CONTEXT:  COPY postgres_table, line 2, column col5: "{foo_value,bar}value}"

Output:

$ cat processed_data
1234    e@mail.addy 43210   0123456789  {foo_value,bar_value}...
12345   e@mail.addy 43210   0123456789  {foo_value,bar\}value}...

\COPY script:

$ cat copy.sql
\COPY postgres_table(col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23,col24,col25) FROM 'processed_data' CSV DELIMITER E'\t';

Execute:

$ psql -h host -U user -f copy.sql database
Timing is on.
COPY 2
Time: 5.966 ms

Query that col5:

database=# select col5 from postgres_table;
          col5           
-------------------------
 {foo_value,bar_value}
 {foo_value,"bar}value"}
(2 rows)

Time: 3.388 ms

Is it possible to copy an enum type from one schema to another

For whatever reason, when calling a function, and only in the function parameters, I cannot use the types the api can normally reference using qualified notation

That is because PostgREST uses a CTE when building the query to call the function and casts the data to the types of the parameters as seen in these lines of code.

There is a closed issue in the GitHub repository mentioning this problem that is labeled as won't fix.

The other is the topic of this post: is there a way to automatically create a copy of the type in the core schema to one in the api? Or, is there a way to reference the core.my_type using an alias?

You could create a DOMAIN as a workaround. That way, any modification you do using ALTER on the underlying private data type will be reflected on the domain. For instance:

create schema api;
create schema private;
create role web_anon nologin;
-- web_anon doesn't have usage on the private schema
grant usage on schema api to web_anon;

-- create type and domain
create type private.grade as enum('a','b','c','d','e');
create domain api.grade as private.grade;

-- The function uses the domain api.grade instead of private.grade
create or replace function
  api.get_grade(g api.grade) returns text as $$
begin
  return (select g);
end;
$$ language plpgsql;

-- This change will reflect on the domain
alter type private.grade add value 'f';

---

Related Topics