Re: [SQL] SQL syntax rowcount value as an extra column in the result set
Hi, Is this what you are trying to do? postgres=# select * from (select count(*) from people ) p, (select firstname from people)p2; count | firstname ---+--- 5 | Mary 5 | Mary 5 | John 5 | John 5 | Jacob (5 rows) I do not know about the performance impact of such a query (cartesian join) Regards, Jayadevan From: "Snyder, James" To: Date: 26/03/2010 03:21 Subject:[SQL] SQL syntax rowcount value as an extra column in the result set Sent by:pgsql-sql-ow...@postgresql.org Hello I’m using PostgreSQL (8.4.701) and Java (jdbc, postgresql-8.4-701.jdbc4.jar) to connect to the database. My question is: what is the SQL syntax for PostgreSQL to achieve the following: I want to receive the rowcount along with the rest of a result set. For example, let’s say the following query returns select first_name from people; first_name = Mary Sue Joe and the following query returns the value select count(*)as ROWCOUNT from people; ROWCOUNT == 3 3 What I’m looking for is the output as ROWCOUNT , first_name = 3 , Mary 3 , Sue 3 , Joe so I can use JDBC (snip-it) as follows: resultSet.getInt(“ROWCOUNT”) resultSet.getString(“first_name”) On a side note, Oracle allows the following syntax to achieve the above: select count(*) over () as ROWCOUNT , first_name from people Thanks,Jim DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] SQL syntax rowcount value as an extra column in the result set
Hi, I don't think so. Oracle - SQL> select count(*) over () as ROWCOUNT , first_name from people; ROWCOUNT FIRST_NAME -- - --- 6 Mary 6 Mary 6 John 6 John 6 John 6 Jacob 6 rows selected. PostgreSQL postgres=# select count(*) over () as ROWCOUNT , first_name from people; ERROR: syntax error at or near "over" LINE 1: select count(*) over () as ROWCOUNT , first_name from people... ^ Regards, Jayadevan From: Thomas Kellerer To: pgsql-sql@postgresql.org Date: 26/03/2010 03:26 Subject:Re: [SQL] SQL syntax rowcount value as an extra column in the result set Sent by:pgsql-sql-ow...@postgresql.org 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 on Postgres Thomas -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] SQL syntax rowcount value as an extra column in the result set
Hi, >It works, but you should use a recent version: >test=*# select count(1) over (), i from foo; > count | i >---+ > 8 | 1 > 8 | 2 > 8 | 3 > 8 | 6 > 8 | 7 > 8 | 9 > 8 | 13 > 8 | 14 >(8 rows) > test=*# select version(); >version > > PostgreSQL 8.4.2 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real > (Debian 4.3.2-1.1) 4.3.2, 64-bit > (1 row) Thank you for setting that right. Apologies for not checking version. Is this approach better compared to postgres=# select * from (select count(*) from people ) p, (select firstname from people)p2; count | firstname ---+--- 5 | Mary 5 | Mary 5 | John 5 | John 5 | Jacob (5 rows) This gives me postgres=# explain select * from (select count(*) from people )as p, (select firstname from people)p2; QUERY PLAN - Nested Loop (cost=14.00..30.42 rows=320 width=226) -> Aggregate (cost=14.00..14.01 rows=1 width=0) -> Seq Scan on people (cost=0.00..13.20 rows=320 width=0) -> Seq Scan on people (cost=0.00..13.20 rows=320 width=218) Since I don't have 8.4, I am not in a position to do explain on that version. My guess - over () will be better. My query does sequential scans/nested loop...(if there are no indexes) Regards, Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] LEFT OUTER JOIN issue
Hi, > SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as ct > LEFT OUTER JOIN h_part as hp > ON ct.dat = hp.datmesure > AND ct.heur = hp.heuremesure > WHERE > hp.poste_idposte = 275 > ORDER BY ct.dat, ct.heur > dat heur datmesure heuremesure t > --- > 15/03/2008 0:00 15/03/2008 0:008,3 > 15/03/2008 3:00 15/03/2008 3:0012 > 15/03/2008 6:00 15/03/2008 6:0015 > 15/03/2008 9:00 15/03/2008 9:0018 > 15/03/2008 12:00 nullnull null > 15/03/2008 15:00 nullnull null Would this work? SELECT ct.dat,ct.heur,hp.datmesure,hp.heuremesure,hp.t FROM calendar_temp as ct LEFT OUTER JOIN h_part as hp ON ct.dat = hp.datmesure AND ct.heur = hp.heuremesure WHERE coalesce(hp.poste_idposte,275) = 275 ORDER BY ct.dat, ct.heur dat | heur | datmesure | heuremesure | t +--++-+-- 2008-03-15 | 00:00:00 | 2008-03-15 | 00:00:00| 8.3 2008-03-15 | 03:00:00 | 2008-03-15 | 03:00:00| 12.0 2008-03-15 | 06:00:00 | 2008-03-15 | 06:00:00| 15.0 2008-03-15 | 09:00:00 | 2008-03-15 | 09:00:00| 18.0 2008-03-15 | 12:00:00 || | 2008-03-15 | 15:00:00 || | (6 rows) Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] [GENERAL] How to Get Column Names from the Table
> Use: > \d tablename And what I really like about it is the way you can make a guess about the table name and use * . postgres-# \d mt* Table "public.mt1" Column | Type | Modifiers +-+--- id | integer | Table "public.mt2" Column | Type | Modifiers +-+--- id | integer | Table "public.mt3" Column | Type | Modifiers +-+--- id | integer | Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- 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] SUM the result of a subquery.
> SELECT SUM ( > (SELECT i.id_item, i.price, SUM (o.quantity), ROUND (SUM > (o.quantity) * i.price, 2) AS cost > FROM orders o > JOIN items i ON i.id_item = o.id_item > WHERE o.date_order BETWEEN '2010-01-01' AND '2010-01-31' > GROUP BY i.id_item, i.price) > ); > > No luck. Obviously SUM expects an expression, not a set of rows. Is > there a way to perform a sum of the resulting rows? > I don't have a PostgreSQL server to try this right now. But you are looking for something like SELECT SUM (cost) from ( (SELECT i.id_item, i.price, SUM (o.quantity), ROUND (SUM (o.quantity) * i.price, 2) AS cost FROM orders o JOIN items i ON i.id_item = o.id_item WHERE o.date_order BETWEEN '2010-01-01' AND '2010-01-31' GROUP BY i.id_item, i.price) ) as x Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- 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] find and replace the string within a column
Hello, > the below one help's me to find the data within the two brackets. > > SELECT name,(REGEXP_MATCHES(name, E'\\(.+?\\)'))[1] from person; > regexp_matches > > (S/o Sebastin ) > - > Trying to work with your code - update table set name = substr( name,1,strpos(name, (REGEXP_MATCHES(name, E'\\(.+?\\)')) )-1 ) || substr( name,strpos(name, (REGEXP_MATCHES(name, E'\\(.+?\\)')) ) + 1 ,char_length(name)) I am trying to find what is there before the pattern and after the pattern and concatenating them . Please see documentation for proper use of substr,strpos,cahr_length etc. Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- 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] Calculate next event date based on instance of the day of week
Hello, > I have a table which stores an event date. The event is stored as a > standard mm/dd/yy entry. > > I then need to calculate based on the dd value, WHICH day of the > week that is (e.g. Wednesday) and which occurrence of that day of > the week, within the month, it is (e.g. the THIRD Wednesday). > Here is an example to reach this far postgres=# create table mt(myd date); postgres=# insert into mt select current_date+se from (select generate_series(1,1) as se ) as x; postgres=# select * from mt order by postgres-# myd limit 10; myd 2010-12-04 2010-12-05 2010-12-06 2010-12-07 2010-12-08 2010-12-09 2010-12-10 2010-12-11 2010-12-12 2010-12-13 (10 rows) This is the query to get the data in the format you want... select myd, d ,w from ( select myd, to_char(myd,'Day') as d , to_char(myd,'W') as w ,rank() over (partition by to_char(myd,'W') order by myd ) as x from mt order by myd ) as t order by myd ; myd | d | w +---+--- 2010-12-04 | Saturday | 1 2010-12-05 | Sunday| 1 2010-12-06 | Monday| 1 2010-12-07 | Tuesday | 1 2010-12-08 | Wednesday | 2 2010-12-09 | Thursday | 2 2010-12-10 | Friday| 2 2010-12-11 | Saturday | 2 2010-12-12 | Sunday| 2 2010-12-13 | Monday| 2 2010-12-14 | Tuesday | 2 2010-12-15 | Wednesday | 3 2010-12-16 | Thursday | 3 2010-12-17 | Friday| 3 2010-12-18 | Saturday | 3 2010-12-19 | Sunday| 3 2010-12-20 | Monday| 3 2010-12-21 | Tuesday | 3 2010-12-22 | Wednesday | 4 2010-12-23 | Thursday | 4 2010-12-24 | Friday| 4 2010-12-25 | Saturday | 4 2010-12-26 | Sunday| 4 2010-12-27 | Monday| 4 2010-12-28 | Tuesday | 4 2010-12-29 | Wednesday | 5 2010-12-30 | Thursday | 5 2010-12-31 | Friday| 5 Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- 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 the max viewd product_id for user_id
Hello, > I went this way, but for a large number of user_id's, it's quite slow: > > CREATE VIEW v_views AS >SELECT user_id, product_id, count(*) as views >FROM viewlog >GROUP BY user_id, product_id > > SELECT >DISTINCT user_id, >(SELECT product_id FROM v_views inn WHERE inn.user_id = out.user_id > ORDER BY views DESC LIMIT 1) as product_id, >(SELECT views FROM v_views inn WHERE inn.user_id = out.user_id ORDER BY > views DESC LIMIT 1) as views > FROM >v_views out > Does this work faster? select x.user_id,y.product_id,x.count from (select user_id, max(count ) as count from (select user_id,product_id, count(*) as count from viewlog group by user_id,product_id) as x group by user_id ) as x inner join (select user_id,product_id, count(*) as count1 from viewlog group by user_id,product_id ) as y on x.user_id=y.user_id and x.count=y.count1 Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- 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 the max viewd product_id for user_id
> > The issue in both approaches is that if I have two product_ids that are > > viewed same number of times and share the first place as most viewed > > products by that user, I'll get only one of them (LIMIT 1 OR MAX() can > > only return one row :). > > > > And then, to jump again into my own mouth - your approach, Jayadevan, > correctly gives me both product_id's if they're viewed the same number > of times. > Good. It should, since we are joining on count and user_id. I was surprised to see your mail which said it wouldn't :). Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- 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] Postgresql function which compares values from both tables
Hi, > To: pgsql-sql@postgresql.org > Subject: [SQL] Postgresql function which compares values from both tables > > hi , > how to write a function which should read data from 2 tables having > same number of columns and should show the common values from those tables. If you want to compare and ensure that the values are same for all columns, please have a look at INTERSECT http://www.postgresql.org/docs/9.1/static/sql-select.html Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] How to limit access only to certain records?
HI, > > is there a way to limit access for some users only to certain records? > > e.g. there is a customer table and there are account-managers. > Could I limit account-manager #1 so that he only can access customers > only acording to a flag? > > Say I create a relation cu_am ( customer_id, account_manager_id ). > Could I let the database control that account-manager #1 can only see > customers who are assigned to him in the cu_am-relation? > > For now I do this in the front-end but this is easily circumvented for > anyone who has a clue and uses some other client like psql. Using a VIEW? Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."
Re: [SQL] DB link from postgres to Oracle; how to query Dbname.tablename?
In PostgreSQL, you always connect to a 'database', then query tables. So if
you are connecting to the 'wrong' database, you will get the error you
mentioned. You can troubleshoot this in many ways -
one way would be to enable logging on PostgreSQL side and check the log and
see which database you are connecting to. Another way will be to execute
the PostgreSQL function current_database()
http://www.postgresql.org/docs/9.3/static/functions-info.html
instead of the query you are using right now and verify if you are
connecting to the correct database.
On Wed, Oct 2, 2013 at 5:11 AM, Bhanu Murthy wrote:
> Hi all, greetings!
>
> Using Oracle Heterogeneous Services (Oracle HS) I have configured/created
> a DB link from Postgres 9.3 database into Oracle 11gR3 database (with
> postgres DB user credentials).
>
> SQL> create public database link pg_link connect to "postgres"
> identified by "blahblah" using 'postgresql';
> Since Postgres does not support public synonyms across databases in a
> cluster, how do I connect to a specific database and query a specific table
> in this Postgres cluster using the HS DB link?
>
> Let's say, if I have 2 Postgres databases named pgdb01 and pgdb02 in the
> Postgres cluster, using this DB link that I have created in Oracle, how can
> I query a specific table called table01 from pgdb01 database?
>
> Even though the table user_account exists in pgdb01 database, I cannot
> select from it using the DB link.
>
> SQL> select count(*) from
> mailto:%22user_account%22@pg_link<%22user_account%22@pg_link>
> ;
> *select count(*) from **"user_account"@pg_link*<%22user_account%22@pg_link>
> *; *
> ERROR at line 1:
> ORA-28500: connection from ORACLE to a non-Oracle system returned this
> message:
> ERROR: relation "user_account" does not exist at character 21;
> No query has been executed with that handle {HY000,NativeErr = 1}
> ORA-02063: preceding 3 lines from PG_LINK;*
>
> I tried dbname.tablename syntax, but it didn't work! BTW, all my tables
> belong to public schema.
>
> Does anyone with DB link expertise try to answer my question?
>
> Thanks,
> Bhanu M. Gandikota
> Mobile: (415) 420-7740
>
>*From:* Alejandro Brust
> *To:* pgsql-ad...@postgresql.org
> *Sent:* Tuesday, October 1, 2013 12:30 PM
> *Subject:* Re: [ADMIN] PostgreSQL 9.2 - pg_dump out of memory when
> backuping a database with 3 large objects
>
> Did U perform any vacuumdb / reindexdb before the Pg_dump?
>
>
> El 01/10/2013 09:49, Magnus Hagander escribió:
> > On Tue, Oct 1, 2013 at 11:07 AM, Sergey Klochkov
> wrote:
> >> Hello All,
> >>
> >> While trying to backup a database of relatively modest size (160 Gb) I
> ran
> >> into the following issue:
> >>
> >> When I run
> >> $ pg_dump -f /path/to/mydb.dmp -C -Z 9 mydb
> >>
> >> File /path/to/mydb.dmp does not appear (yes, I've checked permissions
> and so
> >> on). pg_dump just begins to consume memory until it eats up all
> avaliable
> >> RAM (96 Gb total on server, >64 Gb available) and is killed by the oom
> >> killer.
> >>
> >> According to pg_stat_activity, pg_dump runs the following query
> >>
> >> SELECT oid, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
> lomowner)
> >> AS rolname, lomacl FROM pg_largeobject_metadata
> >>
> >> until it is killed.
> >>
> >> strace shows that pg_dump is constantly reading a large amount of data
> from
> >> a UNIX socket. I suspect that it is the result of the above query.
> >>
> >> There are >3 large objects in the database. Please don't ask me
> why.
> >>
> >> I tried googling on this, and found mentions of pg_dump being killed by
> oom
> >> killer, but I failed to find anything related to the huge large objects
> >> number.
> >>
> >> Is there any method of working around this issue?
> > I think this problem comes from the fact that pg_dump treats each
> > large object as it's own item. See getBlobs() which allocates a
> > BlobInfo struct for each LO (and a DumpableObject if there are any,
> > but that's just one).
> >
> > I assume the query (from that file):
> > SELECT oid, lomacl FROM pg_largeobject_metadata
> >
> > returns 3 rows, which are then looped over?
> >
> > I ran into a similar issue a few years ago with a client using a
> > 32-bit version of pg_dump, and got it worked around by moving to
> > 64-bit. Did unfortunately not have time to look at the underlying
> > issue.
> >
> >
>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
>
>
