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)

4
votes
1answer
57 views

Is there a way to insert multiple rows into a table with default values for all columns?

I can insert multiple rows into a table with default values for all columns the RBAR way: create table course(course_id serial primary key); do $$ begin for i in 1..100000 loop insert into ...
0
votes
0answers
48 views

Partial unique constraint spanning multiple tables in postgres

To enforce partial uniqueness in postgres, it is a well known workaround to create a partial unique index instead of an explicit constraint, like so: CREATE TABLE possession ( possession_id serial ...
0
votes
0answers
64 views

Upgrade Postgres 9.3 to 9.4 after Ubuntu 14.04 to 14.10 upgrade

I've just recently upgraded my Ubuntu install from 14.04 to 14.10. This automatically pushed Postgres 9.4 onto the system. I'm trying to migrate my cluster like this: sudo pg_dropcluster 9.4 main ...
1
vote
0answers
28 views

PostgreSQL timezone setting

While I've spent many years as a SQL developer, I'm not really a DBA, but I'm the closest thing that my company has to one. We have recently started moving many of our PostgreSQL databases from Heroku ...
1
vote
1answer
44 views

Inserting into related tables

I have a database setup with the following schema: CREATE TABLE siteInfo ( siteID SERIAL NOT NULL, siteName TEXT NOT NULL, siteHost TEXT ...
6
votes
1answer
460 views

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars

I have a legacy schema (disclaimer!) that uses a hash-based generated id for the primary key for all tables (there are many). An example of such an id is: 922475bb-ad93-43ee-9487-d2671b886479 There ...
1
vote
1answer
21 views

Pre-existing shared memory block (key 5432001, ID 32768) is still in use

I have been seeing this error lately that is causing the server to crash all of a sudden. I have seen elsewhere that the solution is to delete the postmaster.pid and postmaster.opts files and restart ...
2
votes
1answer
62 views

How to return a record from function, executed by INSERT/UPDATE rule (trigger)?

I'm having some issue when I'm trying to join two tables in a single view (for migrating code we want to split one big record into two records). I'm using Postgres 9.4! Here is an SQL fiddle. The ...
0
votes
0answers
20 views

Using Code to Access Database Dump via pgAdmin

I looked over this question on how to import a data dump using pgadmin and found the following code: CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$ tables RECORD; gmdeclare ...
0
votes
1answer
29 views

PostgreSQL and MONEY data type for currency values

For a project, I created a table with a column price MONEY NOT NULL column. And I thought it would handle decimals properly, unlike a floating number (i.e. IEEE rounding issues), but I end up having ...
1
vote
1answer
24 views

How do you drop an unnamed check constraint in a PostgresQL table?

I created a PostgresQL table but I added an unnamed check constraint on one of the columns: CREATE TABLE FOO ( id serial primary key, price_range smallint CHECK (price_range > 0), url varchar(255) ...
0
votes
2answers
34 views

What indexes should one use to optimize a PostgreSQL query with a JOIN depth of 2?

Disclaimer: I am relatively new to PostgreSQL. I'm wondering how to optimize a query that does 2 INNER JOINs. My scenario is fairly simple: Select Posts with a photo (Posts.photo IS NOT NULL) and a ...
5
votes
2answers
153 views

Feasible to have thousands of users in Postgres?

We are creating SAAS where we will at most have 50.000 customers. We are considering creating a user in the Postgres database for each customer. We will map each user that logs into our service to a ...
0
votes
1answer
52 views

Free universe tool monitoring all database [closed]

Is there any free tool to monitor most popular databases like Oracle, MySQL, MSSQL, PostgreSQL. I want to monitor the real time performance of all the above mentioned databases with a single tool like ...
0
votes
1answer
20 views

synchronizing schemas in server

In production server there's a database per company, with each produced from a single template. CREATE DATABASE company_x WITH TEMPLATE company_template; Let's say I have updated the schema, is ...
1
vote
0answers
30 views

PostreSQL execute a default query when no row are returned [duplicate]

I need to calculate the average score of a list of userId. I have a table "user_place_score" (user_id / place_id/ score). Sometimes I do not have the score for a user_id so I need to fetch a ...
1
vote
4answers
49 views

PostgreSQL execute a default query when no row are returned

I need to calculate the average score of a list of userId. I have a table user_place_score: (user_id, place_id, score) Sometimes I do not have the score (no row for (user_id,place_id) in the ...
1
vote
1answer
66 views
+50

Clustered index performance consideration in MySQL & PostgreSQL

In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write. ...
0
votes
1answer
28 views

PGpool Promote to master and Slaves wont replicate on failure

Sorry for the copy paste from stackoverflow, but this is probably a better place to post this. Version pgpool-II-pg93-3.4.0-3pgdg.rhel6.x86_64.rpm I’ve seen some chat online about this from a ...
2
votes
1answer
66 views

Optimise huge Postgres table for fast read

I'm about to create a Postgres table with 1.5 billion rows. The table will just have a single TEXT column. The table is effectively a blacklist. When a user of my software saves certain data, this ...
0
votes
2answers
30 views

Can postgresql WAL be regenerated or resent to the standby server?

Does anyone know if there's a way to resend or regenerate a WAL file that was missed by the standby server? During my testing I've gotten into a situation where the main server fails to send a file or ...
1
vote
1answer
32 views

How do I determine if pgAgent is installed on a Postgres instance?

I have inherited some problems and I need to determine if pgAgent exists on some number of installations (CentOS) and is running background jobs. These jobs could be run from cron, pgAgent, or an ...
0
votes
1answer
80 views

PostgreSQL SELECTs not returning correct result following recovery?

I took a copy of the current database from a live slave using pg_basebackup and recovered it to a brand new isolated computer following the instructions in the docs. Once the new database had started ...
1
vote
1answer
87 views

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function] when sub query is used

I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine. SELECT ...
1
vote
1answer
62 views

How to store day and time in postgres?

I have an events table in which various events could be happening in time slots each day. What I mean is each event has many time slots each day of the week. For example, event A can be have slots ...
1
vote
0answers
35 views

postgresql 9.3 with Ubuntu 14.04.1 LTS throw 's ERROR: invalid page in block 105 of relation base/16385/17407

This is Ubuntu 14.04.1 LTS with postgresql 9.3 in production, Now i got this below error's in log. # tail -f /var/log/postgresql/postgresql-9.3-main.log 2015-01-08 14:10:16 IST CONTEXT: automatic ...
-1
votes
1answer
33 views

Postgresql function to create table in other database

I Want a function to create Table in the different database , Database name is the argument for the function and table structure and names are static {No need to pass as the arguments}
0
votes
1answer
33 views

How can I always get Timestamp in certain format without having to explicitly define it everytime

When I run a select on a timestamp I get something like: 2015-01-02T23:22:36.000Z, 1/7/2015 11:03:31 AM In order to format properly I have to do: SELECT to_char(entered, 'MM/DD/YYYY HH12:MI:SS ...
0
votes
1answer
50 views

Scaling PostgreSQL TRIGGER(s)

How Postgres triggers mechanism scales ? We have a large PostgreSQL installation and we are trying to implement an event based system using log tables and TRIGGER(s). Basically we would like to ...
2
votes
2answers
63 views

Is there a way to “SELECT and TRUNCATE” without losing new data?

I have an application that writes raw log files to a table called new in a PostgreSQL database -- lots of rows and lots of duplicate data. Via a cron job running every minute, I normalize the raw data ...
1
vote
1answer
24 views

“No Loop” Constraint for hierarchical data in postgresql

I've got table like this in postgres: id primary key parent_id foreign key on id some data columns I need no a constrain which prevents cycles on the parent relations. There might be more tree in ...
-2
votes
1answer
21 views

I cant connect to PosgreSQl throgh PG Admin

While i try to connect throgh PGAdmin this error message displayed " Error connecting to the server: FATAL: Peer authentication failed for user "postgres" "
0
votes
1answer
43 views

Restoring a database using Postgres data files instead of .sql files

I backed up my Ubuntu 14.04 system using rsync to a USB stick, but apparently it wasn't formatted for booting. The original disk has been erased and I put a fresh install of Ubuntu on it. In migrating ...
3
votes
0answers
154 views

Monitoring Amazon RDS with pgtop

I recently migrated a Postgres database to Amazon RDS and I am lacking a good live monitoring tool. I tried pgtop, but I can't get it to work on Amazon. I can see the header with the DB statistics ...
-4
votes
1answer
47 views

How do i convert bigint to int in postgreSQL [closed]

I wanted to find out negative values in one of the columns of type bigint. so when i do select *from tablename where columname(bigint) <0 ... its throwing me an error. SQL Error [42883]: ERROR: ...
1
vote
1answer
60 views

Should database be denormalized if I need an index across multiple tables

I have a catalog of album and song names. The song has a foreign key to the album table. I expect queries like: SELECT * FROM song s JOIN album a ON a.id = s.album_id WHERE LOWER(CONCAT_WS(' ', ...
-1
votes
2answers
36 views

Logging all the queries in postgres

I am using Postgres as my Database. I need to know whether I can extract all the queries that got executed with out logging them as logging all the queries will degrade the performance of database. ...
0
votes
1answer
63 views

Postgres incomplete startup packet error

I am attempting to install Postgres 9.3 on Ubuntu 14.04 and I am getting an irksome error on startup. Here's what I'm seeing: $ sudo service postgresql restart * Restarting PostgreSQL 9.3 database ...
0
votes
1answer
28 views

Syntax Error In Postgresql Function [closed]

Why am I getting: ERROR: syntax error at or near "'SELECT '" In: CREATE OR REPLACE FUNCTION GetListings (lon double precision, lat double precision, schemas name[]) RETURNS TABLE( schema ...
1
vote
1answer
43 views

Postgres Restore Not Restoring Bytea Data

This is a very urgent situation. We have a postgres 9.4 database installed on a CentOs machine. We are trying to take a backup from a Heroku Postgres database and restore it to the database on ...
0
votes
1answer
16 views

Selecting one variant out of different similar results

I have a table that looks as follows: Book Format Contents A html ... A markdown ... B html ... C markdown ... I want to return only 1 version of a book. ...
7
votes
1answer
85 views

unable to insert row at id, but row does not exist

Here is the weird problem i am facing. I am trying to enter data using following query insert into product_product (id, product_tmpl_id, make_equip, model_equip, name_template, serial_num_equip, ...
0
votes
1answer
64 views

Postgres Enable TCP/IP Connection

I have installed postgres-9.4 database server on a CentOS 6.6 system. I am trying to connect to this server from my laptop (connected to the same network. Laptop's ip is 192.168.1.105. I am running ...
0
votes
1answer
29 views

PostgreSQL Date Check Costraint

How could I add a Check-costraint for Date type in PostgreSQL? For example, CHECK 'DateXYZ>01/02/98' A typecast is needed?
1
vote
2answers
75 views

Understanding multiple table join with aggregation

I have a basic question on how JOIN works on multiple tables. I want to count occurrences of Foreign Key in link1 & link2 CREATE TABLE main ( id SERIAL PRIMARY KEY, name text NOT NULL ); ...
0
votes
1answer
37 views

Can not run PostgreSQL after drop table

I've deleted database from PostgreSQL and after this I can do nothing. Here what i receive with createuser $ createuser -D new_user createuser: could not connect to database postgres: FATAL: role ...
1
vote
1answer
55 views

Postgresql Rename Table without Updating View Definitions

I've found out today that renaming tables in Postgresql also renames the VIEW definitions for Views that use the table I renamed automatically? Is there any way to turn this off? So I rename the ...
1
vote
2answers
57 views

Get the pais_id not associated to fabricante_distribuidor_id and producto_solicitud_id, the opposite from SELECT query

I have this query for get all the pais_id given a fabricante_distribuidor_id and producto_solicitud_id, the query works fine. SELECT DISTINCT nom.pais_id FROM negocio.fabricante_producto_solicitud ...
0
votes
0answers
28 views

Does someone have a cannonical example of setting up PostgreSQL with PLV8?

I'm hoping that someone can either post or point me to a guide for setting up PostgreSQL (9.4) and PLV8. I'm not an expert at PostgreSQL by any means and have been reading about and wanting to play ...
0
votes
0answers
36 views

PostgreSQL replace primary key and converting non-serial to serial type

I'm basically building my own version of phpPgAdmin (if I get good enough to make it good enough I'll post it on a repository hosting service) and currently I'm revising my primary key tool to replace ...