Gary Stainburn wrote on 20.09.2013 18:30:
You need to define the primary key as deferrable:
create table skills_pages
(
sp_idserial not null,
sp_sequence integer not null,
sp_title character varying(80),
sp_narative text,
primary key (sp_id) deferrable
);
Cheers.
Gary Stainburn wrote on 20.09.2013 18:07:
I want to add a new page after page 2 so I try to increase the sequence number
of each row from page 3 onwards to make space in the sequence for the new
record. However, I get duplicate key errors when I try. Can anyone suggest
how I get round this.
Theodore Petrosky, 03.07.2013 15:41:
sorry, but you misunderstand. this is the correct behavior of SQL.
It is part of the specification to do this.
Not quite. The SQL standard requires folding to uppercase.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make
Dev Kumkar wrote on 03.07.2013 17:50:
Note that adding quotes for aliases will be blessed by PostgreSQL and
then those will be folded to upper case BUT this adds up to lot of
changes in the SQL layer.
I wonder why you need that. I never had the requirement for that.
Which driver/interface do
JORGE MALDONADO, 07.06.2013 15:58:
I need to insert records into a table where one value is fixed and 2 values
come from a SELECT query, something like the following example:
INSERT INTO table1 fld1, fld2, fl3
VALUES value1, (SELECT fldx, fldy FROM table2)
Is this valid?
Respectfully,
Wolfgang Meiners, 22.04.2013 12:19:
Is there a simpler way to check for overlapping timeintervals? I ask
this question, because i have more similar tables with similar layout
and would have to write similar functions again and again.
Do you have the possibility to upgrade to 9.2?
The range
Don Parris wrote on 24.02.2013 23:20:
With many thanks to Misa and others who helped out with my question
about working with hierarchical data, I have now written a blog post
on how I implemented the ltree module to solve my problem.
Emi Lu wrote on 07.01.2013 21:16:
Thanks a lot! I just noticed that my postgresql is 8.3(unnest
function is not there by default). Is there a way that I could
download and load only this function from somewhere?
Are you aware that 8.3 will be de-suppported as of next month?
You should
Hi,
I stumbled over this question on Stackoverflow
http://stackoverflow.com/questions/13564369/postgresql-using-column-data-as-pattern-for-regexp-match
And my initial reaction was, that this should be possible using regexp_matches.
So I tried:
SELECT *
FROM some_table
WHERE
So I tried:
SELECT *
FROM some_table
WHERE regexp_matches(somecol, 'foobar') is not null;
However that resulted in: ERROR: argument of WHERE must not return a set
Hmm, even though an array is not a set I can partly see what the problem is
(although given the really cool array
Pavel Stehule, 27.11.2012 13:26:
My question is: why I cannot use regexp_matches() in the WHERE clause, even
when the result is clearly an integer value?
use a ~ operator instead
So that means, regexp_matches cannot be used as an expression in the WHERE
clause?
Regards
Thomas
--
Mark Fenbers wrote on 29.10.2012 23:38:
Greetings,
I want to be able to select all data going back to the beginning of
the current month. The following portion of an SQL does NOT work,
but more or less describes what I want...
... WHERE obstime = NOW() - INTERVAL (SELECT EXTRACT (DAY FROM
Robert Buck, 02.10.2012 03:13:
So as you can probably glean, the tables store performance metric
data. The reason I chose to use k-v is simply to avoid having to
create an additional column every time a new metric type come along.
So those were the two options I thought of, straight k-v and
Matthias Nagel wrote on 29.09.2012 12:49:
Hello,
is there any way how one can store the result of a time-consuming calculation
if this result is needed more
than once in an SQL update query? This solution might be PostgreSQL specific
and not standard SQL compliant.
Here is an example of what
Matthias Nagel wrote on 29.09.2012 12:49:
Hello,
is there any way how one can store the result of a time-consuming calculation
if this result is needed more
than once in an SQL update query? This solution might be PostgreSQL specific
and not standard SQL compliant.
Here is an example of what
Tom Lane, 19.07.2012 16:52:
If you're using a reasonably recent version of PG, replacing the NOT IN
by a NOT EXISTS test should also help.
Thanks. I wasn't aware of that (and the NOT EXISTS does indeed produce the same
plan as the OUTER JOIN solution)
Now I was wondering if a DELETE
Sergey Konoplev, 20.07.2012 10:21:
On Thu, Jul 19, 2012 at 6:52 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Now I was wondering if a DELETE statement could be rewritten with the same
strategy:
Not at the moment. There have been discussions of allowing the same
table name to be respecified in
Hi,
(this is not a real world problem, just something I'm playing around with).
Lately I had some queries of the form:
select t.*
from some_table t
where t.id not in (select some_id from some_other_table);
I could improve the performance of them drastically by changing the NOT NULL
Gary Stainburn, 23.05.2012 11:47:
Here is a select to show the problem. There is one stock record and two tax
records. What I'm looking for is how I can return only the second tax record,
the one with the highest ud_id
select s_stock_no, s_regno, s_vin, s_created, ud_id, ud_handover_date from
Carlos Mennens wrote on 11.05.2012 21:03:
I have a problem in SQL I don't know how to solve and while I'm sure
there are 100+ ways to do this in ANSI SQL, I'm trying to find the
most cleanest / efficient way. I have a table called 'users' and the
field 'users_id' is listed as the PRIMARY KEY. I
Carlos Mennens wrote on 11.05.2012 21:30:
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
Carlos Mennens wrote on 11.05.2012 21:50:
On Fri, May 11, 2012 at 3:44 PM, Thomas Kellererspam_ea...@gmx.net wrote:
Use this:
alter table users
alter column users_id type integer using to_number(users_id, '9');
(Adjust the '9' to the length of the char column)
When you wrote
Carlos Mennens wrote on 11.05.2012 21:53:
Very good question and asked by myself to the original SQL author and
he explained while he didn't use the most efficient data types, he
used ones he felt would be more transparent across a multitude of
RDBMS vendors. So the answer is no, it would not
Marcel Ruff, 04.05.2012 12:25:
Hi,
is an alias name not usable in the where clause?
select EXTRACT(day from enddate::TIMESTAMP - old_enddate::TIMESTAMP) AS TAGE from
account_h where TAGE5;
ERROR: column tage does not exist
LINE 1: ... TAGE5 ...
You need to wrap the query:
select *
from (
Carlos Mennens wrote on 11.04.2012 19:50:
I'm trying to update a customer record in a table however I need to
change several values (cust_address, cust_contact, cust_email). My
question is how do I properly format this into one single command?
forza=# SELECT cust_id, cust_name, cust_address,
rverghese wrote on 08.02.2012 19:07:
I have a large table with about 60 million rows, everyday I add 3-4 million,
remove 3-4 million and update 1-2 million. I have a script that reindexes
concurrently a couple of times a week, since I see significant bloat. I have
autovac on and the settings are
Gera Mel Handumon, 17.01.2012 07:31:
What version of postgresql that the update compatibility below will be
implemented?
UPDATE COMPATIBILITY
UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id =
Adrian Klaver, 17.01.2012 16:19:
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
Marcin Mirosław wrote on 30.12.2011 12:07:
Would be nice to have an option in PostgreSQL something along the lines
of: 'abort-transaction-on-constraint-violation = false'
That option is called MySQL with MyISAM tables.
Not true.
Oracle and others (I believe at least DB2) behave such
John Fabiani wrote on 30.12.2011 15:26:
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
Manu T, 07.11.2011 08:18:
I am using this query in the procedure and i error is throwing as mentioned
below.and i want to convert the same oracle query into Postgresql.
ERROR--
*ERROR: syntax error at or near OVER
LINE 1: ...heme_id,d.value, d.dr_cr_flg , d.rule_id , RANK() OVER (part...
^
Emi Lu wrote on 21.10.2011 15:36:
Good morning,
Is there a way to temporally disabled foreign key constraints something like
SET FOREIGN_KEY_CHECKS=0
When population is done, will set FOREIGN_KEY_CHECKS=1
Thanks a lot!
Emi
You can define the FKs as DEFERRABLE INITIALLY IMMEDIATE.
Then at
Scott Swank, 18.10.2011 23:47:
I have a postgres 9.1 database up running, no problem. Purely in
terms of writing sql (ddl, dml pg/plsql), what tools are
recommended?
Coming from an Oracle world, I'm thinking of toad, sql developer, etc.
1. psql text editor of choice (if so, which one?)
2.
John Fabiani, 23.09.2011 04:49:
I need to find the max(date) of a field but I need that value later in my
query.
If I
select max(x.date_field) as special_date from (select date_field) from table
where ...)x
I get one row and column.
But now I want to use that field in the rest of the query
Hello all,
this is more a just curious question, rather than a real world problem.
We can combine several CTEs into a single select using something like this:
WITH cte_1 as (
select
),
cte_2 as (
select ...
where id (select some_col from cte_1)
)
select *
from cte_2;
But this
David Johnston, 20.09.2011 16:15:
I'm just wondering if this is intended behavioury, simply not (yet)
implemented or even invalid according to the standard? I didn't
find any reference that it's not allowed in the manual.
Regards Thomas
Try sticking the recursive keyword after the with if
Nicoletta Maia, 14.09.2011 10:30:
SELECT `X`.`consumer_id`, `X`.`move_date` , `X`.`history_timestamp` ,
MIN( `Y`.`history_timestamp` ) AS `start_time`
FROM
`Table` AS `X`
JOIN
`Table` AS `Y`
ON `X`.`consumer_id` = `Y`.`consumer_id`
Bruce Momjian, 20.07.2011 03:03:
Well, to_char() is based on Oracle's to_char(). How does Oracle handle
such a date?
Oracle throws an error for the above example:
SQL select to_date('20110231', 'MMDD') from dual;
select to_date('20110231', 'MMDD') from dual
*
ERROR
Bruce Momjian, 19.07.2011 00:02:
postgres= select to_date('20110231', 'mmdd');
to_date
2011-03-03
(1 row)
is there a way to have to_date() raise an exception in such a case?
it's possible the odd behaviour you get is required by some standard.
That would be
Hi,
I just noticed that to_char() will accept invalid dates such as 2011-02-31 and
adjust them accordingly:
postgres= select to_date('20110231', 'mmdd');
to_date
2011-03-03
(1 row)
is there a way to have to_date() raise an exception in such a case?
Regards
Thomas
--
Jasen Betts wrote on 18.07.2011 11:23:
postgres= select to_date('20110231', 'mmdd');
to_date
2011-03-03
(1 row)
is there a way to have to_date() raise an exception in such a case?
it's possible the odd behaviour you get is required by some standard.
That would be
Jose Ig Mendez, 13.07.2011 09:36:
Hi everybody,
I'm trying to compare in a sentence like this (using PostGres 8.3) :
select * from myTable where id_integer IN ('1,2,3,4')
I want to get the records which key id_integer is 1 or 2 or 3 or 4. the type od my
id, of course, is integer.
I've
Surfing wrote on 29.05.2011 09:38:
Hi all,
I need to write a function that totally empty a schema.
So I have written a TRUNCATE statement for each table and set to 0 each
sequence.
Btw, it could be good to execute a vacuum statement on each table, but from
within the function this is not
Nguyen,Diep T wrote on 12.05.2011 03:59:
Each ID can have different number of score counts.
My goal is to add column order, which shows the order of the values
in column date in descendant order for each property. The expected output
will look like this:
id | date |
Hi,
I'm playing around with 9.1beta1 and would like to create a table where one
column has a non-default collation.
But whatever I try, I can't find the correct name that I have to use.
My database is initialized as follows:
postgres=# select version();
version
Guillaume Lelarge wrote on 07.05.2011 14:02:
create table foo (bar text collate fr_FR) -- collation fr_FR for
encoding UTF8 does not exist
create table foo (bar text collate fr_FR.1252) -- collation fr_FR
for encoding UTF8 does not exist
create table foo (bar text collate fr_FR.UTF8) --
Tom Lane wrote on 07.05.2011 18:48:
Thomas Kellererspam_ea...@gmx.net writes:
My database is initialized as follows:
postgres=# select version();
version
PostgreSQL 9.1beta1, compiled by Visual
Pavel Stehule, 16.02.2011 11:50:
Try to use a standardized information_schema instead - these views are same on
PostgreSQL and Oracle.
Unfortunately they are not the same: Oracle does not support INFORMATION_SCHEMA
Regards
Thomas
--
Sent via pgsql-sql mailing list
Pavel Stehule, 16.02.2011 12:20:
Unfortunately they are not the same: Oracle does not support
INFORMATION_SCHEMA
sorry, I expected so all mature databases support it.
Yes, this is really hard to understand.
I would assume creating the INFORMATION_SCHEMA views based on the existing
Oracle
Piotr Czekalski, 27.01.2011 16:21:
Gentelmen,
I follow this thread and I don't exactly get an idea of yours, but
isn't is as simple as (example: table web.files contains one column
named fileurl ):
select row_number() over(), X.fileurl from (select fileurl from
web.files order by fileurl) X
Samuel Gendler wrote on 11.12.2010 04:23:
psql - not as advanced, doesn't have all the features SQL*Plus has.
On the other hand, it is at least capable of command history and readline
support.
Hmm, for me SQL*Plus does support command history, but this is getting
off-topic now...
ndias, 26.11.2010 17:22:
When doing a insert row with less columns mentioned in into table(col1,
col2, col3,... than the columns that exist on the table, on 1.10.1 it
returns an error saying INSERT has more expressions than target columns
(the error is translated so maybe the text is not exactly
Andreas wrote on 16.10.2010 05:23:
Hi,
how can I collect text-fields from multiple rows into one output row?
I'd like to do an equivalent to the aggregate function SUM() only for text.
The input is a select that shows among other things a numerical column where I
would like to group by.
The
Andreas Forø Tollefsen, 06.10.2010 13:11:
Hi.
I am trying to create a datefield using YEAR, MONTH and DAY fields of type
integer.
I tried this query, but it did not give good results:
select to_date(gwsyear::text || gwsmonth::text || gwsday::text, '-MM-DD')
FROM cshapes
You are missing
Greg Caulton, 10.09.2010 11:46:
delete from form_record_details where form_record_id in (select form_record_id
from forms where form_id= 40003656)
Seems fine at 1am.
However the subquery has a typo in it - there is no form_record_id in the forms
table
But rather than psql throwing an
Wes James, 28.07.2010 19:35:
I'm trying to do this:
select * from table where field::text ilike '%\_%';
but it doesn't work.
How do you escape the _ and $ chars?
The docs say to use \, but that isn't working.
( http://www.postgresql.org/docs/8.3/static/functions-matching.html )
The text
Jean-David Beyer, 14.07.2010 19:05:
I just looked them up in my data definitions. Dates are _stored_ as type
DATE NOT NULL
Very good ;)
Yes, if the data happen to be stored at all. But when a program
generates the dates dynamically and wants to produce queries from them,
it is easier to use
Jean-David Beyer wrote on 14.07.2010 14:37:
My dates are of the form -mm-dd and such.
Storing a date as a string is never a good idea.
And I want to do things like adding or subtracting days, months, or years to it
or from it.
Also the logical comparisons.
Which is all a piece of cake
Campbell, Lance, 13.07.2010 16:58:
I want to subtract to dates to know the number of days different.
Example:
01/02/2010 - 01/01/2010 = 1 day
08/01/2010 - 07/31/2010 = 1 day
How do I do this?
SELECT DATE '2010-02-01' - DATE '2010-01-01';
SELECT DATE '2010-08-01' - DATE '2010-07-31';
Srikanth Kata wrote on 02.07.2010 14:24:
When i am executing this query, i am facing the
select
s.*,a.actid,a.phone,d.domid,d.domname,d.domno,a.actno,a.actname,p.descr as
svcdescr from vwsubsmin s
inner join packages p on s.svcno=p.pkgno
inner join account a on a.actno=s.actno
inner join
John, 11.06.2010 16:17:
Hi,
I'd like to learn the use of window functions and did not find a tutorial
using google (postgres window function tutorial). I'm hoping someone has a
link.
There is one in the manual:
http://www.postgresql.org/docs/current/static/tutorial-window.html
Thomas
--
Tom Lane, 06.05.2010 00:51:
Thomas Kellererspam_ea...@gmx.net writes:
I'm trying to get the output of the to_char(date, text) method in German but I
can't get it to work:
I think you need 'TMMon' to get a localized month name.
regards, tom lane
Ah! Silly me. Now
Jasen Betts, 06.05.2010 11:57:
The manual says the value for lc_time is OS dependent and indeed set lc_time =
'German' does not work on Solaris.
Is there a way to get a list of allowed values for lc_time for a specific
installation?
man -k locale would be my starting point (for anything
Hi,
I'm trying to get the output of the to_char(date, text) method in German but I
can't get it to work:
My understanding is, that I need to set lc_time for the session in order to
change the language used by to_char(), but this does not seem to work for me:
postgres= select version();
Hi,
I'm playing around with functions returning result sets, and I have a problem
with the following function:
-- Create sample data
CREATE TABLE employee (id integer, first_name varchar(50), last_name
varchar(50));
INSERT INTO employee values (1, 'Arthur', 'Dent');
INSERT INTO employee
Tom Lane, 08.04.2010 10:59:
Thomas Kellererspam_ea...@gmx.net writes:
CREATE OR REPLACE FUNCTION get_employees(name_pattern varchar)
RETURNS TABLE(id integer, full_name text)
AS
$$
BEGIN
RETURN QUERY
SELECT id, first_name||' '||last_name
FROM employee
WHERE last_name
junaidmalik14 wrote on 03.04.2010 14:58:
Is there any alternative of mysql function COUNT(DISTINCT expr,[expr...]) in
postgres. We get error if we
write count like this count(distinct profile.id, profile.name, profile.age)
but it works well in mysql.
Reference url is given below
Snyder, James, 29.03.2010 18:33:
Hello,
Is there a way to configure Oracle’s SQL Developer to access a
PostgreSQL database?
Thanks,Jim
As others have pointed out, it's not possible.
The Postgres Wiki contains a list of GUI Tools that work with Postgres:
Snyder, James, 29.03.2010 18:25:
Thanks for all the dialog on this subject.
My version was derived from the postgreSQL's .jar file (specifically named
postgresql-8.4-701.jdbc4.jar) that I'm using. When I do the following:
select version()
I get the following:
PostgreSQL 8.3.6
Then you
Jayadevan M, 26.03.2010 07:56:
Thank you for setting that right. Apologies for not checking version.
The orginal poster stated that he is using 8.4, so that solution will work for
him.
Thomas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your
Snyder, James wrote on 25.03.2010 22:33:
I’m using PostgreSQL (8.4.701)
There is no such version.
The current version is 8.4.3
On a side note, Oracle allows the following syntax to achieve the above:
select count(*) over () as ROWCOUNT , first_name from people
The same syntax will work
Gary Chambers wrote on 21.12.2009 23:15:
The current maintainer is unsure about being able to do the right
thing and recompile the code after fixing the query.
Why not simply add the necessary GROUP BY?
Thomas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to
Tom Lane wrote on 30.10.2009 05:44:
select pg_get_functiondef('foo(int)'::regproc)
select pg_get_functiondef('foo(int4)'::regproc)
select pg_get_functiondef('foo(integer)'::regproc)
but each time I get the error: function foo(integer) does not exist
What am I missing?
You need to use
Mario Splivalo, 29.10.2009 17:51:
I looked at the information_schema.routines, to get information about
the functions in the database, but there doesn't seem to be a way to
extract the parameters information about functions? Where would I seek
for such information?
They are stored as an array
Hi,
I'm playing around with the new pg_get_functiondef() function but I can't get it to work when I need to specify the argument list.
select pg_get_functiondef('foo'::regproc)
works without problems.
However if I have e.g. foo(int) and foo(int, int) I can't get this to work.
I tried
the6campbells wrote on 29.09.2009 04:54:
2. Do you intend to remove the requirement to include the recursive
keyword - as other vendors allow
The standard *requires* the keyword.
As far as I can tell there are two DBMS that require it (Postgres, Firebird)
and two that don't (SQL Server and
Mario Splivalo wrote on 14.09.2009 16:20:
Have you considered refactoring so there's only one table?
Unfortunately I can't do that, due to the
object-relational-mapper-wrapper-mambo-jumbo.
You could still refactor that into one single table, then create two updateable
views with the names
Ray Stell wrote on 12.08.2009 20:19:
http://www.brentozar.com/archive/2009/04/getting-the-most-recent-record/
How this works? What is ttNewer? What is a clustered primary key in mysql?
That article talks about SQL Server not MySQL.
select tt.* FROM TestTable tt
LEFT OUTER JOIN TestTable
Chris, 23.07.2009 09:06:
psql -d dbname
..
# select now();
now
---
2009-07-23 17:04:21.406424+10
(1 row)
Time: 2.434 ms
(csm...@[local]:5432) 17:04:21 [test]
# savepoint xyz;
ERROR: SAVEPOINT can only be used in transaction blocks
Philippe Lang, 10.07.2009 11:10:
Hi,
I'm playing with the new WITH RECURSIVE feature of 8.4. I'm trying to
figure out how to use it with trees.
Here is the test code I use:
I'd like to perform a real recursion, and show the tree structure in a
more appopriate way, like this:
Any idea how to
Steve Crawford wrote on 01.07.2009 00:39:
canon=# select count(maf) from gallo.sds_seq_reg_shw
canon-# where maf ISNULL;
I believe count will only count not-null
Correct
SELECT count(some_col)
FROM some_table;
is the same as
SELECT count(*)
FROM some_table
WHERE some_col IS NOT NULL;
Nicholas I, 29.04.2009 08:39:
Hi,
can anybody me suggest me, how to compare two tables of different
database.
Do you want to compare the data or the structure of the two tables?
Thomas
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
Marco Lechner, 12.03.2009 13:59:
Hi list,
I'm searching for a way to create permanent alias for
tablenames in postgresql. We are storing various versions
of a routable network in postgresql (postgis, pgrouting)
and access a certain version with a bunch of php-skripts.
We like to use aliases for
Marco Lechner, 12.03.2009 15:26:
Hi Mina,
thanks for your answer. I thought about that, but don't
views decrease performance, because they are calculated
on access?
I'm not sure what you mean with calculated. A view is just a SQL query.
There is no difference in executing the SQL query
ivan marchesini wrote on 09.12.2008 11:11:
Hi to all...
I need to create a db that contain link to some pdf files..
At the moment these are simple links (to the files that are stored into
the file system) storing paths into a column of a dbf table...
I need to manage this data considering that
Patrick Scharrenberg, 04.08.2008 17:51:
Hi!
I have to do much inserts into a database where the key most often is
already there.
My current approach is to query for the key (ip-address), and if the
result is null I do the insert.
For every IP-Address I need the ip_addr_id from the same table.
Hi,
I am using xpath_table to convert elements from an XML column to rows.
Now according to
http://www.postgresql.org/docs/8.3/static/xml2.html
this function will be removed in a future version.
That chapter also claims that the new XML syntax covers the functionality of
the xml2 module,
Thomas Mueller wrote on 26.04.2008 18:32:
Literals can still be used when using query tools, or in applications considered 'safe'.
I fail to see how the backend could distinguish between a query sent by a query
tool and a query sent by an application.
Thomas
--
Sent via pgsql-sql mailing
Nacef LABIDI, 22.04.2008 11:54:
Hi all,
I am using Postgres in a Delphi application through ODBC. I am having an
issue with updating records.
When I create a dataset to get the records in a table then after I
update one of these records and then refresh the dataset, the record
goes to the
Hi,
I'm playing around with putting a hierarchy of items into the database. But for
some reason I'm having a mental block understanding the following:
I have a table category with id and parent_id implementing the typical
adjacency model.
To get the first two levels of the hierarchy I use:
hubert depesz lubaczewski, 21.04.2008 16:05:
ROOT, 1, NULL
CHILD1, 2, 1
CHILD2, 3, 1
I would have expected the following result:
ROOT, NULL
ROOT, CHILD1
ROOT, CHILD2
but the row with (ROOT,NULL) is not returned.
why would you expect it?
the columns are: parent and child (on your output).
Steve Midgley wrote on 06.02.2008 21:33:
Hi,
I see this documentation item but can't figure out how to use it:
http://www.postgresql.org/docs/8.2/interactive/infoschema-columns.html
The view columns contains information about all table columns (or view
columns) in the database.
select
Hi,
I have a column with the datatype text that may contain leading whitespace
(tabs, spaces newlines, ...) and I would like to remove them all (ideally
leading and trailing).
I tried
SELECT regexp_replace(myfield, '\A\s*', '')
FROM mytable;
(for leading whitespace, to start with)
But it
Andreas Kretschmer wrote on 28.10.2007 12:42:
I have a column with the datatype text that may contain leading
whitespace (tabs, spaces newlines, ...) and I would like to remove them all
(ideally leading and trailing).
You can use trim() for that:
select 'x' || trim(both '\t' from trim(both '
Andreas Kretschmer wrote on 28.10.2007 13:32:
But it seems my problem was actually caused by something else:
SELECT regexp_replace(myfield, '\s*', '', 'g')
FROM mytable;
you should escape the \, change to ...'\\s*'...
Ah! Didn't think this was necessary, as \t or \n did not need to be
Jon Collette wrote on 21.08.2007 23:26:
Is it possible to run an insert,update, or delete and have it not launch
a trigger like it normally would?
For example could I set a value
DONOTRUN = True;
insert into contacts
Where the trigger on contacts would call a function that would have an
Karthikeyan Sundaram wrote on 18.02.2007 09:15:
Hi,
I am new to postgres. I need some kind of template script or advise
on how to analyse the indexes. In our database, we do delete, insert,
update tons of rows.
http://www.postgresql.org/docs/8.2/interactive/routine-reindex.html
Paul Lambert wrote on 06.02.2007 23:44:
Sort on Weenblows is a bastard to work with, and I don't believe it has
a unique option. I probably should have mentioned this was on Weenblows.
You can get all (or most?) of the *nix/GNU commandline tools for Windows as
well. As native Win32
Rodrigo Sakai wrote on 02.10.2006 18:39:
Hi all,
I need to get all sequences and their respective current values! Is there
any catalog table or any other away to get this???
Quote from the manual at:
http://www.postgresql.org/docs/8.1/static/catalog-pg-class.html
The catalog
Bath, David wrote on 24.11.2005 23:57:
While I can happily create rules on views to allow inserts, updates
and deletes, I can't find a GUI front-end that understands that the
view allows record edits that I can run on linux (whether through X
or web-based doesn't matter) and simply open the
1 - 100 of 108 matches
Mail list logo
