Write a Postgres Get or Create SQL Query

Write a Postgres Get or Create SQL Query

In a SQL DBMS, the select-test-insert approach is a mistake: nothing prevents another process from inserting the "missing" row between your select and insert statements. Do this instead:

INSERT INTO mytable (color, brightness, size, age)
SELECT color, brightness, size, age
FROM mytable
WHERE NOT EXISTS (
SELECT 1
FROM mytable
WHERE color = 'X' AND brightness = 'Y'
);
SELECT (color, brightness, size, age)
FROM mytable
WHERE color = 'X' AND brightness= 'Y';

You should be able to pass that entire text as a single "query" to the DBMS. You might want to consider making it into a stored procedure.

How to generate the create table sql statement for an existing table in postgreSQL

pg_dump -t 'schema-name.table-name' --schema-only database-name

More info - in the manual.

How to get/create SQL statement from access table to put in Postgres DB

I have found a quick and easy way to do it.

The simple step by step

  1. Create the DSN in ODBC Data Source Administrator (ensure that its a
    system DSN not a user)
  2. Open Access database.
  3. Right Click on table you want to export.
  4. In the list of options that come up select Export>ODBC Database
  5. Select Machine Data Source and Select the DSN you created.

If you have x32 Ms-Access and x64 Operating system, access will show only DSN that were entered in odbcad32 (x32 Data Sources) which is not where the control panel takes you to by default. To add the x32 DSN you must go into C:\windows\SysWOW64\odbcad32.exe and recreate it there.

Again make sure its a system DSN or you will get an error from access when trying to export the file.

To answer the Title of the question more specifically you can now open PgAdmin go in to the database you exported the tables to, and postgres will show you the SQL statement to create the table.

Can I use Postgres transactions only for write queries and use read queries without transaction?

So if you're going to query the database for the same rows that you just inserted using a transaction, but haven't committed the transaction yet, then you should read from the database using the transaction.
Eg. You create a user, then you need to create an external account for this user, and the method that creates that external account reads the user from the database and does not get it as a parameter. You can either modify the create external account method so it gets the user as a parameter and then pass to it the just created user, either you can keep the method like it is, but you have to make sure you pass the transaction to it. Otherwise, if the transaction is not committed and is not passed to the read query, the created user won't be found.
Ideally you should avoid this thing by passing the input data to the create external account too, so you don't need to read the user from db, but if for some reason this is not possible, then make sure you read from the db using the transaction.

dynamic sql query in postgres

EXECUTE ... USING only works in PL/PgSQL - ie within functions or DO blocks written in the PL/PgSQL language. It does not work in plain SQL; the EXECUTE in plain SQL is completely different, for executing prepared statements. You cannot use dynamic SQL directly in PostgreSQL's SQL dialect.

Compare:

  • PL/PgSQL's EXECUTE ... USING; to
  • SQL's EXECUTE

See the 2nd last par in my prior answer.


In addition to not running except in PL/PgSQL your SQL statement is wrong, it won't do what you expect. If (select id from ids where condition = some_condition) returns say 42, the statement would fail if id is an integer. If it's cast to text you'd get:

EXECUTE format('SELECT * from result_%s_table', quote_ident('42'));
EXECUTE format('SELECT * from result_%s_table', '"42"');
EXECUTE 'SELECT * from result_"42"_table';

That's invalid. You actually want result_42_table or "result_42_table". You'd have to write something more like:

EXECUTE format('SELECT * from %s', quote_ident('result_'||(select id from ids where condition = some_condition)||'_table'))

... if you must use quote_ident.

SELECT or INSERT a row in one command

Have you tried to union it?


Edit - this requires Postgres 9.1:

create table mytable (id serial primary key, other_key varchar not null unique);

WITH new_row AS (
INSERT INTO mytable (other_key)
SELECT 'SOMETHING'
WHERE NOT EXISTS (SELECT * FROM mytable WHERE other_key = 'SOMETHING')
RETURNING *
)
SELECT * FROM new_row
UNION
SELECT * FROM mytable WHERE other_key = 'SOMETHING';

results in:

 id | other_key 
----+-----------
1 | SOMETHING
(1 row)


Related Topics



Leave a reply



Submit