Lateral subqueries in Postgres

Lateral subqueries in Postgres

Postgres has feature to use value from preceding table using keyword lateral. This is used for cross-referencing. means Subqueries appearing in FROM can be preceded by the key word LATERAL. This allows them to reference columns provided by preceding FROM items. (Without LATERAL, each subquery is evaluated independently and so cannot cross-reference any other FROM item.)

Lateral evaluation proceeds as follows: for each row of the FROM item providing the cross-referenced column(s), or set of rows of multiple FROM items providing the columns, the LATERAL item is evaluated using that row or row set’s values of the columns. The resulting row(s) are joined as usual with the rows they were computed from. This is repeated for each row or set of rows from the column source table(s).

For more information refer to this link

A trivial example of lateral
Example 1:

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

Explanation:
In the above SQL query , the table foo generates a row with id (foo.bar_id) now this id is passed to sub query where it generates the result.

Example 2:

It is often particularly handy to LEFT JOIN to a LATERAL subquery, so that source rows will appear in the result even if the LATERAL subquery produces no rows for them. For example, if get_product_names() returns the names of products made by a manufacturer, but some manufacturers in our table currently produce no products, we could find out which ones those are like this:

 SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL; 

How to solve pg_tblspc problem in postgres

This blog will talk about How to solve pg_tblspc problem in postgres

pg_tblspc problem postgres

Sometime when we are doing some operation on Postgres database, we faced this error.

I faced  it,  when I  was  trying  to  get  values  from  database.

This issue throws error that could not open directory “pg_tblspc/<1234…x>”: No such file or directory

To  solve this  issue  you  first  have  to  create  dump  of  your  database. Steps are ahead …

 

  • psql -h localhost -p 6666 -U postgres -d <yourDBname>-f “E:/xyz/abc.p”

(It will create dump file of your database)

  • Then delete your old database after dumping it.

(dump database <yourDBname>)

  • Then again restore it from your dump file…

 

Or, Its better to use your backup file while restoring..

If  problem  is  still  there then we need  to first restore that <abc.p/abc.sql> file  on  our  local  machine

  • then  again  create  (.p)  file  as  dump  file
  • And restore your postgres by using this new dump file…

You will not come across this problem again……..

 

Another way:-

1) Backup data with pg_dump

 

pg_dump -i -h localhost -p 5432 -U postgres -F c -b -v -f

“/usr/local/backup/10.70.0.61.backup” old_db

 

To list all of the available options of pg_dump, please issue following command.

 

  • Ø pg_dump -?

 

-p, –port=PORT database server port number

-i, –ignore-version proceed even when server version mismatches

-h, –host=HOSTNAME database server host or socket directory

-U, –username=NAME connect as specified database user

-W, –password force password prompt (should happen automatically)

-d, –dbname=NAME connect to database name

-v, –verbose verbose mode

-F, –format=c|t|p output file format (custom, tar, plain text)

-c, –clean clean (drop) schema prior to create

-b, –blobs include large objects in dump

-v, –verbose verbose mode

-f, –file=FILENAME output file name

 

2) Restore data with pg_restore

 

pg_restore -i -h localhost -p 5432 -U postgres -d old_db -v

“/usr/local/backup/10.70.0.61.backup”

 

To list all of the available options of pg_restore, please issue following command.

 

  • Ø pg_restore -?

 

-p, –port=PORT database server port number

-i, –ignore-version proceed even when server version mismatches

-h, –host=HOSTNAME database server host or socket directory

-U, –username=NAME connect as specified database user

-W, –password force password prompt (should happen automatically)

-d, –dbname=NAME connect to database name

-v, –verbose verbose mode

 

Pushpraj Kumar,

Helical IT Solutions