New answers tagged postgresql
2
Rather than making up any keys, why not convert that to a json array?
alter table the_table
alter column the_column type jsonb
using to_jsonb(string_to_array(the_column, ':'));
That would turn str1:str2 into ["str1", "str2"]
If you really need to make up some (numbered keys), you will have to write a helper function:
create function ...
0
You may calculate conditions mask (binary representation, for example):
CREATE TABLE test (id INT, val INT);
INSERT INTO test VALUES (1,1), (1,2), (2,1), (2,2);
SELECT *,
(id=1 AND val=1)::INT + 2 * (id<val)::INT + 4 * ((id+val)!=3)::INT conditions_mask_bin,
(id=1 AND val=1)::INT + 10 * (id<val)::INT + 100 * ((id+val)!=3)::INT ...
3
If your conditions are mutually exclusive (you you only care about matching the first of an order you control) then you could get away with a single column:
SELECT *,
CASE
WHEN name = 'meatfeast' THEN 'name'
WHEN diet = 'vegan' THEN 'diet'
END as reason_for_appearing
FROM
pizza
WHERE
name = 'meatfeast' OR diet = 'vegan'
You repeat your ...
0
Found these. We'll give it a shot.
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows
Windows debugging.
Open Environment Variables (WINDOWS key + BREAK)
Set environment variable "_NT_SYMBOL_PATH" for ...
3
A deadlock is a kind of serialization error: you didn't do anything forbidden, it just so happened that there was an interaction with other active transactions that prevented your transaction from being completed. Your reaction is the correct one: retry the transaction. There is absolutely no need to wait before retrying.
I concur that with a sufficiently ...
0
When the state is idle, then the query shown in pg_stat_acitivy is the last query that was run before it went idle. Since that was apparently COMMIT (and since the state is idle, not 'idle in transaction'), then no, you don't need to worry about autocommit not working.
My assumption is that the connections will be re-used but concerned if we run more tests ...
0
You could make it a bit faster by replacing the TEXT array with an INT array referencing a separate table which would store the tag texts along with an integer key.
Now, here's an idea:
You're always ordering by "created_at DESC" with a LIMIT clause. Or you have a date range, because no-one is really interested in having a million search results ...
1
What a terrible query! Consider using different software.
The only thing that could help are trigram indexes on both columns:
CREATE EXTENSION pg_trgm;
CREATE INDEX ON upload_file USING gin ((name::text) gin_trgm_ops);
CREATE INDEX ON upload_file USING gin ((id::text) gin_trgm_ops);
Then you might get two bitmap index scans and a "bitmap or".
If ...
1
A plain UNIQUE constraint is NOT DEFERRABLE by default. Unique violations are checked after each row. The manual phrases this as:
checked immediately after every command
But it's really checked for every individual written row.
If you define the UNIQUE constraint DEFERRABLE (like a_horse provided), unique violations are checked after each statement. ...
2
Thus, as stated in the Subject, is there any way to suppress the SELECT output?
Yes, by omitting the semi-colon after the SQL statement.
That is, \gexec itself ends the SQL statement.
test=> select 'select 1' \gexec
?column?
----------
1
(1 row)
0
As mentioned in the accepted answer, running chcp in a new command line interpreter context does not work. Here's how I wrapped my Windows PostgreSQL client start in a batch file.
@echo off
setlocal
chcp 1252
if [%1]==[] (set psqlhost=localhost) else (set psqlhost=%1)
if [%2]==[] (set psqluser=postgres) else (set psqluser=%2)
if [%3]==[] (set psqldbase=...
0
The difference is shared hits.
Shared hit means it read the blocks from ram (shared buffers).
Check for shared_buffers parameter on both servers.
0
If I understand you correctly below anonymous block should do it.
DO $$
declare
tabs cursor for
SELECT table_name
FROM information_schema.tables
WHERE table_name LIKE '%^prefix%'
AND table_name NOT LIKE '%suffix$'
AND table_schema not in ('information_schema', 'pg_catalog')
AND table_type = 'BASE TABLE'
ORDER BY table_name;
rec record;
...
1
Another way you can look at it, which might help you conceptualize, is the OR of an ON clause is like using a UNION ALL with the query repeated and only leveraging one predicate of each OR case like so:
-- Gets the fathers
SELECT parents.name AS name, MIN(children.age) AS age FROM people children
INNER JOIN people parents ON parents.id = children.fatherId
...
3
Define the constraint as deferrable:
CREATE TABLE widget (
id serial PRIMARY KEY,
name text NOT NULL,
ordinal int NOT NULL UNIQUE DEFERRABLE
);
Then you can update it in one statement:
update widget
set ordinal = t.new_ordinal
from (
values (1, 3), (3,1)
) as t(id, new_ordinal)
where t.id = widget.id
2
Fastest way to do it is to do it on database.
Write a database function to calculate score run:
update table set computed_Score=calculate_score();
0
You have 2 options:
Before insert trigger to set person_id column with some sequence.
Set default value to sequence next value. Example is below.
create table t1 (id int primary key , x int);
insert into t1 values (1,1);
create sequence t1_Seq start with 2;
alter table t1 alter column id set default nextval('t1_seq');
insert into t1 (x) values (2);
0
I can get master's IP address extracting a regexp from pg_stat_wal_receiver.conninfo:
SELECT (regexp_matches(conninfo,'host=([0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3})'))[1] AS master_addr
FROM pg_stat_wal_receiver;
0
Logically, you can think of an inner join as a cross join with the join condition as a filter: only the rows that match the condition end up in the result.
So each row in children will be joined to those rows in parents where either fatherid or motherid (or both) are equal to the child's id.
1
we face the same problem. Apparently this issue is fixed in version 10.16. I tested 10.9,10.13,10.14,10.5 and all have the same issue.
But with 10.16 it's ok.
I don't see in release note any information on it.
1
If that is too much boilerplate code, then create a function that does the job:
create or replace function make_serial(p_table_schema text, p_table_name text, p_column_name text)
returns void
as
$$
declare
l_sql text;
l_seq_name text;
l_full_name text;
begin
l_seq_name := concat(p_table_name, '_', p_column_name, '_seq');
l_full_name := ...
0
Use barman switch-wal SERVER --force --archive --archive-timeout 30.
For streaming archiving, you should also manually execute barman cron to bring up receive-wal. Otherwise, you need to wait at least 1 min for the cron to take effect automatically, while the switch-wal command waits only for 30 seconds by default, and then fails.
0
Database logging is the wrong end to handle the problem, since the database does not have the required information.
You will have to write your client application so that it logs the information you need whenever it receives a database error.
0
A query of this basic form only needs a single sequential scan and should be as efficient as it gets for the general case:
SELECT x.*
FROM (
SELECT count(*) AS ct
, count(ticker) AS ticker
, count(profits_change) AS profits_change
, count(industry) AS industry
-- , more?
FROM fortune500
) t
CROSS JOIN LATERAL (
...
1
First, the index scan is slower than necessary, because your work_mem is not big enough to contain the bitmap. Increase it until you get no more “lossy” heap blocks during the bitmap heap scan.
The second plan is faster than the first because it uses two parallel workers (which the other plan cannot). But it uses way more resources: it keeps three processes ...
0
While for now Postgres doesn't seem to support interval division, you can of course convert those intervals into a number you can divide - like seconds.
The other proposed answers suggest: EXTRACT(MILLISECOND FROM interval '1 day'), but I found that this sometimes returns 0. And of course one cannot divide by zero.
But the following works much nicer and is ...
1
The rule of thumb is: if the data are just payload to the database and will never be used in WHERE conditions and the like, use json. Else, use jsonb.
0
The problem is that there is no good way to decide whether a statement will modify data or not, and you can only run read-only queries on the standby.
There are programs which try to guess, but they will fail with a statement like
SELECT delete_some_rows(42);
So the only reliable way to do that is if your application program is smart enough to send the ...
0
Use a lateral join:
SELECT x, plans.name
FROM plans
CROSS JOIN LATERAL
generate_series(timestamp '2021-01-01 06:00',
timestamp '2021-01-07 22:00',
plans.duration::interval) t(x)
-1
if it was on premises, only thing you need to is to mount it first and then and run this as a sql query :
DO $$
DECLARE ssql varchar := 'COPY ( SELECT * from schema.table1 ) TO ''/projeler2/Raporlar/SatisRaporlari/test'
|| TO_CHAR(NOW(),'DD_MM_YYYY')
|| '.csv'' WITH CSV DELIMITER '','' HEADER QUOTE ''"'' ;';
BEGIN
EXECUTE ssql;
--raise ...
0
Your first output is showing as EWKT (Extended Well Known Text) while the second output is WKB (Well Known Binary). These are both standard representations of the internal geometry but are designed for different end users (human v machine).
To be honest I have never see PostGIS return WKT in a standard select statement, usually you would wrap the geometry in ...
1
Damn, my sleep() idea was oversimplistic!
SELECT * FROM users WHERE secure_compare(token, 'abcdef')
or...
SELECT * FROM users WHERE token = 'abcdef'
You'll want to index that to make it fast, which means timing information can be leaked not just from the string comparison, but also from the btree index walk. Can we actually do that?
CREATE UNLOGGED TABLE ...
0
What you are basically looking for is a reverse engineering tool for Postgress. For the database products I worked with, the maker provided a reverse engineering tool that could extract the metadata stored in the database itself and transform that back into a database create script, written in SQL. I don't know Postgress, but I expect there to be such a ...
1
Yes, if you have values that increase monotonically, the B-tree index will end up densely packed. Subsequent updates and deletes will still lead to fragmentation, but that is normal.
1
With B-Tree indexes, more importantly is the cardinality of the data you're indexing. The more unique the data is in the index, generally the better candidate it is for a B-Tree index because it'll result in a much more full tree with many branches and leaves. A very non-unique field, like a boolean based field, will always only have two distinct values, and ...
1
Some major upgrades change the on disk database storage format, so they require to dump the database and reload it using pg_dump and pg_restore, which requires server downtime.
If you have a huge database, this could take a while, during which the server is not available.
You also have to thoroughly test everything to make sure the new version doesn't cause ...
1
Businesses generally aren’t in a position to upgraded their software at the drop of a hat. It can takes months or years of preparation to be ready to upgrade. For this reason Postgres — like other software used by business is supported for many years. Postgres still releases a new version every year, but they also release patches to old versions for 5 years. ...
2
They have a 5 Year end of time policy see homepage
That is why 9.5 has got the latest official update in February
You have to check the manual what mandatory sql standard and optional standards which version supports or in case of Windows for example which Version of Windows it is supported.
manuals
0
No, there isn't. But you could consider setting statement_timeout, which will kill any query running longer than a certain time.
0
Alright... let's have some test data.
CREATE UNLOGGED TABLE foo( id INTEGER PRIMARY KEY,
doneCleanup BOOL NOT NULL, alertedUser BOOL NOT NULL,
doneCleanupTimestamp TIMESTAMP NOT NULL, initialTimestamp TIMESTAMP NOT NULL);
INSERT INTO foo SELECT n, random()<0.9, random()<0.9,
'2020-01-01'::TIMESTAMP+'1 SECOND'::INTERVAL*(n + random()*1000),
'2020-01-...
0
There are times where someone wants to write code and not specify each individual column. If I write a script to split out one customers data to another database, not specifying the column names means that I don't need to change the script every time I add a new column. As long as the column order is constant. If there is one table that you want to change ...
2
LC_COLLATE refers to a name of locale from the operating system, whereas COLLATE refers to a collation that should exist in pg_catalog.pg_collation.
pg_catalog.pg_collation is originally populated in the template databases when the PostgreSQL instance is created (by initdb).
Specifically it's the SQL function pg_import_system_collations() that should do that....
7
Another option, using a window function:
select
id, "group", date
from
table1
order by
max(date) over (partition by "group") desc,
"group",
date desc ;
Thnx to @nbk for providing the dbfiddle.uk
3
I added also another group wit the same dates, so that you see that you have also to define an order for the group to be displayed, in case the same dates.
To get your wanted order i added in the CTE a sorting row, which is he max date for every Group
CREATE TABLE table1
("id" int, "group" varchar(6), "date" date)
;
INSERT ...
1
Check for table and index sizes. Your first index uses 3 columns and its size is probably very big so optimizer chooses full table scan.
Index scan vs full table scan also depends on random_page_cost (default value 4).
If random_page_cost is set lower optimizer will chose index scans over full table scans.
5
For this there is a COMMENT SQL command:
COMMENT ON PROCEDURE whatever IS 'some text'
To retrieve it later use the function obj_description().
2
While the following is not a complete solution (column privs aren't included, it doesn't have the function signatures) you should hopefully be able to get most of what you're asking for using:
SELECT rug.grantor,
rug.grantee,
rug.object_catalog,
rug.object_schema,
rug.object_name,
rug.object_type,
rug....
2
First figure out what PostgreSQL version you have. If it is truly 9.5, upgrade before you do anything else. I mean it. The rest of my answer deals with recent PostgreSQL versions.
A row lock won't block autovacuum progress (unless it is an anti-wraparound vacuum), but a table lock can. These are logged if you set log_autovacuum_min_duration to something else ...
0
cvssql from csvkit does the same. It analyses the data, so proper date fields will end up as date field, integer to integer etc. And you can choose to what type of db flavour: firebird, mssql, mysql, oracle, postgresql, sqlite & sybase.
As example the below command analysis the csv file and imports it into postgresql:
csvsql --tabs --quoting 3 --no-...
Top 50 recent answers are included
