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

learn more… | top users | synonyms (1)

0
votes
1answer
212 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
266 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 later,...
1
vote
1answer
285 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
730 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
145 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
242 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
193 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
304 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
121 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
139 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 ...
3
votes
2answers
2k views

How to define alias in an ARRAY_AGG expression?

I'm trying to return pure JSON from a Postgres 9.2 table. SELECT ARRAY_TO_JSON(ARRAY_AGG(ALBUM_ROW)) FROM ( SELECT album, max(release_year) AS release_year, max(artwork_path)...
3
votes
2answers
258 views

Can PostgreSQL support integration test with some kind of throwaway overlay?

It's a common problem to write integration tests that include a database. If the test changes the database then it could effect other tests or the next run of itself. I know that I could wrap my test ...
1
vote
1answer
221 views

Listing all databases in PostgreSQL [closed]

I have a basic question about PostgreSQL: When connecting to my database server via pgadmin3, I can see that there are multiple databases. For example, let's say I can see "test", "test1", and "...
7
votes
4answers
13k views

PostgreSQL: How to backup only One Schema from a database and restore it on another server

I have a database named "A" which has two schemas "B" and "C". I want to backup and restore Schema "B" on a different server? not sure how to do this as I am new to Postgres. Do I have to create a new ...
1
vote
1answer
106 views

Create constraint for stopping mass update to a table

I want to create a constraint so that if more than 5 rows in a table are getting updated with an UPDATE statement it should warn the user / block the query.
5
votes
1answer
2k views

Schema for analytics table in Postgres

We use Postgres for analytics (star schema). Every few seconds we get reports on ~500 metrics types. The simplest schema would be: timestamp metric_type value 78930890 FOO ...
5
votes
2answers
3k views

Postgres log files not rotating

I have a postgres system with a hot standby server. They use the same config file. I've setup the log rotation to use log_rotation_size and log_rotation_age so that the master server is rotated every ...
2
votes
0answers
579 views

Postgres: PANIC: WAL contains references to invalid pages

I have a slave database that is being replicated from a Master DB. All of a sudden, my slave DB just exited and wasn't able to restart. Here are the error messages when it exits. Similar ones appear ...
5
votes
1answer
12k views

Why aren't unsigned integer types available in the top database platforms?

Databases are usually very customizable with varying data types and custom lengths. It surprises me, as I try to look for the syntax to use unsigned int types that they are not available from neither ...
3
votes
1answer
5k views

Are there other ways to select a dynamic list of columns?

I need to let my users specify the list of columns they want to select. So far I know two ways of accomplishing that. 1. Using refcursors CREATE OR REPLACE FUNCTION selecttestwithcolumnlist( ticker ...
4
votes
1answer
513 views

Stored procedure deadlocking itself

I have a strange situation, seen from the log: Process 37278 waits for ExclusiveLock on advisory lock [16421,999999,12864385,2]; blocked by process 53807. Process 53807 waits for ExclusiveLock on ...
1
vote
2answers
3k views

How can I keep a result set without keeping the transaction open?

The following documentation describes how to see the refcursor returned from a function, here, like this: CREATE FUNCTION reffunc(refcursor) RETURNS refcursor AS ' BEGIN OPEN $1 FOR SELECT ...
2
votes
2answers
6k views

How do I install postgresql in Ubuntu?

I'm following these instructions, however I can only get to step 17.2. Despite installing postgresql successfully via the sudo apt-get install postgresql command, upon running initdb -D /usr/...
2
votes
1answer
3k views

Automatic conversion of anonymous records returned from an UDF to well known table-type

Proprietary code (that we cannot change) has a bunch of user defined functions of the type: create or replace function f() returns record as $$ ... $$ which we call in the following way (for ...
-2
votes
1answer
533 views

Example creating a PostgreSQL XML data type

I am looking for an SQL example where a table is created in PostgreSQL which has a field with data type XML.
2
votes
2answers
8k views

Insert values from a record variable into a table

I am developing a user-defined function that takes two arguments: create or replace function gesio( events_table_in regclass, events_table_out regclass) returns void as $$ ... $$ ...
1
vote
0answers
1k views

PgPool is pushing Postgres to 100% cpu usage

I think I've followed the configuration for pgpool correctly, but he's pushing my postgres machines to 100% cpu usage unnecessarily. I have one pgpool machine that works just fine, no overload at all, ...
2
votes
1answer
2k views

Postgres: “Pivot” unioned table based on one column

I have a query that does this sort of thing: (SELECT rel_id, timestmap, y FROM table_1 AS full_ WHERE full_.timestamp BETWEEN %s AND %s ORDER BY full_.timestamp) UNION ALL (SELECT rel_id, timestamp,...
3
votes
2answers
2k views

How to do a minor upgrade postgresql 9.3.0 windows to 9.3.1?

What is the recommended way to perform an upgrade from PostgreSQL 9.3.0 to 9.3.1 (minor upgrade) using the Enterprise DB built windows installer? Should I uninstall first or just install over the ...
0
votes
1answer
151 views

Insert a group of consistent item with foreign key but colliding with existing items

Is there a way to insert a group of items that are dependent but consistent between them with unique primary keys and foreign keys but that are colliding with items in database. For example, given a ...
3
votes
0answers
408 views

PostgreSQL 9.2 - Partitioning: Constraint Exclusion on SELECT not kicking in [closed]

My partitioned table SELECT queries include all partitioned tables even though checks are in place and constraint_exclusion = on. The insert trigger works fine and new rows are inserted into the ...
0
votes
1answer
96 views

How can I run a PostgreSQL outside the standard directories on Linux, ie outside /var/run/postgresql etc?

I need to be able to run PostgreSQL databases outside the standard installation directories, ie run the existing postgres or pg_ctl process pointing to the alternative ports, configuration and data ...
1
vote
0answers
292 views

PostgreSQL how to create a scalable location-based query

I need suggestions because I am not good enough. I have a DB in PostgreSQL that runs on AWS (Amazon Web Services). I have a table "user_location" where there are stored the locations of each user ...
0
votes
2answers
989 views

What is the default size of text field in Postgres?

In the official documentation (http://www.postgresql.org/docs/9.1/static/datatype-character.html), it states text field is variable unlimited length. I wonder what is the default size of this field ...
2
votes
1answer
4k views

Order by alphabet and then by numbers

I have records like A5 A4 Z1 B2 C7 C1A C11A B1 B4 I want them to be sorted in this manner A4 A5 B1 B2 B4 C1 C11A C7 Z1 using the ORDER BY clause. I want them to be sorted by alphabets ...
20
votes
2answers
13k views

Best way to populate a new column in a large table?

We have a 2.2 GB table in Postgres with 7,801,611 rows in it. We are adding a uuid/guid column to it and I am wondering what the best way to populate that column is (as we want to add a NOT NULL ...
5
votes
1answer
274 views

Improve SQL query

I'm looking for ideas how to speed-up my query which is based on two tables only. I've added indexes, run vacuum. Tables: CREATE TABLE vintage_unit ( id integer NOT NULL, vintage_unit_date date, ...
2
votes
1answer
858 views

Partitioning MySQL vs PostgreSQL

For our new system we are looking for database server. We considered a lot of pros/cons for MySQL(InnoDB) vs PostgreSQL and the breaking point for us is partitioning. We will choose the one which ...
2
votes
0answers
1k views

Query optimization on a large table in PostgreSQL

I am not super experienced with databases, just have a college level course under my belt. I am trying to see if an existing query can be optimized. Currently on average takes over 300ms. There is a ...
6
votes
1answer
582 views

How do I monitor PostgreSQL WAL shipping if I'm not using streaming replication?

We have a fairly simple setup to replicate from our on-premise master PostgreSQL database to our presentation tier in AWS. We're using WAL shipping using the archive_command setting. Basically the ...
4
votes
1answer
958 views

Why are query results shortened in pgAdmin 1.18.1?

I've recently installed pgAdmin 1.18.1 and noticed a strange thing: Long json query results are shortened to 256 symbols and then ' (...)' is added. Could someone help me disable this shortening?
4
votes
1answer
2k views

How to change temporary directory for pg_dump?

I'm running out of space from my harddrive which has a 18GB PostgreSQl databse. When I try pg_dump to a different drive, PostgreSQL creates temporary files on the disk it's dumping from, so I run out ...
3
votes
1answer
3k views

Return ResultSet with column names from postgresql Stored procedures

I have the following postgresql Function: CREATE OR REPLACE FUNCTION readMessage(messageFor INT, qid INT = NULL, ctxt INT = NULL, messageFrom INT = NULL, byTimeStamp BOOLEAN = FALSE) RETURNS SETOF ...
1
vote
2answers
523 views

Database design for holding read/unread content state

In forum application I have member table and post table. What I need is to keep track of what posts have been read by which members. What I thought about is to have a junction table with two columns: ...
3
votes
1answer
2k views

How to add FK on a column of composite type in PostgreSQL?

I have a table which contains a composite type and I am trying to connect a column of it to another table's PK. CREATE TABLE aaa (col1 SERIAL PRIMARY KEY); CREATE TYPE bbb AS (col2 INTEGER); CREATE ...