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
1answer
47 views

Compare only day / month / year portion of a timestamp field

I am creating a view for a certain table, where rows have timestamp fields (planned_ts). The view needs to be sorted according to these timestamps, but since the application is only interested by the ...
0
votes
1answer
28 views

Grant access to all tables of a database

I recently wanted to share regular access rights with one user of a server and I realized that a simple CREATE USER and GRANT ALL ON DATABASE commands didn't let him run a simple SELECT on the data. ...
0
votes
1answer
47 views

PostgreSQL function thinks table reference exists when it does not

So I run this query: SELECT f_copy_tbl('parts','parts1'); ...while working on code based off of this answer and I get this error message: ERROR: relation "parts1" already exists pgAdmin III ...
0
votes
1answer
33 views

HAVING ANY in Postgres

I'm looking for the right syntax to filter groups (i.e. groups of rows that have been GROUP BY'd) where at least one row fits a certain condition. For example if I wanted to select a group where at ...
0
votes
1answer
77 views

Compare data between 2 different schemas

We refactored our old database. Some tables were dropped, some tables were split into 2 new tables etc. So now we have an absolutely new DB schema which has many differences from the old one. Someone ...
0
votes
1answer
19 views

Adding column to Postgres table fails

I have a Postgres (8.4.20) table that I'm supposed to add columns to, but it seems to have some problems that I have difficulties to debug: I'm attempting what I thought was a simple ALTER TABLE... ...
0
votes
1answer
45 views

Safe way to perform DB upgrade

If I am reading the documentation correctly, issuing this command psql -v ON_ERROR_STOP=1 database < script.sql where script.sql starts with BEGIN BEGIN; ALTER TABLE... some other DDL... ...
0
votes
1answer
96 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 ...
0
votes
1answer
35 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
1k 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
42 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 ...
0
votes
1answer
33 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?
0
votes
1answer
396 views

PostgreSQL: PANIC: heap2_redo: unknown op code 32

I was doing migrations while the server was still accessing the slave DB. I have a master / slave setup. After I restarted the server, I realized I couldn't connect to the SLAVE anymore. I went into ...
0
votes
1answer
107 views

PostgreSQL - retrieving max primary key column to insert a new row - any alternatives?

We are using a software that needs to request the max primary key column value to add a new row into the table that was requested. I know that there are some alternatives like: Sequences, and GUIDs ...
0
votes
1answer
33 views

Query with column data for a max

In Postgres, I have a table like this: Column | Type | Modifiers ...
0
votes
1answer
68 views

Postgresql - Streaming replication - STONITH

Current situation: PostgreSQL, streaming replication Master failed, I created the trigger file. Slave is promoted to new master After the old master restarted successfully, I have to create base ...
0
votes
1answer
90 views

Reassigning surrogate keys?

I have a few tables with surrogate keys (SERIAL PRIMARY KEYs unique to each tables). These tables are used by other tables, with references to these keys. My usage of these is quite simple : select ...
0
votes
1answer
192 views

How to create a tablespace on Windows 7 for Postgresql 9.2?

I try to run this simple command: CREATE TABLESPACE tb_rec OWNER postgres LOCATION 'd:/tablespaces'; However, the tablespace is not created and I got this message: ERREUR: n'a pas pu ...
0
votes
2answers
137 views

Using pg_dump and psql to transfer table with timestamp into another Db

I am using the following pg_dump and psql command lines through the terminal in order to transfer my table from one database to another. pg_dump -U postgres -t public.table1 database1 | psql -U ...
0
votes
1answer
155 views

After upgrade from 9.1 to 9.3, commands still point to 9.1

Upgraded from 9.1 to 9.3 on my Ubuntu 12.04 server. Everything worked great but running commands like 'createdb' still runs them from the 9.1 path. /usr/lib/postgresql/9.1/bin/createdb: unrecognized ...
0
votes
1answer
655 views

Postgresql doesn't accept replication connection

Plain old streaming replication. PostgreSQL: 9.2.7 Windows 8.1 64 bit My primary and secondary clusters are on same windows machine. I have already done pg_start_backup() and everything, so both ...
0
votes
1answer
50 views

PostgreSQL: Why is the schema owner not the database owner

I've a simple question, yet I am unable to find the answer from the official documentation. When I create a database in PostgreSQL as a specific user, I'm the owner of all the objects under the ...
0
votes
1answer
31 views

Populate a table with “WITH” clause [closed]

How to perform the following select query? with x (x, y, z) as ((2 5 6), (6,3,2)) select * from x I am not sure if this approach is possible. Not even sure if the syntax is correct. But I don't ...
0
votes
1answer
1k views

How to import large sql file in postgres?

Trying to import sql file around 8GB using these commands sudo -u postgres psql yourdb -f my_file.sql sudo -u postgres psql yourdb < my_file.sql sudo -u postgres cat my_file.sql | psql yourdb ...
0
votes
2answers
81 views

How to simulate postgresql recovery mode?

I would like to simulate putting postgresql into "recovery mode". Few times i have situation where postgresql puted himself into recovery mode. In logs i had: Oct 18 06:37:35 xx postgres[5738]: ...
0
votes
1answer
51 views

Postgresql two tablespaces one on SSD and one on SATA III HD

Continuing my previous question PostgreSQL maximize performance SSD the DBA SE community has given me efficient advice on optimal settings for PostgreSQL on SSD. The problem is that I will define two ...
0
votes
1answer
27 views

'Prioritize' one column over another

I'm working with subscription table currently and I'm creating a cron job that will take records based on some condition from the subscription table. I want to select all subscription records that ...
0
votes
1answer
28 views

Ensuring that 1 row has 5 specific records referencing it

Context: a TEST has many CASEs. Typical. However, the catch is that a TEST instance must have exactly 5 instances of CASE (1 to 5). I could do something like this: create table TEST ( ID ...
0
votes
1answer
93 views

When is the postgres auto vacuum executed

I am using an older version of postgres(8.4.20). I know that the auto vaccum process is executed frequently to free disk space of queries that deleted or updated data in tables. I have a database ...
0
votes
1answer
28 views

How to change the mount point for a column in postgresql table?

This is my scenario I have a table t with following columns id int link varchar(500) name varchar(20) sal text In the link column we will store the location for the pdf files(mount point) ...
0
votes
1answer
17 views

The number of copies for each table stored in physical storage

I am new to Postgres and the database field. My question is that how many copies of each table are stored in physical storage device (memory, disk and so on)? I assume we need more than one copies for ...
0
votes
2answers
89 views

Calculating average with AVG for a list of numbers

This is probably a newbie question (bear with me) but how can I calculate the average for a list of numbers using SQL and the AVG function? I mean, I can add together numbers in SQL like: SELECT ...
0
votes
2answers
68 views

Need to group by column while ignoring some text format

I have the following data. Sport -------------- Baseball Football Baseball [a] Football [a] [b] Basketball How can I group this to ignore the data in the brackets?
0
votes
1answer
99 views

Run and Save multiple SELECT statements

Within a Postgres function I would like to run a number of PSQL SELECT commands. For each SELECT command I would like to output written to separate text files. What is the best way to implement ...
0
votes
1answer
479 views

Delete returns nothing in Psycopg2?

I have a fairly simple delete query in a PostgreSQL database that I'm interfacing with via psycopg2. Take the following minimal example: def testDelete(): db = DbInterface() cur = ...
0
votes
1answer
287 views

Change permissions of postgres copy to exported file

I have asked this question on SO but perhaps that was the wrong location. Apologies for double posting but the question may have a better audience here. The original question is: ...
0
votes
1answer
66 views

Translating MySQL query to PostgreSQL

I have a query which works fine in MySQL, however when I try to recreate the same query in PostgreSQL (same DB structure) I just get a syntax error. MySQL: SELECT ticket.queue_id, queue.name, ...
0
votes
1answer
273 views

Bulk insert in multiple tables

I have two tables on which I perform some bulk inserts: key: key_id(pk), key_name related_key: key_id(fk), related_key_id First Insert: values_data = "($$key_1$$), ($$key_2$$)" INSERT INTO key ...
0
votes
1answer
28 views

Separation of users columns in another table

I've got a quite a lot of columns on the users table and I have been cleaning it up recently. Now since I'm searching over the users I figured it would improve performance if I separated verification ...
0
votes
1answer
48 views

How to access pg_settings from separate connection

I am trying to access the pg_settings table from a separate connection belonging to a legacy application. I know how to identify the connection's procid from pg_stat_activity, but I am unsure how to ...
0
votes
1answer
68 views

Need some help with this query or some ideas for change it to better model

I've a dilemma in my application and I don't get how to solve it at DB level. The idea is that a user can be subscribed to one or more services but services are show depending on user type. This can ...
0
votes
1answer
187 views

Postgresql tries to connect to fe80::1, permission denied

I upgraded a couple of days ago to IPv6 on my local test machine and since then I've been experiencing some issue with connecting to my local PostgreSQL server. I set the host to 'localhost', which ...
0
votes
1answer
106 views

PostgreSQL synchronous replication time out?

I'm using PostgreSQL 9.3 with synchronous replication. I have 2 synchronous replicators and 1 async replicator. My problem is when 2 synchronous replicators are down, transaction will wait ...
0
votes
1answer
410 views

group by clause without aggregate function

I've always assumed GROUP BY was designed specifically for aggregate functions and in all other circumstances you should use ORDER BY. For example, we have three tables: orders, shippers, and ...
0
votes
1answer
182 views

Restoring pg_dump hangs

Aim: backup a database and restore it in database2 pg_dump A PG Dump has been created by issuing the following command: c:\Temp>pg_dump -U postgres postgres > backupTest2.sql Opening ...
0
votes
2answers
526 views

Auto increment char varying column prefixed with some characters

I have a table in PostgreSQL for hospital patients and I want to make a primary key for it as PAT0000001 (char varying) and to keep that field as auto-increment as well. Is there a way to achieve ...
0
votes
1answer
28 views

PostgreSQL: How to list the comments attached to a database

How do I display the comments associated with a database, not a table? I've tried \dd and \list , but can't seem to find this. Thanks,
0
votes
1answer
65 views

PostgreSQL, PostGIS: Copying a schema with a geometry column

According to the PostGIS manual, geometry columns should not by added directly, but using AddGeometryColumn(): AddGeometryColumn — Adds a geometry column to an existing table of attributes. I ...
0
votes
1answer
39 views

How do I go about easy table input without creating an application? [closed]

I am converting all my applications from MS Access to PostgreSQL and I have a few personal-use databases. These MS databases have text fields as the primary keys, so I can type the name of something ...
0
votes
1answer
62 views

PostgreSQL : inconsistent result when using WHERE clause on VIEW

This is my VIEW declaration CREATE OR REPLACE VIEW inv_groups_vw AS SELECT gg.id, gg.sku, gg.name, gg.description, CASE WHEN count(i.*) = 0 THEN '[]'::json ...