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
- Create the DSN in ODBC Data Source Administrator (ensure that its a
system DSN not a user) - Open Access database.
- Right Click on table you want to export.
- In the list of options that come up select Export>ODBC Database
- 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
Efficiently Duplicate Some Rows in Postgresql Table
Aspentech Infoplus 21 - How to Connect and Query Data
Rails Activerecord - How to Fetch Records Between Two Dates
How to Multiply All Values Within a Column with SQL Like Sum()
Sqlite Unique Key with a Combination of Two Columns
Aggregate Function Over a Given Time Interval
How Replace Accented Letter in a Varchar2 Column in Oracle
While Loop to Iterate Through Databases
How to Retrieve a Column Value by Name Using Golang Database/Sql
Pyspark/Spark Window Function First/ Last Issue
Thoughts on Index Creation for SQL Server for Missing Indexes
Sql Server 2012 Sp_Helptext Extra Lines Issue
Ant SQL Task: How to Run SQL and Pl/Sql and Notice Execution Failure
How to Insert Data to SQL Server Table Using R