Tagged Questions
1
vote
3answers
290 views
Compute percents from SUM() in the same SELECT sql query
In the table my_obj there are two integer fields:
(value_a integer, value_b integer);
I try to compute how many time value_a = value_b, and I want to express this ratio in percents.
This is the ...
15
votes
2answers
26k views
Postgres password authentication fails
I tried to login with the postgres user from my windows machine to my server with Pgadmin.
But it keeps giving me this error:
psql: FATAL: password authentication failed for user "postgres"
So ...
2
votes
1answer
1k views
Error creating a spatial database. ERROR : could not load library “/usr/pgsql-9.1/lib/rtpostgis-2.0.so”
I am running PostgreSQL 9.1 with Postgis 2.0 on Fedora 15.
When trying to install the raster support,
psql -d database -f rtpostgis.sql -v ON_ERROR_STOP=1
I get the following error
...
10
votes
2answers
12k views
PL/pgSQL checking if a row exists - SELECT INTO boolean
I'm writing a function in PL/pgSQL, and I'm looking for the simplest way to check if a row exists.
Right now I'm SELECTing an integer into a boolean, which doesn't really work. I'm not experienced ...
6
votes
2answers
3k views
Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
In connection with this answer I stumbled upon a phenomenon I cannot explain.
Version:
PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
Consider ...
16
votes
2answers
4k views
Error when creating unaccent extension on PostgreSQL
I am trying to configure PostgreSQL to use fulltext search in my rails app as mentioned in this Railscast.
I am using a fresh Ubuntu 12.04 server running PostgreSQL 9.1.5 installed using apt-get with ...
3
votes
1answer
659 views
How to tell if record has changed in Postgres
I have a bit of an "upsert" type of question... but, I want to throw it out there because it's a little bit different than any that I've read on stackoverflow.
Basic problem.
I'm working on ...
2
votes
1answer
2k views
PostgreSQL - dynamic value as table name [duplicate]
Possible Duplicate:
Postgres Dynamic Query Function
I wish to use the returned string from the query about as a table name for other query.
SELECT 'backup_' || ...
1
vote
1answer
132 views
Generate series of dates - using date type as input
Documentation for generate_series says that argument can be int or bigint for generate_series(start, stop) and generate_series(start, stop, step) cases and timestamp or timestamp with time zone for ...
1
vote
3answers
4k views
Declaring the tuple structure of a record in PL/pgSQL
I can't find anything in the PostgreSQL documentation that shows how to declare a record, or row, while declaring the tuple structure at the same time. If you don't define you tuple structure you get ...
22
votes
1answer
6k views
How to create a new database with the hstore extension already installed?
Recently I went into trouble trying to use hstore with Django. I installed hstore this way:
$ sudo -u postgres psql
postgres=# CREATE EXTENSION hstore;
WARNING: => is deprecated as an operator ...
32
votes
4answers
21k views
How do I import modules or install extensions in PostgreSQL 9.1, 9.2, 9.3, 9.4?
Firstly, if you're not using 9.1+, please refer to this question.
How do I install an extension to PostgreSQL 9.1?
6
votes
2answers
15k views
Rails: rake db:create:all fails to connect to PostgreSQL database
I am trying to create a Rails app that uses PostgreSQL. Here is a description of what I did.
PostgreSQL setup:
I installed PostgreSQL 9.1.3 via the ppa:pitti/postgresql maintained by Martin Pitt. ...
14
votes
6answers
11k views
How to add column if not exists on PostgreSQL?
Question is simple. How to add column x to table y, but only when x column doesn't exist ? I found only solution here how to check if column exists.
SELECT column_name
FROM ...
10
votes
3answers
5k views
Determining the OID of a table in Postgres 9.1?
Does anyone know how to find the OID of a table in Postgres 9.1? I am writing an update script that needs to test for the existence of a column in a table before it tries to create the column. This ...
4
votes
2answers
3k views
Connection refused (PGError) (postgresql and rails)
I keep getting this error when i try to run my localhost using "$rails s":
(Mac OSX 10.8.3)
(ruby 2.0.0p195 (2013-05-14 revision 40734) [x86_64-darwin12.3.0])
(Rails 3.2.11)
(psql (PostgreSQL) ...
11
votes
6answers
6k views
Mountain Lion Postgres could not connect
After my update to mountain lion my postgres doest work. It is still running but my applications cant connect to it anymore.
$ ps aux | grep postgres
postgres 204 0.0 0.0 2446960 836 ...
4
votes
3answers
21k views
PostgreSQL next value of the sequences?
I am using PostgreSQL for my Codeigniter website. I am using grocery crud for add, edit and delete operations. While doing an edit or add, I want to rename an uploaded file dynamically based on the id ...
24
votes
7answers
6k views
Library not loaded: /usr/local/lib/libpq.5.4.dylib while running rake db:create
I am working on a rails app.
Installed Postgresql using postgresql-9.1.2-1-osx.dmg
Installed pg gem.
Then when I executed rake db:create
getting the following error -
...
1
vote
1answer
268 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 ...
25
votes
3answers
22k views
Change type of varchar field to integer: “cannot be cast automatically to type integer”
I have a small table and a certain field contains the type "character varying". I'm trying to change it to "Integer" but it gives an error that casting is not possible.
Is there a way around this or ...
14
votes
1answer
10k views
Query a parameter (postgresql.conf setting) like “max_connections”
Does anyone know if it's even possible (and how, if yes) to query a database server setting in PostgreSQL (9.1)?
I need to view the max_connections (maximum number of open db connections) setting.
10
votes
1answer
3k views
Change schema of multiple PostgreSQL tables in one operation?
I have a PostgreSQL 9.1 database with 100 or so tables that were loaded into the 'public' schema. I would like to move those tables (but not all of the functions in 'public') to a 'data' schema.
I ...
5
votes
2answers
1k views
Postgresql batch insert or ignore
I have the responsibility of switching our code from sqlite to postgres. One of the queries I am having trouble with is copied below.
INSERT INTO group_phones(group_id, phone_name)
SELECT g.id, ...
2
votes
1answer
2k views
Postgres function returning table not returning data in columns
I have a Postgres function which is returning a table:
CREATE OR REPLACE FUNCTION testFunction() RETURNS TABLE(a int, b int) AS
$BODY$
DECLARE a int DEFAULT 0;
DECLARE b int DEFAULT 0;
BEGIN
CREATE ...
2
votes
1answer
2k views
Set custom timezone in Django/PostgreSQL (Indian Standard Time)
In Django documentation for setting timezone, the list of available choices for timezone are actually postgres timezone parameter strings. So it seems Django uses Postgres for retrieving time.
If so, ...
4
votes
3answers
2k views
PostgreSQL: How to perform a select query in a do block?
Question:
I want to port the below SQL code from (MS) SQL-Server to PostgreSQL.
DECLARE @iStartYear integer
DECLARE @iStartMonth integer
DECLARE @iEndYear integer
DECLARE @iEndMonth integer
SET ...
2
votes
1answer
1k views
Copy data between two tables in PostgreSQL using dblink.sql
I am using PostgreSQL 9.1. I need to transfer required columns from one table of one database into another table of another database, but not schema.
I found that dblink.sql file has to be there in ...
1
vote
3answers
441 views
split single row into multiple rows in SQL
In my table there are two fields start and stop which stores the start time and stop time respectively.
for example the Start time = 2014-01-01 23:43:00 and stop = 2014-01-03 03:33:00. This ...
1
vote
1answer
73 views
Attribute number 10 exceeds number of columns 0
This query returns all the rows(around 850+) from the table successfully:
select * from my_db_log
where date_trunc('day',creation_date) >= to_date('2014-03-05'::text,'yyyy-mm-dd');
But when I ...
1
vote
1answer
408 views
“PG::UndefinedTable: ERROR: relation does not exist” with a correct Rails naming and convention
I've read a lot of potsts like this, but all the solutions I've seen are in the nomenclature of the models, naming and Rails convention.
Now I have this problem when I run for first time in ...
387
votes
7answers
90k views
How to exit from PostgreSQL command line utility: psql
How to exit command line utility psql for PostgreSQL?
28
votes
1answer
6k views
How does one drop a template database from PostgreSQL?
postgres=# DROP DATABASE template_postgis;
ERROR: cannot drop a template database
http://www.postgresql.org/docs/9.1/static/manage-ag-templatedbs.html makes it seem like if I set ...
6
votes
3answers
10k 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 ...
14
votes
1answer
9k views
What's a PostgreSQL “Cluster” and how do I create one? [closed]
I am very new to databases, I haven't worked lot on it. Now I want to understand the term database clusters. I googled a lot and found many useful links but I am not able to understand them - maybe ...
5
votes
2answers
11k views
I forgot the password I entered during postgres installation
I either forgot or mistyped (during the installation) the password to the default user of Postgres. I can't seem to be able to run it and I get the following error:
psql: FATAL: password ...
3
votes
1answer
4k views
Dynamically generate columns for crosstab in PostgreSQL
I am trying to create crosstab queries in PostgreSQL such that it automatically generates the crosstab columns instead of hardcoding it. I have written a function that dynamically generates the column ...
7
votes
2answers
7k views
Postgresql insert trigger to set value
Assume in Postgresql, I have a table T and one of its column is C1.
I want to trigger a function when a new record is adding to the table T. The function should check the value of column C1 in the ...
7
votes
3answers
4k views
PostgreSQL: Which Datatype should be used for Currency?
Seems like Money type is discouraged as described here
My application needs to store currency, which datatype shall I be using? Numeric, Money or FLOAT?
6
votes
2answers
7k views
Postgresql date() with timezone
I'm having an issue selecting dates properly from Postgres - they are being stored in UTC, but
not converting with the Date() function properly.
Converting the timestamp to a date gives me the wrong ...
3
votes
2answers
2k views
Cannot connect to Postgres running on VM from host machine using MD5 method
I have a VM set up with Vagrant that has Postgres running on it (on port 5432), forwarded to port 8280 on the host machine.
I have set the password for the default user and I can connect locally just ...
2
votes
1answer
2k views
cannot create extension without superuser role
I'm trying to run unit tests in Django, and it creates a new database. The database has postgis extensions and when I regularly create the database, I use "CREATE ExTENSION postgis".
However, when I ...
2
votes
2answers
4k views
How to take backup of functions only in Postgres
I want to take backup of all functions in my postgres database.How to take backup of functions only in Postgres?
1
vote
1answer
230 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
0answers
107 views
How to Query on HSTORE[] in postgres - 9.2 and above?
I am using postgress-9.3 with Rails 4.
I have a data type array of hstore(HSTORE[]).
Please see the normal query on product table.
SELECT id, specifications FROM "products" limit 10;
id | ...
0
votes
1answer
700 views
Why does PostgreSQL treat my query differently in a function?
I have a very simple query that is not much more complicated than:
select *
from table_name
where id = 1234
...it takes less than 50 milliseconds to run.
Took that query and put it into a ...
10
votes
2answers
2k views
Java queries against PGPool II cause “unnamed prepared statement does not exist” errors
I have a Java app that uses a Postgres database and I'm trying to introduce PGPool in order to scale up my database. I'm running into a problem where Postgres throws the following error: unnamed ...
6
votes
1answer
1k views
Moving average in postgresql
I have the following table in my Postgresql 9.1 database:
select * from ro;
date | shop_id | amount
-----------+----------+--------
2013-02-07 | 1001 | 3
2013-01-31 | 1001 | ...
5
votes
4answers
4k views
How to restore a single table from a .sql postgresql backup?
A table's rows were mistakenly deleted from the database. We have a db backup which results in a sql file that can restored like so:
psql -h localhost -d proddump -f ...
5
votes
2answers
6k views
No password prompt for postgresql superuser
After I installed PostgreSQL 9.1 on Ubuntu 12.04 I set the password for the "postgres" superuser account. I want all users to have to enter their password when loging in. This is why I configured ...
