New answers tagged

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 ...
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 ...
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 ...
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....
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 ...
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 ...
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 ) ...
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 ...
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
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 ...
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 ...
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;
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 ...
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 ...
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 ...
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 ...
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 ...
-1 votes

How to view data in pgAdmin III

For pgAdmin 4: Servers -> your_server -> Databases -> your_database -> Schemas -> public -> Tables
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 ...
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