I don’t know quite how to phrase this so please help me with the title as well. 🙂 I have two tables. Let’s call them A and B. The B table has a a_id foreign key that points at A.id. Now I would like to write a SELECT statement that fetches all A records, with an additional column containing the
Tag: postgresql
How to extract year and month from date in PostgreSQL without using to_char() function?
I want to select sql: SELECT “year-month” from table group by “year-month” AND order by date, where year-month – format for date “1978-01″,”1923-12”. select to_char of couse work, but not “right” order: Answer e.g. http://www.postgresql.org/doc…
SQL: Advantages of an ENUM vs. a one-to-many relationship?
I very rarely see ENUM datatypes used in the wild; a developer almost always just uses a secondary table that looks like this: But the same thing can also be shown using a user-defined type / ENUM: (Example shown using PostgreSQL, but other RDBMS’s have similar syntax) The biggest disadvantage I see to …
How to drop multiple tables in PostgreSQL using a wildcard
When working with partitions, there is often a need to delete all partitions at once. However Does not work. (The wildcard is not respected). Is there an elegant (read: easy to remember) way to drop multiple tables in one command with a wildcard? Answer Use a comma separated list: If you realy need a footgun,…
Use email address as primary key?
Is email address a bad candidate for primary when compared to auto incrementing numbers? Our web application needs the email address to be unique in the system. So, I thought of using email address …
Variable value assignment using RETURNING clause
I try to do this, but it’s a syntax error, what am I doing wrong? my table: Answer You need to use the INTO clause in the RETURNING to set the value being returned into your variable: You also need to specify the data type of your variable; I’m glad to see postgresql supports %TYPE and %ROWTYPE.
Query to group by maximum depth of PostgreSQL Ltree?
I want to query for all products with the name “Shania Twain”, but I want group them only by the tree with the deepest nlevel. Assuming I have a table like the following with the name categories So, for example, The problem lies with the HAVING clause requiring a boolean expression. The clause MAX…
How to show row numbers in PostgreSQL query?
I’d like to show the observation number for each record returned by a PostgreSQL query. I think in 8.4 windowing functions can perform this capability.
Slow simple update query on PostgreSQL database with 3 million rows
I am trying a simple UPDATE table SET column1 = 0 on a table with about 3 million rows on Postegres 8.4 but it is taking forever to finish. It has been running for more than 10 min. Before, I tried to run a VACUUM and ANALYZE commands on that table and I also tried to create some indexes (although
PostgreSQL Index Usage Analysis
Is there a tool or method to analyze Postgres, and determine what missing indexes should be created, and which unused indexes should be removed? I have a little experience doing this with the “profiler” tool for SQLServer, but I’m not aware of a similar tool included with Postgres. Answer I …