Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [postgresql]

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

2
votes
3answers
48 views

Identify proper index for group and order by date and hour (2 columns)

PostgreSQL 10.4 I have a table that has already an index on date column. Current query plan is doing a Bitmap Heap Scan using the existing date index. I would like to add a new index for this query, ...
0
votes
0answers
7 views

Are Cataloge and relation cache in PostgreSQL per connection or global to the server?

I am trying to figure out if Postgres' relation and catalog cache are per connection or per server? I did find that plan caches are per connection. It would make sense for the cache to exist for as ...
0
votes
1answer
9 views

Grant CREATE SCHEMA (only) to users

Imagine I have one shared database set up for my class that each student has the potential to access (access to the database is open and only requires kerberos auth). The public space is where I ...
0
votes
1answer
93 views

postgresql revoke access to login role

I executed this query that gave schema access to a login role called test GRANT ALL ON SCHEMA open_waters TO test; I want to now revoke the access to test
1
vote
0answers
85 views

PGpool not properly detecting which postgres server is master

I am in the process of building a resilient database cluster and currently have 3 database servers running postgres 10 and repmgr. repmgr is taking care of the streaming replication including ...
8
votes
1answer
16k views

How to grant drop table/function privileges to a role in PostgreSQL?

I want to grant drop privileges on all tables and functions (not only those owned by the user) in certain schema of a specific database to a specific role. However, GRANT ALL PRIVILEGES is not enough ...
0
votes
0answers
19 views

Inserting data by related uuid

I'm building an API and most of my tables that are "visible" to the API have two keys: an id and a uuid field. The id is used for internal relationships, and the uuid is regarded as the id for ...
0
votes
1answer
93 views

What does pg_stat_activity.xact_start mean for autovacuum threads?

If I see something like: db=> SELECT datname,pid,state, (now() - query_start)::interval, query FROM pg_stat_activity where state != 'idle' and query like 'autovacuum%'; datname | pid | state | ...
2
votes
1answer
50 views

Fastest way to extract full table in Postgres

I'm trying to dump a table with 50M records to a file, and my goal is to reduce the time in which this action is performed. I usually use the COPY metrics TO 'metrics.csv' DELIMITER ',' CSV; This ...
1
vote
1answer
17 views

Foreign Keys with ON DELETE SET NULL are still deleted when TRUNCATE CASCADE is called on the foreign table in Postgres

Maybe I am missing something here: CREATE TABLE public.example_table ( id integer UNIQUE ); CREATE TABLE public.foreign_table ( id integer, example_table_id integer, CONSTRAINT ...
0
votes
1answer
20 views

psql table description

in psql if i do \d+, I get something like Schema | Name | Type | Owner | Size | Description --------+-------------------------+----------+--------+------------+----...
1
vote
1answer
42 views

How to get all dates belonging to a timestamp range

I have a tsrange as input. For example: tsrange('2019-03-04 20:00'::timestamp, '2019-03-06 20:00'::timestamp) I want to generate all dates belonging to this range. I started with the following, ...
2
votes
1answer
91 views

Postgresql import user from linux

I install postgresql 9.2 on a Ubuntu 12.10 machine I tried to import linux's user to postgresql but only way I can figure out is CREATE USER Jack WITH PASSWORD '1234'; and do it 10 times (if ...
0
votes
0answers
12 views

Using LTREE type when the final node has ~1000 descendants, which exist under other final nodes

Is it inappropriate to use LTREE type when the final node has ~1000 descendants, which exist under other final nodes? My data is essentially a directory tree, with the final level containing text ...
0
votes
0answers
6 views

Generate Postgresql schema using generator like an ORM (like ActiveRecord or Ecto etc)

I'm wondering if there is a software (a generator, a web page, an executable, a command line, etc.) that allows me to generate a Postgresql scheme since I'm not an expert and I wouldn't know where to ...
0
votes
2answers
90 views

Modelling users and groups - but with multiple types of groups

I'm modelling a user management system in PostgreSQL that is fairly typical in that it consists of users, groups and permissions (and the relationships between them). However, its seems I'm dealing ...
2
votes
0answers
46 views

Does Azure SQL natively support being the subscriber in transactional replication where Oracle is the publisher?…and if PostgreSQL is the publisher?

Is there a native way to setup transactional replication where an Azure SQL database is the subscriber and the publisher database is in Oracle? What if the publisher database is in PostgreSQL? If ...
0
votes
1answer
29 views

(POSTGRES) ON CONFLICT … WHERE condition doesn't seem to be working

We're trying to populate a new table for a new feature with information from an old table, but we are going to be running this query as a script multiple times because we want to catch any user ...
1
vote
0answers
14 views

Postgres Row Level Security policy optimizes poorly compared to inline version

I have a query which looks like this: SELECT post.id, post.author_id, post.published_at, post.content FROM post WHERE post.group_id = 1 ORDER BY post.published_at DESC, post.id LIMIT 5; ...
57
votes
3answers
115k views

How to use psql with no password prompt?

I wrote a script to REINDEX indexes in a database. Here is one of them: echo -e "\nreindex for unq_vbvdata_vehicle started at: `date "+%F %T"`" >> ${LOG_FILE} psql -U ${USERNAME} -h ${HOSTNAME} ...
0
votes
2answers
25 views

Source of Entry in Postgres

I want to make a small web app using Postgres as database. Is there a way to accept database access if and only if the SQL command is coming from my app, blocking any other access to the database?
1
vote
1answer
59 views

Optimize queries that need to be 100% ACID compliant but can tolerate latency in persistence

Introduction I'm writing a trading program in C++. The program involves having user balances and orderbooks. The orderbook is basically a set of buy and sell orders. When submitting an order, say a ...
2
votes
2answers
1k views

Where is the default postgres user path environment set?

This is probably simple but I can't seem to find the answer. We have a server running postgresql 9.4 on CentOS 6.6. Recently we had somebody run a yum install for postgres which installed postgresql ...
1
vote
1answer
22 views

Extending condition in a join with Postgres

I have a situation like this: with my_data as ( select A.a1,B.b1,B.b2 from some_table A left join lateral ( select b1, b2 from X where X.id = A.id limit 1 ) B ON TRUE ) select * from ...
3
votes
1answer
2k views

How do you monitor slow query in AWS RDS Postgres

My RDS Postgres has 5 read replica and it's the maximum number of read replica and cannot scale out by increasing read replica. In this situation, some of the read replicas occur too much high CPU ...
0
votes
1answer
13 views

Using postgres to select a filtered array

I'm looking to select data from a table that contains an ID, and an array of values. But in the query results, i want to filter the array and remove values that overlap with a white list. For ...
0
votes
1answer
94 views

With PostgreSQL getting ERROR: COPY format “CSV” not recognized

Whever I try to copy from test=# \COPY table FROM 'file.csv' WITH ( FORMAT 'CSV', HEADER true ); ERROR: COPY format "CSV" not recognized What's the reason for this? I know I've used this before.
2
votes
0answers
34 views

What's the more performant database design for query speed and write overhead when storing many boolean columns?

Setting the scene I have a PostgreSQL 11 database running on a very fast 64core, 10 SSD HW RAID 10 server. In the database I need to create a table with a couple of million rows, let's call it cars. ...
1
vote
1answer
118 views

Streaming replication version between different minor versions

I want to know few details about streaming replication. I want to setup streaming replication between version 9.4.4 and 9.4.20 ? I have the master server in centos6.6 with postgres version 9.4.4 ...
0
votes
0answers
15 views

How to workaround Postgres foreign data wrapper not supporting INSERT with an ON CONFLICT

I have a large (~10 million rows) table that needs to be copied to another database. The foreign database should store on the latest version of each row, so normally I would INSERT with an ON CONFLICT ...
0
votes
0answers
13 views

How to create an optimized view that fills in sparse observations with modeled data?

I have a table of sensor observations with obs_ts timestamp, sensor_id text, sensor_val int which is partitioned by month (e.g.: observations_201903). There are approximately 150k sensors, providing ...
2
votes
1answer
38 views

Can't create read-write Postgresql user

I'm trying to create two users in a Postgres database—one with read-write access to all tables in two schemas, and one with read-write-create (i.e., able to make DDL changes) to the same schemas. I ...
1
vote
1answer
38 views

structuring media uploads (users, images, videos) using sequelize.js

(using pseudocode to simplify) say I have a User (users table) User { id: PK } and I want to have them upload images and videos (media). I want to be able to differentiate between images or ...
0
votes
1answer
240 views

Query tables with information_schema in pgsql

I am trying to query the tables contained in a schema from an insert trigger on another table. My process is : Manually create the schema with DB Manager inside QGIS. Load tables foo, bar, baz. ...
0
votes
2answers
67 views

PostgreSQL: Copy Table Functions to another database schema

as the title said. Can we move or copy existing table functions to another database schema? I can get all the functions from a database schema using command below (Taken from here): SELECT ...
1
vote
1answer
19 views

Get longest strings in jsonb column

I have a postgresql table with a jsonb column holding a lot of different data. This data needs to be moved into a DB2 database, but I need to know the max length of each individual "node" within the ...
1
vote
1answer
28 views

How much time should a backup of a 100Gb database take using streaming server setup?

I've installed barman on a dedicated server to backup all my postgresql servers. One of the server is now using 100Gb space and the instruction barman backup myserver takes 23 hours. I find it too ...
0
votes
0answers
30 views

Flatten multiple tables into a single large table [on hold]

We are planning to serve analytics for our platform. As of now, data is in relational database and current small scale analytics are running on top of this relational data. Most of the analytics data ...
1
vote
0answers
13 views

Received an unsupported type from Postgres. (14) (SQLGetData)

I have database in PostgreSQL. It is simplest possible database with schema presented here: http://www.postgresqltutorial.com/postgresql-create-table/ I want to migrate it to MySQL. I trying to ...
2
votes
1answer
97 views

Get max url from PostgreSQL statement

I have a very complex query which generates a view in PostgreSQL. Now the query returns duplicated rows, well almost duplicated: one field, the "url" column is different. Now we still want to group ...
0
votes
1answer
95 views

How to optimize query with order by

Have query which generate OpenERP ORM. Table have 100k rows. SELECT "tbl".id FROM "tbl" WHERE (("tbl"."active" = 'True') AND ("tbl"."is_company" IS NULL or "tbl"."is_company" = false )) ORDER BY ...
0
votes
0answers
22 views

Query top and specific ratings for table in postgresql [on hold]

Say, for example, we have players with their current level and timestamp when this level was got. Table looks like: CREATE TABLE tst_ratings ( player varchar PRIMARY KEY, level integer, ...
1
vote
2answers
114 views

Increasing query efficiency of queries spanning multiple tables

I am looking for ways to improve the performance of some relatively complicated queries against both PostgreSQL and MySQL databases. (One of the requirements of the application which the database is ...
-1
votes
1answer
18 views

Postgresql 9.6 db connection our Oracle 11.2.0.4. db

I am trying to do a connection between tables/fields between our Postgresql database and an Oracle database. When updates are made in one it needs to be as closely as possible updated in the other, ...
0
votes
0answers
11 views

MultiMaster Postgres replication [on hold]

I'm writing an app that ti be used by our team, as well as our multiple customers, so that each one of us (our organization and our customers) has an updated copy of the data base on his premise for ...
2
votes
1answer
100 views

PostgreSQL most efficient way to reference multiple tables

I am looking for the most efficient way to reference multiple tables in one table, when there is only one reference possible at a time. Which means tables A and B are referenced by table C, but both A ...
1
vote
1answer
26 views

Insert- and Read-only table in Postgres

I want to create an "immutable" Postgres database, where users can insert/select (write/read) data only, but can not update/delete (change/delete). I came to know there is FOR UPDATE lock, but did ...
0
votes
0answers
24 views

Combine different index types in postgres

we have query for fulltext where we have also another condition. Can you tell me what is correct usage of multiple indexes of different type? for instance: select * from foo_tbl foo where foo....
1
vote
0answers
23 views

Changing Postmaster File Name in PostgreSQL

I have two instances of PostgreSQL with master-slave replication between them and want to use GlusterFS. Everything works fine until the second instance wants to store the data in the GlusterFS. ...
0
votes
1answer
95 views

Copy postgis layer from S3 to Heroku

I have a dump of a postgis layer (layer.dump), which I am trying to add to my heroku database (mydatabase). The postgis layer is stored on S3 (https://s3.amazonaws.com/layer.dump). I would like to add ...