PostgreSQL Database Forum

Hello all, I have setup 2 groups on a database: admin users I have granted all on testing to admin and tried to grant select on testing to users; If I do a \d as an admin I see the schema if i do it as user I see nothing.. If i select * from testing i see everything in the table as a admin but if i do the same thing as a member of users...

Hi I am trying to work out if this is possible in a select query I have a group by query which could result in several rows, what I want to do is do a text equivalent of a sum() eg: SELECT sum(inv_id),date,cust from invoice group by date,cust

Hi, I have this plain text file with about 5000 lines. Each line may have 4 or 5 fields, all delimited with a tab. I've made a table named t_zip_codes with 5 fields. When i run (in postgres command line) the command COPY t_zip_code FROM zip_codes.txt;

I'm trying to create and populate some study tables using a couple of scripts that came with my 'SQL in 10 minutes' book. This is how I tried to run the script to create and populate tables in a database named test: test-# \i create.txt psql:create.txt:1: ERROR: syntax error at or near "" at character 1 Here is the top of the script:...

Dear PostgreSQL experts, I have encountered a problem with temporary tables inside plpgsql functions. I suspect that this is a known issue; if someone could confirm and suggest a workaround I'd be grateful. My function creates a couple of temporary tables, uses them, and drops them before returning:

Hello, is there a possibility starting or stopping postgres database and creating database instance by JAVA? Is there some API available for this? If yes, can you please write some example, how to do it? Thank you in advance, with best regards,

All of my non-superusers are restricted from creating databases. Whenever I upgrade Postgres, I have to hand-edit my dump and change: CREATE USER usr1 WITH SYSID 101 PASSWORD '...' NOCREATEDB NOCREATEUSER; into: CREATE USER usr1 WITH SYSID 101 PASSWORD '...' CREATEDB NOCREATEUSER; and then after the restore, I do a:

Hi, Is there any generic to select columns by position ie select the first, third, and fifth columns from .... I can't see anything in SQL...not to sure about PL/PgSQL... Thanks

Hello, How come within a create schema block I cannot create a sequence? I have entered in: CREATE SCHEMA joe CREATE SEQUENCE joe_seq start 1 CREATE TABLE joe_table (int id, varchar name) ; and I get a syntax error for SEQUENCE. though if it is just tables I do not

Hi there, I want to put a number of records (variable number depending on a attribute of a table) into a certain table with a trigger statement. I have created the follwing trigger: CREATE FUNCTION vullalles() RETURNS trigger AS ' BEGIN FOR i in 0..7 LOOP

Hi, I'm noticing NOTICE messages can't be turned off with my cygwin-based platform. I've had other's confirm it works on linux. Here is my session: $ psql -U postgresql itn Welcome to psql 7.4.5, the PostgreSQL interactive terminal.

I am setting up a single PostgreSQL installation to be used by several users. Can I restrict a database user from connecting and creating objects in other databases but his/her own? So far I can only restrict a user from creating more databases or users. (Yes, I have set up a proper pg_hba.conf, but once a user is connected, he can switch...

Is there any way to convince explain to go do its thing when given a query with placeholders in it? I'm trying to do some performance checking of some of the queries built into a system I'm building. The SQL's all done with placeholders, for safety and ease of twiddling, but EXPLAIN... EXPLAIN doesn't like them. Trying throws an "ERROR: ...

I guess it would be great if Pgsql had a way to find a database definition via a system stored procedure like other database platforms have. There are two ways I've found so far: SELECT attname as "name", typname as "type", atttypmod - 4 as "size", relhaspkey as "is_primary_key", * FROM

I'd like to save the list of users, groups, and databases regularly to a separate file (just in case). The output of pg_dumpall suffices for my need: ================================= DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0'); CREATE USER ... WITH SYSID ... PASSWORD '...' NOCREATEDB...

Hi, I have a set of libraries and C/C++ programs with embedded access to a PG database. I'm using ecpg. I'm working on a Linux RedHat9 platform. Until now I was in PG 7.4.2 : $ ecpg --version ecpg (PostgreSQL 7.4.2) 3.1.1 $ g++ --version g++ (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)

Hi All, I'm developing some scripts to create my schema to be run by other developers and I'd like to have them not spit out those NOTICE messages when you create a table regarding the implicit indexes that are made since it just makes it harder to see when there is a real error. I've tried running psql with the quiet argument, I've set...

Hi all! Apologies for sending this mail but some time back there was a thread on supporting resilient transactions - i.e. transactions which do not roll back if one query in 100 fails. This was thought to be useful in case someone is typing on the psql prompt and makes a spelling mistake. I would like to know what was the result of that...

Dear my friends... I am using SuSE 9.1 and postgres 7.2.x . Each time if I want to connect to postgres with postgres I do like this: " patrixlinux@patrix:~> su Password: patrix:/localhome/patrixlinux # su postgres

Hi all! I am running postgres 8 beta1 and for some reason it is really slow in execution. I am not able to figure out why. On a fresh install of postgres, the following code executed through jdbc ******************** for (int i=0; i < 10000; i++) { s.execute("insert into tab values (" + i + ",'" + i + "')") ;

Hello Hopefully someone can shed some light on the following issue. After chatting at irc.freenode.net/#postgresql, without success, this is my last effort before giving up and using a temp table. Essentially, I would like to pass a RECORD variable from one function to another using plpgsql: func2(record)

It might be a good idea to place a reference to table 8-1 in the various subsections of the Datatypes section in the online postgresql documentation. Otherwise, when hyperjumping from the table of contents to a specific section, table 8-1 may not be in sight (as it is not for integer datatypes) and the user is left wondering why these...

Hi, I have a set of libraries and C/C++ programs with embedded access to a PG database. I'm using ecpg. I'm working on a Linux RedHat9 platform. Until now I was in PG 7.4.2 : $ ecpg --version ecpg (PostgreSQL 7.4.2) 3.1.1 $ g++ --version g++ (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)

I had RH9 Linux. It came with pgSQL, but I couldn't seem to figure out how to get PL/pgSQL going. I read the HTML documentation that came with it and was confused until I tried a few different variations and guessed about some things. I've finally got it working and here's what the SQL looks like to enable and test it: -- ON YOUR SYSTEM,...

I have a table that stores TEXT information. I need query this table to find *exact* matches to the TEXT... no regular expressions, no LIKE queries, etc. The TEXT could be from 1 to 10000+ characters in length, quite variable. If it matters, the TEXT may contain UNICODE characters... Example: CREATE TABLE a (id SERIAL, thetext TEXT);...