New answers tagged postgresql
0
PostgreSQL:
You must provide a database name in the Data Source DSN.
Run Management Studio as Administrator
You must omit the DBName from the query:
SELECT * FROM OPENQUERY([LinkedServer], 'select * from schema."tablename"')
1
To have psql verify the server certificate, you must specify the appropriate connection string options. This is done by the libpq client shared library, so it will work the same with all clients that use it.
You can directly specify the options in the connection string:
psql 'host=... port=... dbname=... user=... sslmode=verify-full sslrootcert=/path/to/ca-...
1
Some loose opinions which may be useful but may be completely off:
The model looks OK and should be scalable as far as I can tell.
Relational model (3NF) is the king of storage size, and data consistency. It's not the king of "easy querying". See customer_id note below.
In actual long term business, maintainability is often more important than performance. ...
0
Please consider this... not a ready-made solution but a suggestion which should lead you in a proper direction.
In the first part for contact_compliants, it has IN which means
either one of the names for nested will increment count. which is not
my requirement. I want to count only when when both the names matches.
Both? Then your whole idea with ...
0
Follow the psycopg docs. Plus, use json.dumps because dict conversion is not (yet) supported.
import json
import psycopg2
with psycopg2.connect('') as conn:
with conn.cursor() as cur:
with open('pg-json-import.json') as my_file:
data = json.load(my_file)
cur.execute(""" create table if not exists json_table(
...
0
There's no need to have python parse the json; just pass through the file contents, retrieved with .read(), to cursor.execute() as a parameter (& make sure to wrap in in a tuple, as shown below). And don't forget connection.commit() or you'll never actually see the inserted data as it'll be rolled back when the connection closes after python finishes ...
2
You can't access the fields of the new record dynamically using names stored in a variable.
The only way I can think of, is to convert the NEW record into a JSON value, then you can access the value "dynamically" through the name:
this_column_value := (to_jsonb(new) ->> this_column)::int;
EXECUTE FORMAT('SELECT COUNT(*) FROM %I WHERE %I = $1', ...
1
Very likely your table statistics are outdated.
Try the following:
ANALYZE ubdrecord_partition_05_2020;
If that improves the estimate, you can reduce autovacuum_analyze_scale_factor for the partitions to have PostgreSQL calculate the statistics more often.
0
Perhaps more of a question / comment.
This code below preserves the original ordering. How can it be improved / made more efficient.
In essence, I believe it will also accomplish the set task.
select e
from
(
select array_agg(e) over (order by wf_rn) as e, row_number() over (order by wf_rn DESC) as wf_rn
from
(
select *, row_number()...
3
The ALTER TABLE needs an "access exclusive" lock on the table. While it will only hold that lock for microseconds once it does gets it, it still might block indefinitely while waiting to acquire it if any other session is already holding any lock on the table. And once it does block then other sessions, even ones wanting a weaker lock, will block behind it....
0
Not all log messages include the query text. log_duration, for example, just logs the duration, not the text of the query. If the query text isn't in the csv file to start with, it won't get populated into the table.
Using log_min_duration_statement=0 rather than log_duration will log both the duration and the text of all queries.
0
Use your computer's command-line environment to list the names of all the files in your directory
On windows, use cmd + dir or PowerShell+gci
On macOS, probably terminal+ls
This might also be a good opportunity to familiarise yourself with psql and the meta-commands which you could use for this purpose
Use the list of files to create the commands you want
...
0
PgAdmin4 has no option to modify graphically but as per PostgreSQL Document, we can alter the sequence data type to smallint, integer, and bigint
Alter table column & sequence data type and set max value for the sequence
ALTER SEQUENCE public.case_audit_case_audit_uid_seq AS bigint;
ALTER SEQUENCE public.case_audit_case_audit_uid_seq MAXVALUE ...
0
To filter out NULL values, use WHERE. To get no more than one row, use LIMIT:
SELECT id
FROM my_table
WHERE id IS NOT NULL
LIMIT 1
(This can return a random row, unless you use ORDER BY.)
If you have multiple such queries, you can still use them as scalar subqueries with COALESCE or WHEN.
0
Any other way this can be handled?
You can reduce the need of coalesce by adding not null when you create the table.
You must select table in the outer query first. And then using IN operator if there are several hits.
https://www.db-fiddle.com/f/f8nZn5LQfbmKxAdxX2fdej/14
5
You could use the additional module pg_prewarm. Has to be installed once per database. See:
PostgreSQL: Force data into memory
It can "prewarm" tables as well as indexes. To do it for your index:
SELECT pg_prewarm('test.test_table_idx');
Unless you get index-only scans (which you do not with the index at hand), you might want to prewarm the table as well:...
0
It would be performance suicide to first build a huge jsonb object from joining two tables, and then filter a single record representing an underlying row. You would filter first, of course.
Going out on a limb, this might be what you are trying to do:
SELECT to_jsonb(rr) AS record
FROM (
SELECT (SELECT to_jsonb(row) FROM (
SELECT c....
0
So, this isn't, from what I can tell, an issue; the planner is behaving in a perfectly sane way, and the query is executing plenty quickly. And, to be clear, it is using the index for the current month, it's just using a different plan, a Bitmap Index Scan feeding into a Bitmap Heap Scan instead of a single-level Index Scan.
The reason you're seeing this - ...
2
This answer helped me solve a slightly different ambiguous column problem.
I have a table where we do daily roll-ups into the same table multiple times per day. We need to re-calculate the daily roll-up on an hourly basis, which means we're updating the same row 24 times per day.
Paraphrasing the above:
INSERT INTO accounts as act
(affiliate_code, ...
4
Another answer from another postgresql contributor.
PostgreSQL will not even try to use any indexes during execution of "alter table set not null". It is just not implemented.
Proper implementation of index scan is difficult part. We cannot just do something like this query
select exists(select from foos where bar1 is null)
from alter table command for ...
2
Indeed there is no such function, but you can easily write your own:
create function array_set(p_input anyarray, p_index int, p_new_value anyelement)
returns anyarray
as
$$
begin
if p_input is not null then
p_input[p_index] := p_new_value;
end if;
return p_input;
end;
$$
language plpgsql
immutable;
(If you never need it for anything different ...
1
You need to use unnest to make a table from the array and array_agg to aggregate it back into an array.
To replace the third element in an array of integer with 42, you could for example use
SELECT array_agg(
CASE WHEN num = 3
THEN 42
ELSE elem
END
ORDER BY num
)
FROM unnest('{1,2,4,5}'::...
0
the problem is that for some reason postgres stops using the index on the column "TBL_NAME".
when the index is used, the table is filtered first, and the remaining values are all ints.
but when the index is not used, the cast will happen on all the rows, and will fail on the strings.
as a workaround, I set enable_seqscan = false, and that forces postgres ...
2
Is there a down-side to splitting them into multiple smaller views and joining them by customer_id?
Yes, definitely. Each view has to scan the whole underlying table on its own, and then you add 20 joins after that. The index does not apply to the derived tables you are joining. The single SELECT can make do with a single scan over the table (or index), it ...
1
Here is a rough sketch of the comments:
CREATE TABLE Race
( Race_id INT NOT NULL PRIMARY KEY -- I prefer to use the same name through out the model
);
CREATE TABLE Racer
( Racer_id INT NOT NULL PRIMARY KEY -- See above
, Rank INT NOT NULL
, CONSTRAINT AK1_Racer UNIQUE (Rank, Racer_id) -- Super key
);
CREATE TABLE Result -- Is not "Result" a better ...
0
My problem was that after each update on relation_b, the trigger asks to update relation_b, so it calls itself and enter in a loop.
I solved it configuring the trigger to begin when one field is updated, except the field it updates :
CREATE OR REPLACE FUNCTION function1() RETURNS trigger AS
$$
BEGIN
UPDATE relation_b as b
SET field_1 = a.field_1
...
2
The two tables person and praktikant in your example are two distinct tables. Inserting rows into praktikant does not insert rows in person, but
SELECT ... FROM person;
is effectively the same as
SELECT ... FROM ONLY person
UNION ALL
SELECT ... FROM praktikant;
Look at the EXPLAIN output for both queries to confirm that.
Each of these tables has its own ...
0
It seems that the problem is caused by the glibc update issue that @a_horse_with_no_name highlighted in the comments: https://postgresql.verite.pro/blog/2018/08/27/glibc-upgrade.html
I suppose it would be enough to get rid of the duplicates and reindex that table, there will be no new duplicates after that.
1
No it should'nt. You misunderstood what synchronous replication means. Synchronous mean than you should have at least one copy at any time no matter what. If you don't then use asynchronous replication. Postgres is semi-sync replication by design if you use replication slots. Replication in Postgres is so much better than in MySQL, in fact MySQL has terrible ...
0
Amazing. You have that many indexes on sql_reciept_sms (many of which are certainly superfluous), but you still managed to forget the crucial index for this query:
CREATE INDEX ON sql_reciept_sms (userid);
1
Comparing each row in keys with all rows in members_keys is not how the join is actually implemented.
This is the query plan without any indexes:
create table members(id int, name text);
create table keys(id int, color text);
create table members_keys(member_id int, key_id int);
explain select * from keys join members_keys on keys.id = members_keys.key_id ...
0
Struggling with this problem for days.Finally got help from the EDB team
My problem got solved by doing the following steps :
1) Open the command prompt and go to the following directory.
cd "C:\Program Files\PostgreSQL\10\bin"
2) Once you are inside the "C:\Program Files\PostgreSQL\10\bin" directory execute the following command.
.\initdb.exe -D "C:\...
0
This has nothing to do with freezing.
VACUUM doesn't shrink the table, except that it will truncate any empty pages at the end of the table. Obviously an autovacuum job was faster than your manual VACUUM, because there were no dead tuples left.
To shrink and reorganize the table, run
VACUUM (FULL) my_schema.table;
but be aware that this will exclusively ...
0
By adding the NOT VALID option to the ALTER ... ADD CONSTRAINT statement it's possible to ignore the the validation of the CONSTRAINT on the existed data.
ALTER TABLE layers
ADD CONSTRAINT layers_name_uniqueness
CHECK (layers_name_uniqueness_check(bucket, name, parent))
NOT VALID
;
1
Try PgpoolII or PGpool with Regmgr
1. Opensource
2. High Available
3. Pool connection with single external db access port
To reach reasonable high availabe, I think you need has at least 3 servers.
Here is the pgpoolII online document of how to create robust cluster system and avoid the single point of failure or split brain
Diagram source
0
Since barman 2.4, there are two option for after recovery behavior.
They are
--target-action option, accepting the following values:
shutdown: once recovery target is reached, PostgreSQL is shut down
pause: once recovery target is reached, PostgreSQL is started in pause state, allowing users to inspect the instance
promote: once recovery target is ...
0
Well.
try following barman command instead of fixed timeout:
barman cron
barman switch-wal <server-name>
I face the same issue when add a new server in to barman backup config. It alwsy happened. After check stackoverlog and barman docker, I noticed that if the datbase base is not very busy, there will no wal(xlog)generated during the new server ...
1
Autovacuum is triggered by the table statistics on the table, and as long as your manual VACUUM (FREEZE) is not done, these are not updated. That is why anti-wraparound autovacuum processes will still start.
But that's not a big problem: Only one VACUUM can run on a table at any given time. Now anti-wraparound autovacuum workers won't give up when they ...
0
Plain initdb creates a PostgreSQL cluster in the normal fashion, with all files contained in a single directory. Consequently, it is very easy to get rid of that new cluster: just delete the created directory and all files in it.
2
I looked at the source code (function ATRewriteTable in src/backend/commands/tablecmds.c), and PostgreSQL always uses a sequential scan of the table to verify NOT NULL constraints.
So creating indexes won't speed up the execution.
0
Your code below does not return the desired results:
select
json_build_object(
'ID', s.page_id,
'Domain', domain,
'Sections', COALESCE(json_object_agg (
s.title,
json_build_object(
'ID', s.sid,
'Texts', t.txts,
'Images', i.imgs
)
order ...
0
Just SET the value for field_1 BEFORE INSERT OR UPDATE. Just, make sure you use the alias for the new row of data.
needs to be edited for postgresql syntax
CREATE TRIGGER trigger1
BEFORE INSERT OR UPDATE
ON relation_b
FOR EACH ROW
begin
-- "new" represent "current row being inserted/updated"
-- just modify the values BEFORE it is stored in ...
0
The actual column name "p_attribute" and the key "p_attributes" in the json format were different...
2
Dig into PG10 source code https://github.com/postgres/postgres/blob/REL_10_STABLE/src/backend/access/transam/xlog.c#L2224, thanks @pifor for the hint
/*
* Calculate CheckPointSegments based on max_wal_size_mb and
* checkpoint_completion_target.
*/
static void
CalculateCheckpointSegments(void)
{
double target;
/*-------
* Calculate the ...
0
Especially in cases where feature count to search for is larger, in order to avoid constructing mega query statements, you may consider instead building a temporary table to hold searched for features, and do a simple INNER JOIN with, as previously noted, GROUP BY counts.
This is exactly a replacement for building a long query with SELECT ... feature IN ( ...
1
According to PostgreSQL 10 source code, this is due to CHECKPOINT_CAUSE_XLOG:
From xlog.c in function XLogWrite:
* Request a checkpoint if we've consumed too much xlog since
* the last one. For speed, we first check using the local
* copy of RedoRecPtr, which might be out of date; if it looks
* like a checkpoint is ...
1
But when I run the same SELECT in the father table, it is slow. I saw the EXPLAIN and seems that PostgreSQL uses each child's index, instead of use only the father index.
To search in the father table only, use the ONLY keyword:
SELECT FROM ONLY MasterAccount.imovel ....
The manual:
If ONLY is specified before the table name, only that table is ...
9
SELECT record_id, rec_date
, CASE WHEN rec_date::time < '08:00' THEN 'Night'
WHEN rec_date::time >= '20:00' THEN 'Night'
ELSE 'Day' END AS indicator
FROM records;
Why?
It's a matter of date-math rather than date-format. You want to do the math correctly and efficiently. The format of 'Day' and 'Night' are not in ...
0
So, I followed the lead of using the crosstab function in Postgresql. This is where I got :
The crosstab function enables me to obtain a set of records with one record for each house and for each feature_name a column with the feature_value :
SELECT * FROM crosstab (
' SELECT house_pkid, feature_name, feature_value
FROM house_features
WHERE ...
3
According to docs BETWEEN transforms into:
a BETWEEN x AND y
is equivalent to
a >= x AND a <= y
That means that x must be lesser than y.
Also in the docs you can find there is a BETWEEN SYMMETRIC, that can help in your question.
BETWEEN SYMMETRIC is like BETWEEN except there is no requirement that
the argument to the left of AND be ...
Top 50 recent answers are included
