PostgreSQL is an enterprise class, open-source, object-relational database management system (ORDBMS) available for all major platforms including Linux, UNIX, Windows and OS X. Please mention your exact PostgreSQL version when asking questions.

learn more… | top users | synonyms (1)

0
votes
0answers
17 views

postgresql how can i get list of trigger which are fired when function X is working?

my goal is to list all trigger names which are working when function X is working. i assumed it has something to do with pg_proc and pg_constraints but i couldn't figure it out.
0
votes
1answer
22 views

postgresql double count query

I have a table with 3 columns: key, status and rank. status contains numbers and rank contains t or f. key is auto numbered and irrelevant here. my goal is to count how many in each status are t and ...
3
votes
1answer
38 views

Local Backup from Amazon RDS PostgreSQL database

I have a PostgreSQL database on an Amazon RDS instance. I want to have a local dump of this instance. So far, I've tried with this: PGPASSWORD="Password" pg_dump -h ...
1
vote
1answer
31 views

Returning case-sensitive input of case-insensitive array match

Taking a JSON table with some simple data: CREATE TABLE jsonthings(d JSONB NOT NULL); INSERT INTO jsonthings(d) VALUES('{"id":1,"email":"Test1@test.com"}'); INSERT INTO jsonthings(d) ...
0
votes
1answer
37 views

Adding elements into PostgreSQL json array field

I have table where one of the fields is JSON array. I need to append received JSON array into that field without overriding existing values. Something like that: CREATE OR REPLACE FUNCTION ...
0
votes
2answers
41 views

How to access row value from UPDATE sub query function?

I would need to use UPDATE like this: update offer set (bank, bank_id) = (cost, gid) from ( select cost, osm_points.gid from pgr_kdijkstraCost( 'SELECT gid as id, source, target, walk_cost as cost ...
0
votes
1answer
24 views

Postgres one-liner import & export SQL without login & without asked for password

I'm looking for one-liner command for import & export SQL without login into postgres role or psql prompt. Below is what I got for import SQL, but it asking password every time: psql -h ...
0
votes
0answers
21 views

Can a AWS PostgresSQL RDS instance be provided with a stop words file?

I need to provide a custom list of stop words. From the manual section on dictionaries the way to do this is to put a stopwords file in $SHAREDIR/tsearch_data/. Is it possible to do this when using ...
0
votes
0answers
44 views

Import SQL & export CSV using Golang - how to escape backslash?

I'm trying using Golang for import SQL from /home/z/Documents/data.sql and export to CSV /tmp/filename.csv Since out of the box Golang access from inside psql, the import SQL command will be: \i ...
1
vote
1answer
46 views

Query for sub-array in multi-dimentional array

How do I fix the following sql query: DROP TABLE IF EXISTS array_test; CREATE TABLE array_test ( id serial primary key, name text, array_column integer[][] ); insert into array_test (name, ...
0
votes
1answer
26 views

What is the difference between xact_start and query_start in postgresql?

I am reviewing over the pg_stat_activity view. And there are two fields that are indistinguishable to me: xact_start:The time at which the transaction started to run which could be different ...
0
votes
1answer
21 views

Access operator added by extension inside a schema

I am writing an application that uses postgres. Each microservice uses its own schema inside the database. So connect to the database schema I use this jdbc string: ...
0
votes
1answer
34 views

Postgresql regex match all digits in column

How do I extract all digits from a string with regex in pgsql? select regexp_matches('dsa8a552a5a2a5?', '\d+'); Will output only 8. Need to get 8552525
0
votes
1answer
45 views

improve long running query

I have this query INSERT INTO interactions( user_id, external_object_id, origin, interaction_type, interaction_sub_type, published_at, origin_id, created_at, updated_at) SELECT ...
7
votes
0answers
149 views

Avoiding “Row by row” fetch method when dealing with source LOB columns

I have a legacy PostgreSQL database source (ODBC) that I'm attempting to migrate to new SQL Server schema using SSIS. I'm getting a warning saying: 'Row by Row' fetch method is enforced because ...
0
votes
1answer
50 views

Divide a PostgreSQL database into schemas

My Postgres database will be focused on multidisciplinary organizations. The project axis is: a person could belongs to diverses disciplines (ex: arts, sports) for the same organization, and this ...
0
votes
2answers
26 views

Table constraint, number of row taken by a fk key

Is it posible to add a table constraint, to prevent an insert if there are already 3 row of the same fk key My table: ProjectTitles (id(pk), project_id(fk), title_id(fk)) Check if project_id has ...
0
votes
1answer
15 views

backup postgresql database to submit with project

I have a final year project written in Matlab in conjunction to a postgresql database called 'hm'. I asked in which way could I submit the database and I was told to perform a back up of the database ...
1
vote
1answer
103 views

Postgres 9.4, replication slots, doesn't work failover master

I've installed postgres master and slave nodes by this manual: At the master (10.0.0.1): sudo -u postgres initdb -D /data/postgres/main Change postgresql.conf: listen_addresses = '*' wal_level = ...
0
votes
0answers
20 views

doing a insert while selecting an specific user

I'm importing and legacy database to a new database, and changing data format. In this legacy database, I have data as following: Interaction id: 5458d717cd07870859000004 user_id: ...
7
votes
5answers
2k views

SELECT DISTINCT on multiple columns

Supposing we have a table with four columns (a,b,c,d) of the same data type. Is it possible to select all distinct values within the data in the columns and return them as a single column or do I ...
0
votes
1answer
62 views

Postgres Partition Table Trigger inserting duplicate records

I have a trigger applied on my database table. But when i insert data into it via hibernate jpa it creates duplicate rows in master table. Here is the trigger CREATE OR REPLACE FUNCTION ...
0
votes
1answer
195 views

Mysterious inequality behavior when comparing date strings

Can someone explain this mysterious Postgres behavior? I'm performing the following queries at the psql command line. mydb=> select '20150526' > '2015-05-25'; -[ RECORD 1 ] ?column? | t ...
3
votes
1answer
61 views

Query the definition of a materialized view in Postgres

I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view: SELECT * FROM ...
1
vote
0answers
37 views

pg_restore fails with materialized view

I’m trying to pg_restore a db dump that contain a materialized views. If it is relevant, the materialized view is of a foreign data wrapper. It fails with the following errors: pg_restore: ...
0
votes
1answer
52 views

GROUP BY very slow when returning many rows

I have a query to aggregate data per day. When I run the query for a single day it is very fast. However, when I run the query for multiple days the time cost increases much faster than ...
0
votes
1answer
42 views

Is it possible to select distinct values in a json document using PostgreSQL?

I have a column that is using the JSON type. I would like to perform a query to select all distinct records for a particular field in the JSON string: I.e. given these three documents { id: 1, s: ...
1
vote
0answers
42 views

Master/Slave - streaming replication restrict function to master

How can I run a function a on master only? I don't want to connect directly to my master server, because the master server might change. Or can I change my function to work on this setup? Because ...
0
votes
1answer
43 views

Why would you need a tablespace?

I learned how to create tablespaces, rename it, assign an owner to it, and drop it. But I don't understand why I would even need it. A tablespace is simply a storage area on disk assigned to a ...
1
vote
2answers
47 views

Speed up creation of Postgres partial index

I am trying to create partial indexes for a large (1.2TB), static table in Postgres 9.4. My data is completely static, so I am able to insert all data, then create all indexes. In this 1.2TB table, ...
0
votes
0answers
21 views

PostgreSQL updates hang when two servers involved

I have an update statement that works fine when only a single server is involved, but never completes when it comes from two servers. I'm connecting with JDBC 9.1-902 to a 9.3 database on CentOS 6.6. ...
9
votes
2answers
103 views

How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?

I understand that a trigger on table t defined with FOR EACH STATEMENT will run once when I execute an UPDATE t .... Now, when t is defined with FOREIGN KEY ... REFERENCES a ... ON UPDATE CASCADE, ...
3
votes
1answer
61 views

PostgreSQL bytea vs smallint[]

I'm looking to import large (100Mb -- 1 GB) multi-channel time-series data into a PostgreSQL database. The data comes from EDF format files that chunks the data into "records" or "epochs" of typically ...
0
votes
1answer
38 views

Why have both a postmaster and postgres executable?

It's a little confusing how you have several executables to start postgresql, e.g. postmaster, postgres, pg_ctl. postmaster -D /usr/local/pgsql/data postgres -D /usr/local/pgsql/data pg_ctl -D ...
0
votes
1answer
51 views

How should I index a UUID in Postgres?

I'm new to PostgreSQL and somewhat new to databases in general. Is there an established way of how we should index UUIDs in Postgres? I'm split between using hashing and using a trie, unless there's ...
0
votes
1answer
38 views

How to remove data from multiple tables with different foreign keys without using ON DELETE CASCADE?

I have the following SELECT statement: SELECT * FROM "Users" u INNER JOIN "Calendars" c ON (u."UserId" = c."UserId") INNER JOIN "Actions" a ON (c."CalendarId" = a."Calendar_CalendarId") WHERE ...
9
votes
1answer
503 views

Why is SELECT * much faster than selecting all columns (in a different column order) by name?

On a table with columns a,b,c,d,e,f,g,h,i,j,k I get: select * from misty order by a limit 25; Time: 302.068 ms And: select c,b,j,k,a,d,i,g,f,e,h from misty order by a limit 25; Time: 1258.451 ms ...
1
vote
0answers
48 views

Postgres not using the index even when rows returned is 5% of the table

I have a postgres table with the following structure: +---------+-------------+-------------+----------+---------+---------+ | id | timestamp | numvalues | text1 | text2 | text3 | ...
1
vote
1answer
88 views

Could not connect to postgres after installing 9.4 and uninstalling 9.3

I have installed postgresql 9.4 and I removed postgresql 9.3 from my computer. Now when I try to run: foo@bar:~$ psql I get the error psql: could not connect to server: No such file or directory ...
0
votes
1answer
57 views

could not open log file “/var/log/postgresql-2015-05-24_181212.log”: Permission denied

I installed postgresql from source on Ubuntu 14.04 (I also installed from repositories too before, I just want to get a feel for both of them right now, as I will heavily rely on this database). So ...
0
votes
1answer
38 views

Multiple database directories with postgresql

I installed postgresql from source on Ubuntu 14.0.4. It is located in /usr/local/pgsql. At first, I created a data directory at /usr/local/pgsql/data and then ran initdb -D /usr/local/pgsql/data I ...
0
votes
1answer
60 views

How to execute two transactions concurrently in PostgreSQL?

I want to execute two transactions concurrently in pgAdmin: --The first transaction should modify the age of Paul BEGIN; DECLARE newage INTEGER; --the original age of Paul is 32 newage := Select age ...
0
votes
1answer
28 views

PostgreSQL ODBC and Microsoft Access date/time in aggregate functions

I am trying to replace our MS Access 2003 databases with PostgreSQL 9.3. The first step is to use our MS Access front end to the upsized databases in PostgreSQL. Everything seems to work very well ...
1
vote
1answer
40 views

Performing search on a column by query

Up until today I've used LIKE %term% to query for rows on particular column term. Then I've read about FULL TEXT index. And I think I misunderstood how Full text index works. I have an API that ...
0
votes
0answers
37 views

Is there some tricks to better index a URL column in postgres?

I'm working with a table that needs fast retrieve rows by its url column. But they all will be starting with http://www.: http://www.google.com http://www.amazon.com http://www.stackoverflow.com I ...
0
votes
0answers
65 views

Postgres 9.4 - How to really grant all privileges on a database to another user?

Just a heads up, I'm fairly new at this whole DBA thing. Okay, with that out of the way I've been struggling mightily lately with granting access to a database in postgres to a user. Let's call the ...
0
votes
0answers
25 views

Conditionaly query in postgresql function

I have a simple query below : CREATE OR REPLACE FUNCTION companies_all_teams( ...
0
votes
1answer
23 views

PostgreSQL: a Login Role which can select from any Views

I need to write a SQL file which must do the following: create a role called 'selection' grant connect/usage to the current database grant select/execute to all views of all schemas (or at least on ...
0
votes
0answers
45 views

Postgres uses a lot of cpu

First of all, I am a bit newbie in case of postgres database but I was requested to check this out. The problem is that postgres processes occupy time to time almost 100% inside the linux system - I ...
0
votes
0answers
37 views

Postgres Query Optimisation (Rails App)

I have a very slow running SQL query that is run from my Rails app using the Model.find_by_sql method. I've made a number of optimisations but it's still extremely slow. It would be great if anyone ...