New answers tagged postgresql
0
You could use jsonb_array_elements instead:
SELECT jsonb_array_elements('["one", "two"]'::jsonb) ->> 0
2
You cannot update a derived table, because it's transient. Even if you could, the UPDATE wouldn't necessarily respect the ORDER BY in the subselect.
What you want is a deferrable primary key constraint, which will be validated at the end of the transaction:
...
CONSTRAINT current_way_nodes_pkey PRIMARY KEY (way_id, sequence_id)
DEFERRABLE INITIALLY ...
0
Not everything is replicated with logical replication: only INSERT, UPDATE and DELETE (from v11 on also TRUNCATE). So it is definitely not a substitute for streaming replication. Also, streaming replication performs better.
Switching from streaming replication to logical replication is possible with little down time if you suspend the application on the ...
0
When a record is added that needs to be updated, store its ID in a third table. When it's updated, delete. The small size of the new table will guarantee performance, no matter how large the existing tables are.
1
Unless you have simplified your problem to the point it has become a different problem, you don't even need a join to socks as all the information you need is in stripes:
select id from stripes s where color = 'red'
and not exists (select 1 from stripes s2 where s2.id = s.id and s2.color = 'blue')
You don't even need a separate process to add stripes:
...
0
The failure limit is 2,000,000,000, which is ten times more than the autovacuum_freeze_max_age cutoff of 200,000,000. So there is nothing to worry about there, you are very far from failure and it will automatically kick in in plenty of time.
But once an anti-wraparound vacuum does kick in, it might cause inconvenience due to the table lock. So you might ...
1
In PostgreSQL, as with any other SQL database, you must end each SQL statement with a semicolon. In your screenshot, both create database test; and select * from postgres; must be terminated with a semicolon, as I have done.
0
No, VACUUM will not freeze all rows, so it won't set datfrozenxid to the current transaction ID.
VACUUM won't freeze any row that is less than vacuum_freeze_min_age transactions old (default 50 million).
Rows that have remain unmodified for a while have good odds of staying around, so it is worth freezing them. New rows will often get deleted or updated ...
1
The result will always be the same: db will have all rows from db_view1 at stage A added twice.
The SELECT that is part of the INSERT will see stage A, just like the query in the other question.
1
We are allowed to reference the derived table totals1 in the WHERE clause, as you already do but here we are actually referencing a column, not the table as a whole:
WHERE accounts.id = totals1.account_id
But while totals1 is still visible inside the subquery, it is not allowed to use it in a FROM clause.
The only tables allowed to use in a FROM clause are ...
0
JSONB, unlike JSON, has ordering operators defined on it. That is all that's needed to create histograms. So JSONB does have them, regardless of how useful they may be. So my question is, why does table_b not have them? I think this has been the case since JSONB was created.
Also v13 changed the way selectivity of match operators was estimated. Now it ...
0
This is what I understand from the source code. Although server can discard records older than flush_lsn, in practice, pgoutput needs record older than flush_lsn to perform the logical decoding. i.e., to translate physical changes to logical changes. These older records are related to schema changes. Thus, usually restart_lsn < flush_lsn and restart_lsn ...
2
Due to parallel query introduced in PostgreSQL 9.6, your SELECT will automatically be parallelized, so you won't gain anything by using multiple connections. You can override the default degree of parallelization by setting the parallel_workers storage parameter on the table.
Concurrent inserts can run in parallel. If there are conflicts, like unique ...
0
The WAL records remain; WAL is never modified retroactively. They will be replayed during recovery, but since there is no COMMIT records for that transaction in the WAL, they will remain invisible.
1
By virtue of PostgreSQL's multi-versioning implementation, no transaction will be blocked. Each of these queries will see the state of the database like it was before the insert started.
PostgreSQL takes a snapshot of the database at the start of the query, and it sees the data as they were when the query started. Also, you never see modifications from a ...
1
You should definitely check the tsbs project: https://github.com/timescale/tsbs/
It can help you to do this by configuring the --workers param in the tsdb_load command. You can apply the parameter while running queries with the tsbs_run_queries command to confirm how it will behave.
In my benchmarks, generally, a 16 CPU server works well with 12 to 24 ...
0
You need to create a new index and replace the old one:
CREATE UNIQUE INDEX CONCURRENTLY newidx ON tab (name, price, sku);
DROP INDEX name_price_sku_unique_index;
ALTER INDEX newidx RENAME TO name_price_sku_unique_index;
1
When you select from a view, it runs as the user who created the view. Apparently this view was created by a user who did not have access (or later lost access) to the underlying table.
Why would someone do that? No idea. The permissions system is flexible and let you do all sorts of wonderful things, but also all sorts of nonsense.
1
We have found the causer : it was the pg_dump process, it took over 33GB of RAM during metadata export, a bit of more RAM saved the export.
-1
First check the query you setup on Cron by running it on the db where the tables reside.
If the query is OK then set it again on Cron and set the Cron timings to * * * * * so that you can check the results on real time. If it succeeded then you can change the Cron timings.
Please refer the link below for detailed explanation on Pg_Cron:
https://docs.aws....
1
Here is a query to help find all tables that have the WITH OIDS set. It also generates the DDL to remove.
SELECT 'ALTER TABLE "' || n.nspname || '"."' || c.relname || '" SET WITHOUT OIDS;'
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE 1=1
AND c.relkind = 'r'
AND c.relhasoids = true
...
1
Timescale splits the data into "child table" called chunks across the time.
The chunks are based on time interval, and you can choose the time interval.
If you want to compress your data, you can add compression policies that can run every X hours to compress the data older than X time interval.
The problem can come if systems get off-line for more ...
2
replication without quotes is not the actual name of a database, it is a magic word. It does not match against all. You could list all,replication to cover both in the same line.
0
I received an answer on SO.
You cannot use non-deterministic collations at a database level in Postgres 12. You can only use non-deterministic collations in column definitions.
1
Contrary to wide-spread belief, it does not matter if you put columns with many different values first in the index or not.
What matters is that you put those columns first that are used with the = operator in the WHERE clause. If all columns are compared with = (and there are no ORs in the WHERE clause), the order does not matter for the performance of that ...
0
According to the pgadmin documentation there are to ways to unlock the user.
1. Updating SQLite DB (pgAdmin4.db):
Locate the pgAdmin4.db file and open it using any DB Browser (or DB Browser for SQLite)
After opening the DB file, head towards ‘Execute SQL’ section.
Run below query :
UPDATE USER SET LOCKED = false, LOGIN_ATTEMPTS = 0 WHERE USERNAME = <...
1
You do not need a recursion, you can calculate the matrix of all the possible string combinations.
I find this to be more straightforward:
DROP TABLE IF EXISTS strings;
CREATE TABLE strings (
id SERIAL PRIMARY KEY,
string CHAR(1)
);
INSERT INTO strings
(string)
VALUES
('A'), ('B'), ('C'), ('D')
;
WITH
string_matrix AS (
SELECT
...
0
There's a number of things you need to change.
A temp table is not what you want, they are removed as soon as the connection that created them closes, and they cannot be accessed from any other connection. Use a regular table instead.
You have an unneeded left parenthesis (that is unclosed) in the CREATE TABLE AS statement; it's just CREATE TABLE <tblname&...
1
That should work fine, unless you configured your manually built PostgreSQL with some option that renders it incompatible with the binary packages, like --with-blocksize=<something other than 8k>.
There are other corner cases, like one installation using --with-system-tzdata, and the other using the time zone definitions shipped with PostgreSQL, so ...
1
As documented in the manual the complete formula for the cost value also includes a CPU part that is calculated per row
The estimated cost is computed as (disk pages read * seq_page_cost) + (rows scanned * cpu_tuple_cost). By default, seq_page_cost is 1.0 and cpu_tuple_cost is 0.01
The above is the formula for a Seq Scan thus the cost value for sequential ...
0
No , Its not required to add each users/host every time for new users or hosts. You can just specify the below:
host all all 0.0.0.0/0 md5
After specifying the above one on the pg_hba.conf file just have a reload of the system using below command by login into the postgres using psql:
postgres=> SELECT ...
1
The locale parameter of CREATE DATABASE expects a name of locale that can be recognized and used by the setlocale POSIX function of the underlying operating system, or whatever is the equivalent if the OS is Windows.
On the other hand, the names of collations that are user-defined by CREATE COLLATION and whose scope is only the current database do not have ...
0
All the pg_hba that i have seen provided by default had 'all' lines, which match all users not already covered by earlier lines. Unless your mysterious tutorial had you remove those lines first, adding lines back in one by one would not be necessary. you also don't need to restart, a reload will work. You can also add lines for groups, with membership ...
0
The document you followed is not wrong but can be improved. After editing the pg_hba.conf the database does not need to be restarted but it just needs to reload the configuration for this.
pg_ctl reload will do this.
You do need to edit pg_hba.conf to allow access. You can use wildcards so you don’t need to do this for every user. Since you will be using ...
7
So I'm not sure why you want to do this, but I'll just assume you've exhausted all other options.
TLDR, he's a fiddle to do what you want: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=27cdc7ef6eaf179936d4d048276b139b
Explanation
To do this we need three things:
A list of elements
A collation with a sort order we agree with for this purpose
A recursive ...
1
WITH RECURSIVE
cte AS ( SELECT id, cnt, rel_id
FROM test
WHERE rel_id IN (id, '')
UNION ALL
SELECT test.id, test.cnt + cte.cnt, test.rel_id
FROM cte
JOIN test ON cte.id = test.rel_id AND test.rel_id <> test.id )
SELECT *
FROM cte
ORDER BY SUBSTRING(id FROM 2)::INT;
https://dbfiddle.uk/?rdbms=...
3
Whatever you do, don't use a text type to store fixed-length binary data. For example, UUIDs stored as text take 33 bytes, while the UUID type takes only 16 bytes. Your "short-UUID-like type" is 13 characters, taking 14 bytes when stored as text. For a saving of only 2 byes and a risk of collisions, I wouldn't use it.
0
Similar to what ypercubeᵀᴹ suggested:
If I understand correctly, it could probably be solved with a complex regex, e.g. CHECK (code SIMILAR TO '(A|AG|AL|AS|B|C|D|DA)(1|2|3|4)(X|Y|Z)') for a code that consists of 3 parts, the first as described in the question, the second part a digit from 1 to 4 and the 3rd part of a character, either X, Y or Z.
This ...
2
User postgres has no "execute" permissions on the /media directory. Hence the error.
1
The value matches the condition because it contains A.
Regular expressions are not only complicated to understand, they are also often expensive to process (there is a connection between these two). So if you can do without them, do it.
In this case, the check constraint should be
ALTER TABLE mytable
ADD CONSTRAINT verif_code CHECK (code IN ('A', 'AG', 'AL', ...
2
I think you've answered your own question, but here are some observations.
From the question, I suspect that the abc database had all of its tables created in the default public schema, while the xyz database has its tables created in the xyz schema. Since public is part of the default search path, psql could find abcs tables successfully while in xyz you ...
0
If you create the subscription to the new master with (copy_data = false) it will not try to copy the data again.
As long as you let all changes replay before dropping the old subscription, and create the new subscription before anyone else is allowed to start making new changes, then there should be no loss of data.
That is a bit fraught though. It would ...
1
I saw this answer and it partially solved the problem I was having ("The relations appear to be missing because Postgres does not copy the search_path to the new database").
That is, I can see the tables like this:
xyz= \dt xyz.*
but not like this:
xyz= \dt
I tried setting the search path, currently:
"$user", public
to:
public, xyz
...
2
directory "/var/lib/postgresql/11/main" is not a database cluster
directory
It means that this directory does not contain a PG_VERSION file, or it's somehow unreadable.
This file contains one line of text with the major version of PostgreSQL that corresponds to this data directory (11 in your case).
This might imply that the data directory has ...
0
If anyone else has this problem I managed to get this fixed by switching the where clause to where id = ANY($1) and using bigint[] as the argument type
2
I had a similar problem, also using Supabase. I ended up making a view with the editable columns for the users table, where the view does the row-level filtering:
CREATE OR REPLACE VIEW editable_user AS
SELECT u.name
FROM pub_users u
WHERE auth.uid() = u.id;
In my case I only wanted my users to be able to edit their own name, but this should be ...
1
Changing NOT IN to NOT EXISTS as suggested by a_horse_with_no_name was the solution:
not in () in Postgres is not as efficiently optimized as in Oracle. Try rewriting that as a NOT EXISTS condition.
I replaced the subquery
AND (bd.INSTANCE_ID, bd.ATTRIBUTE_ID, bd.INSERTED) NOT IN (SELECT ... )
with
AND NOT exists
( SELECT 1
FROM my_data dc
...
1
To speed up the inner loop as much as possible, you could use this strange and very specialized index:
CREATE INDEX ON my_data (
(CASE WHEN (tenant = 1) THEN 1 WHEN (tenant = 2) THEN 3 ELSE -1 END),
inserted
)
WHERE modified_record IS NOT NULL
AND tenant = ANY ('{1,2}'::bigint[])
AND status = 'DELETED';
3
Any solution that DELETEs a lot of rows from a database table is painful. You will always end up doing something along the lines of WITH d AS (DELETE ... RETURNING ...) INSERT INTO ... SELECT * FROM d, which takes a long time, entails a lengthy autovacuum run and leaves the table bloated.
The king's way to solve this problem is to use partitioning. Then you ...
2
Since your postgresql.conf has the parameter data_directory set, it seems likely that postgresql.conf is in a directory different from the data directory.
When starting the database, the argument to the -D option is not the data directory, but the directory that contains postgresql.conf.
So, assuming that you are following the normal Debian/Ubuntu layout, it ...
Top 50 recent answers are included