New answers tagged postgresql
0
votes
PG Admin Sessions
That is showing the last query the session ran before it went idle. That is an introspection query, where the app is trying to figure out what are the capabilities of the database it is connected to. ...
- 33.2k
2
votes
Accepted
Unable to change password in postgres even though I'm logged in as that user
Since you (or someone else) have managed to create a role with a case-sensitive name, you now have to quote it every time to prevent Postgres from converting it to lowercase:
\du "ABcDeFGh"
...
- 22.4k
2
votes
Postgres SSL client certificate - database server CA failed to start
You also have to provide the server certificate (by default server.crt) and the corresponding private key (default server.key with restrictive permissions) in the data directory, otherwise you cannot ...
- 35.4k
0
votes
PSQL avoid insert failing because a trigger failed
my goal is that if a trigger on INSERT fails, the original INSERT proceeds either way
Presumably the trigger was put there for a Reason.
Just trying to "get round" that Reason is going to ...
- 6,983
3
votes
Accepted
ON CONFLICT with index column name is a syntax error
I found that part of the page to be a bit confusing, but I guess the "(" in:
( { index_column_name |
must be present. There are several examples on the page which all uses parantheses ...
- 22.1k
0
votes
PSQL console encoding
The key thing here is which terminal application you use, it's not possible in the cmd.exe, even the cmder with cmd.exe is not able to show all UTF8 characters. But who uses cmd.exe and cmder these ...
- 101
1
vote
How to get second last record from table using subquery
There is no such thing as the "second last" row in a table. Rows in a relational database have no implied sort order. To identify the "second last", you will have to use an order ...
- 73.3k
0
votes
How to turn JSON array into Postgres array?
The accepted answer's solution:
CREATE OR REPLACE FUNCTION jsonb_array_to_text_array(_js jsonb)
RETURNS text[]
LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
'SELECT ARRAY(SELECT ...
- 1
4
votes
Accepted
Implement foreign key constraint when there are several parent tables
You are not missing anything, this is a known tough problem in relational databases. The concept doesn't lend itself to models like that naturally.
Your solution 2. is a good one, with the check ...
- 35.4k
0
votes
In postgres unable to create database
Your statement is blocked by a lock. Most likely, somebody is connected to the template1 database. Terminate that database session, and the CREATE DATABASE statement should proceed.
- 35.4k
2
votes
Accepted
How to order by cumulative value?
I suspect window functions or common table expressions ...
You were on the right track, but took the wrong turn. The feature you are looking for is a window function:
SELECT w.id, w.step_id, s.tot, w....
- 157k
0
votes
Accepted
Join on table with common part in jsonb in nested array
You need to first unnest the JSONB array using jsonb_array_elements. You can do this using a CROSS JOIN LATERAL, which you can place inside the JOIN, allowing you to use it as part of the join ...
- 7,711
7
votes
Accepted
How to get a user's password in PostgreSQL?
The password is encrypted according to the documentation:
The password is always stored encrypted in the system catalogs. The
ENCRYPTED keyword has no effect, but is accepted for backwards
...
- 3,025
4
votes
Accepted
How to format a fixed-length number as a binary string?
You can cast integer or bigint to bit(n). The rest is easy.
Notably, there is no cast for smallint (int2) in standard Postgres - up to & including version 15:
test=> SELECT castsource::regtype
...
- 157k
0
votes
"root" execution of the PostgreSQL server is not permitted
if there is sudo permission then
sudo -u postgres psql
or using docker postgres
if just root access
try
su - postgres
then
psql
- 101
2
votes
Optimizing select count result in Postgresql
At this scale you have to resort to tricks to handle counting and their effectiveness depends entirely on your business logic.
The most "simple" one is just cache the result in the ...
- 102
0
votes
PostgreSQL: changing password for a user is not working
In my case that was due to env variable POSTGRES_PASSWORD in docker container.
1
vote
What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE?
Future Bookings,
You want to allow people to book a time and date in the future and not be have bookings jump around when the politicians suddenly decide to change to daylight savings rules, or ...
- 2,628
1
vote
password authentication failed for user "postgres"
if you speify a host it makes a TCP/IP connection to that host.
"local" requires unix domain sockets
to make a local (unix domain socket) connection don't specify a host.
psql -U postgres &...
- 2,628
0
votes
How to order by cumulative value?
I think this does the trick:
WITH f AS (
SELECT p.id, sum(q.duration) AS tot
FROM steps p
INNER JOIN steps q
ON p.workflow_id=q.workflow_id AND q.position<=p.position
GROUP BY p.id
)
...
- 123
3
votes
Optimizing select count result in Postgresql
From the PostgreSQL Wiki:
The reason is related to the MVCC implementation in PostgreSQL. The fact that multiple transactions can see different states of the data means that there can be no ...
- 15.5k
1
vote
Accepted
How to add a column to a table with populated data? [POSTGRES]
You can add a generated column:
alter table orders
add column ts timestamp
generated always as (dt + tm) stored;
Additional things to concider:
Don't add a column at all. It does not add ...
- 22.1k
1
vote
Accepted
pg_restore error when using the --single-transaction: options c/--clean and -a/--data-only cannot be used together (even so the -a flag was not used)
I don't know how you get the different language messages to be used.
But there is an obvious copy-and-paste mistake in the French translation file src/bin/pg_dump/po/fr.po:
#: pg_restore.c:338
#, c-...
- 33.2k
1
vote
How to add a column to a table with populated data? [POSTGRES]
Its trivial
UPDATE orders SET full_date = order_date+ order_time
- 19
0
votes
How to add a column to a table with populated data? [POSTGRES]
I managed to solve this issue by using Update Join:
Just in case anyone in the future would be interested, this did the trick:
BEGIN;
ALTER TABLE orders ADD COLUMN full_date TIMESTAMP;
UPDATE orders ...
- 23
0
votes
How to retrieve the current dataset in a table function with RETURN QUERY
You could use a function similar to this one:
CREATE FUNCTION get_random_rows(n integer) RETURNS SETOF part
LANGUAGE plpgsql AS
$$DECLARE
max_id bigint;
min_id bigint;
i integer;
...
- 35.4k
1
vote
What is a valid use case for using TIMESTAMP WITHOUT TIME ZONE?
2 example use cases:
You have a movie that will be premiered in cinemas globally on the same date, and at the exact same local time everywhere, e.g. "9am locally, regardless of where you are&...
- 233
2
votes
Accepted
How to merge and transform data in Postgres
You can use string_agg() for this
select name,
string_agg(concat(quality, ':', magnitude), ', ') as personality
from the_table
group by name;
With a more up-to-date Postgres version, I would ...
- 73.3k
0
votes
Slow connect time to postgresql on Windows 10
I ended up at this question because in my pgAdmin4 (Version 6.12, on windows 10) it sometimes took many tens of seconds or even minutes for a simple SELECT * FROM mytable even for tables with only a ...
-2
votes
Query the definition of a materialized view in Postgres
Postgres 14.5
SELECT schemaname, matviewname, definition FROM <database_name>.pg_catalog.pg_matviews;
- 97
1
vote
Passwordless access to psql from Administrator WITHOUT any password (Windows)
PostgreSQL on Windows does not support "peer" authentication method, which is what I assume you mean by "ident" as "ident" itself is rarely used (and I wouldn't trust it ...
- 33.2k
0
votes
Postgres: Insert all valid data into a table while logging errors
To get an exception called for each bad record you pretty-much need to use a loop.
you could use an on insert do instead rule, but that just creates a loop if you're inserting many rows.
it's probably ...
- 2,628
3
votes
Accepted
PostgreSQL: does the WAL group commit affect synchronous replication performance?
commit_delay should have no impact on synchronous replication. No matter how it is set, PostgreSQL waits until the WAL is flushed locally (which may take longer with commit_delay set to a non-zero ...
- 35.4k
5
votes
Accepted
Postgres: Create unique index with conditional
Yes, the syntax is almost what you imagined. They are called partial indexes (or filtered) and can be unique.
CREATE UNIQUE INDEX event_unique_ower_uqx -- index name
ON table_name (event_id)
...
0
votes
Track row affected by a plpgSQL's DML from Python code block
After calling execute(), the cursor rowcount attribute contains the number or rows affected, as returned by the server. The statusmessage attribute contains the entire tag.
Reference: https://www....
- 624
0
votes
Why on earth is this INSERT statement failing?
The code is fine. You can test it in any Postgres instance you have access to or online, e.g. at dbfiddle.uk
Apparently eversql has a syntax validator that works correctly more or less but it doesn't ...
1
vote
Accepted
How do you run "SELECT" on the composite type argument of an SQL function?
The question title and question body ask different questions, so I will address both.
As respects the question title: You don't need to SELECT against a composite type, you can reference fields in it ...
- 624
3
votes
What are the usecases for stored procedures (`CREATE PROCEDURE`) as of Postgresql 14?
Until PostgreSQL v11, PostgreSQL survived quite well without procedures, so they are indeed not absolutely necessary. The benefits are
more SQL standard compliance
a more natural way than RETURNS ...
- 35.4k
1
vote
Accepted
Duplicate key issue with a Foreign constraint
There are two/three problems with the design of the 3rd table:
CREATE TABLE IF NOT EXISTS category_items (
category_id SERIAL PRIMARY KEY,
partno TEXT NOT NULL,
FOREIGN KEY (partno) ...
1
vote
Accepted
Cross join 2 table with json data and get all rows
You need to do a left join to your function call. Since a left join requires an ON condition, you can just use ON true. That is a bit weird, but a commonly needed trick with lateral joins.
LEFT join ...
- 33.2k
0
votes
Duplicate key issue with a Foreign constraint
I have figured it's a quite simple fix, I am just feeling a bit silly for missing it. Since in category_items, category_id is the PRIMARY KEY, it will default to being UNIQUE. I need a separate ID for ...
- 13
0
votes
Accepted
Track row affected by a plpgSQL's DML from Python code block
Write it with a RETURNING clause and then perform cursor.fetchall() to get affected rows and then their len() in the code.
upd_sql = """
UPDATE STG01.UPDATE_MINUS_ONE_CTRL_TAB
...
- 102
1
vote
Expensive column causes slow query with large offset
Assuming id is a primary serial key, and therefore indexed by b-tree, the separate queries use the index for the query, while the combined query doesn't. I am not sure if it's your mistake, but the ...
- 102
4
votes
Accepted
What am I missing in my crosstab query here?
I am not a fan of the crosstab() function as I find it more complicated than filtered aggregation (and it doesn't workaround the fact that you need to specify all result columns manually).
The ...
- 73.3k
2
votes
Make sure that such a field combination exists in another table when inserting
What I also want to achieve is that inserting a row into Table A_B is impossible if there is no such (id, user_id) combination in Table_A.
Am I misunderstanding something or did you not consider
...
- 345
-1
votes
How to view data in pgAdmin III
For pgAdmin 4: Servers -> your_server -> Databases -> your_database -> Schemas -> public -> Tables
- 99
0
votes
Wal position to wal archive number
You can use pg_controldata to get the latest checkpoint's redo wal file.
and pg_archivecleanup to remove wals before that point.
pg_controldata -D $PGDATA
g_control version number: 1002
...
- 101
4
votes
Accepted
Is there any impact on PostgreSQL database to run AUTO VACUUM frequently?
You can change the autovacuum scale factor for an individual table:
ALTER TABLE tab SET (autovacuum_vacuum_scale_factor = 0.01);
VACUUM is not cheap, so running it more often will take more resources....
- 35.4k
0
votes
How to filter a table remove duplicated keys and keep unique value for each key in one table?
STRING_AGG() examples, as per Laurenz.
Here's how you'd apply it to your table:
SELECT
name,
STRING_AGG(class, ',') AS class
FROM YourTable
GROUP BY name
- 26.7k
1
vote
Accepted
Postgresql system tables frequently seen in autovacuum and DDL operations are taking time
It is normal that these catalog tables have lots of dead row versions if you keep creating and dropping tables. Unfortunately it is not possible to configure autovacuum to run fast on a certain ...
- 35.4k
Top 50 recent answers are included