Tagged Questions

2
votes
3answers
47 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
18 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
84 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
29 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
23 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
17 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
29 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
36 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
35 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
0answers
24 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
33 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
41 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
0answers
12 views

JDBCExceptionReporter referred position

how to find what position is JDBCExceptionReporter is reffering to ,in the following error reported ?? JDBCExceptionReporter: ERROR: column "objecttype" is of type objecttype but expression is of ...
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
0answers
46 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
35 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
57 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
116 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
59 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
35 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
82 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
182 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
77 views

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

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
28 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
51 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
138 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
54 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
49 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
45 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
40 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
69 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
228 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
81 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
39 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
64 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
68 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
67 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
vote
1answer
203 views

how to change the template database collection coding on postgresql

I want build new postgreSQL database by: CREATE DATABASE newdb WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'zh_CN.UTF-8' CONNECTION LIMIT = -1; and the ...
0
votes
1answer
26 views

Postgres archive_cleanup_command is not recognized

I put this in my postgresql.conf (line 193): archive_cleanup_command = 'pg_archivecleanup C:\\pg\\archivedir %r' Now, when I am trying to launch my server, I get this error : LOG: unrecognized ...
0
votes
1answer
21 views

Parsing the value and copying it to another column in postgresql

Column_1(varchar) has values with the format like 1024 MB , 2048 MB etc. There also exits Column_2(int). Now i would like to parse the value in column_1 and update the column_2 with values like 1024, ...
1
vote
3answers
182 views

PostgreSQL: How to create index on very large table without timeouts?

I am trying to add a simple index with the following SQL in Postgres, but the command keeps timing out: CREATE INDEX playlist_tracklinks_playlist_enid ON playlist_tracklinks (playlist_enid); The ...
3
votes
1answer
73 views

UNION ALL on Json data type

I need to do a UNION ALL in Postgres 9.2, using JSON data type, but when doing it, Postgres replies with this error: ERROR: could not identify an equality operator for type json SQL state: 42883 ...
0
votes
2answers
161 views

How to RAISE a NOTICE in PostgreSQL?

I'm trying to run this in PostgreSQL 9.2: RAISE NOTICE 'hello, world!'; And the server says: Error : ERROR: syntax error at or near "RAISE" LINE 1: RAISE NOTICE 'hello, world!' ^ Why?
3
votes
2answers
72 views

Why this COUNT() takes that long in PostgreSQL 9.2?

My table is (there are some other columns): id INTEGER amount INTEGER There is an index on amount. The query is: explain analyze select count(amount) from receipt Output is: Aggregate ...

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