Tagged Questions

3
votes
2answers
15 views

ORDER BY lower case of output column

I'm able to run this Postgres query without any issue: select (select product_types.name from product_types where product_types.id = products.product_type_id) AS product_type_name from products ...
0
votes
1answer
17 views

How to change a table constraint to deferrable in postgresql?

I have this simple contraint set on my sales table CONSTRAINT sales_stamping_id_key UNIQUE (company_id, stamping_id, invoice_number, billed) and i can't seem to find a way to change this ...
1
vote
2answers
40 views

Variables for identifiers inside IF EXISTS in a plpgsql function

CREATE OR REPLACE FUNCTION drop_now() RETURNS void AS $BODY$ DECLARE row record; BEGIN RAISE INFO 'in'; FOR row IN select relname from pg_stat_user_tables WHERE ...
0
votes
0answers
24 views

Correct index's on simple table

I have a table in PostgreSQL 9.1: _id | integer | not null default nextval('"01f9073e-e6b8-46bf-882f-9a4cd0a69a66__id_seq"'::regclass) _full_text | tsvector | tlRecordID | text | ...
0
votes
1answer
25 views

Dump a table without sequence table in postgres

I have a table named test with id column. And id column has updated by sequence called "test_id_seq". This test table was created by the user "A" and another user "B" have the read permission. While ...
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 ...
0
votes
0answers
14 views

Unable to import tab delimited file in postgres

I'm importing a tab delimited file into Postgres database using copy command. One of the field has "\" at the end. This is somehow getting interpreted as a new column while writing into a table. What ...
1
vote
1answer
28 views

Convert escaped Unicode character back to actual character in PostgreSQL

Is there a way how I can convert the following string back to the human-readable value? I have some external data where all non-ascii characters are escaped. Example strings: 16 ...
1
vote
2answers
74 views

Why does Drop Index require commit?

In my multi-threaded program, one thread drops indexes on a table (this happens first), and other threads insert records in the same table. It so happened that when dropping index is attempted, the ...
0
votes
1answer
24 views

Is there a simple command to pack all database ids to have no gaps

I have my Postgre database which I used hibernate sequence to generate ids. But the sequence was common for all tables. But now my plan is to have a separate sequence for each table. Bow the what I ...
0
votes
1answer
36 views

Combine similar data from multiple schemas

I have a table in a common schema which contain details about all enterprises of the application like id, name, logo, web_site_address, schema_name etc. Then there are schemas for each enterprise ...
0
votes
1answer
18 views

Select sum of an array column in PostgresSQL

If I have the following table: Table "users" Column | Type | Modifiers ---------------+------------------+----------- id | integer | not null default ...
0
votes
1answer
22 views

Obfuscating data in postgres

I'd like to obfuscate data in specific columns in postgres 9.1. For instance, I want to give all the people a 'random' first and last name. I can generate a pool of names to use: select name_first ...
0
votes
0answers
21 views

PostgreSQL service starting on Windows without Windows knowing

My server has an application that has a service dependency on PostgreSQL 9.1 running. For some reason when I restart the server the postgres.exe tasks start in the Task manager (all 8 of them for the ...
0
votes
1answer
31 views

Postgres/Postgis - Querying all values in clipped raster

I'm currently using Postgres 9.1 My goal is to clip a PostGIS raster with a polygon. Then I would like either an postgres array or delimited set of values for each of the raster pixels that are ...
0
votes
0answers
15 views

Get the list of allowed hosts in host-based authentication

I am aware that I have to add the IP addresses of remote hosts in pg_hba.conf file and restart the PostgreSQL server for changes to take effect. But I would like to get a list of hosts currently ...
0
votes
1answer
31 views

UPDATE in PostgreSQL 9.1 seems to misuse placeholder types

Given the following schema (as an example) CREATE TABLE "test" ( "id" int, "title" varchar ); in NodeJS, I try to do an UPDATE with the following client.query( 'WITH new_vals ...
0
votes
2answers
56 views

how to set up postgres database for local rails project

I recently got a new machine and would now like to work on my projects from github. I'm curious as to how to properly set up the postgres database on my local machine. I have postgresql, pgadmin3 and ...
0
votes
1answer
49 views

set up postgres users, roles and databases for rails

This really has me stuck, I'm a bit of a noob trying to use postgres for rails 4 I have postgres installed: $ psql --version psql (PostgreSQL) 9.1.10 contains support for command-line editing $ ...
0
votes
1answer
30 views

Server library could not be loaded and the dynamic link library LIBPQ.dll

Please i have a serious problem here. When i want to debug my application in visual studio i have this message box error : "The ordinal 133 could not be located in the dynamic link library ...
0
votes
3answers
47 views

Counting Number of Users Whose Average is Greater than X in Postgres

I am trying to find out the number of users who have scored an average of 80 or higher. I am using Having in my query but it is not returning the count of number of rows. The Schema looks like: ...
0
votes
1answer
79 views

Accessing PostgreSQL 9.1 Temporary Tables from PHP / AJAX

DATABASE I have a normalized Postgres 9.1 database and in it I have written some functions. One function in particular "fn_SuperQuery"(param,param, ...)" returns SET OF RECORD and should be thought ...
0
votes
1answer
59 views

Alter a Column Data Type Postgres

Hey I have just started working on PostgreSQL, and I am wondering how can we change a column's data type, I tried the following command: alter table tableName alter column columnName type timestamp ...
1
vote
2answers
165 views

postgres hstore exists and doesn't exist at same time

I set up a Rails app on a remote server and created an hstore extension sudo -u postgres psql CREATE EXTENSION hstore; I then deployed an iteration of the app that uses hstore in one of the ...
0
votes
2answers
55 views

Postgresql Procedure not getting called from JDBC

I am working with postgresql procedures and trying to call a procedure from my JDBC program. But getting runtime exception saying procedure doesn't exist eventhough I cross-checked and verified that ...
2
votes
1answer
76 views

Postgres recursive query to update values of a field while traversing parent_id

This is the table user_id | parent_id | lft --------|-----------|----- 1 | | 0 2 | 1 | 0 3 | 1 | 0 4 | 2 | 0 Here is a query to ...
0
votes
2answers
43 views

limit field's value with value from another table before write

Are Postgres TRIGGERs transactional by default like (I've read) in MySQL? I've created a TRIGGER procedure that uses a simple IF to limit a column's value with a value from another TABLE with a ...
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
1
vote
1answer
33 views

unable to locate postgresql in etc centos

I tried installing postgresql in my server which has centos I followed this link I am facing few complexity here. I could not locate postgresql file in /etc directory. psql (8.4.13, server 9.2.4) ...
0
votes
2answers
107 views

Why can't I get the postgresql server to run?

Ok. I have been trying to solve this problem for several days. I installed, uninstalled, and reinstalled Postgresql 3 times. I followed precisely the instructions in this forum: ...
1
vote
2answers
23 views

PostgreSQL seems to be using an old version after update

I uninstalled my PostgreSQL because I'm trying to use version 9.1.9. I tried reinstalling it. It successfully installed from what I can see. However, when typing in psql --version on the command ...
1
vote
1answer
80 views

How much cost check constraints in Postgres 9.x?

I'd like to know if there are some benchmark to compare how much cost insert some check constraints on a table of 60 columns where on 20 i'd like to insert a constraints of NotEmpty and on 6 rows ...
0
votes
2answers
64 views

Get result from query in DO satement

How to run SQL statement within an IF condition in plpgsql? I don't want to create or replace a function. This is what I tried: DO LANGUAGE plpgsql $$ BEGIN IF 'Khosla' = 'Khosla' THEN SELECT * FROM ...
0
votes
1answer
70 views

How to generate script file of a database in postgresql with insert queries instead of copy query?

In postgresql, I had taken backup. That file didn't have any insert queries instead it used Some copy statments like COPY user_customers (userid, customerlist) FROM stdin; 476830 customer1:customer2 ...
0
votes
1answer
340 views

How to connect hibernate with PostgreSQL in eclipse?

I try to connect hibernate with PostgreSQL, but I don't know why it is not working. I have searched many posts about the setting, but they are not working for me. Thank you very much! Below is the ...
0
votes
1answer
40 views

Xpath in Postgresql

I have the following XML data stored in PostgreSQL database (in column named result, type is XML): <are:Ares_odpovedi ...
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
48 views

Postgresql Log Connection

Is it normal if I have this : 2013-09-24 12:08:24 WIT LOG: connection authorized: user=xxxxxx database=xxxx 2013-09-24 12:08:24 WIT LOG: connection received: host=xx.x.xx.91 port=59878 2013-09-24 ...
0
votes
1answer
31 views

pg_relation_size command is not providing size

I need to find size of a table in posgreSQL. I am using following command but it is giving me 0 as an output. Could you please inform me how can I get the size of a table? INPUT: select ...
0
votes
0answers
51 views

SQL database query display extra dates

I am making a database with postgresql 9.1 Given tables: CREATE TABLE rooms( room_number int, property_id int, type character varying, PRIMARY KEY (room_number, property_id) ); Insert ...
1
vote
2answers
421 views

Updating multiple rows with different primary key in one query in PostgreSQL?

I have to update many columns in many rows in PostgreSQL 9.1. I'm currently doing it with many different UPDATE queries, each one that works on a different row (based on the primary key): UPDATE ...
1
vote
1answer
49 views

How to use LO datatype with postgresql?

What I'm currently trying to do: I am using a software with Postgresql as the backend DB. The software supplies an API that uses a web service to communicate with the software/DB. I am trying to ...
0
votes
1answer
313 views

Postgres SELECT … FOR UPDATE in functions

I have two questions about using SELECT … FOR UPDATE row-level locking in a Postgres function: Does it matter which columns I select? Do they have any relation to what data I need to lock and then ...
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, ...
0
votes
1answer
36 views

SQL to change values (var char(20)) of a column [closed]

Table_neme:employee details -------------------------------------------- c_bp_id | value | name |address -------------------------------------------- 10001 |001 |john | 10002 |mathew ...
0
votes
1answer
40 views

using Replace funcion in postgresql

I am trying not to show the period in the values using the Replace function but i am getting Buffer Overflow error when i use my select statement like this: SELECT Volume, ...
0
votes
2answers
45 views

Simple SQL Query framing

I'm relatively new to SQL and postgres. I've relation R1 and R2 with few columns , one each being some measurement,say R1.m and R2.m . Now i need to find for every row in R1 , the minimum of some ...
0
votes
1answer
279 views

Trouble connecting to Postgresql Database in Vagrant Guest Box with Induction from Host Machine

I'm trying to connect to my PostgreSQL database which is inside of a guest machine (using Vagrant and VirtualBox). I'm trying to connect to it with Induction http://inductionapp.com/ But I am ...
0
votes
1answer
103 views

Accidently deleted data from PostgreSQL 9.1 table. Is there any way to restore the data?

We were doing an in house tool with data base as PostgreSQL 9.1. Accidentally running a delete script we lost the data in three tables. We didn't have a backup. :( A try on the manuals, it didn't ...
0
votes
2answers
80 views

update row counts in a table

i want to update the row counts table of my own which is created in the datbase with the number of rows of each table in the db (postgres) so i am trying out something like this but it is not ...

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