Re: [SQL] Unique index VS unique constraint
On 10/04/2013 09:48 AM, JORGE MALDONADO wrote: I have search for information about the difference between unique index and unique constraint in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. The way I think of it is, that since the SQL standard does not mention indices an INDEX (UNIQUE or otherwise) is just the databases application of a constraint. So for practical purposes they are the same thing. Respectfully, Jorge Maldonado -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unique index VS unique constraint
On 10/04/2013 10:41 AM, luca...@gmail.com wrote: Il 04/10/2013 18:48, JORGE MALDONADO ha scritto: I have search for information about the difference between unique index and unique constraint in PostgreSQL without getting to a specific answer, so I kindly ask for an explanation that helps me clarify such concept. 2 main differences. First is the meaning: primary key identifies a record. A unique just tells you that that value of the record, in the table is unique. If you use keys, db structure will be more intelligible (my opinion). Not sure I follow, you can have a unique index that is not a primary key. A primary key is special kind of unique index: http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html The primary key constraint specifies that a column or columns of a table can contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables can rely on this set of columns as a unique identifier for rows. Second one is functional: in an unique constraint you can allow NULL values and ignore them. A primary key does not allow this. Respectfully, Jorge Maldonado Regards, Luca. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] openclinica
On 09/30/2013 04:20 PM, hugh holston wrote: I cant understand why I am not able to access and open my openclinica webpage, . So what do I do. My guess is you will get a answer sooner here: https://community.openclinica.com/forums/users-email-forum -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] table constraint on two columns
On 07/22/2013 07:16 AM, Vik Fearing wrote: On 07/22/2013 04:05 PM, ldrlj1 wrote: Postgres 9.2.4. I have two columns, approved and comments. Approved is a boolean with no default value and comments is a character varying (255) and nullable. I am trying to create a constraint that will not allow a row to be entered if approved is set to false and comments is null. CHECK constraints work on positives, so restate your condition that way. A row is permissible if approved is true or the comments are not null, correct? So... ...add constraint chk_comments (approved or comments is not null)... This does not work. yada, yada, yada... add constraint chk_comments' check (approved = false and comments is not null). The constraint is successfully added, but does not work as I expected. That's not the same check as what you described. An additional comment, did you put the check constraint on a column or the table? From the docs: http://www.postgresql.org/docs/9.2/interactive/sql-createtable.html: .. A check constraint specified as a column constraint should reference that column's value only, while an expression appearing in a table constraint can reference multiple columns... -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] transaction isolationa level - SERIALIZABLE
On 05/13/2013 02:22 AM, Marcin Krawczyk wrote: Hi list, I have some problems with SERIALIZABLE isolation level, namely my users are plagued with concurrency errors. As of postgres 9.1 (which I'm running) there has been a change to SERIALIZABLE logic, unfortunately my application has not been updated to work with the new logic. I don't have an access to it's code and the only thing I can do is to report the issue to the authors. But before I do it, since I don't actually need SERIALIZABLE for my use, is it possible to have transactions always run in default READ COMMITTED mode, regardless of application level SET SESSION CHARACTERISTICS AS TRANSACTION command ... ? (like e.g in postgres 8.1 where SERIALIZABLE = READ COMMITED) I don't think so: http://www.postgresql.org/docs/9.1/interactive/config-setting.html Furthermore, it is possible to assign a set of parameter settings to a user or a database. Whenever a session is started, the default settings for the user and database involved are loaded. The commands ALTER ROLE and ALTER DATABASE, respectively, are used to configure these settings. Per-database settings override anything received from the postgres command-line or the configuration file, and in turn are overridden by per-user settings; both are overridden by per-session settings. regards mk -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Postgres trigger issue with update statement in it.
On 04/03/2013 09:08 PM, Kaleeswaran Velu wrote: Hello Friends, I am new to Postgres DB. Recently installed Postgres 9.2. Facing an issue with very simple trigger, tried to resolve myself by reading documents or google search but no luck. I have a table A(parent) and table B (child). There is a BEFORE INSERT OR UPDATE trigger attached in table B. This trigger has a update statement in it. This update statement should update a respective record in table A when ever there is any insert/update happen in table B. The issue here is where ever I insert/update record in table B, getting an error as below : ** Error ** ERROR: cannot begin/end transactions in PL/pgSQL SQL state: 0A000 Hint: Use a BEGIN block with an EXCEPTION clause instead. Context: PL/pgSQL function func_update_payment() line 53 at SQL statement Line no 53 in the above error message is an update statement. If I comment out the update statement, trigger works fine. Can anyone shed some lights on this? Your help is appreciated. Without seeing the actual code it is a guess, but here it goes. From this: ERROR: cannot begin/end transactions in PL/pgSQL I would say you tried to create a transaction in the function. Could be you wrote BEGIN; instead of BEGIN ... END; Thanks and Regards Kaleeswaran Velu -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Reading from file without superuser privilege
On 03/21/2013 04:43 AM, Jose Antonio Quintana/UPC wrote: I need to read from a file in order to update a table. The manual says that it is necessary to have the superuser privilege to read from a file. Is it possible to read files without this privilege? What sort of file, any file or one you want to do a COPY or \copy from? For any file you would need to use one of the untrusted languages, plpythonu for example. They need to be installed by a superuser. It is possible to create a function in an untrusted language as the superuser and then confer the superuser privileges to other users for that function by using SECURITY DEFINER, see here: http://www.postgresql.org/docs/9.2/interactive/sql-createfunction.html For COPY : The file must be accessible to the server and the name must be specified from the viewpoint of the server. http://www.postgresql.org/docs/9.2/interactive/sql-copy.html For \copy: This is an operation that runs an SQL COPY command, but instead of the server reading or writing the specified file, psql reads or writes the file and routes the data between the server and the local file system. This means that file accessibility and privileges are those of the local user, not the server, and no SQL superuser privileges are required. http://www.postgresql.org/docs/9.2/interactive/app-psql.html Thanks. ___ José Antonio Quintana Romero Unitat de Projectes Vicegerència de Desenvolupament Organitzatiu i Personal Edifici Vèrtex. Planta 3 Pl. Eusebi Güell, 6 08034 - Barcelona -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Creating a new database with a TEMPLATE did not work
On 02/25/2013 02:49 PM, mkumbale wrote: Hi, I am new to PostgreSQL. I have an empty PostgreSQL DB containing tables but no data. I issued the following command in PGADMIN SQL editor: CREATE DATABASE NewDefault WITH OWNER = postgres ENCODING = 'UTF8' TEMPLATE = Default TABLESPACE = pg_default LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252' CONNECTION LIMIT = -1; Although it created the NewDefault DB, it does not contain any of the tables in Default. Default was disconnected when I executed this command. What am I doing something wrong? So you have a database named Default? Probably not a good name because: http://www.postgresql.org/docs/9.2/interactive/sql-createdatabase.html template The name of the template from which to create the new database, or DEFAULT to use the default template (template1). So at a guess you are actually creating the new database from template1. Thanks, Murali -- View this message in context: http://postgresql.1045698.n5.nabble.com/Creating-a-new-database-with-a-TEMPLATE-did-not-work-tp5746627.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with extract(epoch from interval ...
On 01/23/2013 03:00 AM, Ian Lawrence Barwick wrote: Hi 2013/1/23 Kaloyan Iliev kalo...@digsys.bg: Hi, I have a little problem with extract epoch from interval. It seems that the query works if the interval is in a string but not from DB field. Could someone provide support. (...) You have a casting error; instead of: dbr=# SELECT extract(EPOCH FROM INTERVAL b) FROM a; ERROR: syntax error at or near b LINE 1: SELECT extract(EPOCH FROM INTERVAL b) FROM a; use SELECT extract(EPOCH FROM b::INTERVAL) FROM a; or SELECT extract(EPOCH FROM CAST (b AS INTERVAL)) FROM a; Would it not be simpler: test= SELECT extract(epoch from b) from a; date_part --- 2592000 7776000 15552000 (3 rows) As the b field is already an interval. Regards Ian Barwick -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning the number of rows output by a copy command from a function
On 01/16/2013 08:30 AM, James Sharrett wrote:
I have a function that generates a table of records and then a SQL
statement that does a COPY into a text file. I want to return the
number of records output into the text file from my function. The
number of rows in the table is not necessarily the number of rows in the
file due to summarization of data in the table on the way out. Here is
a very shortened version of what I'm doing:
CREATE OR REPLACE FUNCTION export_data(list of parameters)
RETURNS integer AS
$BODY$
declare
My variables
Begin
{ A lot of SQL to build and populate the table of records to export}
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Return 0;
end
$BODY$
LANGUAGE plpgsql VOLATILE
strSQL gets dynamically generated so it's not a static statement.
This all works exactly as I want. But when I try to get the row count
back out I cannot get it. I've tried the following:
1.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL into export_count;
Return export_count;
This give me an error saying that I've tried to use the INTO statement
with a command that doesn't return data.
2.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Get diagnostics export_count = row_count;
This always returns zero.
3.
strSQL := 'copy (select MyColumns from MyExportTable) to MyFile.csv with
CSV HEADER;';
Execute strSQL;
Return row_count;
This returns a null.
Any way to do this?
If it helps:
http://www.postgresql.org/docs/9.2/interactive/sql-copy.html
On successful completion, a COPY command returns a command tag of the form
COPY count
The count is the number of rows copied.
So it looks like you will need to parse the string for the count.
Thanks in advance,
James
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning the number of rows output by a copy command from a function
On 01/16/2013 09:16 AM, James Sharrett wrote: The problem I have is that I get nothing back when the COPY is run inside the function other than what I explicitly return from the function so I don't have anything to parse. It's odd that the record count in the function is treated differently than from sql query in GET DIAGNOSTIC since the format and information in the string (when run outside of the function) are exactly the same. Execute strSQL into export_count; What is export_count DECLAREd as? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] returning the number of rows output by a copy command from a function
On 01/16/2013 09:26 AM, James Sharrett wrote: Integer Well copy is returning a string, so try changing the type. You will have to parse that string for the count. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to generate drop cascade with pg_dump
On 01/08/2013 01:53 PM, Emi Lu wrote: Hello, May I know how to generate drop table cascade when pg_dump a schema please? E.g., pg_dump -h db_server -E UTF8 -n schema_name -U schema_owner --clean -d db_name ! ~/a.dmp In a.dmp, I'd like to get: drop table t1 cascade; drop table t2 cascade; ... ... Only dropping constraints within a schema is not good enough since there are dependencies on other schema. That is a limitation of dumping by schema. http://www.postgresql.org/docs/9.2/interactive/app-pgdump.html Note: When -n is specified, pg_dump makes no attempt to dump any other database objects that the selected schema(s) might depend upon. Therefore, there is no guarantee that the results of a specific-schema dump can be successfully restored by themselves into a clean database. If you want to reach across schemas you either need to do a whole database dump or modify a partial dump or create your own script. Thanks a lot! Emi -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] copy users/groups
On 01/08/2013 07:08 AM, Edward W. Rouse wrote: We have a database that was updated from 7.4.1 to 9.1.3. I've gotten everything working, but now it seems that the users and groups weren't restored. Probably because they weren't backed up. Is there a way to dump just the users and groups, plus the passwords and permissions, and restore them without overwriting what's been manually added to the new database? Users/groups i.e roles are global to a cluster, you need to use pg_dumpall: http://www.postgresql.org/docs/9.1/interactive/app-pg-dumpall.html pg_dumpall -g Will dump only the global items. Edward W. Rouse -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query execution based on a condition
On 12/29/2012 11:05 AM, JORGE MALDONADO wrote: I have a query similar to the one shown below but, depending on the value of a field, only the first SELECT statement shoud execute and the other 3 should be ignored. Is there a way to achieve this situation? Probably so, but is hard to be specific without more information. In a particular the condition/field being tested and values being tested for. Respectfully, Jorge Maldonado -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Load UTF8@psql into latin1@mysql through JDBC
On 12/12/2012 09:13 AM, Emi Lu wrote: Good morning, Is there a simple way to load UTF8 data in psql to mysql(with latin1 encoding) through JDBC? This would seem to be dependent on the MySQL JDBC adapter. From here: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-charsets.html All strings sent from the JDBC driver to the server are converted automatically from native Java Unicode form to the client character encoding, including all queries sent Thanks a lot! Emi -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Load UTF8@psql into latin1@mysql through JDBC
On 12/12/2012 09:47 AM, Emi Lu wrote: Is there a simple way to load UTF8 data in psql to mysql(with latin1 encoding) through JDBC? This would seem to be dependent on the MySQL JDBC adapter. From here: http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-charsets.html All strings sent from the JDBC driver to the server are converted automatically from native Java Unicode form to the client character encoding, including all queries sent This does not help. The reason I asked this is because through mybatis + JDBC, loading data from psql@utf8 to mysql@latin1, the french character cannot be auto-loaded. You might get an answer sooner at: https://groups.google.com/group/mybatis-user Thanks. -- Emi -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_restore problem
On 09/14/2012 01:58 AM, Kjell Øygard wrote:
1 - Ok, I was not aware of that
2 - I used version 9.1.4 of pg_dump
3 - The command was in a script, se below
pdir=/usr/local/postgresql-9.1.4/
bdir=/backup/`hostname -s`/dump/
export PATH=${pdir}/bin:$PATH
# make sure tmp files are not readable by others
umask 0077
for db in `psql -l -t -h localhost | awk '{print $1}' |grep -v
template|grep -v postgres`
do
pg_dump -h localhost -F c -Z -b $db ${bdir}/${db}.tmp mv
${bdir}/${db}.tmp ${bdir}/${db}.dump
I do not see anything obviously wrong.
Two suggestions.
1) Use the 9.2 version of pg_dump. Newer versions know about changes in
data handling and are also backward compatible(to 7.0).
2) As of 8.3(I believe) the -b switch is redundant for whole database dumps.
When you do the above dump are there large objects in the 9.2 database
in spite of the errors?
rgds Kjell Inge Ø
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_restore problem
On 09/12/2012 12:23 AM, Kjell Øygard wrote:
Morning guys...
I have two servers , one with postgres 9.2rc1 and one with postgres
9.1.4. I need to do a restore from a dump from 9.1.4 to 9.2rc1 and I get
this error:
pg_restore: [archiver (db)] Error from TOC entry 177675; 2613 579519
BLOB 579519 primar
pg_restore: [archiver (db)] could not execute query: ERROR: duplicate
key value violates unique constraint pg_largeobject_metadata_oid_index
DETAIL: Key (oid)=(579519) already exists.
Command was: SELECT pg_catalog.lo_create('579519');
This just keep repeat itself in the log.
The command used is: pg_restore -O -U user -d database2 database2.dump
dump.log 21
Appreciate any help
Several things:
1) The production version of 9,2 is out(9.2.0).
2) When you did the dump from 9.1.4 did you use the 9.1.4 or 9.2 version
of pg_dump?
3) What was the pg_dump command you used?
--
Rgds
Kjell Inge Øygard
Electronic Chart Centre
www.ecc.no http://www.ecc.no
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] must appear in GROUP by clause issue
On 06/29/2012 01:02 PM, George Woodring wrote: Any suggestions would be appreciated. Are there any other errors before or after the above that might relate? Are the function bodies the same on both servers? George Woodring -- iGLASS Networks www.iglass.net http://www.iglass.net -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insane behaviour in 8.3.3
On 06/14/2012 01:39 AM, Achilleas Mantzios wrote:
Hello,one remote user reported a problem and i was surprised to witness the
following behaviour.
It is on postgresql 8.3.3
dynacom=# BEGIN;
BEGIN
dynacom=#
dynacom=#
dynacom=# insert into xadmin(appname,apptbl_tmp,gao,id,comment)
dynacom-#
values('PMS','overhaul_report_tmp','INSERT',nextval('overhaul_report_tmp_pkid_seq'),'
zzz ');
INSERT 0 1
dynacom=#
dynacom=# insert into
items_tmp(id,vslwhid,serialno,rh,lastinspdate,classused,classsurvey,classsurveydate,classduedate,
dynacom(#
classpostponed,classcomment,defid,machtypecount,totalrh,comment,attachments,lastrepdate,pmsstate,xid,classaa)
dynacom-# select
id,vslwhid,serialno,rh,lastinspdate,classused,classsurvey,classsurveydate,classduedate,classpostponed,
dynacom-#
classcomment,defid,machtypecount,totalrh,comment,attachments,lastrepdate,pmsstate,currval('xadmin_xid_seq'),
dynacom-# classaa from items where id=1261319;
INSERT 0 1
dynacom=# -- in the above 'xadmin_xid_seq' has taken a new value in the first
insert
dynacom=# SELECT currval('xadmin_xid_seq');
currval
-
61972
(1 row)
dynacom=# SELECT id from items_tmp WHERE id=1261319 AND xid=61972;
id
-
1261319
(1 row)
dynacom=# -- ok this is how it should be
dynacom=# SELECT id from items_tmp WHERE id=1261319 AND
xid=currval('xadmin_xid_seq');
id
(0 rows)
dynacom=# -- THIS IS INSANE
This code has run fine (the last SELECT returns exactly one row) for
5,409,779 total transactions thus far, in 70
different postgresql slave installations (mixture of 8.3.3 and 8.3.13) (we
are a shipping company),
until i got this error report from a user yesterday.
What could be causing this? How could i further investigate this?
The only thing I could come up with is:
SELECT id, currval('xadmin_xid_seq') from items_tmp WHERE id=1261319 ;
Its grasping at straws, but I can not come up with a logical reason for the
above.
Achilleas Mantzios
IT DEPT
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump: aborting because of server version mismatch
On 05/02/2012 12:55 PM, Mitesh Shah wrote: Hi, I am trying to create a daily backup cron script but it fails with an error as below: Any pointers to resolve this will be greatly appreciated. Thanks, Mitesh Shah mitesh.s...@stripes39.com mailto:mitesh.s...@stripes39.com *(1) Error:* bash-3.2$ sh pg_backup_rotated_orig.sh Making backup directory in /Users/miteshshah/Documents/2012-05-02-daily/ -e Performing full backups -e Plain backup of mitesh *pg_dump: server version: 9.1.2; pg_dump version: 9.0.5* The problem is you are using an older version of pg_dump to dump a newer database. That will not work. Possible solution: You are running via cron. Cron has its own environment. Unless you are explicit in your pathing you can get surprising results, see above. Find the path to the 9.1.2 version of pg_dump and use that absolute path in your script. *pg_dump: aborting because of server version mismatch* -e All database backups complete! -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Finding Max Value in a Row
On 05/11/2012 12:30 PM, Carlos Mennens wrote: Thanks for all the help thus far everyone! I sadly didn't create/design the table and would love to create a SEQUENCE on that particular field but not sure how unless I DROP the table and create from scratch. Currently the data TYPE on the primary key field (users_id) is CHAR and I have no idea why...it should be NUMERIC or SERIAL but it's not so my question is if I want to ALTER the column and create a sequence, would I simply do: ALTER TABLE users ALTER COLUMN users_id TYPE serial ; Obviously if any of the data stored in users_id is actual CHAR, I'm guessing the database would reject that request to change type as the existing data would match. However the data type is CHAR but the field values are all numeric from 100010 - 100301 so I'm hoping that would work for SERIAL which is just INTEGER, right? Well the question to ask is if it is declared CHAR was that done for a legitimate reason? One reason I can think of is to have leading 0s in a 'number'. Might want to double check that code downstream is not depending on CHAR behavior. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
On 03/01/2012 09:04 AM, Carlos Mennens wrote: On Thu, Mar 1, 2012 at 11:38 AM, Eric Ndengang eric.ndengang_fo...@affinitas.de wrote: Hi You can try this command REASSIGN OWNED BY TO ... like this: REASSIGN OWNED BY previous_role TO new_role; DROP OWNED previous_role; I did as follows: iamunix=# \c postgres SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) You are now connected to database postgres as user carlos. You are working on the database 'postgres' not 'iamunix'. REASSIGN works on the current database only. So you just REASSIGNED any objects in postgres. postgres=# REASSIGN OWNED BY carlos TO lauren; REASSIGN OWNED postgres=# DROP OWNED BY carlos; DROP OWNED iamunix=# \d List of relations Schema | Name | Type | Owner +--+--+ public | dept | table| carlos public | dept_id_seq | sequence | carlos public | employees| table| carlos public | employees_id_seq | sequence | carlos public | manager_lookup | view | carlos public | managers | table| carlos public | managers_id_seq | sequence | carlos Change into iamunix and do the REASSIGN. That didn't work for some reason but mostly because I don't follow the concept of what's being done. I've now since changed the database role owner back to Carlos so now 'Carlos' owns both the database and all of it's tables. Can we start fresh and assume I just got the request to change the specified database and all of it's tables, sequences, views, triggers to Lauren? See above. For future reference including the Postgres version would be helpful. This area ownership/grants/etc has undergone a lot of changes over the various versions. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Change Ownership Recursively
On 03/01/2012 11:37 AM, Carlos Mennens wrote: I changed to the suggested database which is owned by 'Carlos' and did as instructed. Everything worked fine. Thank you! In your previous post my guess is this: iamunix=# \c postgres was really meant to be: iamunix=# \c - postgres The first changes to database postgres as current user, the second changes the user while remaining on the current database. On Thu, Mar 1, 2012 at 11:23 AM, Carlos Mennens carlos.menn...@gmail.com wrote: I did do a Google search for PostgreSQL 9.1 change ownership recursively but either couldn't find what I was looking for or missed it. On Thu, Mar 1, 2012 at 1:36 PM, Adrian Klaveradrian.kla...@gmail.com wrote: For future reference including the Postgres version would be helpful. This area ownership/grants/etc has undergone a lot of changes over the various versions. I specified above I was using 9.1 PostgreSQL. Oops, my mistake, I never got to the PS:( -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] SQL View to PostgreSQL View
On Sunday, February 26, 2012 10:50:16 am Rehan Saleem wrote: Hi , I am trying to convert sql view to postgresql view but i am getting the following error i dont know how to handle dbo. in postgresql and when i remove dbo. from table name then view got created but it does not show any data, while this is working perfectly fine in sql, here is my code and error details i am getting this error , how can i fix this. ERROR: schema dbo does not exist LINE 15: from dbo.kbsites a inner join dbo.kbsites b on a.chr=b.chr ^ ** Error ** ERROR: schema dbo does not exist SQL state: 3F000 Character: 761 Do you in fact have a schema dbo? If so can you access the schema and table using some other method, for instance using psql? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problems with ODBC connections
On Saturday, February 25, 2012 10:05:07 am Sandeep Reddy wrote: Hi, I am completely new to postgres and I have some problems with ODBC connection. 1) I am running postgres server in Fedora Linux, and I am planning to connect server from windows client. 2) I have installed 64bit version of postgresql ODBC drivers 3) I am making sure that my postgres is running with -p correct port and -i option to accept remote connections. Still I am getting error saying Connection can not be established. Any quick pointers are highly appreciable. The actual error message would be helpful:)? A common reason for this is a firewall between the client and server that is preventing connection to the server port. Another reason is a pg_hba.conf that has not been configured to allow connections from the client. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] on insert rule with default value
On Wednesday, February 22, 2012 5:52:39 am Ron Peterson wrote: 2012-02-21_15:51:30-0500 Ron Peterson rpete...@mtholyoke.edu: My rule below does not insert the the same uuid value into the test_log table as is created in the test table when I insert a new value. I know I've worked through this before, but I'm not remembering why this is. What's a right way to do this? Obviously I can use a trigger function. I'm mostly wondering if there are any tricks to accomplishing this with rules (I like the simple syntax). I suspect the problem here is that 'new' on insert refers to the function used to calculate the new value, /not/ the new value that is actually inserted into the table. There are probably reasons for that; but it would seem nicer to refer to the actual new table value rather than the expression used to calculate it. My 2c. The simplest explanation I could find is here: http://www.postgresql.org/docs/9.0/interactive/querytree.html the target list... For INSERT commands, the target list describes the new rows that should go into the result relation. It consists of the expressions in the VALUES clause or the ones from the SELECT clause in INSERT ... SELECT. The first step of the rewrite process adds target list entries for any columns that were not assigned to by the original command but have defaults. Any remaining columns (with neither a given value nor a default) will be filled in by the planner with a constant null expression. If you want all the gory details read through section 37, in particular 37.3 :) The above is why I use triggers now. It is a lot easier to follow the logic in a trigger than in a rule. -Ron- -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump - 8.3 - schemas
On 02/16/2012 10:59 AM, Edward W. Rouse wrote: I am working with a database set up by someone else. They set it up so that most tables are in the public schema, but they also have a reports schema; database.public.tables and database.reports.tables. If I do a pg_dump of the database, I only get the public schema. If I do a dump with --schema=reports, I only get the reports schema. Is there a way to get all the schemas from a single pg_dump or am I forced to use separate ones? This is also for future issues where there may be more than 2. The pg_dump should work. What is the exact command line statement you are using? Are doing both dumps as the same user? Thanks Edward W. Rouse -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump - 8.3 - schemas
On 02/16/2012 11:31 AM, Edward W. Rouse wrote: To answer the second question first, yes; both as the same user. pg_dump -v -f $bkfile -F c -U $USER $DATABASE So how are you determining that only the public schema is being dumped? One thing to check is the search_path setting in postgresql.conf. This can create the illusion that only one schema is available in a database. One way to check is to use the fully qualified name for a table you know to be in the reports schema. Ex: select * from reports.some_table; -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pg_dump - 8.3 - schemas
On 02/16/2012 12:17 PM, Edward W. Rouse wrote: Well, when I do a restore using the created file, reports isn't there. i.e. the select from reports.table gives an error and, from psql, \l doesn't list it and \dn doesn't show it. But that all does work on the original database. What is the error reported? What do the Postgres logs show when you restore the data? Do a pg_restore -l against the dump file. This will list the contents of the dump. See if the schema and contents are there. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] type cast about int to bit
On Sunday, February 05, 2012 10:11:12 pm zoulx1982 wrote: hi， there is a problem about type cast that i don't understand, follow is my test. postgres=# select 10::bit(3); bit - 010 (1 row) postgres=# select 10::bit varying(3); ERROR: cannot cast type integer to bit varying LINE 1: select 10::bit varying(3); ^ postgres=# my question is why int can cast to bit , i want to know the reason. thank you for your timing. My guess it depends on the fact that bit types are stored as either char or varchar depending on whether they are bit or bit varying. In the first case you are basically doing an int--char, for which there is a built in cast. In the second case you are doing int--varchar for which there is not a cast. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] type cast about int to bit
On Monday, February 06, 2012 6:42:45 pm zoulx1982 wrote: you mean there is no cast function for int to varchar ? i see sure it is. That is why I said my guess:) If you want to see what is actually going on take a look at: http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/varbit.c;h=adb08369ed28ab6b52aa2cd5213bcd5b4d8de7ad;hb=HEAD The ERROR though is coming further up, in the parser , if I am following correctly. This because as you have found out there is no direct cast from integer to varbit. Why that is for someone else to answer, as I don't know. Though a little playing around got this, not pretty but it seems to work: test(5432)aklaver=SELECT 10::bit(3)::varbit(3); varbit 010 (1 row) test(5432)aklaver=SELECT 10::bit(3)::varbit(4); varbit 010 (1 row) test(5432)aklaver=SELECT 10::bit(4)::varbit(4); varbit 1010 (1 row) test(5432)aklaver=SELECT 10::bit(4)::varbit(3); varbit 101 -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Display Length Between Var Varchar
On Tuesday, January 31, 2012 8:08:06 pm Carlos Mennens wrote: I've noticed when I set a field to char, it takes up lots of space over varchar: iamunix=# SELECT * FROM music; id | band| album |date| asin|label +---+--++-- --+-- 1 | Dance Gavin Dance | Downtown Battle Mountain | 2007-05-15 | B000OQF4PQ | Rise Records (1 row) iamunix=# SELECT * FROM music; id | band| album |date| asin|label +---+--++-- --+-- 1 | Dance Gavin Dance | Downtown Battle Mountain | 2007-05-15 | B000OQF4PQ | Rise Records (1 row) I don't know how well it will show in plain text via email but does anyone know why the field display width is wasted with so much white space when set to char? You will find that the Manual is very helpful in this regard:) http://www.postgresql.org/docs/9.0/interactive/datatype-character.html character varying(n), varchar(n)variable-length with limit character(n), char(n) fixed-length, blank padded -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] date range to set of dates expansion
On Thursday, January 19, 2012 7:59:27 am Gary Stainburn wrote: The following code works in 8.4 but not 8.3. Anyone know why, or what I need to do to change it? SELECT aid, asid, date_range (asdate, afdate)::date AS asdate, acomments FROM availability In 8.4 it returns the expanded dataset as required. In 8.3 I get: ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function date_range line 4 at RETURN NEXT As to why it works in 8.4 vs 8.3 http://www.postgresql.org/docs/8.4/interactive/release-8-4.html Support set-returning functions in SELECT result lists even for functions that return their result via a tuplestore (Tom) In particular, this means that functions written in PL/pgSQL and other PL languages can now be called this way.' In 8.3- I believe you could only call it as SELECT * from date_range (asdate, afdate)::date AS asdate; Is there a way to use the integer only generate_series in 8.3 to generate dates by typecasting to/from integers? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] date range to set of dates expansion
On 01/19/2012 09:17 AM, Samuel Gendler wrote: On Thu, Jan 19, 2012 at 8:20 AM, Adrian Klaver adrian.kla...@gmail.com mailto:adrian.kla...@gmail.com wrote: On Thursday, January 19, 2012 7:59:27 am Gary Stainburn wrote: The following code works in 8.4 but not 8.3. Anyone know why, or what I need to do to change it? SELECT aid, asid, date_range (asdate, afdate)::date AS asdate, acomments FROM availability In 8.4 it returns the expanded dataset as required. In 8.3 I get: ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function date_range line 4 at RETURN NEXT As to why it works in 8.4 vs 8.3 http://www.postgresql.org/docs/8.4/interactive/release-8-4.html Support set-returning functions in SELECT result lists even for functions that return their result via a tuplestore (Tom) In particular, this means that functions written in PL/pgSQL and other PL languages can now be called this way.' In 8.3- I believe you could only call it as SELECT * from date_range (asdate, afdate)::date AS asdate; I don't think you can have that cast there when it is in the from-clause. That was a cut and paste error on my part, I just copied that line from the original query. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] UPDATE COMPATIBILITY
On Tuesday, January 17, 2012 12:49:44 am Thomas Kellerer wrote: Gera Mel Handumon, 17.01.2012 07:31: None as far as I know. You need to rewrite it to: UPDATE accounts SET contact_last_name = s.last_name, contact_first_name = s.first_name FROM salesmen s WHERE s.id = accounts.sales_id For completeness, you could also do: UPDATE accounts SET (contact_last_name,contact_first_name)= (s.last_name,s.first_name) FROM salesmen s WHERE s.id = accounts.sales_id Gets you a little closer to what you want:) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Unable To Modify Table
On Thursday, January 12, 2012 8:42:59 am Carlos Mennens wrote: I seem to have an issue where I can't modify a table due to another tables foreign key association: [CODE]trinity=# \d developers Table public.developers Column| Type | Modifiers --++--- id | character(10) | not null name| character(50) | not null address | character(50) | city| character(50) | state | character(2) | zip | character(10) | country | character(50) | phone | character(50) | email | character(255) | Indexes: developers_pkey PRIMARY KEY, btree (id) Referenced by: TABLE orders CONSTRAINT fk_orders_developers FOREIGN KEY (id) REFERENCES developers(id) [/CODE] Now when I try and change the values before I alter the field TYPE, I get an error that another table (orders) with a foreign key associated with public.developers 'id' field still has old values therefor can't change / modify the 'developers' table. [CODE]trinity=# UPDATE developers SET id = '1000' WHERE id = '11'; ERROR: update or delete on table developers violates foreign key constraint fk_orders_developers on table orders DETAIL: Key (id)=(11) is still referenced from table orders. [/CODE] How does one accomplish my goal? Is this difficult to change or once that foreign key is created, are you stuck with that particular constraint? You are pushing in the wrong direction. You need to make the change in the table 'orders'. This assumes the FK in 'orders' has ON UPDATE CASCADE enabled. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare table names
On Monday, January 09, 2012 8:12:18 am Tony Capobianco wrote: I have these 3 tables: tablename tmp_staging0109 tmp_staging1229 tmp_staging0108 I'd like this query: select tablename from pg_tables where tablename like 'tmp_staging%' and tablename 'tmp_staging1230'; To return this result: tablename tmp_staging1229 However, I'm receiving: tablename tmp_staging0109 tmp_staging1229 tmp_staging0108 How can I write this correctly? As far as I can tell it is correct. 0108,0109 and 1229 are all less than 1230. What happens if you do?: select tablename from pg_tables where tablename like 'tmp_staging%' and tablename 'tmp_staging1230' and tablename 'tmp_staging1228; Thanks. Tony -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare table names
On Monday, January 09, 2012 8:12:18 am Tony Capobianco wrote: tablename tmp_staging0109 tmp_staging1229 tmp_staging0108 How can I write this correctly? Had another idea. If you are looking for the highest numbered table below a certain number then maybe this: test(5432)aklaver=\d name_test Table public.name_test Column | Type| Modifiers +---+--- fld_1 | character varying | test(5432)aklaver=SELECT * from name_test ; fld_1 - tmp_staging0109 tmp_staging0108 tmp_staging1229 (3 rows) test(5432)aklaver=select fld_1 from name_test where fld_1 like 'tmp_staging%' and fld_1 'tmp_staging1230' order by fld_1 desc limit 1; fld_1 - tmp_staging1229 Thanks. Tony -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] compare table names
On Monday, January 09, 2012 8:28:43 am Tony Capobianco wrote: I see what you're saying: pg=# select tablename from pg_tables where tablename like 'tmp_staging%' and tablename 'tmp_staging1230' and tablename 'tmp_staging1228'; tablename tmp_staging1229 This query is part of a larger script where I want to dynamically select tablenames older than 10 days and drop them. The tables are created in a tmp_stagingMMDD format. I know postgres does not maintain object create times, how can I write this to select tables from pg_tables that are older than 10 days? Well with out a year number(i.e. YYMMDD) that is going to be difficult around the year break. As an example: test(5432)aklaver=select * from name_test; fld_1 - tmp_staging0109 tmp_staging0108 tmp_staging1229 (3 rows) test(5432)aklaver=SELECT fld_1 from name_test where fld_1 'tmp_staging'|| to_char(current_date-interval '10 days','MMDD') and fld_1 'tmp_staging0131'; fld_1 - tmp_staging1229 Thanks. Tony -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] avoid the creating the type for setof
On Friday, December 30, 2011 6:26:19 am John Fabiani wrote: Hi, I recall somewhere I saw a simple plpgsql function that returned a table with more than one record that did not use a defined type or a temp table ( at least I think I did). Is it possible to create such a function that will return more than one record and not require a record type or temp table? something like return setof record as return query select ... http://www.postgresql.org/docs/9.0/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-PARAMETERS 39.3.1. Declaring Function Parameters Search for RETURNS TABLE return Johnf -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using a generated series in function
On Saturday, December 17, 2011 8:50:52 am John Fabiani wrote: As always I respect your insights - Adrian. I do understand what I did wrong in my first attempt at getting my statement to work. But it is either over my head or there is something missing. Where is the from in select now()? If it makes you happier:) test(5432)aklaver=SELECT * from now(); now --- 2011-12-17 10:00:34.929144-08 (1 row) I have been using similar SQL statements for years. I never questioned why there was not a 'from' until this list noted that I was missing a 'From'. I then went to the postgres site to read. That's how I determined what I had done incorrectly. SELECT List The SELECT list (between the key words SELECT and FROM) specifies expressions that form the output rows of the SELECT statement. The expressions can (and usually do) refer to columns computed in the FROM clause. So items in the SELECT are not required to be derived from as FROM clause I hope this is not one of those things like javascript where all white space is ignored unless it's not! I hate that language! It appears that everything needs a 'From' in SQL (reading the doc's) and the above statement is missing a 'From'! It is a Postgres extension to the SQL standard: http://www.postgresql.org/docs/9.0/interactive/sql-select.html#SQL-SELECT-LIST Compatibility Omitted FROM Clauses PostgreSQL allows one to omit the FROM clause. It has a straightforward use to compute the results of simple expressions: SELECT 2+2; ?column? -- 4 Some other SQL databases cannot do this except by introducing a dummy one-row table from which to do the SELECT. Note that if a FROM clause is not specified, the query cannot reference any database tables. For example, the following query is invalid: SELECT distributors.* WHERE distributors.name = 'Westward'; PostgreSQL releases prior to 8.1 would accept queries of this form, and add an implicit entry to the query's FROM clause for each table referenced by the query. This is no longer allowed. As always everyone - thanks for your help! -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Column ... does not exist (view + union)
On Saturday, December 17, 2011 2:21:30 pm Stefan Weiss wrote: I know, but the problem only occurs when I want to sort by a column which hasn't been selected, and thus cannot be referred to by its index. For normal (non-union) queries, this is possible: SELECT relname FROM pg_class WHERE relhasindex ORDER BY relpages; In this trivial case, PostgreSQL knows where to look for relpages. Not so in a union: SELECT relname FROM pg_class WHERE relhasindex UNION SELECT relname FROM pg_class WHERE relhasoids ORDER BY relpages; (ERROR: column relpages does not exist) I understand the error now (I think), and I know how to avoid it. SELECT relname FROM pg_class WHERE relhasindex UNION (SELECT relname FROM pg_class WHERE relhasoids ORDER BY relpages); thanks, stefan -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] using a generated series in function
On Friday, December 16, 2011 3:56:48 am John Fabiani wrote:
I have solved my problem. But this still does not explain the idea of
from
From original query:
..(function_name(303, week_date::date)) as week_date where week_date..
Reason why that did not work:
http://www.postgresql.org/docs/9.0/interactive/sql-select.html
SELECT List
...
An output column's name can be used to refer to the column's value in ORDER BY
and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must
write out the expression instead.
...
Why the below does work:
SELECT List
The SELECT list (between the key words SELECT and FROM) specifies expressions
that form the output rows of the SELECT statement. The expressions can (and
usually do) refer to columns computed in the FROM clause
FROM Clause
select
A sub-SELECT can appear in the FROM clause. This acts as though its output
were created as a temporary table for the duration of this single SELECT
command. Note that the sub-SELECT must be surrounded by parentheses, and an
alias must be provided for it. A VALUES command can also be used here.
select foo.week_date, xchromasun._chromasun_getqtyordered(303,
foo.week_date) as week_qty from
(select ((date_trunc('week', '2011-11-20'::date )::date) + (i+6)) as
week_date from generate_series(0,84,7)
i ) as foo
The above works!
Johnf
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FW: Hi
On Saturday, September 10, 2011 2:22:32 pm Mandana Mokhtary wrote: From: Mandana Mokhtary Sent: 10 September 2011 23:21 To: pgsql-sql-ow...@postgresql.org Subject: Hi Hi All I tried to import shapfile into postgres using this comand: pgsql -c -s 3006 -W LATIN1 c:/.. |psql _U postgres (database name) I could import some but not all. I got this error that I do not have any idea about it: current transaction is aborted, commands ignored until end of transactions block. Means just what it says, a transaction was aborted due to error and at that point all further commands where ignored. at fisrt, the name of the shapfiles have some Swedish alphabet which i changed it to latin I would appreciate any help. The actual command string would be nice:) If the above was it then that explains the problem. First I think you are looking for psql not pgsql. Second it should be psql -U postgres ... Regards, Mandana -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] bigint and unix time
On Tuesday, August 16, 2011 2:12:52 am Janiv Ratson wrote: Hi Adrain and thank you, Trac 0.12 uses microseconds as time value. What do you suggest? extract(epoch ..) returns seconds which you are trying to compare to microseconds. The solution would be to divide your 'time' values by 1,000,000 to make them seconds. Like: SELECT ticket, time/100, author, field, oldvalue, newvalue FROM ticket_change where time/100 BETWEEN extract(epoch from (date 'now' - integer '30')) AND extract(epoch from (date 'now')); Thanks, Janiv. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] bigint and unix time
On Sunday, August 14, 2011 11:23:08 pm Janiv Ratson wrote: Hi and thanks, If my 'time' column is being saved as bigint: 128732389900. How do I write a query to check if the 'time' field is greater than now - 30 (past 30 days)? So what you want is not what values are greater than some point 30 days ago which is what your previous query asked and answered, but the values between a point 30 days ago and today. The easiest way is to use BETWEEN: test(5432)aklaver=\d big_int_test Table public.big_int_test Column | Type | Modifiers +-+--- bint | bigint | rint | integer | test(5432)aklaver=SELECT * from big_int_test ; bint |rint --+ 128732389900 | 1310799600 test(5432)aklaver=SELECT bint FROM big_int_test WHERE bint BETWEEN extract(epoch from (date 'now' - integer '30')) AND extract(epoch from (date 'now')); bint -- (0 rows) That being said, if your time values are the order of magnitude shown they will not meet the criteria above. Is the time value supposed to be seconds? Thanks, Janiv,. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] bigint and unix time
On Sunday, August 14, 2011 4:13:30 am Janiv Ratson wrote: Hi, I have the following query: select ticket as ticket, time as created, author as reporter, cast(extract(epoch from (date 'now' - integer '30')) as bigint) from ticket_change tc where field = 'status' and newvalue = 'reopened' and time extract(epoch from (date 'now' - integer '30')) order by time I'm trying it get all records that their 'time' is within the past 30 days. However, the time is bigint: 128732389900 While the extract(epoch from (date 'now' - integer '30')) is 1310677200 Bigint versus integer refers to the max values that the field can contain. For a given value of integer the storage should be the same for each up to the limit of the integer field. Would seem that whatever is putting values into time is inflating the values if they are actually referring to contemporary time values. As you understand, I always get all records ... How do I solve it? Thanks, Janiv. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pgadmin debugger
On Sunday, August 14, 2011 10:15:43 am David Harel wrote: On Sat, 2011-08-13 at 12:56 -0700, Adrian Klaver wrote: On Saturday, August 13, 2011 12:39:44 pm David Harel wrote: Greetings, I use Ubuntu 10.04. I have postgresql version 8.4.8 installed also I have pgadmin version 1.10.2. I can't find debugger_plugin.so which is needed to debug pgplsql using pgadmin: http://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PLPgSQ L-De bugger.html Any idea? http://www.enterprisedb.com/docs/en/8.4/pgadmin/debugger.html Trying to compile (no configure script found) the thing I get: pldebugger # make Makefile:42: ../../src/Makefile.global: No such file or directory Makefile:43: /contrib/contrib-global.mk: No such file or directory make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. Looking at README.pldebugger indicates you need the development code for Postgres. Is your Postgres installation from the Ubuntu packages or did you compile it yourself? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] pgadmin debugger
On Saturday, August 13, 2011 12:39:44 pm David Harel wrote: Greetings, I use Ubuntu 10.04. I have postgresql version 8.4.8 installed also I have pgadmin version 1.10.2. I can't find debugger_plugin.so which is needed to debug pgplsql using pgadmin: http://www.postgresonline.com/journal/archives/214-Using-PgAdmin-PLPgSQL-De bugger.html Any idea? http://www.enterprisedb.com/docs/en/8.4/pgadmin/debugger.html -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] why these results?
On Monday, August 01, 2011 3:50:00 pm Wes James wrote: select count(*) from table; count --- 100 (1 row) is correct select count(*) from table where col::text ~~* '%text%'; count --- 1 (1 row) is correct. But now if I do: select count(*) from table where col::text !~~* '%text%'; count --- 98 (1 row) Shouldn't it be 99? That is out of 100 records there is one that has text in column col so the !~~* should return 99 rows. ?? NULL value in field? -wes -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ERROR: malformed record literal: ,DETAIL: Missing left parenthesis?
On 07/06/2011 12:03 PM, Emi Lu wrote:
A question about: ERROR: malformed record literal:
DETAIL: Missing left parenthesis.
Can someone tell me what cause the error?
Table z_drop;
Column | Type
-+
run_date | character varying(128)
adm_year | character varying(4)
adm_sess | character varying(1)
faculty | character varying(128)
ac_cycle | character varying(128)
deg_code | character varying(128)
discipline | character varying(128)
thesis | character varying(128)
elig_stype | character varying(128)
stud_source | character varying(128)
applied | numeric
reviewed | numeric
accepted | numeric
confirmed | numeric
registered | numeric
hold | numeric
forward | numeric
refused | numeric
cancelled | numeric
other | numeric
pending | numeric
PREPARE test(z_drop) AS INSERT INTO z_drop VALUES ($1, $2, $3, $4, $5,
$6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20,
$21) ;
I have fixed it.
It should not be z_drop, it should be real column names.
The mailing list email appears so slow :-( Only after 4 hours it show!
What happens if you do?:
PREPARE test AS INSERT
My rough guess is that z_drop is being applied to the first parameter only.
Emi
EXECUTE test('', '1', '1', '1', '1', '1', '1', '1', '1', '', 1, 1, '1',
'0', '0', '0', '0', '0', '0', '0', '0') ;
Thank you,
Emi
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] problem with update data sets from front ends 8.4, Ubuntu 10.04
On Monday, May 23, 2011 9:53:47 am Dean le Roux wrote: postgres 8.3 to 8.4 Open office base 3.2 Ubuntu 9.04 to 10.04 (new install) Did the OO version change also? I recently upgraded from Ubuntu 9.04 to Ubuntu 10.04. Security and other updates have been effected. I used webmin to restore the databases. Since migrating to Linux around 2006 we were successful in converting our mdb file to postgresql - only one glitch was a difference in -1 as YES. We successfully used the systems with open office as a front end for the last few years. Until now - after the upgrade I can view data but not update data. Usually, in Base, that indicates that it can not find a primary key. FYI: The below is redundant: CONSTRAINT FINANCEDETAILS_pkey PRIMARY KEY (FINDETID), CONSTRAINT FINANCEDETAILS_FINDETID_key UNIQUE (FINDETID) From the docs: http://www.postgresql.org/docs/8.4/interactive/sql-createtable.html Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables can rely on this set of columns as a unique identifier for rows. Experience with sql is very limited as I have always used sql query builders over the years. The system has worked well with multiple tables, and in the past there was no problems with sending sql statements to postgresql. My problem in OOO 3.2 base (other front ends also) is that there is continually a problem with not allowing queries to update data back to postgresql. What are you using to connect to the database JDBC, ODBC, other? I believe something has changed perhaps in sql from 8.3 to 8.4, or I have missed something with restoring the files. Did you do a full restore or selective? Any assistance offered will be appreciated. -- Adrian Klaver adrian.kla...@gmail.com
Re: [SQL] Get id of a tuple using exception
On Thursday, April 14, 2011 3:56:51 am f vf wrote: Hello, i'm using a pl/sql procedure and I prevent inserting duplicate tuples using an exception for example: BEGIN INSERT INTO Triples(id, subject, predicate, object) VALUES (id, sub_i, pred_i, obj_i); * EXCEPTION WHEN unique_violation THEN --do something. *In some cases I have interest in getting the id of the tuple that was already in the table when the exception is triggered. Is there a way for the EXCEPTION to return that id instead of using a select to know wich was the id of the triple already existing in the table? If the id is the PRIMARY KEY then it would be the same as the id you tried to INSERT correct? Thanks, Filipe -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Get id of a tuple using exception
On Thursday, April 14, 2011 6:50:57 am f vf wrote: No, the id is nextval( 'triples_seq'), if I do nothing, so its something like: BEGIN INSERT INTO Triples(id, subject, predicate, object) VALUES (nextval( 'triples_seq'), sub_i, pred_i, obj_i); EXCEPTION WHEN unique_violation THEN --do something. The unique constraint is applyied to the subject, predicate and object fields. So, if I try to insert anything that has these 3 fields equal to any tuple that already exists in the table I want to get the id of the original tuple. To your original question I am not aware of a way of returning the id of the offending tuple, other than through a SELECT. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] is there a refactor
On Tuesday, April 05, 2011 7:27:24 am John Fabiani wrote: Hi, I would like to have a simple way to retrieve information for a field name. By that I mean have some SQL select that will return all the tables a field name exist within a database. I did not find anything with google but of course google depends on the search string. Thanks in advance, Johnf test(5432)aklaver=SELECT table_name from information_schema.columns where column_name = 'id'; table_name user_test table2 table1 hours jedit_test topics t2 stone serial_test messages binary_test user_test timestamp_test role_t py_test money_test lock_test local_1 lang_test interval_test foob fooa fldlength fk_1 default_test csv_null check_two check_test array_test (29 rows) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] what's wrong in this procedure?
On 02/25/2011 09:46 AM, Camaleon wrote: This error is returned Erro de SQL: ERROR: column Aguardando Pagto does not exist at character 352 create or replace function get_historico() RETURNS SETOF twiste.type_cur__historico AS ' SELECT o.data_fim, sum(t.num_itens * t.valor) AS total, count(t.*) AS transacoes FROM ofertas o JOIN transacao t ON o.ofertas_id = t.ofertas_id JOIN municipio m ON o.municipio_id = m.municipio_id WHERE o.data_fim= now() AND t.status IN(Aguardando Pagto, Em análise, Aprovado, Completo) GROUP BY o.data_fim; ' language 'sql'; the column is t.status and not Aguardando Pagto; what's wrong ? thanks Try single quotes, 'Aguardando Pagto' -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] quotes etc
On Tuesday, February 22, 2011 12:26:41 pm John Fabiani wrote:
Hi,
I would have thought that there would be a simple built-in function that
would escape the quotes as ('D' Andes') to ('D\' Andes'). But I did not
see anything?
I am I wrong?
Johnf
Dollar quoting ? :
http://www.postgresql.org/docs/9.0/interactive/sql-syntax-lexical.html
4.1.2.4. Dollar-Quoted String Constants
test(5432)aklaver=SELECT $$D' Andes$$;
?column?
--
D' Andes
--
Adrian Klaver
adrian.kla...@gmail.com
Re: [SQL] Help needed in skipping column for copy command
On 01/17/2011 05:59 AM, Amar Dhole wrote: I have table created as follows CREATE TABLE D_2147483927_2147484848_TAB( CP VARCHAR(256) , CPR VARCHAR(256) , CHOUSENO VARCHAR(256) , CSTREET VARCHAR(256) , CLOCALITY VARCHAR(256) , CCITY VARCHAR(256) , CPROVINCE VARCHAR(256) , CCOUNTRY VARCHAR(256) , CZIP VARCHAR(256) , CCO VARCHAR(256) ) I am using copy command to copy the content of file into the table. (one.txt) PR,PRO,HOUSENO,STREET,LOCALITY,CITY,PROVINCE,COUNTRY,ZIP,CON ,,A-24 Siddi vihar apt.,Near Krishna Chowk,New Sanghvi,Pune,MH,India,411027 In the above data, data for last column is missing. Looks like two columns missing. I see 10 headers and 8 data values unless that is a cut and paste issue. copy D_2147483927_2147484848_TAB from 'D:/work/one.txt' with delimiter as ',' quote '' csv HEADER ; I get the following error as ERROR: missing data for column ccontains Where does ccontains from? I am not seeing it in the table or the text file. CONTEXT: COPY d_2147483927_2147484848_tab, line 2: q,q,A-24 Siddi vihar apt., Near Krishna Chowk,New Sanghvi,Pune,MH,India,411027 Can any one please tell me how can I make copy command to ignore the data missing column ? as the data in file is unknown so it column name is which is missing is not known in advance. You can specify a column list to COPY. See here: http://www.postgresql.org/docs/9.0/interactive/sql-copy.html Thanks Amar -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] explicit casts
On Wednesday 05 January 2011 4:24:34 pm Iuri Sampaio wrote:
Hi there,
I installed postgresql 8.4 on my box and now i have troubles with the
following query regarding explicit casts.
select to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'),
'fmMonth') as month,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), '')
as year,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'J') as
first_julian_date_of_month,
to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as
num_days_in_month,
to_char(trunc(to_date(:the_date, '-mm-dd'), 'Month'), 'D') as
first_day_of_month,
to_char(last_day(to_date(:the_date, '-mm-dd')), 'DD') as last_day,
trunc(add_months(to_date(:the_date, '-mm-dd'), 1),'Day') as
next_month,
trunc(add_months(to_date(:the_date, '-mm-dd'), -1),'Day') as
prev_month,
trunc(to_date(:the_date, '-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months(to_date(:the_date, '-mm-dd'), -1)),
'DD') as days_in_last_month,
to_char(add_months(to_date(:the_date, '-mm-dd'), 1), 'fmMonth')
as next_month_name,
to_char(add_months(to_date(:the_date, '-mm-dd'), -1),
'fmMonth') as prev_month_name
from dual
the value assigned to the variable :the_date is '2010-01-05'
The error is
Error: Ns_PgExec: result status: 7 message: ERROR: function
to_date(timestamp with time zone, unknown) does not exist
LINE 1: select to_date(date_trunc('month',add_months( $1 ,1)),'-...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: select to_date(date_trunc('month',add_months( $1
,1)),'-MM-DD') - 1
CONTEXT: PL/pgSQL function last_day line 6 at SQL statement
how would i apply the following solution
date_trunc('month', p_date_in + interval '1 month')::date - 1
to fix the query above?
cheers,
iuri
If I am following this right the problem is in the last_day function and in
particular the return value of the add_months function used in the
date_trunc(). To be sure we would need to see those functions. As a shot in the
dark:
select to_date(date_trunc('month',add_months( $1 ,1)::date)...
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] explicit casts
On Wednesday 05 January 2011 5:22:30 pm Iuri Sampaio wrote:
So far,
I could write the following query
select to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'),
'fmMonth') as month,
to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), '') as
year,
to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), 'J') as
first_julian_date_of_month,
to_char(last_day('2010-01-02')::date, 'DD') as num_days_in_month,
to_char(trunc(to_date('2010-01-02', '-mm-dd'), 'Month'), 'D') as
first_day_of_month,
to_char(last_day('2010-01-02')::date, 'DD') as last_day,
trunc(add_months(to_date('2010-01-02', '-mm-dd'), 1),'Day') as
next_month,
trunc(add_months(to_date('2010-01-02', '-mm-dd'), -1),'Day') as
prev_month,
trunc(to_date('2010-01-02', '-mm-dd'), 'year') as beginning_of_year,
to_char(last_day(add_months('2010-01-02', -1))::date, 'DD') as
days_in_last_month,
to_char(add_months(to_date('2010-01-02', '-mm-dd'), 1), 'fmMonth')
as next_month_name,
to_char(add_months(to_date('2010-01-02', '-mm-dd'), -1), 'fmMonth')
as prev_month_name
from dual
But i still miss some lines in order to properly explicit casts in the
query
cheers,
iuri
My guess is you upgraded from a version prior to 8.3. In 8.3 many of the
implied
casts where removed, so you probably have to go over your code and make the
corrections.
Go here for more detail:
http://www.postgresql.org/docs/8.4/interactive/release-8-3.html
Section
E.21.2.1. General
A temporary solution can be found here:
http://petereisentraut.blogspot.com/2008/03/readding-implicit-casts-in-postgresql.html
An important tip from the blog-
The gist was, only restore the casts you need, not all of them.
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create role
On Friday 31 December 2010 8:22:23 am tcapobia...@prospectiv.com wrote: On 31 December 2010 05:14, Tony Capobianco tcapobia...@prospectiv.com wrote: esave_dw= \d members Did not find any relation named members. esave_dw= esave_dw= \d esave.members Table esave.members Column | Type | Modifiers -+-+--- memberid | numeric | not null etc How can I get this so I don't have to preface the \d with the schema name every time? Hi Tony, you should changes the default search_path for the specified users. http://sql-info.de/postgresql/schemas.html take a look at practical schema usage section. Gibransyah, That did the trick! Thanks for your help. I modified my role name from developer to dev since I already have a dev schema. Here's the steps I ran below to get it working: create role dev login; alter role dev set default_tablespace=dev; alter role dev set search_path=dev,staging, esave, support, email,public; grant usage on schema esave to dev; grant usage on schema dev to dev; grant select on members to dev; grant create on schema dev to dev; grant create on tablespace dev to dev; I am a little confused as to why I had to grant usage create on dev to dev since it's both the dev role's default_tablespace and has a schema named after it. Either way, this corrects my issue. Thanks for your help! Tony First tablespace != schema. From here http://www.postgresql.org/docs/9.0/interactive/manage-ag-tablespaces.html: Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects. By using tablespaces, an administrator can control the disk layout of a PostgreSQL installation. This is useful in at least two ways. First, if the partition or volume on which the cluster was initialized runs out of space and cannot be extended, a tablespace can be created on a different partition and used until the system can be reconfigured. Second, tablespaces allow an administrator to use knowledge of the usage pattern of database objects to optimize performance. For example, an index which is very heavily used can be placed on a very fast, highly available disk, such as an expensive solid state device. At the same time a table storing archived data which is rarely used or not performance critical could be stored on a less expensive, slower disk system. Second, from the schema docs if you want 'user' schemas than The value for search_path must be a comma-separated list of schema names. If one of the list items is the special value $user, then the schema having the name returned by SESSION_USER is substituted, if there is such a schema. (If not, $user is ignored.) The default value for this parameter is '$user, public' (where the second part will be ignored if there is no schema named public). This supports shared use of a database (where no users have private schemas, and all share use of public), private per-user schemas, and combinations of these. Other effects can be obtained by altering the default search path setting, either globally or per-user. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create role
On Friday 31 December 2010 8:22:23 am tcapobia...@prospectiv.com wrote: On 31 December 2010 05:14, Tony Capobianco tcapobia...@prospectiv.com wrote: esave_dw= \d members Did not find any relation named members. esave_dw= esave_dw= \d esave.members Table esave.members Column | Type | Modifiers -+-+--- memberid | numeric | not null etc How can I get this so I don't have to preface the \d with the schema name every time? Hi Tony, you should changes the default search_path for the specified users. http://sql-info.de/postgresql/schemas.html take a look at practical schema usage section. Gibransyah, That did the trick! Thanks for your help. I modified my role name from developer to dev since I already have a dev schema. Here's the steps I ran below to get it working: create role dev login; alter role dev set default_tablespace=dev; alter role dev set search_path=dev,staging, esave, support, email,public; grant usage on schema esave to dev; grant usage on schema dev to dev; grant select on members to dev; grant create on schema dev to dev; grant create on tablespace dev to dev; I am a little confused as to why I had to grant usage create on dev to dev since it's both the dev role's default_tablespace and has a schema named after it. Either way, this corrects my issue. Thanks for your help! Tony In my previous message I forgot to add the following. The set commands and grants are not linked. Setting something does not necessarily confer privileges for that object. The search_path for instance. It really only sets up the search order for unqualified object names. What you can see or do with those objects is determined by the privileges on those objects. Those privileges come from either the role that created the object or are GRANT(ed) by a sufficiently privileged role to another role. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create function problem
On 12/30/2010 09:35 AM, Pavel Stehule wrote: Hello you badly use a IF statement. It's not C. Every IF must to finish with END IF this is IF .. THEN ELSEIF .. THEN .. ELSE END IF Regards Pavel Stehule To follow up I think the OP was looking for ELSEIF not ELSE IF. Changing the ELSE IF to ELSEIF should fix it. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] create role
On Thursday 30 December 2010 2:14:23 pm Tony Capobianco wrote: Hi, I'm successfully executing the below: create role developer login; alter role developer set default_tablespace=dev; alter role developer set search_path=dev,staging, esave, support, email, public; grant select on members to developer; grant create on schema dev to developer; However, when I do this: psql (8.4.5, server 8.4.2) Type help for help. esave_dw= \d members Did not find any relation named members. esave_dw= esave_dw= \d esave.members Table esave.members Column|Type | Modifiers -+-+--- memberid| numeric | not null etc How can I get this so I don't have to preface the \d with the schema name every time? Thanks. Tony Did you log out and then back in as developer? Per: http://www.postgresql.org/docs/9.0/interactive/sql-alterrole.html The remaining variants change a role's session default for a configuration variable, either for all databases or, when the IN DATABASE clause is specified, only for sessions in the named database. Whenever the role subsequently starts a new session, the specified value becomes the session default, overriding whatever setting is present in postgresql.conf or has been received from the postgres command line. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] insert from a select
On Wednesday 24 November 2010 4:07:43 pm John Fabiani wrote:
Hi,
I have a strange issue that is mostly likely me not understanding
something. I always thought that an insert statement would accept any
select statement. I'm guessing I am wrong.
I have created a temporary table (tempclass) that is exact match to an
existing table ('esclass').
When I attempt to do the following
insert into tempclass Select cl.pkid, cl.depart, cl.sessionid, cl.instrid,
cl.classseq,(select facility from esloc where esloc.pkid = cl.locationid)
as facility, cl.schedule from esclass cl where cl.pkid in (14507,14508)
I get the following error:
ERROR: column schedule is of type date but expression is of type
character varying
LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select fa...
^
HINT: You will need to rewrite or cast the expression.
The error makes no sense to me. But most important if I just run the
select statement it works perfectly.
Like I said the table tempclass (a temporary) is a dup of table esclass
so none of it makes sense. Of course I did say I'm missing something.
So why isn't the select statement working with the insert?
Looks like an off by one situation. See error detail below:
LINE 1: ... cl.depart, cl.sessionid, cl.instrid, cl.classseq,(select fa..
^
Looks like the result of the 'select facility.. is being inserted into the
schedule column.
Johnf
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help with plpgsql function.
On Saturday 13 November 2010 11:15:51 pm Pavel Stehule wrote: } Hello you can use a RETURN QUERY statement - some like CREATE OR REPLACE FUNCTION foo(IN i int, OUT a int, OUT b int) RETURNS SETOF RECORD AS $$ BEGIN IF i = 1 THEN RETURN QUERY SELECT 10,20 UNION ALL SELECT 30,40; ELSE RETURN QUERY SELECT 60,70 UNION ALL SELECT 80,90; END IF; RETURN; END; $$ LANGUAGE plpgsql; SELECT * FROM foo(1); SELECT * FROM foo(2); Regards Pavel Stehule FYI the OP is using 8.2 :) RETURN QUERY is 8.3+ -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] psql -f COPY from STDIN
On 11/12/2010 02:03 PM, Tarlika Elisabeth Schmitz wrote: The following command works fine when pasing it to psql via the -c option: cat event.csv | \ psql -c COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS '' When executed from a file via -f, it does nothing (no error messages either): event.sql: COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS '' COPY (event_id, event_name) FROM STDIN DELIMITER AS ',' NULL AS ''; ^ cat event.csv | psql -f event.sql What's the problem? Many thanks in advance. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] COPY command and required file permissions
On 06/23/2010 02:01 PM, bruno.scovoli wrote: I have an script with many \copy commands. For example: \copy privilegios from '/work/eclipse-workspace/Canoan Server/database/load/privilegios.dat' \copy privilegios_de_usuarios from '/work/eclipse-workspace/Canoan Server/database/load/privilegios_de_usuarios.dat' \copy classificacoes from '/work/eclipse-workspace/Canoan Server/database/load/classificacoes.dat' \copy tipo_de_produto from '/work/eclipse-workspace/Canoan Server/database/load/tipos.dat' This lines are OK. But when I remove the preceding backslash from them I get the error that you mentioned. Harold A. Giménez Ch. wrote: Hi all, In migrating an application from sql server to Postgres, I created a ruby script that extracts csv files from sql server (from a windows box), then SCPs them into a directory (/home/ruby_process) on the server running Postgres (a Fedora core 8) and finally runs the Postgres COPY command for each of the csv files. When the script runs the COPY commnand, I get the following error (for the genders table): ERRORC42501 M could not open file /home/ruby_process/genders.csv for reading: Permission denied Fcopy.c L1694 RCopyFrom (RuntimeError) My question is, what are the set of permissions required to perform a postgres copy? I've set the permissions on the directory so that the postgres group owns the directory and has read and execute permissions, as follows: drwxrwxr-x 3 ruby_process postgres I have also tried moving the files to /tmp and performing the copy from there, unsuccesfuly. Any ideas would be appreciated. Thanks, -Harold Are you connecting to the database as a superuser to run the COPY command? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Round integer division
On Friday 25 June 2010 3:53:01 pm Lee Hachadoorian wrote: Is it documented anywhere that floating-point numbers round scientifically, that is 0.5 rounds to the nearest even number? Compare: SELECT round(2.5::real), round(2.5::numeric), round(3.5::real), round(3.5::numeric); generates 2 | 3 | 4 | 4 I stumbled across this when I was trying to use round(a::real/b::real) to generate a rounded result to dividing integers, and noticed sometimes 0.5 was truncated and sometimes it was rounded up. Couldn't find anything about this in the archives or the data type documentation. Is there something obvious that I'm I missing? Thanks, -- Lee Hachadoorian PhD Student, Geography Program in Earth Environmental Sciences CUNY Graduate Center See here for explanation: http://archives.postgresql.org/pgsql-general/2010-03/msg00969.php -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] COPY command and required file permissions
On Friday 25 June 2010 6:07:35 pm Bruno Scovoli Santos wrote: * Are you connecting to the database as a superuser to run the COPY command? * Yes. This \copy command is in the database build script (create table blablabla). So it almost (I think) must have to be a superuser. But hey, my intent was just to guide the guy (Harold A. Giménez Ch.) to follow my practice (to use a preceding backslash). I dont bother to have to add that preceding backslash in my script. ;-) Actually my question was directed to Harold, I put my reply in the wrong place. In any case COPY and \copy are different commands. COPY is done from the server's perspective, \copy from the clients. Hence the permission issues will be different. See below for more detail. http://www.postgresql.org/docs/8.4/interactive/sql-copy.html http://www.postgresql.org/docs/8.4/interactive/app-psql.html -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] sorry, now with subject... trigger nextval(seq)
On Thursday 20 May 2010 5:53:51 pm tla...@gwdg.de wrote:
Hello all,
I'm new to triggers in PostgreSQL. I have to create a trigger on insert to
increment a sequence to overcome MS-Access' limitation in acknowledging
serial datatype.
Could anyone put me on right track? I was looking the whole day for
references on that... Years ago I quickly found a reference how to do it
in Oracle. However, I could not find anything that explained how to do
this in postgresql... I think, it should go the direction see below... But
how exactly :-/ ?
Many thanks for any help, Torsten
create table testtab (
pid bigint,
sometext text
);
create sequence test;
-- hmm something like this...?
CREATE FUNCTION count_up (varchar) RETURNS opaque AS '
DECLARE
pid_num bigint;
BEGIN
select into pid_num from select nextval($);
RETURN pid_num;
END;
' LANGUAGE 'plpgsql';
-- and how will the trigger looks like
???
You know serial is just a shortcut for:
pid int NOT NULL DEFAULT nextval('seq')
I think you will find Access will place nice if you use the long form to define
your autoincrement.
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Please delete my email
On 03/15/2010 10:18 AM, Eduardo Palafox wrote: Hi!, I don't want to receive more emails from postgresql. Please remove my email from your delivery list. Thanks _ Prefiero un día sin coche que sin Messenger www.vivirmessenger.com To unsubscribe go here: http://www.postgresql.org/mailpref/pgsql-sql Thanks, -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Remove my e-mail
On 03/15/2010 10:24 AM, Daniel Guedes wrote: Hi!, I don't want to receive more emails from postgresql. Please remove my email from your delivery list. Thanks To unsubscribe go here: http://www.postgresql.org/mailpref/pgsql-sql Thanks, -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plpgsql loop question
On 02/10/2010 08:29 AM, Andrea Visinoni wrote: hi, i have a table called zones: idzone, zone_name and several tables called zonename_records (same structure), where zonename is one of the zone_name in the zones table. What i want to do is a function that union all of this tables dinamically based on zones table, this is what i've done so far: CREATE OR REPLACE FUNCTION get_all_records() RETURNS SETOF record AS $BODY$DECLARE zones record; recs record; BEGIN for zones in select lower(zone_name) as n from zones loop for recs in select * from quote_ident(zones.n || '_records') loop return next recs; end loop; end loop; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; but i get this error! ERROR: wrong record type supplied in RETURN NEXT CONTEXT: PL/pgSQL function get_all_records line 9 at RETURN NEXT Andrea One thing I would do is rename your zones record variable. pgsql does not deal well with a variable having the same name as a schema object, in this case your table zones. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Schema's, roles and privileges
On Monday 30 November 2009 6:50:27 am Michael Gould wrote: I have a database with a schema called ISS. This is where all of our application definitions are stored. We did add 2 contribute modules (citext) and guid generator and both of these by default went to the public schema. It is our intent to not allow any access to public by our users. A few questions 1. Can I reinstall the contrib modules in the ISS schema only or do they need to be in the public schema 2. If they need to stay in the public schema and I don't want to give any insert, update, delete or select access to public, can I revoke those privileges and just give execute on the functions that were added by the contrib module. 3. If I can reinstall the contrib modules in the application schema, can I delete the public schema or does it still need to be there and I would just revoke all except for the superuser id which would be for our installer or tech support if needed. We have a separate userid for the security administrator. All of the functions that the security administrator needs are provided by a application module and they will not be directly accessing the database via a SQL utility at all. Best Regards -- Michael Gould, Managing Partner Intermodal Software Solutions, LLC 904.226.0978 904.592.5250 fax From a quick look it would seem the easiest solution would be to change the search_path in: citext.sql.in uuid-ossp.sql.in These files are found in the respective contrib directories. Uninstall the modules. Rerun make and then reinstall. From here: http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html There is nothing special about the public schema except that it exists by default. It can be dropped, too. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Sqldf - error message
On Friday 20 November 2009 6:43:53 am Pavel Stehule wrote: 2009/11/20 Tom Lane t...@sss.pgh.pa.us: Marvelde, Luc te l.temarve...@nioo.knaw.nl writes: If I run this SQL query: sqldf(SELECT + dbo_tbl_Terrein.RingCentraleNaam, + dbo_tbl_Broedsels.BroedselID + FROM ((dbo_tbl_BroedselLocatie + INNER JOIN dbo_tbl_Broedsels ON dbo_tbl_BroedselLocatie.BroedselID = dbo_tbl_Broedsels.BroedselID) + INNER JOIN dbo_tbl_Nestkasten ON dbo_tbl_BroedselLocatie.NestkastID = dbo_tbl_Nestkasten.NestkastID) + INNER JOIN dbo_tbl_Terrein ON dbo_tbl_Nestkasten.TerreinNummer = dbo_tbl_Terrein.TerreinNummer + WHERE (((dbo_tbl_Terrein.RingCentraleNaam)='Oosterhout a/d Waal'));) I get the following message: Error in sqliteExecStatement(con, statement, bind.data) : RS-DBI driver: (error in statement: no such column: dbo_tbl_Broedsels.BroedselID) It looks to me like sqldf is unaware of the rules about identifier case-folding in Postgres. That column would come back named broedselid, but it's probably looking for BroedselID. Or possibly it's expecting the qualifier dbo_tbl_Broedsels. to be included in the returned column name. Either way, you need to bug sqldf's authors to fix it. is it Postgres? I see Error in sqliteExecStatement It is more complicated than that see: http://code.google.com/p/sqldf/ It is a framework sqldf over a framework rsqlite over sqlite. The data frames the OP is talking about are variables that are coerced to being 'tables'. I can see no mailing list for sqldf itself. The closet I could come is https://stat.ethz.ch/mailman/listinfo/r-help which seems to have quite a few discussions on sqldf and would probably be the better place to ask this question. regards Pavel Stehule regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to order varchar data by word
On Tuesday 20 October 2009 6:39:23 am Oliveiros C, wrote: Hello, Tom. Thank you for your e-mail Even though I am not familiar with the exact procedure to switch to other locales, I'm gonna research this more deeply. Anyway, I would appreciate if you could spare me some time and give me a couple of pointers to this subject. Again, thank you Best, Oliveiros A good place to start is: http://www.postgresql.org/docs/8.4/interactive/locale.html Check out the Further Reading section at: http://www.postgresql.org/docs/8.4/interactive/multibyte.html#AEN30078 -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to order varchar data by word
On Wednesday 14 October 2009 7:13:22 am Oliveiros C, wrote: Hello, list. I have a table with a varchar field that I would like to order by word, not by ordinal, which seems to be the default on postgres. Does anyone have a clue on how this can be done? Many thanx in advance, Best, Oliveiros Can you show the SQL you are using? -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rules, functions and RETURNING
On Thursday 17 September 2009 8:35:52 am Nico Mandery wrote:
Hello list,
I am trying to wirte a rule which calls a PLPgSQL-function upon an
Insert in a table. Here is a somewhat simplified example of what i got
so far:
CREATE TABLE mytable (
mytable_id serial PRIMARY KEY,
something text
);
CREATE OR REPLACE FUNCTION _rule_insert_my(something text)
RETURNS integer AS
$BODY$
BEGIN
-- do something
return mytable_id;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
CREATE OR REPLACE RULE _insert AS
ON INSERT TO mytable DO INSTEAD SELECT
_rule_insert_my(new.something) AS mytable_id;
So far this works quite well. But I got a few situations where I need to
do a query which uses RETURNING to get the value of the newly generated
primary key. Like this one:
INSERT INTO mytable (something) VALUES ('some text') RETURNING mytable_id;
This breaks because I did not specify a RETURNING-Clause in the rule.
But how can specify RETURNING with SELECT?
Thank your in advance for your help.
regards,
nico
--
Nico Mandery
I am going to assume that '--do something' is more complicated then getting the
mytable_id. If that is the case why not create an INSERT function/trigger that
does the 'something' and then just do:
INSERT INTO mytable (something) VALUES ('some text') RETURNING mytable_id;
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] mail alert
On Wednesday 12 August 2009 6:27:25 am Jan Verheyden wrote: Hi, I got a bit stuck... I was looking for a solution for option a) Maybe I'll first explain the situation a bit more: I have one database for patient registration Another one for image storage And a third one for book keeping A patient should be registered first before the images are stored, so if there is a new line in the second database with an id which does not exist yet, it has to be notified in the book keeping database. Now the questions: 1) Can I do this with the inner join (tables subject_id from DB1, pat_id from DB2), there it is two different databases 2) Once it is notified in the book keeping that is not registered yet, is it best to poll on this column to send a warning, or use a trigger?? Thanks!! If at all possible, try to move all that information into schema's of one database. As it stands now you have a lot of moving parts to keep track of via external processes. It is possible but you lose transactional support and trust me that turns into a royal pain. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Bit by commands ignored until end of transaction block again
On Thursday 23 July 2009 12:39:23 am Glenn Maynard wrote: The ORM on a whole is decent, but there are isolated areas where it's very braindamaged--this is one of them. They have a stable-release API-compatibility policy, which I think just gets them stuck with some really bad decisions for a long time. -- Glenn Maynard None of the options listed in the URL below work?: http://docs.djangoproject.com/en/dev/topics/db/transactions/#topics-db-transactions This is the development version of the docs so may contain some new options. In particular look at Savepoint rollback and Database-level autocommit. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help combining 2 tables together
On Friday 22 May 2009 6:48:43 am Richard Ekblom wrote:
Hello
I have frequently encountered the need of combining two tables into one.
First, please take a look at the following table setups...
CREATE TABLE topics (
id SERIAL PRIMARY KEY,
topic TEXT NOT NULL
);
CREATE TABLE messages (
id SERIAL PRIMARY KEY,
topic INTEGER REFERENCES topics(id),
message TEXT NOT NULL
);
Example of a topics table:
IDTOPIC
1 Are squares better then circles?
2 My favorite food
Example of a messages table:
IDTOPICMESSAGE
1 2I like lasagna!
2 2Pizza is also a favorite
3 1I like circles, they remind me of pizza
Notice that the number of topics may differ from the number of messages.
Now I want to combine these tables with a single SELECT to get...
Combined table:
ID TOPIC MESSAGE
1My favorite foodI like lasagna!
2My favorite foodPizza is also a favorite
3Are squares better then circles?I like circles, they remind me
of pizza
I have seen different examples of this with something called JOIN but
they always give me only two rows. How can I do this when the two tables
may have different sizes to produce exactly the combined table above???
Some SQL for Postgres if you want to set up this example...
CREATE TABLE topics (id SERIAL PRIMARY KEY,topic TEXT NOT NULL);
CREATE TABLE messages (id SERIAL PRIMARY KEY,topic INTEGER REFERENCES
topics(id),message TEXT NOT NULL);
INSERT INTO topics(topic) VALUES('Are squares better then circles?');
INSERT INTO topics(topic) VALUES('My favorite food');
INSERT INTO messages(topic,message) VALUES(2, 'I like lasagna!');
INSERT INTO messages(topic,message) VALUES(2, 'Pizza is also a favorite');
INSERT INTO messages(topic,message) VALUES(1, 'I like circles, they
remind me of pizza');
SELECT * FROM topics;
SELECT * FROM messages;
Thanks in advance
/RE
test=# SELECT m.id,t.topic,m.message from topics as t,messages as m where
m.topic=t.id order by m.id;
id | topic | message
+--+--
1 | My favorite food | I like lasagna!
2 | My favorite food | Pizza is also a favorite
3 | Are squares better then circles? | I like circles, they
: remind me of pizza
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: Now I remember. Its something that trips me up, the RECORD in RETURN setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a better explanation- http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the same concept as a record variable, even though such a function might use a record variable to hold its result. In both cases the actual row structure is unknown when the function is written, but for a function returning record the actual structure is determined when the calling query is parsed, whereas a record variable can change its row structure on-the-fly. -- Adrian Klaver akla...@comcast.net For this particular case the following works. CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record AS $Body$ DECLARE croid integer; DECLARE R RECORD; BEGIN SELECT INTO croid 2; SELECT INTO R croid,$1; RETURN R; END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: Now I remember. Its something that trips me up, the RECORD in RETURN setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a better explanation- http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html# PL PGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the same concept as a record variable, even though such a function might use a record variable to hold its result. In both cases the actual row structure is unknown when the function is written, but for a function returning record the actual structure is determined when the calling query is parsed, whereas a record variable can change its row structure on-the-fly. -- Adrian Klaver akla...@comcast.net For this particular case the following works. CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record AS $Body$ DECLARE croid integer; DECLARE R RECORD; BEGIN SELECT INTO croid 2; SELECT INTO R croid,$1; RETURN R; END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver akla...@comcast.net Forgot to show how to call it. test=# SELECT * from test_function(1) as test(c1 int,c2 int); c1 | c2 + 2 | 1 (1 row) -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
- Peter Willis pet...@borstad.com wrote: Adrian Klaver wrote: On Wednesday 01 April 2009 4:31:20 pm Peter Willis wrote: Hello, I am having a problem with a FUNCTION. The function creates just fine with no errors. However, when I call the function postgres produces an error. Perhaps someone can enlighten me. --I can reproduce the error by making a test function --that is much easier to follow that the original: CREATE OR REPLACE FUNCTION test_function(integer) RETURNS SETOF RECORD AS $BODY$ DECLARE croid integer; BEGIN --PERFORM A SMALL CALCULATION --DOESNT SEEM TO MATTER WHAT IT IS SELECT INTO croid 2; --A SELECT STATEMENT OUTPUTS RECORDS (one in this case) SELECT croid,$1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE --The call looks like the following: SELECT test_function(1); --The resulting error reads as follows: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function test_function line 5 at SQL statement ** Error ** ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function test_function line 5 at SQL statement You have declared function to RETURN SETOF. In order for that to work you need to do RETURN NEXT. See below for difference between RETURN and RETURN NEXT: http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS Thank you for the pointer. I tried using FOR/RETURN NEXT as suggested but now get a different error: CREATE OR REPLACE FUNCTION test_function(integer) RETURNS SETOF record AS $BODY$ DECLARE croid integer; DECLARE R RECORD; BEGIN SELECT INTO croid 2; FOR R IN SELECT croid,$1 LOOP RETURN NEXT R; END LOOP; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE There is now an error : ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function test_function line 7 at RETURN NEXT ** Error ** ERROR: set-valued function called in context that cannot accept a set SQL state: 0A000 Context: PL/pgSQL function test_function line 7 at RETURN NEXT PostgreSQL doesn't seem to see 'R' as being a SET OF RECORD Peter Did you happen to catch this: Note that functions using RETURN NEXT or RETURN QUERY must be called as a table source in a FROM clause Try: select * from test_function(1) Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
On Thursday 02 April 2009 4:22:06 pm Peter Willis wrote: Adrian Klaver wrote: Did you happen to catch this: Note that functions using RETURN NEXT or RETURN QUERY must be called as a table source in a FROM clause Try: select * from test_function(1) I did miss that, but using that method to query the function didn't work either. Postgres doesn't see the result as a tabular set of records. Even if I replace the FOR loop with: quote FOR R IN SELECT * FROM pg_database LOOP RETURN NEXT R; END LOOP; /quote I get the same error(s). I don't think postgres likes the unrelated 'SELECT INTO variable [column] FROM [QUERY] LIMIT 1' lines before the FOR loop... I think I need to go back and approach the function from a different direction. Thanks for all the pointers. Peter Now I remember. Its something that trips me up, the RECORD in RETURN setof RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a better explanation- http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS Note that RECORD is not a true data type, only a placeholder. One should also realize that when a PL/pgSQL function is declared to return type record, this is not quite the same concept as a record variable, even though such a function might use a record variable to hold its result. In both cases the actual row structure is unknown when the function is written, but for a function returning record the actual structure is determined when the calling query is parsed, whereas a record variable can change its row structure on-the-fly. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] FUNCTION problem
On Wednesday 01 April 2009 4:31:20 pm Peter Willis wrote: Hello, I am having a problem with a FUNCTION. The function creates just fine with no errors. However, when I call the function postgres produces an error. Perhaps someone can enlighten me. --I can reproduce the error by making a test function --that is much easier to follow that the original: CREATE OR REPLACE FUNCTION test_function(integer) RETURNS SETOF RECORD AS $BODY$ DECLARE croid integer; BEGIN --PERFORM A SMALL CALCULATION --DOESNT SEEM TO MATTER WHAT IT IS SELECT INTO croid 2; --A SELECT STATEMENT OUTPUTS RECORDS (one in this case) SELECT croid,$1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE --The call looks like the following: SELECT test_function(1); --The resulting error reads as follows: ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function test_function line 5 at SQL statement ** Error ** ERROR: query has no destination for result data SQL state: 42601 Hint: If you want to discard the results of a SELECT, use PERFORM instead. Context: PL/pgSQL function test_function line 5 at SQL statement You have declared function to RETURN SETOF. In order for that to work you need to do RETURN NEXT. See below for difference between RETURN and RETURN NEXT: http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Fwd: Re: [SQL] Creating a function with single quotes
Memo to self: Remember hit reply all. -- Forwarded Message -- Subject: Re: [SQL] Creating a function with single quotes Date: Friday 20 February 2009 From: Adrian Klaver akla...@comcast.net To: Leif B. Kristensen l...@solumslekt.org On Friday 20 February 2009 6:13:03 am you wrote: On Friday 20. February 2009, Shawn Tayler wrote: Hello Jasen and the List, I tried the $$ quote suggestion: create function f_csd_interval(integer) returns interval as $$ BEGIN RETURN $1 * interval '1 msec' END; $$ LANGUAGE 'plpgsql'; Here is what I got: edacs=# \i 'f_csd_interval.sql' psql:f_csd_interval.sql:7: ERROR: syntax error at or near END LINE 1: SELECT ( $1 * interval '1 msec') END ^ QUERY: SELECT ( $1 * interval '1 msec') END CONTEXT: SQL statement in PL/PgSQL function f_csd_interval near line2 edacs=# The error at or near END is curious. There must be something wrong in the line before it but I can't see it. Suggestions? You should place a semicolon at the end of the RETURN line, and remove the one after END, BTW, simple functions as this are better written in the SQL language. I can't speak for the validity of the code itself, but you can rewrite it as create function f_csd_interval(integer) returns interval as $$ SELECT $1 * interval '1 msec' $$ LANGUAGE SQL; -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ Actually you need both semicolons. One after the RETURN statement and one after the END statement See below for full details: http://www.postgresql.org/docs/8.3/interactive/plpgsql-structure.html -- Adrian Klaver akla...@comcast.net --- -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to excute dynamically a generated SQL command?
On Saturday 03 January 2009 5:57:32 pm John Zhang wrote:
Hi the list,
Referring to the PostgreSQL 8.3 documentation 38.5.4. Executing Dynamic
Commands , the command for executing a dynamic command is:
EXECUTE command-string [ INTO [STRICT] target ];
I am to execute an sql statement created dynamically, which is represented
in a variable sSql.
Here is an example:
sSql='INSERT INTO hm_raster.hm_airphotos( file_ext, airphoto) VALUES
('.tif', lo_import( E''C:\\HM\\Data\\Flightmap.tif'');'
You are missing a ')' Should be
VALUES ('.tif', lo_import( E''C:\\HM\\Data\\Flightmap.tif''));';
EXECUTE sSQL;
It raises the error as:
ERROR: syntax error at end of input
LINE 1: ...E'C:\\HM\\Data\\Flightmap.tif')
^
I would appreciate a lot if you offer your input. Thanks a lot.
John
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] effectiveness tool
On Tuesday 14 October 2008 3:34:53 pm Judith Altamirano wrote: Hello everybody!! I just want to know if there be some tool to analize the performance of a query, I mean to qualify the effectiveness, speed, if I have the correct indexes. Hope somebody can help me Thanks in advanced!! Take a look at: http://www.postgresql.org/docs/8.3/interactive/sql-explain.html and: http://www.postgresql.org/docs/8.3/interactive/using-explain.html -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
-- Original message -- From: Christopher Maier [EMAIL PROTECTED] I have a table where some rows are originally entered, and others are logically deduced from original rows by triggers. For instance, if one row contains data indicating that A B and another row indicates B C, the triggers will generate a new row indicating that A C. All deduced rows have a boolean attribute (named deduced) that is TRUE only if the row was generated by such a deduction. A value of FALSE indicates that the row was original data, entered by a user. When original data is modified, the triggers are responsible for removing any deduced rows that are now invalid and generating new rows that are now implied. I would like to make it so that the only way that deduced rows can be deleted is through the actions of these triggers; I don't want a user inadvertently deleting a deduction when the underlying premises (the original rows that were used to generate the deduced rows) still imply that deduction is valid. Users should only be able to manipulate the original data. I can create a trigger that will prevent deletion of deduced rows easily enough, but I'm not sure how to let rows targeted for deletion by these deduction triggers through. Is there a way to pass some sort of state indicator into a trigger? Is this at all possible? Thanks in advance, Chris -- From the manual http://www.postgresql.org/docs/8.2/interactive/plpgsql-trigger.html: Row-level triggers fired BEFORE may return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). If a nonnull value is returned then the operation proceeds with that row value Could you have the the trigger examine the row to see if it meets the criteria for deletion. If it does RETURN a NON NULL value so the trigger completes, otherwise RETURN NULL to prevent the DELETE. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
On Friday 10 October 2008 11:25:05 am Christopher Maier wrote: On Oct 10, 2008, at 2:05 PM, Alvaro Herrera wrote: Looks like you should revoke DELETE privilege from plain users, and have your delete trigger be a security definer function. There would be another security definer function to delete non-deduced rows which users can call directly. Thanks, Alvaro. So you're suggesting I create a function like this: CREATE FUNCTION user_delete(identifier my_table.id%TYPE) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $$ BEGIN ... -- do various checks ... DELETE FROM my_table WHERE id = identifier; ... END; $$; Correct? That sounds like it would work. If at all possible, I'd like to keep the interface the same for all my tables, though (i.e., users don't have to be concerned with whether they can do regular SQL deletes, or if they have to call a special function). I suppose that can ultimately be hidden, though. I will try this approach and see how it works out. If there is any other way to achieve this goal, however, I would be interested to hear. Thanks again. --Chris A possible approach, not fully tested. REVOKE DELETE from normal users as suggested above. GRANT DELETE to privileged_user Semi psuedo-code below. CREATE OR REPLACE FUNCTION check_delete RETURNS TRIGGER AS $Body$ BEGIN IF current_user != 'privileged_user' AND old.deduced = 'f' THEN SET LOCAL ROLE 'privileged_user'; --Do your sanity checks and create DELETE statements RETURN OLD; ELSIF current_user != 'privileged_user' AND old.deduced ='t' THEN RETURN NULL; ELSIF current_user = 'priviliged_user' THEN RETURN OLD END; $Body$ LANGUAGE plpgsql; -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with delete trigger: how to allow only triggers to delete a row?
On Friday 10 October 2008 1:57:28 pm Adrian Klaver wrote: On Friday 10 October 2008 11:25:05 am Christopher Maier wrote: On Oct 10, 2008, at 2:05 PM, Alvaro Herrera wrote: Looks like you should revoke DELETE privilege from plain users, and have your delete trigger be a security definer function. There would be another security definer function to delete non-deduced rows which users can call directly. Thanks, Alvaro. So you're suggesting I create a function like this: CREATE FUNCTION user_delete(identifier my_table.id%TYPE) RETURNS VOID LANGUAGE plpgsql SECURITY DEFINER AS $$ BEGIN ... -- do various checks ... DELETE FROM my_table WHERE id = identifier; ... END; $$; Correct? That sounds like it would work. If at all possible, I'd like to keep the interface the same for all my tables, though (i.e., users don't have to be concerned with whether they can do regular SQL deletes, or if they have to call a special function). I suppose that can ultimately be hidden, though. I will try this approach and see how it works out. If there is any other way to achieve this goal, however, I would be interested to hear. Thanks again. --Chris A possible approach, not fully tested. REVOKE DELETE from normal users as suggested above. GRANT DELETE to privileged_user Oops the above is wrong. In testing I used a login role that automatically inherited the privileged role I was using below. Using a different login role showed me the error. Semi psuedo-code below. CREATE OR REPLACE FUNCTION check_delete RETURNS TRIGGER AS $Body$ BEGIN IF current_user != 'privileged_user' AND old.deduced = 'f' THEN SET LOCAL ROLE 'privileged_user'; --Do your sanity checks and create DELETE statements RETURN OLD; ELSIF current_user != 'privileged_user' AND old.deduced ='t' THEN RETURN NULL; ELSIF current_user = 'priviliged_user' THEN RETURN OLD END; $Body$ LANGUAGE plpgsql; The above would still work as long as the privileged role(user) was not assigned to normal users and the privileged role had DELETE rights to the table. Also the function would need to be created with the privileges necessary to do the SET ROLE. -- Adrian Klaver [EMAIL PROTECTED] -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] PL/pgSQL function syntax question?
-- Original message -- From: Ruben Gouveia [EMAIL PROTECTED] i get the following error when i try and create the following function: Basically, i am trying to have two different dates compared and only the most recent returned to me. This seems pretty straight forward, what I am doing wrong here? create or replace function fcn_pick_date(v_dt date) returns date as $$ DECLARE v_dt date; BEGIN for v_record in select last_periodic, last_boot from mediaportal loop if v_dt = v_record.last_boot then v_dt := v_record.last_periodic; else v_dt := v_record.last_boot; end if; end loop; return (v_dt); END; $$ LANGUAGE 'plpgsql'; ERROR: loop variable of loop over rows must be record or row variable at or near loop at character 195 ** Error ** ERROR: loop variable of loop over rows must be record or row variable at or near loop SQL state: 42601 Character: 195 You need to DECLARE v_record as a RECORD variable. v_record RECORD; -- Adrian Klaver [EMAIL PROTECTED] ---BeginMessage--- i get the following error when i try and create the following function:Basically, i am trying to have two different dates compared and only the most recent returned to me. This seems pretty straight forward, what I am doing wrong here? create or replace function fcn_pick_date(v_dt date) returns date as $$ DECLARE v_dt date; BEGIN for v_record in select last_periodic, last_boot from mediaportal loop if v_dt = v_record.last_boot then v_dt := v_record.last_periodic; else v_dt := v_record.last_boot; end if; end loop; return (v_dt); END;$$ LANGUAGE plpgsql;ERROR: loop variable of loop over rows must be record or row variable at or near loop at character 195** Error ** ERROR: loop variable of loop over rows must be record or row variable at or near loopSQL state: 42601Character: 195 ---End Message--- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem inserting data
On Friday 20 June 2008 5:20 am, Tk421 wrote: Hello everybody. I''ve just converted an access database to postgres. The conversion was ok, but when i'm trying to insert data on some tables with boolean types from a Visual Basic application i receive an error. A sample: table example, with firs field of integer, second of type text and third of type boolean the query: INSERT INTO EXAMPLE VALUES (1, 'text', 0) When i used access this worked fine, in boolean types 0 is false and 1 is true, but in postgres (8.1 and 8.3 version) i receive the next error: column premarco is of type boolean but expression is of type integer My question is, can i force postgres to accept integer values as boolean without changin query to INSERT INTO EXAMPLE VALUES (1, 'text', 0::boolean). This is a problem because if I have to do this i will have to test about one hundred queryes Thank you very much Two options: 1) Change the column in Postgres to an integer type. 2) Change the assignment in pg_cast from explicit to implied for the int4,bool cast. See for more details: http://www.postgresql.org/docs/8.3/interactive/catalog-pg-cast.html -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] cross-database references are not implemented
On Tuesday 03 June 2008 6:12 am, Paul Dam wrote: Hoi, I have a database with 2 schemas: - public - export In the export schema I have tables that are filled during an export process. There is some data I want to have in a table in the public schema as well. I wrote a trigger function that after insert in the export table does an export in the public table. If I do an insert I get the error message: ERROR: cross-database references are not implemented. How can I solve this? Met vriendelijke groet, Paul Dam You will need to show the actual query, but I suspect you have an extra period in your table name. Instead of public.table_name you have something like public.table.name. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Problem with now() in function pgsql
On Saturday 05 April 2008 10:46 am, [EMAIL PROTECTED] wrote: Hi, I have a problem in a function pgsql with Reading TIMESTAMP calling the function now() the problem is this: At the begin of function I read timestamp... select now() into TS_BEGIN; the function execute some statement... Many seconds after I reread the timestamp select now() into TS_END; and TS_BEGIN is identical TS_END!!! Can anyone help me? Thanks in advance. Luke. You want to use clock_timestamp() or equivalent not now(). As you found out now() fixes the time at the start of the transaction and does not change, clock_timestamp() does. See URL below for a more complete explanation: http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
FW: Re: [SQL] Question 2 Interval and timestamptz
-- Forwarded Message: --
From: [EMAIL PROTECTED] (Adrian Klaver)
To: Shawn [EMAIL PROTECTED]
Subject: Re: [SQL] Question 2 Interval and timestamptz
Date: Wed, 26 Mar 2008 16:24:39 +
-- Original message --
From: Shawn [EMAIL PROTECTED]
Hello!
The second part of my question is:
Given a value as an interval, see previous posting, is there a simply
method
to take a given timestamptz value and a given interval value and create the
sum or difference of the 2 in timestamptz format?
The scenario is that the afore mentioned tables, see previous post, in
addition to the duration varchar field, also have a field called
event_at_utc
which is a timestamptz type. I need to calculate the end time of the event
given the interval calculated from the duration (varchar) field.
Any all all help is greatly appreciated.
Shawn
select '2008-03-26 09:21:44':: timestamptz + (('134987'::int/1000)* interval '1
second');
?column?
2008-03-26 09:23:58-07
(1 row)
Forgot to Reply All.
--
Adrian Klaver
[EMAIL PROTECTED]
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
