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
2answers
23 views

How to use Italian locale for Postgres messages

On Oracle I have set: export NLS_LANG=Italian_ITALY.UTF8 .. on Unix shell and now I get status and error message in Italian. How to set in Postgres shell (psql)? I have set: SET lc_messages TO ...
0
votes
1answer
38 views

Postgres automatic insert time stamp with time zone (india) [closed]

Is there any way to insert timestamp with zone automatically when query is inserted ? (just like id auto increment as i don't want to pass the the parameters in sql as time its same every where if we ...
0
votes
1answer
30 views

Transfer data to New Laptop [closed]

I am completely new to PostgreSQL My data is in the directory c:\postgres.......data and the database name is GXPGRAN. I need to copy this to a new laptop. Can somebody please give me all the steps ...
0
votes
1answer
49 views

Inner join on array column in Postgres

In Postgres 9.4, I'm trying to pull the names of all columns that are involved in a UNIQUE constraint for a given table in Postgres. It looks like the names of such columns are contained in ...
0
votes
1answer
216 views

How to upgrade postgresql 8.4 to 9.4

I want to upgrade my postgresql 8.4 to 9.4. the documentations are not clear to me. Will I lose my old databases if I do the upgrade? How can I backup my old databases if I am to lose them after ...
0
votes
1answer
36 views

Insert sum of the row data of each record into separate column in postgreSQL?

I have a table id name class social science math Total 2 Max Three 85 56 85 3 Arnold Three 55 40 75 4 Krish Four 60 50 70 ...
0
votes
1answer
28 views

Dropped column still has value when recreated with Postgres table of 150M rows

I need to set the column to NULL. Until now, this has worked, but for some reason, on this table which is much larger than the rest, it doesn't seem to be working: ALTER TABLE ...
0
votes
2answers
66 views

How to rollback transaction if row changed?

I am using Postgres 9.3. When I update row in a table my flow looks like this: Fetch old row (ex. SELECT * FROM tbl WHERE id = 1) Validate new data in scope of old data (old row fetched in point 1) ...
0
votes
2answers
220 views

check key if exists in other table without fk constraint

Lets say i have: table a (id_a int); table b (id_b int, id_a int); I want to check if b.id_a exists in "a" before a new row in "b" will be inserted. Normally I would just add a fk constraint on ...
0
votes
1answer
40 views

What is held in memory during the life of a CTE?

Assuming a multi-read/multi-write CTE, what exactly is held in memory during the life of the statement? Is all data except RETURNING data discarded at the end of each sub-statement?
0
votes
1answer
461 views

AWS RDS PostgreSQL dump/restore - syntax error

I am trying to import a postgresql database from an EC2 instance to RDS on the same subnet, so far I am doing the following... 1) Dump EC2 DB: pg_dump --host localhost --port 5432 -Fc ...
0
votes
2answers
90 views

pg_stat_activity - is it possible to decompose a stored procedure further?

I got a long running query in pg_stat_activity: datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | ...
0
votes
2answers
82 views

Is making joins between different table spaces slower?

In MySQL and PostgreSQL, is making joins between different tablespaces inherently slower, compared to joining tables in the same tablespace?
0
votes
2answers
50 views

join value in a RETURNING which is not inserted

Im doing an insert from a query which joins two tables, then I want the new ID from the inserted row, and a field from the original row which is not involved in the insert.Is it possible? I get a ...
0
votes
1answer
2k 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
43 views

Database storage limit for open source PostgresSQL

I just started using PostgresSQL. When I installed SQL Server Express years back, you typically had a database storage limit if you didn't install the full version. I think I remember it being 4GB, ...
0
votes
2answers
2k views

Performance Benchmark - MySQL vs PostgreSQL [closed]

this is my first question here in DBA. I wish to do a performance comparison between MySQL and PostgreSQL using same database on the basis of total time taken to parse + execute + send result back ...
0
votes
1answer
854 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
75 views

PostgreSQL roles versus users, grant permissions

I'm currently learning the differences between PostgreSQL and MySQL as I've got a new project and I also intend to migrate my existing software from MySQL to PostgreSQL. I've actually started creating ...
0
votes
3answers
175 views

Can i have one foreign key to both parent and it's childs in postgresql?

I want to have a foreign key to reference table and it's children. Here's how I tried and failed: CREATE TABLE parent ( id serial, CONSTRAINT parent_pk PRIMARY KEY(id) ); CREATE TABLE child ( ...
0
votes
1answer
643 views

Use if exists in postgresql [closed]

I know about the exists feature, but that does not help me with the given situation. What I want is: Check if some row exists, and if exists then check for an another row if row exists. Here's ...
0
votes
1answer
106 views

Use serial ID for multiple columns and return it from INSERT

I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value. This was done fine in ...
0
votes
1answer
906 views

Backing entire Postgres database server up with pg_basebackup

I back Postgres DB 9.1 up using following command: pg_basebackup -h 127.0.0.1 -U replicationuser -D /storage/test_backup -Ft -z -P In postgresql.conf I have this set: wal_level = hot_standby ...
0
votes
1answer
62 views

PostgreSQL SQL syntax - JOIN and substrings

I have a single table of hostnames and NFS exports, some but not all of which are related. Example entries: HOST VOLUME ----- ---------- host1 vol1 host1 vol2 host1 ...
0
votes
2answers
214 views

SUM() the result of a stored procedure

I use the Pl/Proxy extension in order to shard a table. It works like a charm, except for this : I have a stored procedure which runs on 4 shards. It looks like this : CREATE OR REPLACE FUNCTION ...
0
votes
1answer
39 views

Can PostgreSQL results include the query?

I run the following command to run 2 queries in a sql file. Could be 1, could be 50 queries. Depends on the deployment. psql -U <username> -h <servername> -d <databasename> -f ...
0
votes
1answer
249 views

Postgres live backup

Goal: Maintaining a constant live backup of a postgres cluster. In case of cluster failure, backup/Secondary will contain most up to date view of Primary cluster. This is not a hot standby ...
0
votes
1answer
88 views

ORDER BY that doesn't contain repeating values

Relevant SQL Fiddle (postgres 9.2) How do I state a SELECT .. ORDER BY .. that avoids having repeated values for a column? That is, for rows of the column "rank" (1,3,3,1) how do I get a result so ...
0
votes
1answer
113 views

Database design for small startup

For a small startup that doesn't have much expected traction in the very beginning. We are currently working with only one postgres database without replications and at risk of failures. I've done ...
0
votes
1answer
2k views

Using dynamic column names in PostgreSQL

I am using PostgreSSQL 8.3. I want to execute this query: select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc'; Twist in the story is column name colum_fr should be generated ...
0
votes
1answer
57 views

Why is it not possible to alter a view with a “CREATE OR REPLACE”?

The Postgresql documentation is clear on this point: you can not change the columns of an existing view. The new query must generate the same columns that were generated by the existing view query ...
0
votes
1answer
472 views

Close all connections to postgres without having a connection

I have a cron job that runs a script that connects to a remote postgres database that I do not administer. We are allotted a single connection and, on the occasion that I or a colleague log in to run ...
0
votes
1answer
63 views

Postgresql create database from backup programmaticly [closed]

I got a big problems here. Let's say I got the table A (with columns like id, name etc) in the main database. So when I making INSERT into A I need to create new database lets say B_id. I got .backup ...
0
votes
1answer
85 views

How to grant select on a view … based on IP adress?

I wonder if it's possible to allow a client A to "see" some data in a view while allowing any other clients, namely B and C, to see ALL data from the same view. The distinction between A, B and C ...
0
votes
3answers
84 views

Drop database owned by specific user in PostgreSQL

I have created database with bad symbols at the end of its name. Now I want to drop it but I don't know how to type its name in the console. Can I remove databases owned by this user? I tried to DROP ...
0
votes
1answer
351 views

PostgreSQL dblink password encrypted or not?

I have two PostgreSQL servers: server 1: 192.168.0.1 server 2: 192.168.0.2 I want to connect to the database on server 2 from server 1. I do it like this: SELECT ...
0
votes
1answer
171 views

Postgres CYCLE and existing values

I love that SERIALs can wrap around with CYCLE. However, I can't seem to figure out if that there are lingering values, will Postgres skip over them or throw an error. In other words, I have a table ...
0
votes
1answer
123 views

Subtracting dates give me error in Postgresql

I use age to get the interval between to date fields. Their type is timestamp. My code is as below: select age(timestamp MySecondDateField, timestamp MyFirstDateField) The data in my field ...
0
votes
1answer
81 views

Rotate Postgres log at 5GB

How to make postgres 9.1 log to have maximum size 5GB and to be circle when comes to 5GB ? At the moment my log file is over 500GB. /var/lib/postgres/
0
votes
1answer
387 views

Importing data from MS Access database to an existing Postgres database

I'm developing a Django based website with a Postgres DB. The problem I'm having is importing the data from an Access DB into it. To be specific, I'm not trying to translate/import the schema from ...
0
votes
2answers
1k views

PostgreSQL - pgAdmin error creating a server

I have the latest versions of PostgreSQL and its GUI-frontend, pgAdmin, installed. When I try to register a new server a fatal error appears when pgAdmin tries to connect to the newly created server - ...
0
votes
1answer
637 views

PostgreSQL FTS and Trigram-similarity Query Optimization

I have recently started working on PostgreSQL and I have around 12M rows to handle in which I want to apply Full Text Search. I don't have any prior experience in handling such databases. I have tried ...
0
votes
2answers
110 views

calling a function with parameters in insert

Is it possible in SQL ellegantly achieve the following: insert into my_table (the_geom) VALUES geomfromwkb('\x010100000058b4b610a73a27408144b7147f194840',4326)), ...
0
votes
2answers
68 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 ...
0
votes
1answer
522 views

pg_upgrade 9.2 to 9.3 error

I'm trying to upgrade a PostgreSQL 9.2 database cluster to a 9.3 cluster on a new server. I've copied the old 9.2 cluster (using rsync) to /srv/data/postgres on the new server and want to convert it ...
0
votes
3answers
3k 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 ...
0
votes
1answer
151 views

Best read performance when every query is known, and the data doesn't change

I'm trying to maximise performance on an underpowered PostGIS server - 1 core, 4 CPUs. The data (OpenStreetMap) will be infrequently (monthly?) loaded, and never changes otherwise. Each of the 30 or ...
0
votes
1answer
201 views

Entity with >150 attributes: One table or many tables with relationships

I have entity with 170 attributes. It's data from vehicle monitoring. Every entity has datatime label and unique id of gps terminal. Datetime and id of terminal - these are conditions for GROUP BY ...
0
votes
1answer
4k views

Error: Unable to write inside TEMP environment variable path

I am installing PostgreSQL 9.2.4-1-windows.exe on Windows 7 64 with Service Pack 1. I get the following, rather famous it seems, error: There has been an error. Unable to write inside TEMP ...
0
votes
2answers
890 views

Which schema is better for a shopping project? [closed]

I'm working on a business-to-customer project that has variety of product types. There are a few properties like name, description, brand_id that each product has but there are also many specialized ...