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)

1
vote
1answer
21 views

Query single column, all rows in Postgres 2D Array

Is there any way to fix the query below so I don't have to specify an arbitrary max row count? DROP TABLE IF EXISTS array_test; CREATE TABLE array_test ( id serial primary key, name text, ...
0
votes
0answers
32 views

Optimize query with access to tiny fraction of data

I am trying to optimize some queries but I can only use a tiny portion of the actual data they will be executed against later. Probably the query optimizer will generate completely different plans ...
1
vote
1answer
23 views

Postgresql ignoring a substring in the middle of a string

I may have phrased the title of this question badly. I have a documents table that has a filename, path, and file_content_type columns. I want to search against the filename column to determine if ...
0
votes
1answer
23 views

Possible to use a field within a query in PostgreSQL?

I want to query all events that are going on right now. Each events record has at and minutes fields for the time range of the event. I might query the events table like this: SELECT * FROM events ...
0
votes
1answer
17 views

Select row if column data was not selected

How can I selected rows from a table when a column data is different. Table: id(pk), name, title, book_id, date I want to get all the rows where book_id is uniq. Data example: test1, test2, 1, ...
0
votes
1answer
44 views

What is the purpose of the keyword “||” in PostgreSQL? [closed]

I'm new to PostgreSQL and I'm trying to figure out what does the operator || mean? Google didn't help with that...
0
votes
1answer
23 views

Postgresql select columns in join query [closed]

say that i do inner join in query... the result will give columns from both table. Is there a way to specify that i want only columns from one of the tables without specifying all columns names ...
2
votes
1answer
42 views

is it possible to run postgres with no WAL files being produced?

I am looking at a way of running a 9.2 postgres database on redhat 6 without any WAL files being produced. We have a end of day batch process which generates a large number of WAL files (and has ...
0
votes
0answers
17 views

Postgresql list all functions associated with table and column [duplicate]

I have a table named SW and in it a column named ID. I want to list all functions from all Schemas that use this table and this column. I've wrote the following query: SELECT n.nspname AS ...
0
votes
2answers
44 views

PostgreSQL: how do I list all triggers that use a specific field?

for example i have a student table with (studentid) i want to get a list of triggers that use this field... for example if in table X there is a trigger that uses studentid i want it to be shown in ...
0
votes
0answers
46 views

PostgreSQL -> MySQL Migration with MySQLWorkbench

I'm trying to migrate data from a PostgreSQL DB to a MySQL DB (it's for permissions in the VPC, so please nobody link me the 'choose-something-else'). I'm getting this error from MySQL Workbench when ...
1
vote
0answers
20 views

postgres \copy file to RDS timeout

Postgres version 9.3, hosted on Amazon RDS. I have a messages table with 10G of data. I connected the database using psql on the EC2. Then used \COPY (select * from messages) TO '/tmp/messages.csv' ...
3
votes
1answer
59 views

Drop primary key without dropping an index

Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to ...
0
votes
0answers
10 views

Query table with the same name on different schemas in PostgreSQL [duplicate]

I want to query table with the same name (for instances "walls") in multiple schemas on a PostgreSQL database. Is it possible? I am searching for something like this: select * from *.walls; I need ...
0
votes
3answers
41 views

Boolean check on fields returning any of them except if all have same value

Assuming we have 4 boolean fields a,b,c,d How can we return any of the fields that are true, but not nothing when all 4 are true? where a=true or b=true or c=true or d=true will return a row where ...
0
votes
0answers
36 views

How to list all tables associated with specific foreign key?

I have this Postgresql query: select (select r.relname from pg_class r where r.oid = c.conrelid) as table, (select r.relkind from pg_class r where r.oid = c.conrelid) as type, (select ...
1
vote
0answers
30 views

postgresql Insert into table B based on foreign key in table A, if exists in A else

the scenario is as follows: I have an invoice and supplier table. The invoice references supplier on supplier_id. I want to insert into invoice. If supplier_company does not exist in the supplier ...
0
votes
0answers
23 views

postgresql view all tables with specific FK

I have a table of suppliers with supplierID (PK) and supplierName in Schema X I want to find out all tables names in all Schemas where the supplierID is FK there. How can i write a query that does ...
0
votes
0answers
27 views

Loop through fields on a before-insert trigger?

Here's my table: CREATE TABLE foo ( i_am_pk CHARACTER VARYING(255) PRIMARY KEY NOT NULL, dont_touch_this CHARACTER VARYING(255) NOT NULL, name CHARACTER VARYING(255) NOT NULL, addr CHARACTER ...
2
votes
0answers
47 views

Postgresql upgrade issue

I'm trying to test the Postgresql 9.3 -> 9.4 upgrade process on the development environment on windows and I get weird issue during the pg_upgrade invocation: PS D:\bin\postgres-9.4> ...
0
votes
1answer
39 views

PostgreSQL force upper case for all data

Is there a way to force all text data t uppercase without recurring to writing a function for every table or doing it client side?
0
votes
0answers
16 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
33 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
29 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
34 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
40 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
23 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
37 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
45 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
24 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
33 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
138 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
47 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
25 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
86 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
1k 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
54 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
194 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
56 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
31 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
50 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
40 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
41 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 ...