All versions of PostgreSQL. Add a version-specific tag like postgresql-9.4 if that context is important.

learn more… | top users | synonyms (1)

1
vote
1answer
489 views

PostgresSQL copying newer entries from a table in one database to a table in a database on a remote machine

I have two databases, I'm going to need to get more recent data from one database table and place it in another table that is on another PostgresSQL database on a different machine. The structure of ...
5
votes
1answer
16k views

PostgreSQL selecting empty fields of integer type

I have a table and I need to select all the rows with an empty value for the fk_fc_id field (as a prelude to deleting them), Column | Type | ...
1
vote
0answers
168 views

How to merge rows based on how much data they have?

I am cleaning data and I formed a table something like this: file | product | color | product_id --------------------------------------------- one.csv pipe blue null ...
26
votes
3answers
28k views

How to turn json array into postgres array?

I have a field user that holds a json document that roughly looks like that: { name: 'foo', tags: ['foo', 'bar'] } I would like to turn the tags array into a concatenated string (foo, bar). ...
3
votes
2answers
671 views

Can't get this query to work (left outer join) Postgres [closed]

I've got this query I'd like to get working. I'm trying to count all the instances of a foreign key after a certain date that isn't null, and then left join that to the distinct of the original table, ...
2
votes
0answers
292 views

How do you stop Postgres from performing writes inside base/ directory during pg_dump?

We are dumping a 4TB postgres database using pg_dump. The source is on a copy-on-write snapshot filesystem, and the target is an external drive. While the pg_dump is running we get about 4-5MB/sec ...
0
votes
0answers
197 views

Postgres chooses a bad query plan

The following query finishes in 1.5s (which is ok, the table contains about 500M rows): explain (analyze, buffers) select sales.* from sales join product on (product.id = sales.productid) join date ...
2
votes
1answer
5k views

Cannot start psql with user postgres “Could not change directory to /home/user ”

I did a silly mistake. I wanted to set postgres permissions to a folder for exporting data from my database. So I set something like sudo chmod 777 -u postgres /home/user and then my home partition ...
1
vote
1answer
3k views

Grant postgres superuser to linux account

On my development (Ubuntu linux) laptop I have a postgres OS account, which owns the Postgres installation. When I want to perform any postgres activities, create/drop databases etc, I must first su ...
1
vote
1answer
6k views

Cannot enter psql command “FATAL: the database system is starting up” [closed]

I cannot login into postgresql server using psql command (to create / update / delete table) because some sort of error like below. the command : [root@Server-Database pg_log]# su postgres ...
3
votes
1answer
2k views

PostgreSQL: FATAL: database locale is incompatible with operating system [closed]

I'd like to log in to PostgreSQL in my server to change my root (postgres) password . Debian GNU/Linux 7.1 wheezy PostgreSQL 9.1 After changing below: $ sudo vi ...
5
votes
2answers
3k views

Call multiple functions from trigger?

I run PostgreSQL-9.2.4 Is it possible to call 2 functions from a trigger? Let's say I have two functions for two different tables to be executed if following trigger fires: Trigger: CREATE TRIGGER ...
2
votes
1answer
473 views

Strange Postgres Hstore syntax with curly braces {key=value}

I'm using the following program https://sourceforge.net/projects/jailer/ to extract a subset of data from my database with correct associations following my foreign key constraints. The program ...
1
vote
1answer
3k views

Calling another function inside a postgres function

I have a function A and function B. Function A Name is f_a() and the code is: Select id,address from A; Function B Name is f_b() code is: Select C.address,B.name From (Select id,name from B, ...
1
vote
0answers
486 views

Correct pg_hba.conf to allow script to run locally with a specific db-user

I've got a working postgreSQL database with following pg_hba.conf: local all postgres peer local all all ...
3
votes
1answer
144 views

selective undoing of commited transactions in PostgreSQL

I have inherited a small (<1M rows) PostgreSQL DB which contains complex business rules split across several tables. Updates are very light, reads not so heavy, but the data is extremely valuable. ...
3
votes
0answers
260 views

Limiting PostgreSQL logging

I have a table that can contain reasonably large BYTEA values (the same could apply to large TEXT values). It looks like this: CREATE TABLE testtable ( id SERIAL PRIMARY KEY, name TEXT UNIQUE ...
6
votes
1answer
447 views

Can we use old primary to sync with standby after failover in PostgreSQL?

If my standby (postgres) is behind by few seconds before failover. What is the easiest way to sync back my old primary after failover in PostgreSQL. In Oracle we have the option of Reinstating a ...
0
votes
1answer
88 views

Use regex in postgres

I want to use regexp_replace() to replace the string name1-01.name2-02.1 to name1.01-name2.02.1 How to use the regex here?
0
votes
1answer
3k views

Insert blank row in the postgres query result

I have below table after run a postgres query select * from tbl 1 type_1 name-1 2 type_1 name_2 3 type_2 name_3 4 type_2 name_4 5 type_3 name_5 how can I add a blank line in between each type so I ...
9
votes
1answer
17k views

Postgres function assign query results to multiple variables

I need to assign values to 2 variable as below in Postgres function. a := select col1 from tbl where ... b := select col2 from tbl where ... How can I assign 2 values to 2 variables in one line ...
0
votes
1answer
711 views

Unique key in SQL Power Architect? Forward Engineering

I want to know if there is a way to create unique keys (not PKs) in SQL Power Architect using forward engineering. I've searched google, saw the tutorials by the software vendor, and checked out in ...
1
vote
1answer
1k views

Postgresql streaming replication and wal archive?

We have postgresql 9.1 configured for streaming replication with a WAL archive, and this works great. We would like to use a shared WAL archive so we can restore from periodic base backups. The ...
2
votes
1answer
9k views

Permission denied for relation <table>

I have ran the following SQL in psql: CREATE USER bspu LOGIN; CREATE DATABASE bsp OWNER bspu; GRANT ALL PRIVILEGES ON DATABASE bsp TO bspu; \c bsp CREATE TABLE users ( id SERIAL PRIMARY KEY, ...
0
votes
2answers
92 views

Amount of memory used by postgres

I am curious as to whether the performance of postgres depends on the amount of RAM available. i.e. will postgres performance(during reads) on several runs get affected by the amount of RAM that is ...
1
vote
0answers
289 views

Does a high CPU usage -say 98%- necessary mean the database will be slower?

I have 4 python scripts each running in their own virtual machine. The scripts are accessing a central postgresql database. All scripts are reading from the same table but posting to different tables ...
2
votes
1answer
6k views

Select next and previous rows

I have the following table: CREATE TABLE post ( id bigint primary key, thread_id bigint, is_notice boolean, title text, content text ) I display the list ...
2
votes
1answer
88 views

Slony Replication: Will data eventually be 100% replicated if daemon dies for long period?

I am using Slony to replicate a Postgres database. If my slony daemon exits for whatever reason, and doesn't start up hypothetically until 1 month later, will all the data that has accumulated in the ...
10
votes
1answer
4k views

Why would you index text_pattern_ops on a text column?

Today Seven Databases in Seven Weeks introduced me to per-operator indexes. You can index strings for pattern matching the previous queries by creating a text_pattern_ops operator class index, as ...
8
votes
1answer
7k views

Need for indexes on foreign keys

I'm struggling with indexes, primary keys and foreign keys... And the need of having them all. If I have two tables, both of them have an integer as a primary key. The first table references ...
2
votes
2answers
1k views

How to create a blank version of a PostgreSQL database

I've been using pgAdmin to create a database and test it. I'm at a point now where I need to install this db on different servers. I've been using pg_dump to do this and I've been able to muddle my ...
1
vote
2answers
896 views

Postgres sequence usage

I have an Postgres database with two users: dev and junit. Junit and dev objects are dropped and created regularly. They both have the same objects unshared for tables, however, sequences are shared, ...
0
votes
1answer
36 views

Conditional when creating a domain

How can I define or change a domain based on another attribute value? F.e. domain for salary is a numeric value > 500. But if a date the person was hired > January 1st, 2013, then the salary must be ...
1
vote
3answers
808 views

Proper use of database functions for business logic

I have a database in postgres with 147 tables; these tables contain data generated by functions that have most of the logic of the system, for example how to build an accounting entry. These ...
9
votes
3answers
13k views

save/export PGAdmin server list configuration settings

I have a ton of configured connections listed in the Server Groups, is there a way I can save this? not just save the passwords but the Server Group Settings
0
votes
1answer
194 views

Merge two tables on partly matching column

I have below 2 tables. old_name old_value Tom1 100 Kate1 80 Jim1 70 new_name new_value Tom2 70 Kate2 100 Jim2 80 I want to make a new table look like ...
4
votes
1answer
4k views

Cannot get my slave - replication server to start

My replication server will not start. I have followed the instructions here: http://opensourcedbms.com/dbms/setup-replication-with-postgres-9-2-on-centos-6redhat-el6fedora/ As well as several other ...
2
votes
0answers
252 views

Replication slave re-reading WAL on restart

I have a standard streaming replication setup going with PostgreSQL 9.1. It works fine. However, if I stop the slave cleanly (to move it or perform other maintenance) and restart it a short time ...
1
vote
1answer
267 views

Copy few rows from one database to another

I have two PostgreSQL databases on two different machines and the network connection is slow. I would like to copy few selected rows from the first instance to the second. Unfortunately, the full dump ...
1
vote
0answers
693 views

How can I force the optimizer to use hash join in PostgreSQL?

In SQL Server, it is select * from A inner hash join B on A.a = B.b. Does PostgreSQL have similar join hint?
0
votes
1answer
143 views

Constrain exclusion to fetch latest N rows

I'm managing a table with several million records which are being inserted in real time. A part of my app needs to display the last N inserted records, so at the beginning I just queried for: ...
0
votes
1answer
233 views

Optimizing PostgreSQL hstore for lots of small updates

I have a situation where I will have one web session per row, but this session will generate dozens of updates to an hstore field in that row, one k/v at a time. At the end, I will have a completed ...
2
votes
2answers
190 views

Is this an example of an Enum (Enumerated Type)

I'm looking at a code base that I have inherited. I'm new to Postgres (but have an Oracle background), and I think this in one of the queries is an example of an enum. Can someone confirm if I'm ...
1
vote
1answer
2k views

Postgresql indexes in memory

I use Postgresql 9.2. I have tables stored on a ramdisk, but now indexes on those relations are located on a regular disk. I want to move them into ram too, but for all I know postgresql loads indexes ...
4
votes
1answer
2k views

Postgres on Engineyard space at 100%, pg_xlog filled up with files

I have a database that is growing rapidly. VERY rapidly. My /db directory is at 100% now, and there are a bunch of new files in pg_xlog. My background is in Oracle and MySQL. I do not know what is ...
1
vote
1answer
1k views

pg_dump Error while running backup

I encountered an error on PostgresSQL. Here is details for the error which i have seen during the backup. pg_dump: SQL command failed pg_dump: Error message from server: ERROR: found toasted toast ...
0
votes
1answer
283 views

store log of update,insert operation of postgresql user

I have to create postgresql user in postgresql 9.0 and give grant on my database xxx and whose all log of insert and update operation should be stored in one external log file so that i can have all ...
1
vote
0answers
118 views

Cloning Postgres schema/tables across multiple servers

I'm working on a project that has databases across multiple servers, with one server for each business client. The project has some identical 'base' tables that are present in all the databases (for ...
6
votes
1answer
137 views

Is Postgres ignoring my function cost annotation?

Why does this: create or replace function dummy() returns double precision as $$ SELECT random() $$ LANGUAGE SQL COST 777; explain select dummy(); return this: Result (cost=0.00..0.01 rows=1 ...
1
vote
1answer
5k views

How to convert bytea to text value in postgres?

In my application I insert data in database using C code, since the strings I receive from an untrusted source I have escaped them using PQescapeByteaConn of libpq library. Which is working perfectly ...