Tagged Questions
327
votes
7answers
77k views
How to exit from PostgreSQL command line utility: psql
How to exit command line utility psql for PostgreSQL?
56
votes
2answers
37k views
“use database_name” command in PostgreSQL
I am beginner to PostgreSQL.
I want to connect to another database from the query editor of Postgres - like the USE command of MySQL or MS SQL Server.
I found \c databasename by searching the ...
28
votes
4answers
18k 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?
26
votes
1answer
5k 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 ...
22
votes
6answers
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 -
...
21
votes
2answers
18k 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 ...
21
votes
1answer
5k 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 ...
18
votes
2answers
21k views
List tables in a Postgresql Schema
When I do a \dt in psql I only get a listing of tables in the current schema (public by default).
How can I get a list of all tables in all schemas or a particular schema?
14
votes
3answers
7k views
Postgres ENUM data type or CHECK CONSTRAINT?
I have been migrating a MySQL db to Pg (9.1), and have been emulating MySQL ENUM data types by creating a new data type in Pg, and then using that as the column definition. My question -- could I, and ...
13
votes
2answers
22k 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 ...
13
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 ...
13
votes
1answer
9k 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.
12
votes
1answer
8k 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 ...
12
votes
6answers
10k 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 ...
11
votes
2answers
11k views
postgres 9.1 now() default value are all showing same timestamp
I have created my tables with a column (type: timestamp with timezone) and set its default value to now() (current_timestamp()).
I run a series of inserts in separate statements in a single ...
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 ...
10
votes
1answer
18k views
Resetting password of PostgreSQL on ubuntu [closed]
i am using Ubuntu.some days before i installed Postgresql database for ruby on rails.i created a superuser for database server and i forgot the password of postgresql superuser.can any one please help ...
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 ...
9
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 ...
9
votes
1answer
6k views
SELECT .. INTO to create a table in PL/pgSQL
I want to use SELECT INTO to make a temporary table in one of my functions. SELECT INTO works in SQL but not PL/pgSQL.
This statement creates a table called mytable (If orig_table exists as a ...
9
votes
4answers
4k views
PostgreSQL field type for unix timestamp?
PostgreSQL field type for unix timestamp :
to store it as unix time stamp
to retrieve it as a unix timestamp as well.
Have been going through Date/Time Types postgreSQL V 9.1.
Is integer the ...
9
votes
1answer
3k views
PostgreSQL 9.1: How to concatenate rows in array without duplicates, JOIN another table
I am using PostgreSQL 9.1 and need help with concatenating multiple rows in one. I need to do that in 2 tables. When I use two times array_agg() functions I get duplicated values in result.
Tables:
...
8
votes
2answers
10k 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 ...
8
votes
3answers
14k views
Store the query result in variable using postgresql Stored procedure
How to store the query result in variable using postgresql Stored procedure
i have a stored procedure
CREATE OR REPLACE FUNCTION test(x numeric)
RETURNS character varying AS
$BODY$
DECLARE
name ...
8
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 ...
8
votes
1answer
686 views
PostgreSQL multiple authentication methods
How can I set up multiple authentication methods for the same host/database/user rule? I want to be able to log in to my postgres user using both sudo -u postgres psql -U postgres (without having to ...
8
votes
1answer
2k views
Failed to find conversion function from unknown to text
In one of my select statements I've got the following error:
ERROR: failed to find conversion function from unknown to text
********** Error **********
ERROR: failed to find conversion function from ...
7
votes
2answers
8k views
ALTER TABLE, set null in not null column, PostgreSQL 9.1
I have a table with not null column, How to set a null value in this column as default?
I mean, I want to do something like this:
postgres=# ALTER TABLE person ALTER COLUMN phone SET NULL;
but it ...
7
votes
2answers
6k 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
4answers
163 views
IN subquery's WHERE condition affects main query - Is this a feature or a bug?
Assume the two tables:
Table A: A1, A2, A_Other
Table B: B1, B2, B_Other
In the following examples, is something is a condition checked against a fixed value, e.g. = 'ABC' or < 45.
I wrote a ...
7
votes
2answers
1k views
Aggregate values over a range of hours, every hour
I have a PostgreSQL 9.1 database with a table containing a timestamp and a measuring value
'2012-10-25 01:00' 2
'2012-10-25 02:00' 5
'2012-10-25 03:00' 12
'2012-10-25 04:00' 7
'2012-10-25 ...
6
votes
4answers
4k views
cannot find adminpack.sql in postgresql 9.1
I'm trying to get it installed and use in ubuntu. Was following this page to install the additional instrumentation packages.
Install Server Instrumentation for Postgresql 8.4
To install Server ...
6
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
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 ...
6
votes
2answers
3k views
Is it possible, and what is the syntax for a nested hash in Postgresql's HStore type?
I'm not even sure that Postgres' HStore data type can contain nested hashes, and if they can, how to insert them?
Here's what I've tried so far:
-- Database: test1
-- DROP DATABASE test1;
/*
CREATE ...
6
votes
2answers
14k 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. ...
6
votes
1answer
572 views
Can't run rake db:migrate a tsvector GIN index for Postgresql in Rails using Railscast sample
I'm following along on Ryan Bates' excellent tutorial on using the built-in PostgresQL full-text search in Rails. I'm currently using pg_search gem un-indexed no problem but I need to improve the ...
6
votes
1answer
977 views
Returning a nested composite type from a PL/pgSQL function
I'm trying to return nested data of this format from PostgreSQL into PHP associative arrays.
[
'person_id': 1,
'name': 'My Name',
'roles': [
[ 'role_id': 1, 'role_name': 'Name' ],
[ ...
6
votes
1answer
963 views
PostgreSQL - CTE upsert returning modified rows
I've written an 'upsert' query using CTEs that looks something like this:
WITH
new_data (id, value) AS (
VALUES (1, 2), (3, 4), ...
),
updated AS (
UPDATE table t set
value = ...
6
votes
2answers
161 views
Postgresql ColdFusion CFQuery Block
I am new to ColdFusion, I have written a block of Postgres code in CFQuery :
<cffunction name="insertToReport" access="private" returntype="struct" output="false" >
<cfset var xyz = ...
5
votes
6answers
3k views
how to exclude null values in array_agg like in string_agg using postgres?
If I use array_agg to collect names, I get my names separated by commas, but in case there is a null value, that null is also taken as a name in the aggregate. For example :
SELECT g.id,
...
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, ...
5
votes
2answers
8k views
How do I install pgcrypto in postgresql 9.1 on Windows?
The web page for Postgresql says that pgcrypto is included in the download for Postgresql 9.1. There is no pgcrypto.sql file, however. If I look in the share\extension directory there are 3 files:
...
5
votes
2answers
8k views
to_char(number) function in postgres
i want to display/convert a number to character (of it's same length) using to_char() function .
In oracle i can write like
SELECT to_char(1234) FROM DUAL
But in postgres
SELECT to_char(1234)
...
5
votes
4answers
3k 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
1answer
9k views
How do I convert an integer to string as part of a PostgreSQL query?
How do I convert an integer to string as part of a PostgreSQL query?
So, for example, I need:
SELECT * FROM table WHERE <some integer> = 'string of numbers'
where <some integer> can be ...
5
votes
2answers
5k views
Strange PostgreSQL “value too long for type character varying(500)”
I have a Postgres schema which looks like:
The problem is that whenever I save text longer than 500 characters in the description column I get the error:
value too long for type character ...
5
votes
1answer
6k 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 ...
5
votes
1answer
2k views
Slony and PGPool for fail-over
We're considering Slony and PGPool as alternatives to handle failover in our application -and it seems like since we're gonna need at least two DB servers, we could take advantage of load balancing ...
5
votes
3answers
5k views
PostgreSQL date difference
I have a PostgreSQL function which calculates date difference:
CREATE OR REPLACE FUNCTION testDateDiff () RETURNS int AS $BODY$
DECLARE startDate TIMESTAMP;
DECLARE endDate TIMESTAMP;
DECLARE ...
