Re: [SQL] Unique index and unique constraint

2013/7/27 Alvaro Herrera alvhe...@2ndquadrant.com PostgreSQL implements unique constraints by way of unique indexes (and it's likely that all RDBMSs do likewise). Also, the syntax to declare unique indexes allows for more features than the unique constraints syntax. For example, you can

Re: [SQL] transactions and ecpg

Hey Wayne 2012/9/22 Wayne Cuddy lists-pg...@useunix.net I'm looking at some code that a coworker ported from Informix to Postgres 9.0. The Informix database did not have transaction logging enabled thus no begins/aborts/commits/rollbacks where in the original code. The way the code has been

Re: [SQL] Finding Max Value in a Row

2012/5/14 Tom Lane t...@sss.pgh.pa.us Carlos Mennens carlos.menn...@gmail.com writes: I'm not understanding why I'm not able to change this column type from char to integer? forza=# ALTER TABLE customers ALTER COLUMN cust_id TYPE integer; ERROR: column cust_id cannot be cast to type

Re: [SQL] array dimensions, pg_catalog.pg_attribute and the \d command

Hey Achilleas, 2012/2/3 Achilleas Mantzios ach...@matrix.gatewaynet.com Hello, just a question regarding multidimensional arrays. When i create an array, its number of dimensions is recorded in pg_catalog.pg_attribute (attndims), but they are not enforced upon insertion, nor is this

Re: [SQL]How to transform table rows into Colum?

Hey Eric, 2011/3/9 Eric Ndengang eric.ndengang_fo...@affinitas.de Hi Guys, I have the following table: Name Value rang salary name1 value1 12500 name2 value2 22600 name3 value 3 3300 and want to obtain the following result:

Re: [SQL] Retrieve the column values of a record without knowing the names

Hey, 2011/2/16 arthur_info arthur_i...@yahoo.com.br Hello, I've got the following function and I want to access the fields values of my record by index. The problem is that my select is retrieving each record line with all values and not each one of each row on my view... How can I solve

Re: [SQL] Is it possible to get DISTINCT rows from RETURNING clause?

Hey Gnanakumar, You can wrap you UPDATE query into SQL function returning TABLE, e.g: CREATE OR REPLACE FUNCTION public.update_mytable() RETURNS TABLE(email text, column1 text, column2 text, column3 text) LANGUAGE sql AS $function$ UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE

Re: [SQL] Get postgresql server ip address

2011/2/8 Gera Mel Handumon gerame...@gmail.com Hello, Is it possible to get postgresql server ip address using sql command? Oh yes, use inet_server_addr() function. Please note, that NULL is returned if connection established via socket. TIA, Geramel

Re: [SQL] why does seq scan instead of index scan

Hey, To avoid creation of custom GiST-index you can use hstore, which supports GiST and GIN indexes. I propose you to add hstore column to the table and write trigger function which will generate hstore from array on insert or update. Or you can use hstore column instead of array. Please, see

Re: [SQL] Correct usage of FOR UPDATE?

Hey Joe, You can wrap the given query in the PL/pgSQL function, catch the exception and return NULL if it occurs. Please see http://www.postgresql.org/docs/9.0/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING 2010/12/9 Joe Carr joe.c...@gmail.com Hello, I have a simple

[SQL] Fwd: Extended query protocol and exact types matches.

Hey sql@, -- Forwarded message -- From: Dmitriy Igrishin dmit...@gmail.com Date: 2010/12/9 Subject: Extended query protocol and exact types matches. To: pgsql-gene...@postgresql.org Hey general@, To be assured and just for calmness. Problem: 1. CREATE TABLE test_tab (id

Re: [SQL] concatenate question

Hey Tony, Because there is no implicit conversion to text. 2010/12/8 Tony Capobianco tcapobia...@prospectiv.com Ok, that worked. Why did I need to cast both as text though? plsql_dw=# select memberid::text||addeddate::text from tmpsv_parent_master limit 5; ?column?

Re: [SQL] PostgreSQL array, recursion and more

Hey Ferruccio, Also consider ltree contrib module as alternative. http://www.postgresql.org/docs/9.0/static/ltree.html ;-) 2010/11/17 Ferruccio Zamuner nonsolos...@diff.org MESH Data Tree: example: Hallux;A01.378.610.250.300.792.380 where: A01 is Body Regions A01.378 is Extremities

Re: [SQL] Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause

Hey Axel, How about this solution: UPDATE foo SET x = y WHERE ANY(string_to_array(blah, ',')) = id; ? 2010/10/25 Axel Rau axel@chaos1.de Good morning, I have a function argument blah of type text containing something like 33,44,55,66 . Can I cast it in some way to use it in an IN

Re: [SQL] Using PL/pgSQL text argument in 'IN (INT,INT,...)' clause

Ooops, sorry UPDATE foo SET x = y WHERE id = ANY(string_to_array(blah, ',')::integer[]); 2010/10/29 Dmitriy Igrishin dmit...@gmail.com Hey Axel, How about this solution: UPDATE foo SET x = y WHERE ANY(string_to_array(blah, ',')) = id; ? 2010/10/25 Axel Rau axel@chaos1.de Good

Re: [SQL] Random sort with distinct

Hey Ozer, How about dynamic queries? 2010/10/2 Ozer, Pam po...@automotive.com I have the following query Select Distinct VehicleMake, VehicleModel From VehicleYearMakeModelTrim Order by random() Limit 10; I don’t want to bring back the random number I just want the sort order to

Re: [SQL] Question about PQexecParams

Hey Kenneth, There are three benefits: - reduces the CPU overhead in both the client and the DB server for converting to/from ASCII numbers You solution based on PL/pgSQL function. I am doubt that execution of PL/pgSQL parser (loadable module, which aren't built into the PostgreSQL server)

Re: [SQL] Question about PQexecParams

Hey Kenneth, Thank you for solution. But sorry, personally, I don't clearly understand the benefits of this code compared with using simple array literals or even array constructors... Conversion overheads from text? Doubtfully... -- // Dmitriy.

Re: [SQL] Jagged/ragged arrays

Hey Craig, Florian Ahh, this. Why not just yes NULLs as values of array? 2010/9/21 Craig Ringer cr...@postnewspapers.com.au On 21/09/2010 3:42 AM, Dmitriy Igrishin wrote: Hey Florian, What do you mean by ragged arrays? At a guess: craig= SELECT '{ {1}, {1,2}, {1}, {1,2,3} }'::integer

Re: [SQL] Jagged/ragged arrays

Hey Florian, What do you mean by ragged arrays? -- Regards, Dmitriy

Re: [SQL] Question about PQexecParams

Hey Steve, 2010/9/11 Steve stev...@gmx.net Hello list, I would like to call a function from my C application by using libpq and PQexecParams. My problem is that I don't know how to specify that I want to send an array to the function. Assume the function is called lookup_data and

[SQL] Boolean output representation.

Hey all, As is known, there are many valid literal values for the true and false state of boolean data type. True whether that output of boolean type shows only using the letters 't' and 'f' or its possible to change the output representation, e.g., to true or false (1, 0)? Regards, Dmitriy

Re: [SQL] Boolean output representation.

Hey Pavel, Thanks for you answer ! And many thanks to developers because my task a bit simplified because of that :) Regards, Dmitriy 2010/8/26 Pavel Stehule pavel.steh...@gmail.com Hello 2010/8/26 Dmitriy Igrishin dmit...@gmail.com: Hey all, As is known, there are many valid literal

[SQL] libpq

Hey all, Is it guaranteed that functions, which accept PGconn* properly works if PGconn* is NULL (or just 0) and returns in such cases the same values as for not-NULL PGconn* ? If so, I think it should be documented ?! Regards, Dmitriy

Re: [SQL] how to escape _ in select

Hey James, Because in future releases the default value of the standard_conforming_strings parameter will change to on for improved standards compliance. You should use string constants with C-Style escapes. Please, see

Re: [SQL] howto delete using a join ?

Hey Andreas, You may want to use the USING clause: DELETE FROM events USING event_types WHERE events.event_type_fk = event_types.event_type.id AND relevance_level IN ( 1, 3, 5, 7) AND ANY_ANOTHER_CONDITION_YOU_ NEED ; Regards, Dmitriy

[SQL] How oids of the base data types often change?

Hey all, For example, when using libpq(-xx), it is possible to get the oid of the type of any column. Is there a guarantee that oids of base types (void, integer, ... ) will not change in future releases of Postgres? Regards, Dmitriy

[SQL] Question about domains.

Hey all, Is there a way to add constraint to the domain that used by a composite type that used by a table? E.g.: CREATE DOMAIN superid AS integer; CREATE TYPE idtype AS ( id superid ); CREATE TABLE mytab (id idtype NOT NULL); ALTER DOMAIN superid ADD CONSTRAINT superid_check CHECK (VALUE

[SQL] Domains based on composite types.

Hey all, Is there are way to create domain based on a composite type? Regards, Dmitriy

[SQL] Emacs sql-postgres (please, sorry for question not about PostgreSQL).

load it from file directly from psql. I think, the problem with Emacs buffers... Please, help! And please sorry, for question not about PostgreSQL... Regards, Dmitriy Igrishin

[SQL] DROP RULE ... CASCADE

Hey all, What objects may depend on the rule (and which of them will removed via cascade rule deletion)? Regards, Dmitriy Igrishin

[SQL] SQL code beautifier for PostgreSQL.

Hey all, Is there any good SQL code beautifier for PostgreSQL to use with Emacs or other text editors? Unfortunately, pg_dump(1) does not format views and rules... It would be nice if we could format unreadable views and rules definitions in favorite editor... Regards, Dmitriy Igrishin

[SQL] Selecting names of indexes that are not dependent on constraints.

Hey all, How can I select names of indexes of table (or even whole schema) that are not dependent on constraints? Unfortunately there is no way to select only independent indexes from pg_indexes view. How can I do it? Regards, Dmitriy Igrishin

Re: [SQL] Selecting names of indexes that are not dependent on constraints.

Of course, I meant the indexes of which constraints do not depend. :) 2010/3/11 Dmitriy Igrishin dmit...@gmail.com Hey all, How can I select names of indexes of table (or even whole schema) that are not dependent on constraints? Unfortunately there is no way to select only independent

Re: [SQL] create user xxx with md5 string?

Hello all. Tom right. Encrypted MD5 passwords computed as md5(password || test10). I checked. Regards, Dmitriy Igrishin. 2009/12/3 Tom Lane t...@sss.pgh.pa.us Gerardo Herzig gher...@fmed.uba.ar writes: Hi all. Im triyng, with no success, to create a user, and set the password with a md5

Re: [SQL] create user xxx with md5 string?

Sorry for my prev post. I mean md5(password || username). 'test10' is a username on which i checked. :) Sorry! 2009/12/3 Dmitriy Igrishin dmit...@gmail.com Hello all. Tom right. Encrypted MD5 passwords computed as md5(password || test10). I checked. Regards, Dmitriy Igrishin. 2009/12/3

Re: [SQL] How to get the previous date?

Hello. current_date - 1 is the solution. 2009/10/14 Shruthi A shruthi.i...@gmail.com Hi, I'm using Enterprise DB PostgresPlus version 8.3. Is there is a simple function or command like previous_date(mydate) which when passed a particular date, simply returns the previous date? I tried

Re: [SQL] ERROR: null value in column id violates not-null constraint

Hello. Note, that you may use SERIAL data type and PostgreSQL will implicitly create sequence for you column, for example, CREATE table test ( id SERIAL NOT NULL PRIMARY KEY, -- PostgreSQL will implicitly create 'test_id_seq' dat text ); Regards, Dmitiy Igrishin

Re: [SQL] ERROR: null value in column id violates not-null constraint

manual editing of the dump file to remove syntax not understood by the older server. Regards, Dmitry Igrishin 2009/10/11 Robert Paulsen rob...@paulsenonline.net On Sunday 11 October 2009 3:32 am, Dmitriy Igrishin wrote: Hello. Note, that you may use SERIAL data type and PostgreSQL

Re: [SQL] select result into string's array

Hello. You should use an array constructor: DECLARE m_array text[]; [..] BEGIN FOR m_array IN SELECT ARRAY[col_1, col_2, col_N] FROM my_table LOOP [..] END LOOP; Regards, Igrishin Dmitriy. 2009/10/9 Alberto Asuero Arroyo albertoasu...@gmail.com Hi, I need to store the result of