4

Not providing any default value or explicitly providing NULL is exactly the same thing. Adding a column with a NULL default value, does not require rewriting the entire table. It is essentially only a metadata update for the table. Quote from the 9.6 manual If there is no DEFAULT clause, this is merely a metadata change and does not require any ...


3

Manual:https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT statement_timestamp() / now() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client). clock_timestamp() returns the actual current time, and therefore its value changes even ...


3

pg_cancel_backend takes a process id as its argument. It doesn't care if that comes from you typing it in, or a from a query. That makes it possible, albeit quite risky, to combine these two into one: SELECT pg_cancel_backend( pid ) FROM pg_stat_activity WHERE usename = 'foo_user' AND state = 'active' ; OK, this doesn't give you option to escalate the "...


3

Just use the simple text search configuration, which works with the simple dictionary and does not do any stemming: CREATE INDEX ON tbl USING GIN (to_tsvector('simple', data)); And query accordingly: SELECT * FROM tbl WHERE to_tsvector('simple', data) @@ to_tsquery('simple', 'foo'); Related: Get partial match from GIN indexed TSVECTOR column Finding ...


2

You understood correctly. Since streaming replication keeps the replica a physical copy of the primary database cluster, you can only replicate between one primary and one replica. There are two options: Keep three clusters on the replica machine, one for each primary cluster. Use logical replication. With logical replication, you have to make sure that ...


2

Use it with your first select: SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE usename='foo_user'


2

You need a function which extracts the IP addresses into an array. The regexp you gave doesn't work for me, so I came up with my own crude one, you will probably want to tweak it to suit yourself: create or replace function extract_ip(text) returns text[] immutable language SQL as $$ select array_agg(x[1]) from regexp_matches($1,'\d+.\d+.\d+.\d+','g')f(x)...


2

The (2, 3) has type record, i.e. Postgres doesn't know which (or even how many) columns it contains. You can use the .* syntax only with known ("registered") types. You could define your own tuple type and use that instead of a record: CREATE TYPE tuple AS (a int, b int); SELECT 1, (CASE WHEN TRUE THEN '(2, 3)'::tuple ELSE '(4, 5)' END).*; SELECT 1, (CASE ...


2

PostgreSQL can only use an index to count the number of rows if the table has been vacuumed recently so that most table blocks are marked "all visible" in the visibility map. Otherwise it has to inspect the table to check ifbthe row is visible or not, and then an index scan is more efficient. I think that the solution is not to count the rows. For a cheap ...


2

Postgres generally handles server restarts pretty well. Assuming that all the correct hardware is in place (battery-backed cache, etc.), bringing up Postgres after a crash will automatically cause it to enter recovery mode, where it will attempt to replay all the WAL files since the last CHECKPOINT. Once it has finish replaying up to a consistent state, ...


1

Your approach is the one I would use. You forgot a unique constraint: ALTER TABLE equipment ADD UNIQUE(id, type); That will be the target of the foreign key constraints. I would not store type as a string. Rather, I would use a lookup table for the types and store the numeric identifier. This will prevent typos, and it will reduce the required storage ...


1

Text values must be converted to numeric before any math operation. UPDATE schema.table SET ColumnA * ColumnC::numeric -- Use same data type as ColumnA Have a look at this tutorial


1

You want to do a LATERAL join, by putting the the function in the FROM list. If you just put the function in the from list after table "a", you don't need the LATERAL keyword, as joins to functions are automatically lateral. If you wrap it in a dummy "select * from", then you do need the keyword: WITH A AS (SELECT DISTINCT trip_id FROM stop_times) SELECT ...


1

In the example CREATE TEXT SEARCH CONFIGURATION fr ( COPY = french ); where the ( COPY = french ) is located? Text search configurations are stored in pg_catalog.pg_ts_config. They can also be displayed with \dF[+] in psql. Can be the copy statement ( COPY = french ) use data from the STDIN with my own rules? No. The COPY clause in CREATE TEXT SEARCH ...


1

You are including literal single quote marks into the LIKE query (as shown in the output of your first query). These literal quote marks are not present in the data, so they do not match. select tablename from pg_tables where tablename like ( select '%_p' || replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%' ); ...


1

You can create unique partial index: create unique index on foo (kind) where kind=2;


1

There is no alternative. Many people would like to have a bug tracker, and there are regularly spirited attempts to introduce one. They usually fail because of lack of buy-in by the community. One of the problems is that it must be possible to continue using e-mail as an interface. The other problem is that PostgreSQL does not want to become dependent on ...


1

I'm not sure this is the correct answer, but reading the docs about ALTER TABLE I would say: No, you can't. where action is one of: ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ] and column_constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK ( expression ) [ NO ...


1

You may want to turn on AUTO VACUUM? AWS Docs suggests that as a best practice Set the autovacuum and once it is done, you can turn it off if you wish. If you want to really run it in a terminal and then disconnect, refer to this answer: How to execute a query from psql without waiting for the result?


1

If the strings are from a restricted set, you can define an ENUM datatype. This translates the strings to integers behind the scenes. create type alph as enum ( 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z'); create table j as select floor(random()*100)::int, array_agg(substring('...


1

Please try this: DELETE FROM journal WHERE id NOT IN ( SELECT j.id FROM journal j WHERE j.created_at >= 636742944000000000 ORDER BY j.created_at DESC ) Please make sure to create the index on created_at and id. In additional, I think id in a table should be unique/primary key.


1

Indexes use space, and a 16 byte key is nothing to worry about. So you should define the primary key on user_relations on (relating_user_id, related_user_id). If you need to search by relation_type, it won't help to put the column into the INCLUDE list, since such columns cannot be used as filter for an index scan. I see two options: In addition to the ...


1

According to the pgAdmin download page, that would be 9.5. Don't select your database version based on a client GUI. PostgreSQL 9.5 will go out of support in about a year. Start using psql, it is included with PostgreSQL and much more powerful. If you insist on a GUI, have a look at the Wiki page listing client tools.


1

While you may be strongly discouraged from continuing to use pgAdmin 3, there's nothing stopping you if you really want to use it. Below you will see pgAdmin 3 connected to a postgresql 12 instance. Note however, that you're very much doing this at your own risk, you're going to have to click through a lot of error messages when running in an unsupported ...


1

To do it with built-in tools of Postgres and the hstore module exclusively, without involving jsonb, as requested, replace: audit_row.row_data = hstore(NEW.*) - excluded_cols; with: audit_row.row_data = hstore( ARRAY ( SELECT ARRAY[key, value] FROM each(hstore(NEW.*) - excluded_cols) WHERE value IS NOT NULL ) ); While ...


1

SELECT t1.reviewer_id AS first, t2.reviewer_id AS second, t1.book_id FROM table AS t1 LEFT JOIN table AS t2 ON t2.book_id = t1.book_id AND t2.reviewer_id > t1.reviewer_id or SELECT MIN(reviewer_id) AS first, CASE WHEN COUNT(reviewer_id) > 1 THEN MAX(reviewer_id) ELSE NULL ...


1

Based on your comments, the INSERT could look something like this: INSERT INTO log (userid, clientaddr, calltime, query) SELECT user_id, -- function argument a.client_addr, a.query_start, a.query FROM pg_stat_activity AS a WHERE a.pid = pg_backend_pid(); The key to identifying the current session is the process ID of the backend ...


1

You can sort using an expression: order by case when se.status in (2, 12, 22) then 1 when se.status in (1, 11, 21) then 2 else 3 -- just in case end This would sort rows with status "processing" before rows with "completed"


1

First cte unnest all elements of the array, second one update each element and then simply update the original table building the array again. with ct as ( select id, jsonb_array_elements(data) dt from t ) , ct2 as ( select id, jsonb_set(dt, '{updatedAt}', '"1571150000"', false) dt2 from ct ) update t set data = (select jsonb_agg(dt2) ...


1

Had created an index on "TELEKGERAET" as it was doing a seq scan. This helped and now the execution time is 5 secs. Thank you Laurenz for all the pointers.


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