psql

Are there any escaping syntax for psql variable inside PostgreSQL functions?

I write PSQL script and using variables (for psql --variable key=value commandline syntax). This works perfectly for top level scope like select * from :key, but I create functions with the script and need variable value inside them. So, the syntax like create function foo() returns void as $$ declare begin grant select on my_tabl...

Show query result column types (PostgreSQL)

Is there a way to easily get the column types of a query result? I read the psql documentation, but I don't think it supports that. Ideally, I'd be able to get something like: columna : text | columnb : integer ----------------+------------------- oh hai | 42 Is there a way I can get this information without...

Write raw SQL for PostGIS

I'm trying to input some data into a PostgreSQL 8.4 database with a PostGIS template. I'm unable to UPDATE polygons: > UPDATE my_table SET coords = POINT(1, 1) UPDATE 0 1 > UPDATE my_table SET box = POLYGON(((1, 1), (2, 3), (3, 3), (1, 1))) ERROR: function polygon(record) does not exist > UPDATE my_table SET box = POLYGON((1, 1), (2,...

Upgrade PostgreSQL 8.1 to 8.3 on Debian Lenny, but zend php still recognize pgsql as version 8.1

The story goes like this: I have Zend server CE PHP5.3 with all the modules available enabled running on Debian Etch. I decided to upgrade Debian Etch to Lenny, then also upgraded PostgreSQL 8.1 to 8.3 Restored the database mydatabase --> $ psql mydatabase < schema_and_data.sql Checked the web site that connects to a postgreSQL databas...

PSQL row update using NEW statment

Hello, since it is possible to do: INSERT INTO some_table VALUES (NEW.*) in pl/pgsql can we do something similar but for UPDATE clause using NEW ? I mean i want to update some row using values from NEW object. Thanks in advance. ...

How can I know what are all the tables I have updated today in psql?

Hi Friends, How can I know what are all the tables I have updated today in psql? Can anyone tell me. Thanks in Advance. ...

disable NOTICES in psql output

How do I stop psql (postgres?) from outputting 'useless' notices? e.g. psql:schema/auth.sql:20: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users" yes psql I know, now be a good tool and stop telling me that you're doing things I want you to do. IMO a program should be silent unless it has an...

Select max date row from results

Using Pervasive SQL, I have a result set: Tp_No Name State Eff_Date Actual Billed 1006 ABC TN 2006-07-01 .1 .5 1006 ABC TN 2008-02-15 .27 .6 1006 ABC TN 2010-09-01 .37 .7 1022 Widget TN 2006-07-01 ....

May we interact with a psql script ?

Can we do something like \echo 'Type username to show its properties'; SELECT * FROM mY_users WHERE username = ?; \echo 'End of script'; in a psql script file ? The system would wait until we enter something then echo the 'End of script' string. ...

django-fts: How to create indexes for psql

Hi, I am investigating how the http://code.google.com/p/django-fts/ application works. I am trying to setup psql FTS to work with the application, but can't understand how to create index correctly. Don't understand how to create the GIN index as it was specified in doc. My model is following: class Product(fts.SearchableModel): ...

PostgreSQL: Which connectivity uses psql tool of PostgreSQL

Does anybody know how psql accesses PostgreSQL database? What it uses odbc, jdbc or some native database library? ...

Putting output from Postgres \i input to a file?

Hello All! Quick question (I hope!): if I use \i to feed an input file into psql, can I get the output from the queries to be saved into a file? If so, how? Thanks!! ...

How to switch databases in psql?

In MySQL I used use database_name; What's the PostgreSQL equivalent? ...

postgresql union

I have this data in postgresql id | rate | hrv | activity | orientation | timestamp | user_id | status ----------+-----------+-----+----------+-------------+------------------------+---------+-------------- 66764728 | 72 | 1 | 0 | 90 | 2010-06-10 18:54:54+00 | 397 | t 66764729 | ...

Shebang for psql

Hello I'm trying to write Postgresql script(s) but having a problem with shebang line #! /usr/bin/psql [ psql_args_here ] -f select now(); This gives me error as if I just entered psql without any arguments in command line. How do I do it right? Thanks. ...

How to use psql triggers(procedure, function) in rails application?

I am trying to use psql triggers in my rails app. So i tried using this migration where execution of psql triggers are supposedly easly -- class AddTriggersToProducts < ActiveRecord::Migration def self.up table :products execute %q{ create trigger trig1 before insert on products for each row begin price...

Interbase PSQL return values

Hi, I wrote a very simple script. I am new to PSQL and I wanted to return some values based on a very simple loop. CREATE PROCEDURE DRAW_DOWN RETURNS( I_VAL INTEGER) AS DECLARE VARIABLE STARTING_BALANCE INTEGER; DECLARE VARIABLE TRADING_SERIES INTEGER; DECLARE VARIABLE I INTEGER; BEGIN SUSPEND; I_VAL = 1; WHILE (i < 5) DO BEGIN /*RA...

how to make array_agg() work like group_concat() from mySQL

So I have this table: create table test ( id integer, rank integer, image varchar(30) ); Then some values: id | rank | image ---+------+------- 1 | 2 | bbb 1 | 3 | ccc 1 | 1 | aaa 2 | 3 | c 2 | 1 | a 2 | 2 | b I want to group them by id and concatenate the image name in the or...

Modify a Table field by adding to it.

I have Table1, where I want to modify previous_sum where previous_sum is the sum of the numbers field in Table 2 up to that specific date. Example: Table1 Date___|___previous_sum 01/01__|___20 01/02__|___50 01/03__|___100 Table2 Date___|___numbers 01/01__|___20 01/02__|___30 01/03__|___50 So, previous_sum is 0 in the beginning but ...