Questions tagged [postgresql]

All versions of PostgreSQL. Add an additional version-specific tag like postgresql-11 if that context is important.

Filter by
Sorted by
Tagged with
1
vote
1answer
510 views

Use NEW in FROM clause in Postgres trigger?

I am trying to write a Postgres trigger to unnest an array field before inserting or updating a new row. E.g. SELECT unnest(something) FROM NEW However, this seems to lead to an error: relation '...
1
vote
3answers
158 views

How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql

I have legacy tables similar to the following: employee ------------------------------------ | employee_id | name ------------------------------------ | 1 | David | 2 ...
1
vote
2answers
634 views

Storing blobs outside the database

I had a PostgreSQL database with loads of scanned documents, as a document bytea column in the table scans, with hundreds of thousands of documents that was large and inconvenient to backup. Also, ...
1
vote
1answer
218 views

Does PostgreSQL have a variable character storage layer that optimizes storage space by automatic dedupe?

My application has a Product and a Purchased table. The Purchased table includes a full copy of the Product row at time of purchase, so that, even if a product name or description was changed, the ...
1
vote
1answer
595 views

How to pass an array to a plpgsql function with VARIADIC parameter

I am trying to define a plpgsql function in Postgresql 10 with several parameters, including an array. I have already defined the function CREATE OR REPLACE FUNCTION mix_table_fields(...
1
vote
1answer
5k views

pg_upgrade doesn't find config file

I'm trying to upgrade PostgreSQL on Ubuntu Server from 9.6 to 10. This is the first time I'm doing this. The server doesn't contain any useful data yet but I want to make sure I can do this properly ...
1
vote
1answer
1k views

Reset search_path to the global, cluster default

Say I permanently alter my database's search_path like this: ALTER DATABASE my_db SET search_path TO "$user",public,other_schema; How can I reset it back to whatever the default that a pristine, ...
1
vote
1answer
48 views

Why doesn't jsonb constructor from a NULL record behave like hstore constructor?

When I construct an hstore CREATE EXTENSION hstore; SELECT hstore(null::my_type); hstore ------------------------------------------------ "name"=>NULL, "...
1
vote
1answer
2k views

Use system timestamp comparison in SELECT query

I have to backup some tables on a daily basis using the condition that the backup will be only for one previous day. I am planning to use copy to dump the records in to a csv file. I need help in ...
1
vote
2answers
1k views

postgres user is not superuser

Im having some problems with my postgresql server. In particular, some databases cannot be dropped. On further inspection, I see that the postgres user is not a SuperUser. There are two other users ...
1
vote
1answer
347 views

Is there a way to enforce uniqueness of arbitrary key-value pairs in a JSONB object column

I use Postgres JSONB columns to store translations for dynamic content of a multi-language site — locales are keys, translated strings are values ({"en": "Hello", "ru": "Привет"}). There're ...
1
vote
1answer
18 views

List Postgresql foreign data wrappers without the use of the `psql` command

As seen in a stackoverflow answer I can use the psql command and the \dew[+] command in order to list the foreign wrappers. But the use of universal database tools such as DBEaver, DataGrid, etc ...
1
vote
1answer
284 views

Trigger to cascade updates to a second table

In my PostgreSQL 9.6 database, I am trying to update 2 fields in table2 with a trigger when table1 is modified. Function: CREATE OR REPLACE FUNCTION schema.maj_tbl2() RETURNS trigger AS $BODY$ ...
1
vote
1answer
3k views

How to edit `postgresql.conf` with pgAdmin 4?

Same Question as this: How to edit postgresql.conf with pgAdmin 3? but updated for the completely new (total re-write) pgAdmin 4. ➥ How does one edit the configuration files with the pgAdmin 4 app? ...
1
vote
1answer
3k views

unable to create schema on amazon rds for postgres

I'm trying to create a schema on a postgres instance located on Amazon rds. That schema would be used to import data from an existing schema built on a local db. Issue is I do not have permissions ...
1
vote
3answers
652 views

Should we centralize databases or have a database with each application? [closed]

My company develops different web applications for different clients. For each new client (or important project), we use a new server with a new MySQL database. This way, an application can access ...
1
vote
1answer
946 views

Postgresql reset_xlog

I just mistakenly moved the pg_xlog folder of a live, running postgres server. When I restarted the server shortly after, it failed to start because of a WAL problem (obviously). For non-technical ...
1
vote
1answer
100 views

JSONB vs BRIN Index

The requirements go like this: Each user has a wallet that can contain tens to a hundred or so different currencies, and for each currency an amount Number of currency can grow > 1600 (make me ...
1
vote
2answers
110 views

Storing time ranges as JSON?

I have table with jsonb field - timetable which has next structure: { 1: [{start:0, finish: 12}], 2: [{start:0, finish: 12}, {start:12, finish:17}], 3: [{start:12, finish: 17}], 4: [{start:0, finish: ...
1
vote
2answers
830 views

Insert values from a record variable into a subclass table

My goal is to create a stored procedure that builds up a _tbl_out which is a subclass of a _tbl_in. I'm following the results of this question, but for this to work, the tables need to have the SAME ...
1
vote
2answers
147 views

postgres parse_indent() function hang [closed]

select parse_ident('abc,efi&xyz'); Will stay there for ever, not returning. But a similar command will finish. select regexp_split_to_array('abc,efi&xyz', '[,&]'); ...
1
vote
1answer
426 views

Cluster command locking read only queries on replica

I noticed that clustering can take a long time and lock the table entirely, even for reads. Index Organized Tables and incremental clustering would be perfect, but sadly they aren't supported by ...
1
vote
1answer
754 views

PostgreSQL function parameters JSON to TYPE

I try to connect my NodeJS API to my database. I have created some type of object for the transport. I use LANGUAGE plpgsql. One example : API_CAR: this type exists on my database I receive an ...
1
vote
1answer
46 views

Replication to subscriber with different name

Is it possible in PostgreSQL to set up the following replication scenarios? Replication between 2 servers with different database name. For example, replicate FROM server_A (primary) database name ...
1
vote
1answer
110 views

How do I an add a DEFAULT on a column if the table already exists?

I want to add a DEFAULT for a timestamp column in PostgreSQL. With SQL Server you use ALTER TABLE ADD CONSTRAINT You can see the example in their docs, ALTER TABLE dbo.doc_exz ADD CONSTRAINT ...
1
vote
1answer
2k views

Using pg_prewarm to load X number of latest rows into cache

We have a large query that is extra slow when customers "run it the first time, early in the morning..." So, I found pg_prewarm that I would like to use load into PG's buffer cache a certain amount ...
1
vote
2answers
2k views

Does Postgresql support different transaction isolation per connection?

I just discovered that H2 doesn't support concurrent connections using different transaction isolation levels. Meaning, changing the transaction isolation of one connection affects all other ...
1
vote
1answer
435 views

Index by subkey in jsonb

I have a table payment with a jsonb column application. It contains payments and the total amount for one or more tours. For example, a parent can pay 1000 $ for all his family, e.g. 4 children and ...
1
vote
1answer
785 views

Passing array from one plpgsql function to another

I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new ...
1
vote
2answers
162 views

Why is this seemingly simple insert trigger function causing deadlocks?

This is a simplified version of my prior question. I removed the many-to-many complexity and still deadlocks. It happens WAY less frequently, but still happens. 😕 The situation... I have a tweet ...
1
vote
2answers
2k views

How to get OS user in Postgres?

The title says it all. In Oracle, we can easily get the OS user using: select sys_context( 'userenv', 'os_user' ) from dual; I usually use that for logging purposes (know who inserted/updated what ...
1
vote
1answer
658 views

pg_dump issue while dumping single schema

pg_dump does refuse to dump a given schema in my DB. pg_dump -d <dbname> -s -n dm_geo -f <filename> Result -- -- PostgreSQL database dump -- -- Dumped from database version 9.5.4 -- ...
1
vote
1answer
61 views

Is there any slowdown in casting from varchar to text?

In writing this answer, I'm just wondering if there is any slow down in casting from varchar to text or if that cast is just a compile-time trick. I've been told they're represented the same way and ...
1
vote
1answer
79 views

Index Query With Limit, Order one One Column, Predicate on Another

I asked this question a few years ago and got an amazing answer that solved all of my problems. However, I also have a very similar query on my Postgres 9.6.3 data that does not perform nearly as ...
1
vote
1answer
23 views

What type of rows does `SELECT my_table FROM my_table` return?

I sometimes encounter this SELECT statement (in JSON-related answers on stackexchanges): SELECT my_table FROM my_table; It seems to turn a one-column result, with multiple rows. What would be the ...
1
vote
1answer
615 views

Search entire database(210 tables) for a unique Id (PK) and get the table name

Rewritten Question: I need to look through an entire database, 210 tables for a unique ID that is a number. I have limited access to my database through things called nodes (Visual programming.) ...
1
vote
1answer
838 views

PostgreSQL on RDS: How to bulk import data with FK constraints?

Is there an efficient way to bulk import data into a PostgreSQL 9.4 database on RDS that makes extensive use of foreign key constraints? The standard method of using pg_restore fails because RDS does ...
1
vote
2answers
148 views

Difficult categorising of rows

First off, I'm not a DBA at all, I'm more of a front-end guy, but I'm currently hitting a brick wall and can't figure out how to tackle it. A database I am working on has a table with several columns. ...
0
votes
2answers
647 views

Materialized views Performance

Postgres’ docs note: While access to the data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view, the data is not always current; ...
0
votes
1answer
9k views

no pg_hba.conf entry for replication connection from host

(Also posted to pgsql-general) I have been configuring a slave server that needs to connect to the host. Both the master and the standby servers have a pg_hba.conf that looks like this: # Allow ...
0
votes
1answer
5k views

Query improvements without UNION

I have the following scenario. A users table, a records table and a memberships table. A user can be associated to zero or more records either directly with the records table records.user_id = users....
0
votes
1answer
171 views

A (simple?) solution to the “logical decoding / sequence” problem in Postgres?

After reading this mind-blowing answer by Craig Ringer to my previous question Achieving "sequence number", rather than dateTime, for updates; postgres specifically I've become interested in ...
0
votes
0answers
40 views

Sum on distinct and group by and filtering

This is the next version of Sum on distinct and group by But It goes deeper I have this : CREATE TABLE active_connections(id serial PRIMARY KEY, source text NOT NULL, user_id int NOT NULL); INSERT ...
0
votes
1answer
441 views

How to change a web application that now needs to become OLTP and OLAP application?

Previously, I did a simple web application for an enterprise client using PHP and MySQL. After some time, it has grown to a point where I realized after talking with people here that I need to treat ...
0
votes
1answer
49 views

Creating a composite type with “char”?

PostgreSQL has a type called char that's stored as an signed 1 byte int The type "char" (note the quotes) is different from char(1) in that it only uses one byte of storage. It is internally used ...
0
votes
3answers
718 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
0
votes
1answer
38 views

Give particular privileges for a table to existing role

My database have some tables such as: Table A Table B Table C I'm using user monitoring with read-only permission. SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE ...
0
votes
1answer
7k views

Combining 2 SELECT queries and printing the results in PostgreSQL

I have 2 queries which I want to use together to produce one output result to the user. The output describes to the user the journey to take to from A to B. I keep getting a syntax error when I try to ...
0
votes
1answer
671 views

Use serial ID for multiple columns and return it from INSERT

I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in ...
0
votes
1answer
3k views

How can I create a bool column for a view based on the result of a statement?

I'm using PostgreSQL as my database. CREATE TABLE groups ( id SERIAL NOT NULL, name TEXT NOT NULL, PRIMARY KEY (id), UNIQUE (name) ); CREATE TABLE users ( id ...