Re: [SQL] datestyle setting
Kenneth Gonsalves wrote: On Wednesday 09 Mar 2005 11:57 am, Tom Lane wrote: If it's a reasonably recent version of PG, either ALTER DATABASE SET or ALTER USER SET might serve. postgresql 7.4. when i type: alter database set datestyle to 'European' i get 'syntax error at or near "datestyle" at character 20' I keep making this mistake, you need to include the database name: ALTER DATABASE my_db_name SET datestyle TO 'ISO'; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] interval +variable
Dear all Is there anyone at there who knows howto use dateadd in pgsql. I have a problem in my function that is like this ; Select into futuredate now() + interval '30 days'; <- this is ok but how can I use a variable intead of '30 days':( Thanks ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] interval +variable
On Wed, Mar 09, 2005 at 10:58:05AM +0200, Fatih Cerit wrote: > Is there anyone at there who knows howto use dateadd in pgsql. I have a > problem in my function that is like this ; > > Select into futuredate now() + interval '30 days'; <- this is ok > but how can I use a variable intead of '30 days':( Are you looking for something like this? ndays := 30; SELECT INTO futuredate now() + ndays * interval'1 day'; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] datestyle setting
On Wednesday 09 Mar 2005 1:40 pm, Richard Huxton wrote: > > I keep making this mistake, you need to include the database name: >ALTER DATABASE my_db_name SET datestyle TO 'ISO'; er ... doesnt seem to be in the docs? Anyway it worked, thanx -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.sourceforge.net àà à! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] datestyle setting
Kenneth Gonsalves wrote:
On Wednesday 09 Mar 2005 1:40 pm, Richard Huxton wrote:
I keep making this mistake, you need to include the database name:
ALTER DATABASE my_db_name SET datestyle TO 'ISO';
er ... doesnt seem to be in the docs? Anyway it worked, thanx
http://www.postgresql.org/docs/7.4/static/sql-alterdatabase.html
Synopsis
ALTER DATABASE name SET parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name RESET parameter
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] [ADMIN] Postgres schema comparison.
|-Original Message- |From: Goulet, Dick [mailto:[EMAIL PROTECTED] |Sent: Montag, 07. März 2005 16:33 |To: John DeSoi; Stef |Cc: pgsql-ADMIN@postgresql.org; pgsql-sql@postgresql.org |Subject: Re: [SQL] [ADMIN] Postgres schema comparison. | | | My favorite for this task is WinSql available from |http://www.synametrics.com/SynametricsWebApp/WinSQL.jsp. It |can compare |the structure and content of the two tables. And will it also generate DIFF-SQL-Scripts to make a target-DB look like a MasterDB? Do you know? How about Structural Changes as adding a column? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] order by question
Hi folks. I seem to remember somewhere being shown how to bump specific rows to the top of a list; something along the lines of: select c_id as key, c_des as value from customers order by c_id = 7, c_id = 160, value; however, although the statement is accepted the two rows specified are not bumped to the top of the list, but instead appear in their correct position in the order by value part. Is it possible and if so how do I do it? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] order by question
Gary Stainburn wrote: Hi folks. I seem to remember somewhere being shown how to bump specific rows to the top of a list; something along the lines of: select c_id as key, c_des as value from customers order by c_id = 7, c_id = 160, value; Looks roughly right. SELECT * FROM foo ORDER BY not(a=6),not(a=4),a; a | b | c ---+--+- 6 | ccc | BBB 4 | aaa | BBB 1 | aaa | AAA 2 | zxxx | AAA 3 | ccc | ZZZ 5 | zxxx | BBB (6 rows) Alternatively: (a<>6),(a<>4),a -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] order by question
O Gary Stainburn έγραψε στις Mar 9, 2005 : > Hi folks. > > I seem to remember somewhere being shown how to bump specific rows to > the top of a list; something along the lines of: > > select c_id as key, c_des as value from customers order by c_id = 7, > c_id = 160, value; use the case ... when .. then ... when ... then ... else ... construct. > > however, although the statement is accepted the two rows specified are > not bumped to the top of the list, but instead appear in their correct > position in the order by value part. > > Is it possible and if so how do I do it? > -- -Achilleus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Table like a field
Hello.
Is there any way to build a table that contain the coluns name for the other
table fields? like this:
create table people(id serial primary key, name varchar(50) );
create table people_fields ( field_name varchar(30) );
insert into people_fields values ('occupation');
insert into people_fields values ('address');
then I create any function or view to get:
SELECT * FROM people; //may return
id - name - | ocuppation - address |
Then if I insert a new record in the people_fields table, the new record will
appear as a new field in the people table.
Of course, its not a new field, but when i select by my function/view i can see
anything like it.
How can I create this function to aggregate the both tables??
Thank you.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [SQL] interval +variable
On Wed, Mar 09, 2005 at 10:58:05 +0200, Fatih Cerit <[EMAIL PROTECTED]> wrote: > Dear all > > Is there anyone at there who knows howto use dateadd in pgsql. I have a > problem in my function that is like this ; > > Select into futuredate now() + interval '30 days'; <- this is ok > but how can I use a variable intead of '30 days':( A better solution may to be to use the date type instead of the timestamp type. Then you can just add an integer number to the date. You also don't have to worry about daylight savings time changes. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] order by question
On Wednesday 09 March 2005 1:06 pm, you wrote: > Gary Stainburn wrote: > > Hi folks. > > > > I seem to remember somewhere being shown how to bump specific rows > > to the top of a list; something along the lines of: > > > > select c_id as key, c_des as value from customers order by c_id = > > 7, c_id = 160, value; > > Looks roughly right. > > SELECT * FROM foo ORDER BY not(a=6),not(a=4),a; > a | b | c > ---+--+- > 6 | ccc | BBB > 4 | aaa | BBB > 1 | aaa | AAA > 2 | zxxx | AAA > 3 | ccc | ZZZ > 5 | zxxx | BBB > (6 rows) > > Alternatively: (a<>6),(a<>4),a Although this does exactly what I want, at first glance it should do exactly the oposite. I'm guessing that for each line it evaluates not (a=6) 0 for true else 1 not (a=4) 0 for true else 1 everything else -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] order by question
On Wed, Mar 09, 2005 at 12:41:55 +, Gary Stainburn <[EMAIL PROTECTED]> wrote: > Hi folks. > > I seem to remember somewhere being shown how to bump specific rows to > the top of a list; something along the lines of: > > select c_id as key, c_des as value from customers order by c_id = 7, > c_id = 160, value; > > however, although the statement is accepted the two rows specified are > not bumped to the top of the list, but instead appear in their correct > position in the order by value part. Are you sure? It looks like you are going to have them appear at the bottom of the list doing the above. Remember that false sorts before true. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Table like a field
[EMAIL PROTECTED] wrote:
Hello.
Is there any way to build a table that contain the coluns name for the other
table fields? like this:
create table people(id serial primary key, name varchar(50) );
create table people_fields ( field_name varchar(30) );
insert into people_fields values ('occupation');
insert into people_fields values ('address');
then I create any function or view to get:
SELECT * FROM people; //may return
id - name - | ocuppation - address |
You'll want to search the mailing-list archives for "crosstab", and also
look in the contrib/ section of the source distribution.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] order by question
Gary Stainburn <[EMAIL PROTECTED]> writes: > > Alternatively: (a<>6),(a<>4),a > > Although this does exactly what I want, at first glance it should do > exactly the opposite. > > I'm guessing that for each line it evaluates > not (a=6) 0 for true else 1 Not really, "not a=6" is an expression that evaluates to a boolean, true or false. true sorts as "greater" than false. That order is counterintuitive but it's because the default sort order is ascending. So the "lesser" false records appear first. If you put "not a=6" in your select column list you'll see the true and false values appear. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] [SOLVED] Postgres schema comparison.
Hi all,
If anyone is interested, here's the final solution
that I'm using to build a list of tables and their md5sums
based on what the psql interface queries when you do '\d [TABLE NAME]'
I attached the function I created, and this is the SQL I run :
select relname||':'||get_table_checksum(relname) from pg_class where relkind =
'r' and relname not like ('pg_%') and relname not like ('sql_%') order by
relname;
This gives the same result for a specific table across all versions of
postgres >= 7.3,
and runs for a minute or so for +- 450 tables on my machine.
It may break if you have some exotic definitions that I didn't test for,
but I think it's pretty solid as it is here.
Kind Regards
Stefan
Stef mentioned :
=> Here's my final solution that runs in less than a minute for +- 543 tables :
=> for x in $(psql -tc "select relname from pg_class where relkind = 'r' and
relname not like 'pg_%'")
=> do
=>echo "$(psql -tc "select encode(digest('$(psql -c '\d '${x}''
mer9188_test | tr -d \"\'\")', 'md5'), 'hex')" mer9188_test | grep -v "^$"|tr
-d " "):${x}"
=> done > compare_list.lst
get_table_checksum.sql
Description: Binary data
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
