Explore our sites

Stack Exchange
tour help

Tagged Questions

0
votes
0answers
24 views

Why postgresql backup is too big? [on hold]

I have postgresql database, only 100 tables, and data in the tables is not big, only one table have 200 000 rows, but when I am try to backup database, as a result I get 7 GB of backup file. Why is ...
1
vote
0answers
13 views

pg_dump to dump table schema with constraint as compact

My pg_dump returning the constraints are in alter query at the end of the dump file. I want those to be inside the create table(...) section. For example. I have created a table as below with a ...
4
votes
2answers
51 views

Loose index scan in Postgres on more than one field?

I have several large tables in Postgres 9.2 (millions of rows) where I need to generate a unique code based on the combination of two fields, 'source' (varchar) and 'id' (int). I can do this by ...
4
votes
1answer
48 views

Problems with a PostgreSQL upsert query

I'm trying to update the database by either updating or inserting a new record into the *vote_user_table*. The table is defined as follows: Column | Type | Modifiers ...
0
votes
1answer
16 views

can't connect to postgres after restarting mac PG::ConnectionBad

It's been a long time since I restarted my mac, and now that I have restarted it, my rails apps will no longer connect to postgres. PG::ConnectionBad The problem is, i can't remember how i installed ...
0
votes
2answers
31 views

Postgresql - How to rank this data?

Here is my data: client_addr | start ------------+----------- 1.2.3.4 | 12:54:06 1.2.3.4 | 12:55:00 5.6.7.8 | 12:54:06 5.6.7.8 | 13:00:00 5.6.7.8 | 11:00:00 9.9.9.9 ...
0
votes
0answers
18 views

Data Recovery: PostgreSQL showing base volume under postgres pg_default tablespace, but does not recognize separate databases

I had an instance of Postgres (v 9.2), running locally on Windows 7. I have yet to isolate the cause, but PG became corrupted in such a way that the server abruptly stopped, and the service would ...
0
votes
0answers
14 views

Getting osm2pgsql usage error while installing OSM Bright for TileMill

I'm trying to install OSM Bright for TileMill, following this tutorial: https://www.mapbox.com/tilemill/docs/guides/osm-bright-mac-quickstart/#osm2pgsql I installed PostGres.app + postgresql 9.2 on ...
2
votes
3answers
53 views

Retrieve last known value for each column of a row

Not sure about the correct words to ask this question, so I will break it down. I have a table as follows: date_time | a | b | c Last 4 rows: 15/10/2013 11:45:00 | null | 'timtim' | 'fred' ...
1
vote
1answer
24 views

how to create dump for specific schema in postgres DB

I have a Postgres database "rafiu" with many schemas namely test1, test2, test3. In this I want to dump the test2 schema and its data. I tried with the following query pg_dump -U postgres -n test2 ...
2
votes
2answers
94 views

How to find whether unique key constraint exists for given columns

I am working on a perl script, where i need to run update queries. But I need to check if the update sql command does not violate unique key constraint. So if I have a table tb(C1,C2,C3) and my ...
0
votes
1answer
36 views

Enable remote access to Postgre 9.2 in ubuntu?

I can't access remotely my postgre database. My fellow is making a QT project that access the database from my web-server. It's very close to the problem from this guy: Is the server running on host ...
1
vote
1answer
24 views

Cannot generate a lot of unique strings

I've got a problem while trying to update a column with a unique string in PostgreSQL. I have a table with about 30kk records in it. What I want is to set a unique sha1 value to a specific column ...
0
votes
1answer
21 views

PostgreSQL populate_record and hstore

I am running Postgres 9.2.4 with hstore extension (on Windows) Given a Table: CREATE Table tmpM ( id bigserial NOT NULL, EventId bigint NOT NULL, LoginId bigint NOT NULL, CONSTRAINT tmpM_key ...
0
votes
1answer
31 views

How to select the key of a group order by column a where a column b is not ordering

is it possible to select the id of the groups ordered accending by column line_to where a column obj_id is not also ordered accending? So the result for my test-data should be 200 because line_no 2 ...
0
votes
0answers
33 views

Speed of many individual updates vs. one large update

I have a table with a primary id column (automatically indexed), two sub-id columns (also indexed), and 12 columns of type geometry(Polygon). If I am going to update ~2 million rows on this table, is ...
1
vote
1answer
40 views

postgresql index fetch on partitioned table

I'm experiencing strange PostgreSQL behavior. I have partitioned history table into smaller pieces based on time History -> History_part_YYYY-MM Check constraints: ...
2
votes
1answer
39 views

Using distinct dates and aggregate functions with postgres

I'm trying to get a set of averages for each month. I'm using DATE_TRUNC to convert the date time into month year only but I still can't get the distinct DATE to work. Seems to ignore it entirely. I ...
1
vote
1answer
28 views

PostgreSQL : SSL with TCP connections

I am trying to configure SSL communication for PostgreSQL. I have a self-signed cerificate and I am following these instructions as specified in URL ...
-1
votes
0answers
35 views

PgPool is pushing Postgres to 100% cpu usage [migrated]

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

Copying data from one table to another

I'm updating data by selecting data from table and inserting into another. However there are some constraints on the other table and I get this : DETAIL: Key (entry_id)=(391) is duplicated. I ...
2
votes
1answer
44 views

SQL postgres aggregation/pivot of data by weeks with totals

I have a table EventLogs which records a given Event's details such as the date of the event and the fee. +------+----------+---------------------------+-------------------+ | id | place_id | ...
0
votes
2answers
38 views

Postgresql lock shared table with multiple users

So, I'm having a synchronization issue here; Let's explain the situation: I have a table called Sendings, that consists of these columns: id sending_object_id type destinatary The id is the ...
1
vote
1answer
54 views

Which tool should we use to execute stored procedures against PostgreSql

I have some stored procedures that return ref cursors. The manual suggests a somewhat convoluted approach to execute them and view the results: -- need to be in a transaction to use cursors. BEGIN; ...
0
votes
1answer
38 views

Postgresql/SQL Run System Catalog function in Select Query

I have a select statement that is pulling back database information such as name, owner, owner's superuser status and tablespace. For Postgresql versions less than 9.2 I was also able to pull the ...
-1
votes
1answer
32 views

What is a 'Schema' in PostgreSQL?

As the question clearly indicates, what is actually a Schema in PostgreSQL that I see in the top level of the hierarchy in pgAdmin(III)?
0
votes
3answers
69 views

postgresql comparing between dates in datetime field

I have been facing a strange scenario when comparing between dates in postgresql(version 9.2.4 in windows). I have a column in my table say update_date with type 'timestamp without timezone'. Client ...
0
votes
3answers
140 views

How to add the Postgresql jdbc driver 9.2-1003-jdbc3 with Maven?

I have found the Postgresql JDBC4 driver here : http://mvnrepository.com/artifact/org.postgresql/postgresql/9.2-1003-jdbc4. But I can't seem to find the 9.2-1003-jdbc3. Do you know of an online ...
1
vote
2answers
75 views

How to use dblink_connect and dblink_connect_u

I have two databases in Postgres and want to access the data from one database to other database tables. I tried like this: dblink_connect('myconn','hostaddr=10.10.30.53 dbname=postgres user=postgres ...
0
votes
1answer
40 views

Insert statement that will not fire trigger

I am using Postgresql 9.2 and i need insert statement, from one table to another, that will not fire trigger (bulk insert?). On this specific table many updates, inserts and deletes occurs. On each ...
0
votes
1answer
24 views

assigning default value for type

How to assign default value to following type of oracle statement into PostgreSQL 9.3? CREATE OR REPLACE FUNCTION(.... ... DECLARE v_var Table01.column01%TYPE := 'SLOW'; BEGIN ... ... END;
3
votes
2answers
52 views

How to Increment values without giving parameters

How can i increment the value of meeting and sub-meeting ids whenever a new meeting is created Table: "Employee" CREATE TABLE employee ( sno SERIAL, emp_id INTEGER, emp_name TEXT, ...
1
vote
2answers
90 views

Postgresql - ERROR : tuple concurrently updated

The following query is performed concurrently by two threads logged in with two different users: WITH raw_stat AS ( SELECT host(client_addr) as client_addr, pid , usename ...
5
votes
1answer
198 views

How to get a DataBase Design for POSTGRESQL to create a ER-Diagram?

I am using postgresql 9.2 postgres=# select version(); version ------------------------------------------------------------- PostgreSQL 9.2.4, compiled by Visual C++ ...
-1
votes
2answers
84 views

Postgresql - full table scan with complex filters taking too long [closed]

We have an issue with a large postgresql (9.2) database which is around ~1 Tb on a server cluster with 100Gb Ram and a shared_buffers settings of 33Gb. The issue is that full table scans on our user ...
1
vote
2answers
31 views

Postgresql - How to convert one timestamp to another timestamp with its closer hour?

I have the following timestamp value : 2013-10-09 12:15:55.724+02 I would like to have this instead : 2013-10-09 12:00:00.000+02 I have tried the following : to_char(creation, 'yyyy-mm-dd ...
0
votes
1answer
30 views

Merging postgres data

I have data in two postgresql databases that needs to be merged into 1. Just to be clear, both databases have "good" data in them from a certain date that needs to be combined. This isn't merely ...
1
vote
1answer
68 views

Getting all columns name in an table using trigger function in postgresql

How can I get all column names and their values in trigger function because i need to validate all column values before inserting into the table.I have tried below code.If we know that column name ...
1
vote
1answer
156 views

Postgresql - How to set application name from JDBC url?

I want to set the application name of the connections of my application. So when I list the rows in pg_stat_activity I can have a non empty application_name column. I have setup the following JDBC ...
1
vote
1answer
64 views

Top 10% of sum() Postgres

I'm looking to pull the top 10% of a summed value on a Postgres sever. So i'm summing a value with sum(transaction.value) and i'd like the top 10% of the value
0
votes
1answer
53 views

How to convert interval to timestamp with time zone with postgresql?

I want to perform this simple query : SELECT pid, MIN(interval '5 minutes' - current_timestamp - state_change) FROM pg_stat_activity AND current_timestamp - state_change <= ...
1
vote
2answers
49 views

handling rails + postgres and timezones

I have an application which uses many different timezones... it sets them in a controller and they change depending on the user. All the times are stored in UTC without a timestamp etc. My ...
1
vote
1answer
46 views

How can I lock a record across multiple instance of a program using hibernate and postrgresql?

I would like to find a way to lock a record from instance A making so that instance B can know that the record is locked. Here is how the GUI is working : On instance A the user ask for modification ...
0
votes
1answer
76 views

pg_dump cannot find the database

Easy points for anyone but me. I am trying to use pg_dump. I am running Mac OSx, postgres.app, 9.2, and I want to back up my db. I tried the other solutions for the same problem, but no success. The ...
1
vote
1answer
321 views

How do i create postgres to oracle dblink?

How do I create dblink in postgres 9.2 ? I want to be able to use it using @ link in oracle? I am using postgres 9.2 64bit. DBlink is from postgres 9.2 to Oracle 11g.
0
votes
2answers
97 views

PostgreSQL drop table command is not reducing DB size

I drooped couple of tables from 'postgres' database. However before dropping tables size of the database was 6586kB and after dropping the tables size of the database remains same. I think size should ...
0
votes
1answer
42 views

How to disallow parallel INSERTs in PostgreSQL?

I have an ON INSERT trigger in PostgreSQL 9.2, which does some calculation and injects extra data into every row being inserted. The problem is that I don't want any two INSERT transactions to happen ...
0
votes
2answers
69 views

Postgres DB Size Command

Is there any command to find all the databases size in Postgres? I am able to find the size of a specific database by using following command: select pg_database_size('databaseName');
0
votes
2answers
71 views

How to parameterize an ALTER ROLE statement in Postgresql?

I'm trying to make a PHP interface to my PSQL database, and I would like a few local users registered on PSQL to log into my DB. I would first create each username for each user, with a generic ...
-2
votes
1answer
75 views

How to use/open psql?

I have installed On windows 7 postgreSQL 9.2 version. Now, I need use psql, so where is this terminal? Can you tell me please how to use for example this comand: psql databasename ? Where must ...

1 2 3 4 5 … 7
15 30 50 per page